Tablica krzyżowa - utylizacja wieloletnich umów

Budowa zapytań Query @ **Jacek_Kotowski** | **02.07.2015**

2
5
104

Baza zawiera listę wieloletnich umów. Przyjęto, że ich utylizacja jest równomierna w całym okresie trwania tj. wartość umowy dzielimy przez czas trwania w miesiącach. Chciałbym poznać miesięczna wartość utylizacji umów dla klientów.

Tabela zawiera pola:

customer, date, duration_in_months, value.

Date oznacza datę rozpoczęcia kontraktu.
Formulą iif sprawdzam, czy w danym miesiącu umowa jest aktywna i przyjmuje wartość utylizacji, jako wartość umowy podzielona przez czas trwania w miesiącach.

Moje 'meta' zapytanie wyglądałoby mniej więcej tak:

Select

customer,
sum(iif(date <= 01.01.2015   and dateadd(month,duration_in_months,date) >eomonth(01.01.2015)  , value/duratnion_in_months,0) as sty),
.
(i tak dla każdego miesiąca)
.
sum(iif(date <= 01.12.2015   and dateadd(month,duration_in_months,date) >=eomonth(01.12.2015)  , value/duratnion_in_months,0) as gru),


from moja_baza_danych

group by customer

Czy takie zapytanie można uprościć, aby uzyskać miesiące w kolumnach, stosując (w Accesie) składnie Transform/Pivot albo (w MS SQL) Pivot?

Z góry dziękuję za podpowiedzi.

Edit.

Łączna wartość miesięczna (utylizacji) umów w roku x np. 2015.

Klient, Sty, Lu, Ma, Kwie, Maj, Cze, Li, Sie, Wrze, etc.

Klient A 21 tys 7 tys etc...

Klient B 32 tys 32 tys etc...

Klient C 18 tys 23 tys etc...

Zapytał
@ Jacek_Kotowski | 01.07.2015
- lider
- laureat
- ekspert
- 60
- 54
- 94

3

1. Która wersja SQL Servera?

2. Czy możemy zmodyfikować tabelę źródłową, tak aby wartość utylizacji była przechowywana w rekordzie?

3. Poproszę o przykład danych wynikowych z zapytania.

Komentarze (1)

Odpowiedział
@ | 03.07.2015
TRENER ALTKOM AKADEMII

Komentarze

Panie Macieju, co do wersji, to używamy MS SQL Express 2014. Możemy zmodyfikować tabelę, jeśli to pomoże, wartość utylizacji w bardzo dużym uproszczeniu przyjęto, że wyniesie po prostu wartość umowy podzielona przez czas trwania w miesiacach. Dane źródłowe - Klient, dosawca data zawarcia umowy, czas trwania umowy (w miesiącach), wartosc (może być wiele umów z tym samym klientem, rozpoczętych w różnym czasie) Dane wynikowe. Łączna wartość miesięczna (utylizacji) umów w roku x np. 2015. Klient, Sty, Lu, Ma, Kwie, Maj, Cze, Li, Sie, Wrze, etc. Klient A - 21 tyś, 7 tyś etc. Klient B - 32 tyś, 32 tyś. Klient C - 18 tyś, 23 tyś umów.

Skomentował : @ Jacek_Kotowski ,05.07.2015

60
54
94

3

Nie do końca rozumiem, czy wynik to ma być: rok, miesiąc, Klient, Suma_utylizacji_w_danym_miesiacu? Co można oczywiście przepivotować, czy sam taki wynik wystarczy?

Przykład PIVOTa:

CREATE TABLE umowy( Klient int
, Rok int
, miesiac int
, suma int)
; 
GO

INSERT INTO umowy
VALUES(1, 2005, 1, 1000)
,(1,2005,2,10000)
, (2,2005,2,200)
, (2,2005,3,2000)
, (2, 2006,2,300)
, (2, 2006,4,3000)
, (3, 2007,5, 400);
GO 

SELECT Klient
,Rok
, [1],[2],[3],[4],[5], [6]
FROM (
SELECT Klient
, Rok
, Miesiac, suma
FROM umowy) p
PIVOT (
SUM(suma) FOR Miesiac in([1],[2],[3],[4],[5], [6])) as PV
ORDER BY Klient

Skomentuj

Odpowiedział
@ | 19.07.2015
TRENER ALTKOM AKADEMII

Pytanie