Sql-server – Aggregating rows from a condition in a linking table in SQL

aggregatesql serverwindow functions

I am having trouble wrapping my head around a query that gives me these results

I have a table that gives me Devices uses per day.

dbo.DailyUses

Uses |   Date   |  Device
3    | 2020-3-1 |    A2
5    | 2020-3-1 |    B3
1    | 2020-3-2 |    A2
12   | 2020-3-3 |    C4

I have another table gives me the possible ErrorCodes of each devices.

dbo.Code_SKU_Combos

ErrorCode |  Device
   12.24  |    A2
   12.25  |    A2
   12.26  |    A2
   12.24  |    B3
   12.26  |    B3
   12.25  |    C4

With those two tables, I want to get how much uses per day did each "errorcode cable devices" where used

Example Output

Uses |   Date   |  ErrorCode
8    | 2020-3-1 |    12.24
3    | 2020-3-1 |    12.25
8    | 2020-3-1 |    12.26
5    | 2020-3-2 |    12.24
5    | 2020-3-2 |    12.25
5    | 2020-3-2 |    12.26
0    | 2020-3-3 |    12.24
12   | 2020-3-3 |    12.25
0    | 2020-3-3 |    12.26

I was thinking of using window functions or join, but the fact that the two tables will be using repeating values just confuses me on creating Partitions and Group By. I feel like I am over thinking it.

I created a cross table of my date range and Error Codes, now I just need to create a SUM() function that looks at dbo.DailyUses based on a filter created from dbo.Code_Sku_Combos

Also I am using Microsoft SQL

Best Answer

A possible solution can be :

The first select gets all combination of date and error combination that exist in the tables.

If you actually want all possible Dates, you need to add another created table, with all possiible dates

CREATE TABLE Code_SKU_Combos
    ([ErrorCode] varchar(5), [Device] varchar(2))
;
    
INSERT INTO Code_SKU_Combos
    ([ErrorCode], [Device])
VALUES
    ('12.24', 'A2'),
    ('12.25', 'A2'),
    ('12.26', 'A2'),
    ('12.24', 'B3'),
    ('12.26', 'B3'),
    ('12.25', 'C4')
;
GO

CREATE TABLE DailyUses
    ([Uses] int, [Date] Date, [Device] varchar(2))
;
    
INSERT INTO DailyUses
    ([Uses], [Date], [Device])
VALUES
    (3, '2020-3-1', 'A2'),
    (5, '2020-3-1', 'B3'),
    (1, '2020-3-2', 'A2'),
    (12, '2020-3-3', 'C4')
;
GO

SELECT DISTINCT [Date],[ErrorCode] FROM DailyUses CROSS JOIN  Code_SKU_Combos
GO

Date       | ErrorCode
:--------- | :--------
2020-03-01 | 12.24    
2020-03-01 | 12.25    
2020-03-01 | 12.26    
2020-03-02 | 12.24    
2020-03-02 | 12.25    
2020-03-02 | 12.26    
2020-03-03 | 12.24    
2020-03-03 | 12.25    
2020-03-03 | 12.26    

SELECT ISNULL (SUM([Uses]),0) AS Uses, DateComp as [Date],[errorcomp] AS ErrorCode
FROM (SELECT DISTINCT [Date] as DateComp,[ErrorCode] AS errorcomp FROM DailyUses CROSS JOIN  Code_SKU_Combos) t1
LEFT JOIN (
DailyUses d INNER JOIN Code_SKU_Combos c ON d.[Device] = c.[Device]) ON t1.DateComp = d.[Date] 
AND  t1.errorcomp = c.[ErrorCode]

GROUP BY [DateComp],[errorcomp]
ORDER BY [DateComp],[errorcomp]
GO

Uses | Date       | ErrorCode
---: | :--------- | :--------
   8 | 2020-03-01 | 12.24    
   3 | 2020-03-01 | 12.25    
   8 | 2020-03-01 | 12.26    
   1 | 2020-03-02 | 12.24    
   1 | 2020-03-02 | 12.25    
   1 | 2020-03-02 | 12.26    
   0 | 2020-03-03 | 12.24    
  12 | 2020-03-03 | 12.25    
   0 | 2020-03-03 | 12.26

db<>fiddle here

Related Question