Zliczanie danych przy pomocy "SUM" w złożonym zapytaniu SQL Serwer

0

Witam

Próbuję skonstruować polecenie w którym oprócz pobierania bezpośredniego danych z wyraźnie określonych pól w wyraźnie określonych tabelach, chce pobrać 2 wartości, które występujątylko jako składowe całości, i w dodatku chce to zrobić w 1 poleceniu.

Obecnie dane wsadowe wyglądają tak:

[Tabela [dbo].[Dni]
screenshot-20171101202744.png

Tabela [dbo].[Pracownik]
screenshot-20171101203027.png

Tabela [dbo].[Parametry]
screenshot-20171101203321.png

Wstępnie polecenie wygląda tak:

SELECT k.Id_LS, k.Pracownik_Imie, k.Pracownik_Nazwisko, k.Pracownik_Etat, e.Kwartal_Id, 
                             e.Parametry_OstatniCiagPrzepracowanychDni, e.Parametry_GodzinaOstatniDzienMiesiaca, 
                             e.Parametry_PoprzedniaWolnaNiedziala, e.LiczbaDniWolnychWKwartale, 
                             e.LiczbaDniPrzepracowanychWKwartale, p.Dni_Urlop, p.Dni_L4, p.Dni_Inne, p.Rok_Id, p.Miesiac_Id
FROM [dbo].[Pracownik] AS k 
INNER JOIN [dbo].[Parametry] AS e ON e.Pracownik_Id = k.Pracownik_Id  
LEFT JOIN [dbo].[Dni] AS p on p.Pracownik_Id = k.Pracownik_Id AND p.Rok_Id = e.Rok_Id AND p.Miesiac_Id = e.Miesiac_Id
WHERE e.Rok_Id = (SELECT Rok_Id FROM [dbo].[Rok] WHERE Rok = '" + wybranyRok + "') 
AND e.Miesiac_Id = '" + wybranyMiesiąc + "' 
AND k.Dzial_Id = (SELECT Dzial_Id FROM [dbo].[Dzial] WHERE Dzial = '" + wybranyDział + "') 

ale niestety ani e.LiczbaDniWolnychWKwartale, ani ** e.LiczbaDniPrzepracowanychWKwartale** ponieważ normalnie nie występują w tabeli.
Ale za to mogę je zliczyć poleceniem SUM!

dla ** e.LiczbaDniPrzepracowanychWKwartale** zliczając pola Dni_Przepracowane - dla miesięcy odpowiadających szukanemu kwartałowi i oczywiście dla odpowiedniego roku i pracownika w najprostszy sposób:

SELECT SUM (Dni_Przepracowane)
FROM [dbo].[Dni]
WHERE Pracownik_Id=1 AND Rok_Id=1 AND Miesiac_Id>=1 AND Miesiac_Id<=3;

ale dla e.LiczbaDniWolnychWKwartale muszę zsumować wartości pól Dni_UstawowoWOlne, Dni_Urlop, Dni_L4, Dni_Inne.

W osobnych poleceniach wiem jak to zrobić, ale jak pobrać te dane w pojedynczym poleceniu, jako pojedynczą skumulowaną wartość, i to w dodatku w wyniku będącą parametrem?

Może ktoś podpowiedzieć jak do tego podejść?

0

Jeżeli dobrze rozumiem twoją potrzebę, to musisz w zapytaniu głównym dodać podzapytanie (np. sumujące). Przykładowo:

Select prac_tab_zewn.Pracownik_Imie
	  ,prac_tab_zewn.Pracownik_Nazwisko
	  ,(
		select sum(ew.LiczbaDniPrzepracowanychWKwartale)
		FROM [dbo].[Pracownik] AS kw 
		INNER JOIN [dbo].[Parametry] AS ew ON ew.Pracownik_Id = kw.Pracownik_Id  
		LEFT JOIN [dbo].[Dni] AS pw ON pw.Pracownik_Id = kw.Pracownik_Id AND pw.Rok_Id = ew.Rok_Id AND pw.Miesiac_Id = ew.Miesiac_Id	
		//połączenie podselekta z selektem zewnetrznym po id pracownika
		WHERE  	kw.Pracownik_Id = k.Pracownik_id
	  ) as sumLiczbaDniPrzepracowanychWKwartale
FROM [dbo].[Pracownik] AS k 
INNER JOIN [dbo].[Parametry] AS e ON e.Pracownik_Id = k.Pracownik_Id  
LEFT JOIN [dbo].[Dni] AS p ON p.Pracownik_Id = k.Pracownik_Id AND p.Rok_Id = e.Rok_Id AND p.Miesiac_Id = e.Miesiac_Id
0

Tak, muszę dodać pod SELECT sumujący.
Z sumowaniem kilku wartości w 1 kolumnie - daje radę, ale drabina wyrosła, jak okazało się, że muszę pobrać dodatkowo zsumowane wartości z 4 kolumn i 3 wierszy w każdej - czyli dla wyznacznika -> kwartał, pobrać wartości z 3 wierszy dla 3 różnych miesięcy czyli dla przykładowego kwartału II

Miesiac_Id = 4; -> pierwszy rzad
Miesiac_Id = 5; -> drugi rząd
Miesiac_Id = 6; -> trzeci rząd

oraz dla kolumn:

Dni_UstawowoWolne
Dni_Urlop
Dni_L4
Dni_Inne

i zwrócić to jako pojedynczą wartość

Pracownik_DniWolne.....

Tak było by znacznie lepiej dla aplikacji, niż pobieranie wszystkiego do programu i sumowanie iteracyjne dla kilku setek pracowników - lepiej, jeśli zrobi to serwer ....
No chyba, że coś źle kombinuję, bo naprawdę jeszcze BARDZO brakuje mi doświadczenia...

0

Jaki ma być wynik zapytania, czy taki jak poniżej ?

Pracownik_id, miesiac_id, suma_z_kilku kolumn
1 4 1000
1 5 700
1 6 500
2 4 444
2 5 222
itd.

lub też z podziałem na kwartały ?

Pracownik_id, kwartał , suma_z_kilku kolumn
1 1 1000
1 2 700
1 3 500
1 4 444

2 1 222
2 ...
itd.

Jeżeli tak, to w zewnetrznym selekcie proponuję zastosowac grupowanie miesiącu lub kwartale (oraz oczywiście pozostałych nieagregowanych kolumnach)

SELECT prac.Pracownik_Imie
	  ,prac.Pracownik_Nazwisko
	  ,prac.miesiac_id
	  ,(
		SELECT sum(par2.LiczbaDniPrzepracowanychWKwartale + par2.Dni_L4 + par2.Dni_Inne)
		FROM 		[Pracownik] AS prac2 
		INNER JOIN  [Parametry] AS par2 ON ew.Pracownik_Id = par2.Pracownik_Id  
		LEFT JOIN   [Dni] AS dni2 ON prac2.Pracownik_Id = kw.Pracownik_Id AND pw.Rok_Id = ew.Rok_Id AND pw.Miesiac_Id = ew.Miesiac_Id		
		//połączenie podselekta z selektem zewnetrznym po id pracownika
		WHERE  prac.Pracownik_Id = prac2.Pracownik_id
			and prac.Miesiac_Id = prac2.miesiac_id
	  ) 
		as sumLiczbaDniPrzepracowanychWKwartale
FROM 		[Pracownik] AS prac 
INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
LEFT JOIN   [Dni] AS dni ON prac.Pracownik_Id = k.Pracownik_Id AND p.Rok_Id = e.Rok_Id AND p.Miesiac_Id = e.Miesiac_Id
GROUP BY  
	   prac.Pracownik_Imie
	  ,prac.Pracownik_Nazwisko
	  ,prac.miesiac_id --per miesiac
	  //jeżeli per kwartał to coś w stylu case when prac.miesiac_id in (1,2,3) then 'kwartał1', case when .... end 
	  //w groupowaniu i we frazie selekt to samo wyrażenie

ps. trochę aliasy mi się rozjechały, ale idea ta sama

0

Idea to: dla Pracownik_Id, Kwartal_Id, podaj: Suma_WyszczególnionychKolumn_Dla_Wszystkich_Miesiecy(rzędów z założenia 3) gdzie kolumny to:(i tu lista nazw kolumn)

0

1 To zamiast miesiac_id uzyj kwartal_id
2 Dopisz jeszcze połączenie z rokiem

     WHERE  prac.Pracownik_Id = prac2.Pracownik_id
                     and prac.Miesiac_Id = prac2.miesiac_id 
                     and prac.rok_id = prac2.rok_id

3 sprawdź, czy ten selekt zwraca wynik, jaki potrzebujesz

0

A to zwyczajny GROUP BY nie starcza?

SELECT 
	k.Id_LS
	,k.Pracownik_Imie
	,k.Pracownik_Nazwisko
	,k.Pracownik_Etat
	,e.Rok_Id
	,e.Kwartal_Id
	,e.Parametry_OstatniCiagPrzepracowanychDni
	,e.Parametry_GodzinaOstatniDzienMiesiaca
	,e.Parametry_PoprzedniaWolnaNiedziala
	,SUM(isnull(e.Dni_UstawowoWOlne,0) + isnull(Dni_Urlop,0) + isnull(Dni_L4,0) isnull(Dni_Inne,0)) as LiczbaDniWolnychWKwartale
	,SUM(e.Dni_Przepracowane) as LiczbaDniPrzepracowanychWKwartale
FROM 
	[dbo].[Pracownik] AS k 
	INNER JOIN [dbo].[Parametry] AS e ON e.Pracownik_Id = k.Pracownik_Id  
	LEFT JOIN [dbo].[Dni] AS p ON p.Pracownik_Id = k.Pracownik_Id AND p.Rok_Id = e.Rok_Id AND p.Miesiac_Id = e.Miesiac_Id
WHERE 
	e.Rok_Id = (SELECT Rok_Id FROM [dbo].[Rok] WHERE Rok = '" + wybranyRok + "') 
	AND e.Miesiac_Id = '" + wybranyMiesiąc + "' 
	AND k.Dzial_Id = (SELECT Dzial_Id FROM [dbo].[Dzial] WHERE Dzial = '" + wybranyDział + "') 
group by
	k.Id_LS
	,k.Pracownik_Imie
	,k.Pracownik_Nazwisko
	,k.Pracownik_Etat
	,e.Rok_Id
	,e.Kwartal_Id
	,e.Parametry_OstatniCiagPrzepracowanychDni
	,e.Parametry_GodzinaOstatniDzienMiesiaca
	,e.Parametry_PoprzedniaWolnaNiedziala
0

ad wemibo:
zaraz przepiszę sobie Twoją podpowiedź tak, żebym zrozumiał zasadę działania tego zapytania i przetestuję co ono robi

ad Panczo:
Wstępnie testuję to:

	   SELECT 
     k.Id_LS
    ,k.Pracownik_Imie
    ,k.Pracownik_Nazwisko
    ,k.Pracownik_Etat
    ,e.Rok_Id
    ,e.Kwartal_Id
    ,e.Parametry_OstatniCiagPrzepracowanychDni
    ,e.Parametry_GodzinaOstatniDzienMiesiaca
    ,e.Parametry_PoprzedniaWolnaNiedziala
    ,SUM(p.Dni_UstawowoWolne + p.Dni_Urlop + p.Dni_L4 + p.Dni_Inne) AS LiczbaDniWolnychWKwartale  ----> (ale to sumuje tylko wartości w rzędzie gdzie miesiąc to Miesiac_Id)
    ,SUM(p.Dni_Przepracowane) AS LiczbaDniPrzepracowanychWKwartale ---> (a to podaje tylko wartość pola = bez agregacji w kwartale)
FROM 
    [dbo].[Pracownik] AS k 
    INNER JOIN [dbo].[Parametry] AS e ON e.Pracownik_Id = k.Pracownik_Id  
    LEFT JOIN [dbo].[Dni] AS p ON p.Pracownik_Id = k.Pracownik_Id AND p.Rok_Id = e.Rok_Id AND p.Miesiac_Id = e.Miesiac_Id

GROUP BY
    k.Id_LS
    ,k.Pracownik_Imie
    ,k.Pracownik_Nazwisko
    ,k.Pracownik_Etat
    ,e.Rok_Id
    ,e.Kwartal_Id
    ,e.Parametry_OstatniCiagPrzepracowanychDni
    ,e.Parametry_GodzinaOstatniDzienMiesiaca
    ,e.Parametry_PoprzedniaWolnaNiedziala

jeszcze bez warunku WHERE, ponieważ tabela dni wymaga przebudowania - nie ma kolumny Kwartal_Id. A Wstępne WHERE po wyjęciu z kodu programu wyglądające tak:

	WHERE 
    e.Rok_Id = (SELECT Rok_Id FROM [dbo].[Rok] WHERE Rok = 2017) 
    AND e.Miesiac_Id = 1 
    AND k.Dzial_Id = (SELECT Dzial_Id FROM [dbo].[Dzial] WHERE Dzial = '" ADMINI "') 

nie odzwierciedla właściwi warunków zapytania dla elementów agregowanych.
I tu jest główny problem.
Wyniki dla wartości wywoływanych bezpośrednio, bez agregacji szukane są dla e.Miesiac_Id, ale dla tych agregowanych, powinny być podane dla e.Kwartal_Id którego częścią jest e.Miesiac_Id.

No i muszę wklepać tam jakieś dane :-)

