Temat: Wynik zapytania dla każdego dnia w miesiącu w jednym...
Jeśli jest określona ilość wad, poniżej zapytanie pisane na piechotę. W przykładzie tylko 4-y wady (może lepiej napisać procedurę ... ).
DECLARE @mc VARCHAR(2)= '3'
SELECT 'nazwaWady 1'AS 'Rodzaj detalu\Dzien mc',SUM(CASE WHEN 1 = t.dzien THEN t.ilosc ELSE 0 END) AS '1'
, SUM(CASE WHEN 2 = t.dzien THEN t.ilosc ELSE 0 END) AS '2'
, SUM(CASE WHEN 3 = t.dzien THEN t.ilosc ELSE 0 END) AS '3'
, SUM(CASE WHEN 4 = t.dzien THEN t.ilosc ELSE 0 END) AS '4'
, SUM(CASE WHEN 5 = t.dzien THEN t.ilosc ELSE 0 END) AS '5'
, SUM(CASE WHEN 6 = t.dzien THEN t.ilosc ELSE 0 END) AS '6'
, SUM(CASE WHEN 7 = t.dzien THEN t.ilosc ELSE 0 END) AS '7'
, SUM(CASE WHEN 8 = t.dzien THEN t.ilosc ELSE 0 END) AS '8'
, SUM(CASE WHEN 9 = t.dzien THEN t.ilosc ELSE 0 END) AS '9'
, SUM(CASE WHEN 10 = t.dzien THEN t.ilosc ELSE 0 END) AS '10'
, SUM(CASE WHEN 11 = t.dzien THEN t.ilosc ELSE 0 END) AS '11'
, SUM(CASE WHEN 12 = t.dzien THEN t.ilosc ELSE 0 END) AS '12'
, SUM(CASE WHEN 13 = t.dzien THEN t.ilosc ELSE 0 END) AS '13'
, SUM(CASE WHEN 14 = t.dzien THEN t.ilosc ELSE 0 END) AS '14'
, SUM(CASE WHEN 15 = t.dzien THEN t.ilosc ELSE 0 END) AS '15'
, SUM(CASE WHEN 16 = t.dzien THEN t.ilosc ELSE 0 END) AS '16'
, SUM(CASE WHEN 17 = t.dzien THEN t.ilosc ELSE 0 END) AS '17'
, SUM(CASE WHEN 18 = t.dzien THEN t.ilosc ELSE 0 END) AS '18'
, SUM(CASE WHEN 19 = t.dzien THEN t.ilosc ELSE 0 END) AS '19'
, SUM(CASE WHEN 20 = t.dzien THEN t.ilosc ELSE 0 END) AS '20'
, SUM(CASE WHEN 21 = t.dzien THEN t.ilosc ELSE 0 END) AS '21'
, SUM(CASE WHEN 22 = t.dzien THEN t.ilosc ELSE 0 END) AS '22'
, SUM(CASE WHEN 23 = t.dzien THEN t.ilosc ELSE 0 END) AS '23'
, SUM(CASE WHEN 24 = t.dzien THEN t.ilosc ELSE 0 END) AS '24'
, SUM(CASE WHEN 25 = t.dzien THEN t.ilosc ELSE 0 END) AS '25'
, SUM(CASE WHEN 26 = t.dzien THEN t.ilosc ELSE 0 END) AS '26'
, SUM(CASE WHEN 27 = t.dzien THEN t.ilosc ELSE 0 END) AS '27'
, SUM(CASE WHEN 28 = t.dzien THEN t.ilosc ELSE 0 END) AS '28'
, SUM(CASE WHEN 29 = t.dzien THEN t.ilosc ELSE 0 END) AS '29'
, SUM(CASE WHEN 30 = t.dzien THEN t.ilosc ELSE 0 END) AS '30'
, SUM(CASE WHEN 31 = t.dzien THEN t.ilosc ELSE 0 END) AS '31'
FROM (SELECT DAY(dataWady) dzien, COUNT(1) ilosc
FROM [dbo].wady w
WHERE wada = 'nazwaWady 1 ' AND MONTH(dataWady) = @mc
GROUP BY DAY(dataWady)) t
UNION
SELECT 'nazwaWady 2',SUM(CASE WHEN 1 = t.dzien THEN t.ilosc ELSE 0 END) AS '1'
, SUM(CASE WHEN 2 = t.dzien THEN t.ilosc ELSE 0 END) AS '2'
, SUM(CASE WHEN 3 = t.dzien THEN t.ilosc ELSE 0 END) AS '3'
, SUM(CASE WHEN 4 = t.dzien THEN t.ilosc ELSE 0 END) AS '4'
, SUM(CASE WHEN 5 = t.dzien THEN t.ilosc ELSE 0 END) AS '5'
, SUM(CASE WHEN 6 = t.dzien THEN t.ilosc ELSE 0 END) AS '6'
, SUM(CASE WHEN 7 = t.dzien THEN t.ilosc ELSE 0 END) AS '7'
, SUM(CASE WHEN 8 = t.dzien THEN t.ilosc ELSE 0 END) AS '8'
, SUM(CASE WHEN 9 = t.dzien THEN t.ilosc ELSE 0 END) AS '9'
, SUM(CASE WHEN 10 = t.dzien THEN t.ilosc ELSE 0 END) AS '10'
, SUM(CASE WHEN 11 = t.dzien THEN t.ilosc ELSE 0 END) AS '11'
, SUM(CASE WHEN 12 = t.dzien THEN t.ilosc ELSE 0 END) AS '12'
, SUM(CASE WHEN 13 = t.dzien THEN t.ilosc ELSE 0 END) AS '13'
, SUM(CASE WHEN 14 = t.dzien THEN t.ilosc ELSE 0 END) AS '14'
, SUM(CASE WHEN 15 = t.dzien THEN t.ilosc ELSE 0 END) AS '15'
, SUM(CASE WHEN 16 = t.dzien THEN t.ilosc ELSE 0 END) AS '16'
, SUM(CASE WHEN 17 = t.dzien THEN t.ilosc ELSE 0 END) AS '17'
, SUM(CASE WHEN 18 = t.dzien THEN t.ilosc ELSE 0 END) AS '18'
, SUM(CASE WHEN 19 = t.dzien THEN t.ilosc ELSE 0 END) AS '19'
, SUM(CASE WHEN 20 = t.dzien THEN t.ilosc ELSE 0 END) AS '20'
, SUM(CASE WHEN 21 = t.dzien THEN t.ilosc ELSE 0 END) AS '21'
, SUM(CASE WHEN 22 = t.dzien THEN t.ilosc ELSE 0 END) AS '22'
, SUM(CASE WHEN 23 = t.dzien THEN t.ilosc ELSE 0 END) AS '23'
, SUM(CASE WHEN 24 = t.dzien THEN t.ilosc ELSE 0 END) AS '24'
, SUM(CASE WHEN 25 = t.dzien THEN t.ilosc ELSE 0 END) AS '25'
, SUM(CASE WHEN 26 = t.dzien THEN t.ilosc ELSE 0 END) AS '26'
, SUM(CASE WHEN 27 = t.dzien THEN t.ilosc ELSE 0 END) AS '27'
, SUM(CASE WHEN 28 = t.dzien THEN t.ilosc ELSE 0 END) AS '28'
, SUM(CASE WHEN 29 = t.dzien THEN t.ilosc ELSE 0 END) AS '29'
, SUM(CASE WHEN 30 = t.dzien THEN t.ilosc ELSE 0 END) AS '30'
, SUM(CASE WHEN 31 = t.dzien THEN t.ilosc ELSE 0 END) AS '31'
FROM (SELECT DAY(dataWady) dzien, COUNT(1) ilosc
FROM [dbo].wady w
WHERE wada = 'nazwaWady 2' AND MONTH(dataWady) = @mc
GROUP BY DAY(dataWady)) t
UNION
SELECT 'nazwaWady 3',SUM(CASE WHEN 1 = t.dzien THEN t.ilosc ELSE 0 END) AS '1'
, SUM(CASE WHEN 2 = t.dzien THEN t.ilosc ELSE 0 END) AS '2'
, SUM(CASE WHEN 3 = t.dzien THEN t.ilosc ELSE 0 END) AS '3'
, SUM(CASE WHEN 4 = t.dzien THEN t.ilosc ELSE 0 END) AS '4'
, SUM(CASE WHEN 5 = t.dzien THEN t.ilosc ELSE 0 END) AS '5'
, SUM(CASE WHEN 6 = t.dzien THEN t.ilosc ELSE 0 END) AS '6'
, SUM(CASE WHEN 7 = t.dzien THEN t.ilosc ELSE 0 END) AS '7'
, SUM(CASE WHEN 8 = t.dzien THEN t.ilosc ELSE 0 END) AS '8'
, SUM(CASE WHEN 9 = t.dzien THEN t.ilosc ELSE 0 END) AS '9'
, SUM(CASE WHEN 10 = t.dzien THEN t.ilosc ELSE 0 END) AS '10'
, SUM(CASE WHEN 11 = t.dzien THEN t.ilosc ELSE 0 END) AS '11'
, SUM(CASE WHEN 12 = t.dzien THEN t.ilosc ELSE 0 END) AS '12'
, SUM(CASE WHEN 13 = t.dzien THEN t.ilosc ELSE 0 END) AS '13'
, SUM(CASE WHEN 14 = t.dzien THEN t.ilosc ELSE 0 END) AS '14'
, SUM(CASE WHEN 15 = t.dzien THEN t.ilosc ELSE 0 END) AS '15'
, SUM(CASE WHEN 16 = t.dzien THEN t.ilosc ELSE 0 END) AS '16'
, SUM(CASE WHEN 17 = t.dzien THEN t.ilosc ELSE 0 END) AS '17'
, SUM(CASE WHEN 18 = t.dzien THEN t.ilosc ELSE 0 END) AS '18'
, SUM(CASE WHEN 19 = t.dzien THEN t.ilosc ELSE 0 END) AS '19'
, SUM(CASE WHEN 20 = t.dzien THEN t.ilosc ELSE 0 END) AS '20'
, SUM(CASE WHEN 21 = t.dzien THEN t.ilosc ELSE 0 END) AS '21'
, SUM(CASE WHEN 22 = t.dzien THEN t.ilosc ELSE 0 END) AS '22'
, SUM(CASE WHEN 23 = t.dzien THEN t.ilosc ELSE 0 END) AS '23'
, SUM(CASE WHEN 24 = t.dzien THEN t.ilosc ELSE 0 END) AS '24'
, SUM(CASE WHEN 25 = t.dzien THEN t.ilosc ELSE 0 END) AS '25'
, SUM(CASE WHEN 26 = t.dzien THEN t.ilosc ELSE 0 END) AS '26'
, SUM(CASE WHEN 27 = t.dzien THEN t.ilosc ELSE 0 END) AS '27'
, SUM(CASE WHEN 28 = t.dzien THEN t.ilosc ELSE 0 END) AS '28'
, SUM(CASE WHEN 29 = t.dzien THEN t.ilosc ELSE 0 END) AS '29'
, SUM(CASE WHEN 30 = t.dzien THEN t.ilosc ELSE 0 END) AS '30'
, SUM(CASE WHEN 31 = t.dzien THEN t.ilosc ELSE 0 END) AS '31'
FROM (SELECT DAY(dataWady) dzien, COUNT(1) ilosc
FROM [dbo].wady w
WHERE wada = 'nazwaWady 3' AND MONTH(dataWady) = @mc
GROUP BY DAY(dataWady)) t
UNION
SELECT 'nazwaWady 4',SUM(CASE WHEN 1 = t.dzien THEN t.ilosc ELSE 0 END) AS '1'
, SUM(CASE WHEN 2 = t.dzien THEN t.ilosc ELSE 0 END) AS '2'
, SUM(CASE WHEN 3 = t.dzien THEN t.ilosc ELSE 0 END) AS '3'
, SUM(CASE WHEN 4 = t.dzien THEN t.ilosc ELSE 0 END) AS '4'
, SUM(CASE WHEN 5 = t.dzien THEN t.ilosc ELSE 0 END) AS '5'
, SUM(CASE WHEN 6 = t.dzien THEN t.ilosc ELSE 0 END) AS '6'
, SUM(CASE WHEN 7 = t.dzien THEN t.ilosc ELSE 0 END) AS '7'
, SUM(CASE WHEN 8 = t.dzien THEN t.ilosc ELSE 0 END) AS '8'
, SUM(CASE WHEN 9 = t.dzien THEN t.ilosc ELSE 0 END) AS '9'
, SUM(CASE WHEN 10 = t.dzien THEN t.ilosc ELSE 0 END) AS '10'
, SUM(CASE WHEN 11 = t.dzien THEN t.ilosc ELSE 0 END) AS '11'
, SUM(CASE WHEN 12 = t.dzien THEN t.ilosc ELSE 0 END) AS '12'
, SUM(CASE WHEN 13 = t.dzien THEN t.ilosc ELSE 0 END) AS '13'
, SUM(CASE WHEN 14 = t.dzien THEN t.ilosc ELSE 0 END) AS '14'
, SUM(CASE WHEN 15 = t.dzien THEN t.ilosc ELSE 0 END) AS '15'
, SUM(CASE WHEN 16 = t.dzien THEN t.ilosc ELSE 0 END) AS '16'
, SUM(CASE WHEN 17 = t.dzien THEN t.ilosc ELSE 0 END) AS '17'
, SUM(CASE WHEN 18 = t.dzien THEN t.ilosc ELSE 0 END) AS '18'
, SUM(CASE WHEN 19 = t.dzien THEN t.ilosc ELSE 0 END) AS '19'
, SUM(CASE WHEN 20 = t.dzien THEN t.ilosc ELSE 0 END) AS '20'
, SUM(CASE WHEN 21 = t.dzien THEN t.ilosc ELSE 0 END) AS '21'
, SUM(CASE WHEN 22 = t.dzien THEN t.ilosc ELSE 0 END) AS '22'
, SUM(CASE WHEN 23 = t.dzien THEN t.ilosc ELSE 0 END) AS '23'
, SUM(CASE WHEN 24 = t.dzien THEN t.ilosc ELSE 0 END) AS '24'
, SUM(CASE WHEN 25 = t.dzien THEN t.ilosc ELSE 0 END) AS '25'
, SUM(CASE WHEN 26 = t.dzien THEN t.ilosc ELSE 0 END) AS '26'
, SUM(CASE WHEN 27 = t.dzien THEN t.ilosc ELSE 0 END) AS '27'
, SUM(CASE WHEN 28 = t.dzien THEN t.ilosc ELSE 0 END) AS '28'
, SUM(CASE WHEN 29 = t.dzien THEN t.ilosc ELSE 0 END) AS '29'
, SUM(CASE WHEN 30 = t.dzien THEN t.ilosc ELSE 0 END) AS '30'
, SUM(CASE WHEN 31 = t.dzien THEN t.ilosc ELSE 0 END) AS '31'
FROM (SELECT DAY(dataWady) dzien, COUNT(1) ilosc
FROM [dbo].wady w
WHERE wada = 'nazwaWady 4' AND MONTH(dataWady) = @mc
GROUP BY DAY(dataWady)) t
Dane testowe:
SELECT * INTO [dbo].wady
FROM (SELECT 'nazwaWady 1' AS wada, GETDATE() AS dataWady
UNION ALL
SELECT 'nazwaWady 2', DATEADD(day,-2,GETDATE())
UNION ALL
SELECT 'nazwaWady 2', DATEADD(day,-1,GETDATE())
UNION ALL
SELECT 'nazwaWady 3', DATEADD(day,-4,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-5,GETDATE())
UNION ALL
SELECT 'nazwaWady 2', DATEADD(day,-4,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-6,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-3,GETDATE())
UNION ALL
SELECT 'nazwaWady 4', DATEADD(day,-7,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-3,GETDATE())
UNION ALL
SELECT 'nazwaWady 4', DATEADD(day,-7,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-3,GETDATE())
UNION ALL
SELECT 'nazwaWady 4', DATEADD(day,-7,GETDATE())
UNION ALL
SELECT 'nazwaWady 1', DATEADD(day,-3,GETDATE())
UNION ALL
SELECT 'nazwaWady 4', DATEADD(day,-7,GETDATE())
) tab