CODEWITHSUNDEEP
sqlsql-server-2008join
Dino
Dino

Reputation: 121

SQL Query needs to return rows even when results are null

I've been replacing a set of CLR stored procedures with straight SQL but I've been stuck on this one report.

The system makes entries into a stats table when an event occurs. If a particular event doesn't occur, there is no entry in the HourlyStats table. 

Table HourlyStats
    UserID    TimeStamp     EventID  Duration
      1       (datetime)       5        36
      2       (datetime)       1       259
      1       (datetime)       2        72
      3       (datetime)       5        36

Let's say there are 5 different eventID's in a table Categories

 Table Categories
       EventID   Description
           1        Break
           2        Supervision
           3        Lunch
           4        Outbound
           5        Inbound

There is also a table of Users

Users
UserID  Name
  1     Tom
  2     Mary
  3     George
  4     Carly

and the output has to look like:

UserID    Description     Sum(TimeSec)
 Tom          Break           Null
 Tom         Supervision       72
 Tom         Lunch            Null
 Tom         Outbound         Null
 Tom         Inbound           36
 Mary        Break            259

I've tried a variety of joins but don't get the results I'm looking for.

It may be that I can't do this directly via a single query. My next approach is to construct a temp table structured like the Output table but with NULL values for the SUM column and then update the table with results.

I've tried many variations. Here is where I started

SELECT HourlyStats.UserID, Categories.Description, SUM(HourlyStats.Duration) AS Expr1
FROM Categories FULL OUTER JOIN
HourlyStats ON Categories.EventID = HourlyStats.EventID
Group by UserID, Description
Order by UserID

Any suggestions?

Upvotes: 0

Views: 64

Answers (3)

Becuzz
Becuzz

Reputation: 6866

You need to cross join the users and categories then left join in the hourly stats. This should do it.

select u.Name, c.Description, sum(hs.Duration)
from Users u
cross join Categories c
left join HourlyStats hs on hs.UserId = u.UserId and hs.EventId = c.EventId
group by u.Name, c.Description
order by u.Name, c.Description

SQL Fiddle

Upvotes: 1

user275683
user275683

Reputation:

What you need is CROSS JOIN to get every possible combination of USERS and EVENTS and than LEFT join to HourlyStats code will be like this

;WITH   base
          AS (
               SELECT u.UserID
                   ,u.name
                   ,c.EventId
                   ,c.Description
                FROM Categories AS c
                CROSS JOIN Users AS u
             )
    SELECT b.name
           ,b.Description
           ,SUM(hs.Duration) OVER ( PARTITION BY hs.UserId, hs.EventID ) AS SumTime
        FROM Base AS b
        LEFT JOIN hourlyStats AS hs
            ON b.UserID = hs.UserId
               AND b.EventId = hs.EventID
        ORDER BY 1 DESC
       ,2 ASC

Upvotes: 1

freakinthesun
freakinthesun

Reputation: 699

Here you go:

SELECT u.Name, c.[Description], hs.Duration
FROM Users u
CROSS JOIN Categories c
LEFT OUTER JOIN HourlyStats hs
ON u.UserID = hs.UserID
AND c.EventID = hs.EventID

Upvotes: 1

Related Questions