Jak tylko to pozmieniam dam znać co teraz wychodzi....

i... śliczne dzięki za zainteresowanie!

napisze co i jak, jak tylko przebuduje bazę i dodam trochę danych do agregacji dla różnych pracowników, w różnych kwartałach i miesiącach.

0

Zamień słowo rząd na rekord, będzie łatwiej zrozumieć co piszesz,

suma oczywiście wynika z where, jeżeli dasz w warunku e.miesiac_id in (1,2,3) to będzie I kwartał, albo bezpośrednio po kwartale: e.kwartal_id=1

0

Dodałem trochę danych, ale jeszcze nie dodawałem pola Kwartal_Id do Tabeli [dbo].[Dni], wykorzystałem podpowiedź Wemibo i już zaczyna być ciepło :-)

Po wykonaniu takiego zapytania:

SELECT 
       prac.Pracownik_Imie
      ,prac.Pracownik_Nazwisko
      ,par.Miesiac_Id
      ,(  
          SELECT sum(dni2.Dni_UstawowoWolne + dni2.Dni_Urlop + dni2.Dni_L4 + dni2.Dni_Inne)
          FROM        [Pracownik] AS prac2 
          INNER JOIN  [Dni] AS dni2 
          ON prac2.Pracownik_Id = dni2.Pracownik_Id  
          LEFT JOIN   [Parametry] AS par2 
          ON par2.Pracownik_Id = prac2.Pracownik_Id AND dni2.Rok_Id = par2.Rok_Id AND dni2.Miesiac_Id = par2.Miesiac_Id 
          WHERE  prac.Pracownik_Id = prac2.Pracownik_Id AND dni.Miesiac_Id = dni2.Miesiac_Id
      ) 
         AS sumaPrzepracowanychDniWMiesiacach
         FROM        [Pracownik] AS prac 
         INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
         LEFT JOIN   [Dni] AS dni 
         ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
         GROUP BY  
                            prac.Pracownik_Id
                           ,prac.Pracownik_Imie
                           ,prac.Pracownik_Nazwisko
                           ,par.Miesiac_Id
                           ,dni.Miesiac_Id 
         ORDER BY prac.Pracownik_Id, par.Miesiac_Id ASC

