CODEWITHSUNDEEP
sql-server
Beginner
Beginner

Reputation: 79

Get Hourly Call data in 24 Time slot for Each users

 WITH CTE1
     AS (SELECT 'ABC' AS [Name], 
                4 AS [Call Count], 
                0 AS [Time_Slot]
         UNION
         SELECT 'XYX' AS [Name], 
                7 AS [Call Count], 
                1 AS [Time_Slot]
         UNION
         SELECT 'TRT' AS [Name], 
                6 AS [Call Count], 
                6 AS [Time_Slot]
         UNION
         SELECT 'DCFG' AS [Name], 
                8 AS [Call Count], 
                7 AS [Time_Slot]
         UNION
         SELECT 'DCS' AS [Name], 
                45 AS [Call Count], 
                18 AS [Time_Slot]
         UNION
         SELECT 'XYX' AS [Name], 
                45 AS [Call Count], 
                9 AS [Time_Slot]
        )
     SELECT *
     FROM CTE1;

consider the output of the above code is

Name    Call Count  Time_Slot
ABC          4         0
DCFG         50        7
DCS          45        18
TRT          6         6
XYX          7         1
XYX          45        9

I wanted to output per user 24 hours data like below,

This data is for user DCFG likewise I wanted for each user (ABC, DCS, TRT, XYX)


Name    Call Count  Time_Slot
DCFG       0        0
DCFG       0        1
DCFG       0        2
DCFG       0        3
DCFG       0        4
DCFG       0        5
DCFG       0        6
DCFG       50       7
DCFG       0        8
DCFG       0        9
DCFG       0        10
DCFG       0        11
DCFG       0        12
DCFG       0        13
DCFG       0        14
DCFG       0        15
DCFG       0        16
DCFG       0        17
DCFG       0        18
DCFG       0        19
DCFG       0        20
DCFG       0        21
DCFG       0        22
DCFG       0        23

Now, what I have tried

it makes sense by using joins I'll not able to achieve what I wanted, by using cross join I get all Time slots but its repeated 24-time slots entry for each user rows, for example user 'XYX' CTE1 has Two Entries As Below

XYX          7         1
XYX          45        9

Cross Join create 24-time slot entry for above each row.

Can anyone suggest to me how I can achieve this, Thank you In advance

Upvotes: 0

Views: 49

Answers (1)

Thom A
Thom A

Reputation: 95827

Use a (inline) tally, CROSS JOIN it to your user table (I assume you have one), and then LEFT JOIN that to your dataset above:

WITH Tally AS(
    SELECT I
    FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23))V(I)),
UserTimes AS(
    SELECT U.[Name],
           T.I AS TimeSlot
    FROM dbo.Users U
         CROSS JOIN T)
SELECT YT.[Name],
       C.CallCount,
       YT.TimeSlot
FROM UserTimes UT
     LEFT JOIN CTE1 C ON UT.[Name] = C.[Name]
                     AND UT.TimeSlot = C.TimeSlot;

Upvotes: 1

Related Questions