SQL Insert into duplicates

My code is as follows:

SELECT DISTINCT
    lc.locationName,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_ONE%' 
       AND s.Date >= GETDATE()) AS TYPE_ONE_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_TWO%' 
       AND s.Date >= GETDATE()) AS TYPE_TWO_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_THREE%' 
       AND s.Date >= GETDATE()) AS TYPE_THREE_COUNT
FROM 
    Schedule AS sc WITH (NOLOCK) --/*This table has timeslotscount, serviceid*/
LEFT JOIN 
    ServiceList AS sv ON sv.ServiceId = sc.ServiceId --/*This table has locationid, serviceid(type1/type2/type3 depending on location[all locations has type1/2/3])*/
LEFT JOIN 
    Location AS lc ON sv.LocationId = lc.LocationId --/*This table has  locationid, locationname*/

I should explain the SUM(), there are differing dates on schedule such as

serviceId | dd/mm/yyyy hh:mm:ss | count for this day

I want to get all the “count” of “serviceId” beyond today hence the ‘date >= GETDATE()’

Basically I want the table to look like so:

LOCATION | TYPE_ONE_COUNT | TYPE_TWO_COUNT | TYPE_THREE_COUNT

I am able to get the values but I get them like so:

LocationOne | 12 |  0 |  0   
LocationOne | 0  | 12 |  0  
LocationOne | 0  |  0 | 34  
LocationTwo | 1  |  0 |  0  
LocationTwo | 0  | 42 |  0  
LocationTwo | 0  | 0  |  9

Whereas I want to display as

LocationOne | 12 | 12 | 34  
LocationTwo | 1  | 42 |  9

Any and all help is appreciated. Where I’m wrong, syntax/performance improvement, references, anything.

Answer

You can do it with conditional aggregation:

SELECT lc.locationName,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_ONE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_ONE_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_TWO%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_TWO_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_THREE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_THREE_COUNT 
FROM Location AS lc 
LEFT JOIN ServiceList AS sv ON sv.LocationId = lc.LocationId
LEFT JOIN Schedule AS sc ON sv.ServiceId = sc.ServiceId AND sc.Date >= GETDATE() 
GROUP BY lc.LocationId, lc.locationName;

Advertisement

Answer