dostaję taki wynik

screenshot-20171102140953.png

teraz popracuję nad tą podpowiedzią


      --jeżeli per kwartał to coś w stylu case when prac.miesiac_id in (1,2,3) then 'kwartał1', case when .... end 
      --w groupowaniu i we frazie selekt to samo wyrażenie

szczerze, to jeszcze nigdy tak nie używałem klauzuli case when, ale widzę, że czas zacząć :-)

1

Ciągle nie widzisz, że bez sensu jest robienie podzapytania żeby wyciągnąć dane z tabeli którą i tak wybierasz w "głównym" zapytaniu?
Case ci nie jest potrzebny bo dane id_kwartału masz w tabeli parametry:

SELECT 
       prac.Pracownik_Imie
      ,prac.Pracownik_Nazwisko
      ,par.kwartal_id
      ,SUM(dni.Dni_UstawowoWolne + dni.Dni_Urlop + dni.Dni_L4 + dni.Dni_Inne) AS sumaPrzepracowanychDniWKwartale
 FROM        
	[Pracownik] AS prac 
	INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
	LEFT JOIN   [Dni] AS dni ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
 GROUP BY  
       prac.Pracownik_Imie
      ,prac.Pracownik_Nazwisko
      ,par.kwartal_id

0

zapytanie w takiej formie zwraca:
screenshot-20171102141853.png

