0

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

4
  • It seems there's a missing info on the dbo.Code_SKU_Combos table: a date to refer to the day that error code happened, otherwise, if the only relation between those two tables is the device, you won't be able to group the results by date for every device from dbo.DailyUses will aggregate every row for that device on dbo.Code_SKU_Combos and you'll have a bunch of rows with the same result number. – Ronaldo Mar 10 at 17:14
  • I see how this may have been confusing. We are not looking at the day the errorcode happened, but rather how many times the a group of devices were used , in which that group of devices is capable of throwing that error. This is to help me normalize another table of error counts – Enoch Levandovsky Mar 10 at 17:30
  • I think I still don't get it. Is the output table you provided a result of only the values on the sample tables? shouldn't this value: 5 | 2020-3-1 | 12.25 be 3 | 2020-3-1 | 12.25 since error code 12.25 is not possible for B3? – Ronaldo Mar 10 at 18:17
  • I am very sorry for the type, you would be correct. The output table is only a result of the two input tables. – Enoch Levandovsky Mar 10 at 18:28
1

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

2
  • Thank You! Ill let you know if it works out after my 30 minutes query finishes – Enoch Levandovsky Mar 10 at 18:31
  • Worked like a charm, Thank You for your time – Enoch Levandovsky Mar 10 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.