Help with SQL Query
I have 3 tables (TableA, TableB, TableC). They all have a common ID column, with TableA having a 1:M relationship with TableB and TableC.
TableA: ID, DATE, TYPE
TableB: ID, TYPE
TableC: ID, TYPE
I want to write a query that outputs a table with the following columns on 168 rows (unique day of week and hour of day)
DOW: Day of Week
HOD: Hour of Day
COUNT: Count of Records in TableA for each hour of day/day of week combination
COUNTTYPEYESA: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableA
COUNTTYPEYESB: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableB
COUNTTYPEYESBC: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableB and TableC
I have written the following query, but it seems as if the counts are for TableC. I just want to get the count for distinct IDs (i.e. records in TableA)
TableA: ID, DATE, TYPE
TableB: ID, TYPE
TableC: ID, TYPE
I want to write a query that outputs a table with the following columns on 168 rows (unique day of week and hour of day)
DOW: Day of Week
HOD: Hour of Day
COUNT: Count of Records in TableA for each hour of day/day of week combination
COUNTTYPEYESA: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableA
COUNTTYPEYESB: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableB
COUNTTYPEYESBC: Count of Records in TableA for each hour of day/day of week combination where TYPE='YES' in TableB and TableC
I have written the following query, but it seems as if the counts are for TableC. I just want to get the count for distinct IDs (i.e. records in TableA)
SELECT
DATEPART(WEEKDAY,DATE) AS DOW,
DATEPART(HOUR,DATE) AS HOD,
COUNT(TableA.ID) AS ALLCOUNT,
COUNT(CASE TableA.TYPE='YES' THEN 1 ELSE NULL END) AS COUNTTYPEYESA,
COUNT(CASE TableB.TYPE='YES' THEN 1 ELSE NULL END) AS COUNTTYPEYESB,
SUM(CASE WHEN TableB.TYPE='YES' AND TableC.TYPE='YES') AS COUNTTYPEYESBC,
FROM TableA, TableB, TableC
WHERE TableA.ID = TableB.ID AND TableB.ID = TableC.ID
GROUP BY DATEPART(HOUR,DATE),DATEPART(WEEKDAY,DATE)
ORDER BY DOW, HODawking00
Can you provide some sample data and the expected results so we have something to test?
SELECT DATEPART(WEEKDAY, A.DATE) AS DOW,
DATEPART(HOUR, A.DATE) AS HOD,
COUNT(DISTINCT A.ID) AS ALLCOUNT,
SUM(CASE WHEN AYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESA,
SUM(CASE WHEN BYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESB,
SUM(CASE WHEN CYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESBC,
FROM TableA AS A
LEFT OUTER JOIN TableA AS AYes
ON A.ID = AYes.ID
AND AYes.TYPE = 'Yes'
LEFT OUTER JOIN TableB AS BYes
INNER JOIN TableC AS CYes
ON BYes.ID = CYes.ID
AND CYes.TYPE = 'Yes'
ON A.ID = BYes.ID
AND BYes.TYPE = 'Yes'
GROUP BY DATEPART(HOUR, A.DATE), DATEPART(WEEKDAY, A.DATE)
ORDER BY DATEPART(HOUR, A.DATE), DATEPART(WEEKDAY, A.DATE)
DATEPART(HOUR, A.DATE) AS HOD,
COUNT(DISTINCT A.ID) AS ALLCOUNT,
SUM(CASE WHEN AYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESA,
SUM(CASE WHEN BYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESB,
SUM(CASE WHEN CYes.ID IS NOT NULL THEN 1 ELSE 0 END) AS COUNTTYPEYESBC,
FROM TableA AS A
LEFT OUTER JOIN TableA AS AYes
ON A.ID = AYes.ID
AND AYes.TYPE = 'Yes'
LEFT OUTER JOIN TableB AS BYes
INNER JOIN TableC AS CYes
ON BYes.ID = CYes.ID
AND CYes.TYPE = 'Yes'
ON A.ID = BYes.ID
AND BYes.TYPE = 'Yes'
GROUP BY DATEPART(HOUR, A.DATE), DATEPART(WEEKDAY, A.DATE)
ORDER BY DATEPART(HOUR, A.DATE), DATEPART(WEEKDAY, A.DATE)
I don't think that will produce 168 rows unless the date field in tableA just happens to cover every hour for every day of the week.
I can't tell for sure if you need the DISTINCT in the COUNTs below or not; naturally uncomment them if you do need them :-) .
--Input parameters----------------
-- specify first day to report on; that day and the next six will be totaled.
DECLARE @start_date datetime
SET @start_date = GETDATE() - 7
--Main code----------------------
-- strip time from @start_time
SET @start_date = DATEADD(DAY, DATEDIFF(DAY, 0, @start_date), 0)
;WITH
-- gen 0-167, as overall hour numbers
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
INNER JOIN cteDigits [100s] ON [100s].digit BETWEEN 0 AND 1
WHERE
[100s].digit * 100 + [10s].digit * 10 + [1s].digit <= 167
)
SELECT
DayOfWeek AS [Day of Week], t.tally % 24 AS [Hour of Day],
COUNT(a.date) AS COUNT,
COUNT(/*DISTINCT*/ CASE WHEN a.type = 'YES' THEN a.id END) AS COUNTTYPEYESA,
COUNT(/*DISTINCT*/ CASE WHEN b.type = 'YES' THEN a.id END) AS COUNTTYPEYESB,
COUNT(/*DISTINCT*/ CASE WHEN c.type = 'YES' THEN a.id END) AS COUNTTYPEYESBC
FROM cteTally t
CROSS APPLY (
SELECT DATEADD(HOUR, t.tally, @start_date) AS Day_Hour
) AS ca1
CROSS APPLY (
--produces a consistent DayOfWeek value (1=Sun,2=Mon,etc.) regardless of DATEFIRST and/or language setting.
SELECT CASE WHEN DATEDIFF(DAY, 5, Day_Hour) % 7 = 0 THEN 7 ELSE DATEDIFF(DAY, 5, ca1.Day_Hour) % 7 END AS DayOfWeek
) AS ca2
LEFT OUTER JOIN dbo.TableA a ON
a.date >= ca1.Day_Hour AND a.date < DATEADD(HOUR, 1, ca1.Day_Hour)
LEFT OUTER JOIN dbo.TableB b ON
b.id = a.id AND
b.type = 'YES'
LEFT OUTER JOIN dbo.TableC c ON
c.id = b.id AND
c.type = 'YES'
GROUP BY
DayOfWeek, t.tally % 24
ORDER BY
[Day of Week], [Hour of Day]
--Input parameters----------------
-- specify first day to report on; that day and the next six will be totaled.
DECLARE @start_date datetime
SET @start_date = GETDATE() - 7
--Main code----------------------
-- strip time from @start_time
SET @start_date = DATEADD(DAY, DATEDIFF(DAY, 0, @start_date), 0)
;WITH
-- gen 0-167, as overall hour numbers
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
INNER JOIN cteDigits [100s] ON [100s].digit BETWEEN 0 AND 1
WHERE
[100s].digit * 100 + [10s].digit * 10 + [1s].digit <= 167
)
SELECT
DayOfWeek AS [Day of Week], t.tally % 24 AS [Hour of Day],
COUNT(a.date) AS COUNT,
COUNT(/*DISTINCT*/ CASE WHEN a.type = 'YES' THEN a.id END) AS COUNTTYPEYESA,
COUNT(/*DISTINCT*/ CASE WHEN b.type = 'YES' THEN a.id END) AS COUNTTYPEYESB,
COUNT(/*DISTINCT*/ CASE WHEN c.type = 'YES' THEN a.id END) AS COUNTTYPEYESBC
FROM cteTally t
CROSS APPLY (
SELECT DATEADD(HOUR, t.tally, @start_date) AS Day_Hour
) AS ca1
CROSS APPLY (
--produces a consistent DayOfWeek value (1=Sun,2=Mon,etc.) regardless of DATEFIRST and/or language setting.
SELECT CASE WHEN DATEDIFF(DAY, 5, Day_Hour) % 7 = 0 THEN 7 ELSE DATEDIFF(DAY, 5, ca1.Day_Hour) % 7 END AS DayOfWeek
) AS ca2
LEFT OUTER JOIN dbo.TableA a ON
a.date >= ca1.Day_Hour AND a.date < DATEADD(HOUR, 1, ca1.Day_Hour)
LEFT OUTER JOIN dbo.TableB b ON
b.id = a.id AND
b.type = 'YES'
LEFT OUTER JOIN dbo.TableC c ON
c.id = b.id AND
c.type = 'YES'
GROUP BY
DayOfWeek, t.tally % 24
ORDER BY
[Day of Week], [Hour of Day]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
by the way:
if TableB or TableC don't have records for every TableA.id then use LEFT JOINS instead of inner joins.
if TableB or TableC don't have records for every TableA.id then use LEFT JOINS instead of inner joins.