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)



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, HOD

Open in new window

VAMS1Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
This should solve the 'distinctiveness'
SELECT
      DATEPART(WEEKDAY, DATE)                                                                  AS DOW
    , DATEPART(HOUR, DATE)                                                                     AS HOD
    , COUNT(DISTINCT TableA.ID)                                                                AS ALLCOUNT
    , COUNT(DISTINCT CASE WHEN TableA.TYPE = 'YES' THEN TableA.ID END)                         AS COUNTTYPEYESA
    , COUNT(DISTINCT CASE WHEN TableB.TYPE = 'YES' THEN TableA.ID END)                         AS COUNTTYPEYESB
    , COUNT(DISTINCT CASE WHEN TableB.TYPE = 'YES' AND TableC.TYPE = 'YES' THEN TableA.ID END) AS COUNTTYPEYESBC

FROM TableA
      INNER JOIN TableB
            ON TableA.ID = TableB.ID
      INNER JOIN TableC
            ON TableB.ID = TableC.ID

GROUP BY
      DATEPART(HOUR, DATE)
    , DATEPART(WEEKDAY, DATE)
ORDER BY
      DOW, HOD
;

Open in new window

but it won't guarantee 168 rows as mentioned by awking00
0
 
awking00Commented:
Can you provide some sample data and the expected results so we have something to test?
0
 
Brian CroweDatabase AdministratorCommented:
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)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
awking00Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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]
0
 
PortletPaulfreelancerCommented:
by the way:
if TableB or TableC don't have records for every TableA.id then use LEFT JOINS instead of inner joins.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.