co jest wartością dni przepracowanych zdefiniowanych dla miesiąca 2 w tabeli [dbo].[Dni] tylko i wyłącznie, brakuje tu danych z miesiąca styczeń i marzec no i oczywiście zsumowania ich

0

Powiedz mi jakim sposobem możesz mieć przefiltrowane dane jeżeli ani Twój, ani moj poprzedni przyklad nie ma warunku where?

0

wiem że nie ma, i własnie kombinuje jak pogrupować miesiące i opisać warunek

0

Ale po co zajmujesz się kolumną miesiecy jak masz kolumne z kwartałem?

http://sqlfiddle.com/#!6/5661aa/3

0

to zapytanie zwraca nam ilość dni dla pracowników o odpowiednim imieniu, czyli wyznacznikiem jest imię, daj mi chwile, bo po przerobieniu tego na Pracownik_Id - zwraca tylko 1 miesiąc, i jeszcze nie wiem czemu, chyba pobieram to ze złej tablicy....

0

nie - nie wiem czemu nie agreguje poprawnie po Pracownik_Id, a robi to po Pracownik_Imie
u mnie to polecenie

SELECT 
       prac.Pracownik_Imie
      ,par.kwartal_id
      ,SUM(dni.Dni_UstawowoWolne + dni.Dni_Urlop + dni.Dni_L4 + dni.Dni_Inne) AS sumaPrzepracowanychDniWKwartale
 FROM        
    [Pracownik] AS prac 
    INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
    LEFT JOIN   [Dni] AS dni ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
 GROUP BY  
       prac.Pracownik_Imie
      ,par.kwartal_id
      order by 1,2

