OOPS, greska, kveri ne prikazuje interval od poslednje transakcije do danas. Ni originalni kveri to ne radi, pa nisam samo ja kriv
Originalni kveri pogresno koristi GetDate() funkciju. U movoj skripti ima kako se to radi pravilno.
Code:
-- kreiramo #Temp tabelu #Data
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
CREATE TABLE #Data -- DROP TABLE Data
(
Obveznik int
, Konto int
, Potrazuje money
, Duguje Money
, Datum DateTime
, PRIMARY KEY (Obveznik, Konto, Datum)
)
-- SELECT COUNT(*) FROM #DAta
-- DELETE #Data
-- Podaci za (Obveznik = 1, Konto = 2)
-- Ove podatke necemo menjati tokom testiranja
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1 , 2, '1-1-2009', 15, 0);
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1, 2, '1-15-2009', 23, 0);
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1, 2, '3-2-2009', 0, 40);
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1 , 2, '4-13-2009', 15, 0);
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1 , 2, '5-1-2009', 0, 10);
INSERT INTO #Data (Obveznik, Konto, Datum, Duguje,Potrazuje ) VALUES (1, 2, '6-10-2009', 20, 0 );
-- pomocna tabela brojeva, trebace nam da sagradimo test records
IF OBject_ID('tempdb..#Numbers') IS NOT NULL DROP TABLE #Numbers
CREATE TABLE #NUmbers (Num int PRIMARY KEY)
DECLARE @N as int
SEt @N=1
WHILE @N<=100
BEGIN
INSERT INTO #NUmbers VALUES (@N)
SET @N = @N+1
END
--SELECT * FROM #NUmbers
-- SELECT * FROM Data
-- da napravimo malo test podataka:
-- Dodajmo jos konta za istog obveznika
INSERT INTO #DAta ( Obveznik, Konto, Datum, Duguje, Potrazuje )
SELECT Obveznik, Konto = Num, Datum, Duguje, Potrazuje
FROM #DAta
JOIN #NUmbers ON 1=1
WHERE Obveznik = 1 AND Konto = 2
AND Num <=15
AND NUm <> 2
-- Sada imamo 15 konta za korisnika 1, sva imaju iste transakcije
-- Dovoljno dobro za testiranje
-- Sad cemo da napravimo po 15 konta za jos 100 korisnika
INSERT INTO #Data (Obveznik , Konto, Datum, Duguje, Potrazuje)
SELECT
Obveznik = Num, Konto, Datum, Duguje, Potrazuje
FROM #Data
JOIN #NUmbers ON 1=1
WHERE Obveznik = 1
AND Num > 1
-- Sad imamo 9000 redova u test tabeli:
SELECT COUNT(*) FROm #Data
-- Sad cemo svaki sedmi rekord da uklonimo iz tabele,
-- da bi malo transakcije bile razlicite od konta do konta, obveznika do obveznika
-- paznj! oVo izvrsite samo jednom!
; WITH SlucajniRedosled AS
(
SELECT
D.*
, RedniBroj = Row_number() OVER (ORDER BY NewID())
FROM #DAta AS D
-- Ne diramo pocetni konto, zbog provere
WHERE NOT (Obveznik = 1 AND Konto = 2)
)
DELETE SlucajniRedosled
WHERE RedniBroj % 7 = 0
-- (1284 row(s) affected)
SELECT COUNT(*) FROM #Data -- 7716
-- Sve do sada bila je samo priprema podataka. Nije potrebno razumeti,
-- dovoljno je odraditi skriptu ---
/**************************************************************************/
-- Odavde pa nadalje radimo stvarni posao ---
-- Resenje bez temp tabela, cisti kveri --
--
DECLARE @KamatnaStopa AS decimal (10,6)
DECLARE @OBveznik int
DECLARE @Konto int
SET @KamatnaStopa = 0.0003
SEt @Obveznik = 1
SEt @KOnto = 2
-- Ovo je za testiranje
--SELECT * FROM #Data AS D
--WHERE D.Obveznik = @Obveznik
--AND D.KOnto = @Konto
--ORDER BY Obveznik, Konto, Datum
; WITH MyUnion AS
(
-- Ako treba, ovde ubacujemo WHERE da bi racunali samo sta treba
-- isti where ubacujemo i kad racunamo OdDatuma DOdatuma
-- vidi dole.....
SELECT Obveznik, Konto, Potrazuje, Duguje, Datum
FROM #Data
--WHERE D.Obveznik = @Obveznik AND D.KOnto = @Konto
UNION ALL
SELECT Obveznik, Konto, Potrazuje=0, Duguj=0, Datum = CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
FROM #Data
--WHERE D.Obveznik = @Obveznik AND D.KOnto = @Konto
GROUP BY Obveznik, Konto
-- Kako se GetDate() svodi na oblik 'samo datum, bez minuta i sekundi'
-- SELECT Getdate() AS GetDate, CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime) AS SamoDatum
)
, DnevniBalans AS
(
SELECT
Obveznik
, Konto
, Datum
, DnevniSaldo = SUM(Potrazuje-Duguje)
FROM MyUnion AS D
GROUP BY Obveznik, Konto, Datum
)
--SELECT * FROm DnevniBalans WHERE Obveznik = 1 AND KOnto =2 -- ovo je ostalo od testiranja kverija korak po korak
, Kumulativ AS
(
SELECT
Obveznik
, Konto
, Datum
, RunningSum = (SELECT SUM(DnevniSaldo)
FROM DnevniBalans AS B
WHERE B.datum <= A.Datum
AND A.Obveznik = B.Obveznik
AND A.konto = B.Konto
)
FROM DnevniBalans AS A
)
--SELECT * FROM Kumulativ WHERE Obveznik = 1 AND KOnto =2
, OdDatumDoDatuma AS
(
SELECT
Obveznik
, Konto
, OdDatuma = (SELECT MAX(Datum)
FROM MyUnion AS B
WHERE B.datum < A.Datum
AND A.Obveznik = B.Obveznik
AND A.konto = B.Konto
)
, DoDatuma = Datum
FROM MyUnion AS A
GROUP BY Obveznik, Konto, Datum
)
--SELECT * FROm OdDatumDoDatuma WHERE Obveznik = 1 AND KOnto =2
SELECT
K.Obveznik
, K.Konto
, K.Datum
, K.RunningSum AS Saldo
, D.DoDAtuma
, BrojDana = DATEDIFF (day, K.Datum, COALESCE(D.DoDatuma, Getdate()))
, KamatnaStopa_Pct = @KamatnaStopa * 100.0
, Kamata = K.RunningSum
* DATEDIFF (day, K.Datum, COALESCE(D.DoDatuma, Getdate()))
* @KamatnaStopa
FROM Kumulativ AS K
JOIN OdDatumDoDatuma AS D ON K.Obveznik = D.Obveznik
AND K.Konto = D.Konto
AND K.Datum = D.OdDatuma
WHERE K.Obveznik = @Obveznik AND K.Konto = @Konto -- ovo je za testiranje, treba obrisati ovaj WHERE
----- WHERE ne bi stavljali ovde nego na pocetku, u MyUnion
ORDER BY
K.Obveznik
, K.Konto
, K.Datum
-- 7716 rows in 0 seconds :-)
kako bi silo sa temp tabelama? Svaka stavka iz WITH bila bi posebna temp tabela.
Prvo bi islo:
SELECT *
INTO #MyUnion
FROM
(
SELECT * FROM #Data
UNION ALL
SELECT Obveznik, Konto, Potrazuje=0, Duguj=0, Datum = CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime) FROM #Data
) AS X
Pa onda:
SELCT
......
INTO #DnevniBalans
FROM #MyUnion
pa dalje
SELECT
.....
INTO #Kumulativ
FROM #DnevniBAlans
i tako redom.
U svakom koraku bismo kreirali jednu temp tabelu koja bi cuvala medjurezultate. To bismo mogli da kontrolisemo, pregledamo i doterujemo dok ne proradi.
Za stored proceduru, obicno je dovoljno napraviti temp atbelu za set podataka koji vraca najvise rekorda.
Spora stored procedure, sta sad?
