CODEWITHSUNDEEP
sqlsql-serverwhere-clause
johnwonderbread
johnwonderbread

Reputation: 97

SQL Where statement to check against other records in same month

I'm attempting to pull records from a database but am having problems constructing the WHERE clause of the query.

I have a database full of accounts tied to a service location. Over time, accounts change but the service location never does. What I want to accomplish is a query that returns all records where a service location only had ONE account attached to it.

Here is a sample of the code, which isn't really all that close to what I need. 

SELECT
c.account_num,
MONTH(fb.[End]) AS 'Month', 
YEAR(fb.[End]) AS 'Year', 
AVG(fb.eDays) AS 'Billing Days'

FROM 
DimServiceLocation AS sl
INNER JOIN FactBill AS fb ON fb.ServiceLocationKey = sl.ServiceLocationKey
INNER JOIN DimCustomer AS c ON c.CustomerKey = fb.CustomerKey

WHERE 
AND fb.eDays IN (28, 29, 30, 31, 32, 33, 34, 35)
AND c.class_name = 'Customer' 
AND (fb.[End] > DATEADD(m, - 24, GETDATE()))

The WHERE clause defines the number of "eDays" (billing days) and the class_name (Customer). There are issues with doing it this way (EX: if a month has 33 billing days, it would return customers that were attached to a location for 29 days, which would not have been account holders for the entire month).

I have no idea how to accomplish this goal. I feel as though the solution may be checking service locations by specific bill periods (defined by a field called period_num) and then querying only those service locations that only had ONE account_num during that period_num.

Any help would be appreciated.

---------- Updated ----------

I mistakenly left out a significant portion of the original question. Apologies to those who helped. The second part of the problem is as follows:

In the original select statement, I also need to return the average Usage billed during an individual period across all similar service locations. These lines of code would be added: 

SELECT
c.account_num, 
sl.[Floorplan Name],
CONVERT(DECIMAL(5,0), AVG(EUsage)) AS 'Average Electric Usage',
COUNT(DISTINCT sl.location_id) AS 'Service Locations', 
MONTH(fb.[End]) AS 'Month', 
YEAR(fb.[End]) AS 'Year', 
AVG(fb.eDays) AS 'Billing Days'

The final result would ideally return this information:

Again, the ultimate goal is to only consider service locations where a single account was attached during an individual billing period.

Thanks again!

---------- Sample Results ----------

Sample Results

Upvotes: 2

Views: 108

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

I think this is the query you need, although some sample data and expected output would definitely make things more certain:

SELECT
    c.account_num
    , MONTH(fb.[End]) AS 'Month', 
    , YEAR(fb.[End]) AS 'Year'
    , AVG(fb.eDays) AS 'Billing Days'
FROM DimServiceLocation AS sl
    INNER JOIN FactBill AS fb ON fb.ServiceLocationKey = sl.ServiceLocationKey
    INNER JOIN DimCustomer AS c ON c.CustomerKey = fb.CustomerKey
WHERE 
    AND fb.eDays IN (28, 29, 30, 31, 32, 33, 34, 35)
    AND c.class_name = 'Customer' 
    AND (fb.[End] > DATEADD(m, - 24, GETDATE()))
GROUP BY c.account_num
    , MONTH(fb.[End]
    , YEAR(fb.[End])
HAVING COUNT(DISTINCT c.account_num) = 1

Upvotes: 1

Related Questions