zwraca:
screenshot-20171102154308.png
czyli sumuje dni dla wszystkich wystąpień danego imienia, które przecież może się powtarzać, a jak próbuję pobrać to samo dla Pracownik_Id - zwraca tylko ostatni miesiąc
... Polecenie

SELECT 
       prac.Pracownik_Id
      ,par.Kwartal_Id
	  ,dni.Miesiac_Id
	  ,SUM(dni.Dni_UstawowoWolne + dni.Dni_Urlop + dni.Dni_L4 + dni.Dni_Inne) AS sumaPrzepracowanychDniWKwartale
 FROM        
    [Pracownik] AS prac 
    INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
    LEFT JOIN   [Dni] AS dni ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
	WHERE par.Kwartal_Id = 1 AND dni.Miesiac_Id > 0
 GROUP BY  
       prac.Pracownik_Id
      ,par.Kwartal_Id
	  ,dni.Miesiac_Id
	  ORDER BY 1, 3

zwraca
screenshot-20171102162913.png

A polecenie

SELECT 
       prac.Pracownik_Id
      ,par.Kwartal_Id
	  ,SUM(dni.Dni_UstawowoWolne + dni.Dni_Urlop + dni.Dni_L4 + dni.Dni_Inne) AS sumaPrzepracowanychDniWKwartale
 FROM        
    [Pracownik] AS prac 
    INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
    LEFT JOIN   [Dni] AS dni ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
	WHERE par.Kwartal_Id = 1 AND dni.Miesiac_Id > 0
 GROUP BY  
       prac.Pracownik_Id
      ,par.Kwartal_Id
	  ORDER BY 1, 2

screenshot-20171102163023.png

a potrzebuje wynik dla Pracownik_Id w rekordach dla danego kwartału i polach dla wszystkich typów dni wolnych...

0

Mieszasz, i to bardzo, generalnie nie widzę nieprawidłowości, jeżeli grupowanie po miesiącach dla pracownika 2 daje 19 w 1 i 9 w 2 miesiącu to wynik pogrupowany po kwartale jest poprawny bo zwraca 28...
Warunek dni.Miesiac_Id > 0 jest bez sensu, bo wtedy nie wiem dlaczego left join, skoro odrzucasz wszystkie nie skojarzone rekordy...

Może po kolei, masz strukturę, pokaż jakie masz dane przykładowe i wynik który chcesz osiągnąć, bo ja osobiście nie rozumiem Twoich oczekiwań....

0

Podesłałem Ci linka do pełnej bazy = może ja po prostu coś porządnie skopałem.....co jest prawdopodobne, biorąc pod uwagę mój poziom wiedzy

0

Ja rozumiem, że zależy Ci na rozwiązaniu problemu, ale ja co do zasady nie pomagam przez wiadomości prywatne. Na marginesie: Nie mam w zwyczaju rozpakowywać archiwów od nie znanych mi osób.
Jeżeli nie potrafisz opisać problemu to użyj sqlfiddle (link masz w moich poprzednich postach) i stwórz zestaw do testowania.
Wtedy skorzysta na tym więcej osób.

0

Masz rację, zagalopowałem się.
ale to trzecie rozwiązanie - rzeczywiście podaje poprawny wynik - tak się zakałapućkałem, że sam już nie wiedziałem co obrabiam.

SELECT 
       prac.Pracownik_Id
      ,par.kwartal_id
	  ,SUM(dni.Dni_Przepracowane) AS sumaPrzepracowaneWKwartale
      ,SUM(dni.Dni_UstawowoWolne + dni.Dni_Urlop + dni.Dni_L4 + dni.Dni_Inne) AS sumaWolnychWKwartale
 FROM        
    [Pracownik] AS prac 
    INNER JOIN  [Parametry] AS par  ON prac.Pracownik_Id = par.Pracownik_Id  
    LEFT JOIN   [Dni] AS dni ON dni.Pracownik_Id = prac.Pracownik_Id AND dni.Rok_Id = par.Rok_Id AND dni.Miesiac_Id = par.Miesiac_Id
 GROUP BY  
       prac.Pracownik_Id
      ,par.kwartal_id
      order by 1,2

agreguje wszystko poprawnie

Bardzo dziękuję za pomoc i poświęcony czas.

1 użytkowników online, w tym zalogowanych: 0, gości: 1