Meni se cini da je ovaj zadatak sasvim zreo da bude mozgalica. Jeste slicno Mozgalici Br 7, ali mislim da nijre potpuno isto.
U svakom slucaju cini mi se da se ovde trazi konkretno resenje bez mnogo teoretisanja, pa sam probao nesto, u mom MS SQL. Mislim da je kod dovoljno standardan da se moze barem razumeti.
Kreairjmo tabelu i neke test podatke:
Code:
CREATE TABLE Z_Primer
(DatumVreme datetime NOT NULL
, Smena int NOT NULL
, Iznos money NOT NULL)
INSERT INTO Z_Primer VALUES ('2007-08-25 16:11:19.527',1,200.00)
INSERT INTO Z_Primer VALUES ('2007-08-25 20:11:19.527',1,100)
INSERT INTO Z_Primer VALUES ('2007-08-25 21:11:19.527',2,250)
INSERT INTO Z_Primer VALUES ('2007-08-25 22:11:19.527',1,400)
INSERT INTO Z_Primer VALUES ('2007-08-26 06:11:19.527',1,150)
INSERT INTO Z_Primer VALUES ('2007-08-26 08:11:19.527',1,150)
INSERT INTO Z_Primer VALUES ('2007-08-26 10:11:19.527',2,250)
INSERT INTO Z_Primer VALUES ('2007-08-26 11:11:19.527',2,150)
INSERT INTO Z_Primer VALUES ('2007-08-26 12:11:19.527',3,250)
INSERT INTO Z_Primer VALUES ('2007-08-26 14:11:19.527',3,175)
INSERT INTO Z_Primer VALUES ('2007-08-26 16:11:19.527',3,250)
INSERT INTO Z_Primer VALUES ('2007-08-26 19:11:19.527',3,100)
INSERT INTO Z_Primer VALUES ('2007-08-26 21:11:19.527',2,150)
INSERT INTO Z_Primer VALUES ('2007-08-26 22:11:19.527',2,250)
INSERT INTO Z_Primer VALUES ('2007-08-26 23:11:19.527',2,100)
INSERT INTO Z_Primer VALUES ('2007-08-27 02:11:19.527',2,300)
SELECT * FROM Z_Primer
DatumVreme Smena Iznos
2007-08-25 16:11:19.527 1 200.00
2007-08-25 20:11:19.527 1 100.00
2007-08-25 21:11:19.527 1 250.00
2007-08-25 22:11:19.527 1 400.00
2007-08-26 06:11:19.527 1 150.00
2007-08-26 08:11:19.527 1 150.00
2007-08-26 10:11:19.527 2 250.00
2007-08-26 11:11:19.527 2 150.00
2007-08-26 12:11:19.527 3 250.00
2007-08-26 14:11:19.527 3 175.00
2007-08-26 16:11:19.527 3 250.00
2007-08-26 19:11:19.527 3 100.00
2007-08-26 21:11:19.527 2 150.00
2007-08-26 22:11:19.527 2 250.00
2007-08-26 23:11:19.527 2 100.00
2007-08-27 02:11:19.527 2 300.00
Bilo bi lepo kad bi mogli da grupisemo po koloni Smena, ali bas ne ide nikako. Kad bi mogli da promenimo dizajn baze, ja bih uveo jos jednu tabelu, Smena u kju bi se upisvao jedinstveni ID za svaku smenu. Tabela Z_Primer bila bi child tabele i izgledala bi nekako ovako:
Code:
SmenaUniqueID DatumVreme Smena Iznos
A 2007-08-25 16:11:19.527 1 200.00
A 2007-08-25 20:11:19.527 1 100.00
A 2007-08-25 21:11:19.527 1 250.00
A 2007-08-25 22:11:19.527 1 400.00
A 2007-08-26 06:11:19.527 1 150.00
A 2007-08-26 08:11:19.527 1 150.00
B 2007-08-26 10:11:19.527 2 250.00
B 2007-08-26 11:11:19.527 2 150.00
C 2007-08-26 12:11:19.527 3 250.00
C 2007-08-26 14:11:19.527 3 175.00
C 2007-08-26 16:11:19.527 3 250.00
C 2007-08-26 19:11:19.527 3 100.00
D 2007-08-26 21:11:19.527 2 150.00
D 2007-08-26 22:11:19.527 2 250.00
D 2007-08-26 23:11:19.527 2 100.00
D 2007-08-27 02:11:19.527 2 300.00
Onda bi zadatak bio trivijalan - GROUP BY SmenaUniqueID bi rsio problem.
Medjutim, verovatno da u ovom momentu ne mozemo menjati dizajn. Znaci, treba da nekeko iz postojecih podataka izvedemo nakakav identifier za grupe redova sa ponavljajucom smenom. Meni to nije bilo lako i nadam toplo se da ce me neko opet poklopiti sa jednostavnijim resenjem, kao u mozgalici za kraj leta :-)
Posle vise proba i gresaka, dobio sam nesto ovako:
Code:
SELECT
DatumVreme
, Smena
, SmenaUPrethodnomSlogu = (SELECT Smena FROM Z_Primer WHERE DatumVreme = PrethodnoVreme)
, BreakPoint = CASE
WHEN Smena = (SELECT Smena FROM Z_Primer WHERE DatumVreme = PrethodnoVreme) THEN 0
ELSE 1
END
, Iznos
-- , PoslednjaSmena
, PrethodnoVreme
FROM
(
SELECT
DatumVreme
, Smena
, Iznos
, PrethodnoVreme = (SELECT MAX(DatumVreme) FROM Z_Primer AS B
WHERE B.DatumVreme < A.DatumVreme
)
FROM Z_Primer AS A
) AS X
sto je dalo ovakav rezultat:
Code:
DatumVreme Smena SmenaUPrethodnomSlogu BreakPoint Iznos PrethodnoVreme
----------------------- ----------- --------------------- ----------- --------------------- -----------------------
2007-08-25 16:11:19.527 1 NULL 1 200.00 NULL
2007-08-25 20:11:19.527 1 1 0 100.00 2007-08-25 16:11:19.527
2007-08-25 22:11:19.527 1 1 0 400.00 2007-08-25 20:11:19.527
2007-08-26 06:11:19.527 1 1 0 150.00 2007-08-25 22:11:19.527
2007-08-26 08:11:19.527 1 1 0 150.00 2007-08-26 06:11:19.527
2007-08-26 10:11:19.527 2 1 1 250.00 2007-08-26 08:11:19.527
2007-08-26 11:11:19.527 2 2 0 150.00 2007-08-26 10:11:19.527
2007-08-26 12:11:19.527 3 2 1 250.00 2007-08-26 11:11:19.527
2007-08-26 14:11:19.527 3 3 0 175.00 2007-08-26 12:11:19.527
2007-08-26 16:11:19.527 3 3 0 250.00 2007-08-26 14:11:19.527
2007-08-26 19:11:19.527 3 3 0 100.00 2007-08-26 16:11:19.527
2007-08-26 21:11:19.527 2 3 1 150.00 2007-08-26 19:11:19.527
2007-08-26 22:11:19.527 2 2 0 250.00 2007-08-26 21:11:19.527
2007-08-26 23:11:19.527 2 2 0 100.00 2007-08-26 22:11:19.527
2007-08-27 02:11:19.527 2 2 0 300.00 2007-08-26 23:11:19.527
(15 row(s) affected)
Uocite kolonu BreakPoint. Svuda su nule, osim tamo gde pocinje nova smena. Ovo se lepo moze iskoristiti za dodelivanje nekkavog UniqueGroupID.
Prethodni kveri se moze napisati u sazetijem obliku, ovde je napisan ovako da bi onaj ko zeli mogao da razume sve medjukorake (izracunavanje PrethodneSmene i vrednosti BreakPoint)
Elem, napravio sam view koji izracunava BreakPoints i na osnovu njega sam izracunao jedinstvene oznake za grupe. Moze i bez view-a, ali je ovako jasnije sta se desava.
Code:
CREATE VIEW V_Breakpoints
AS
SELECT
DatumVreme
, Smena
-- prethodna smena
, SmenaUPrethodnomSlogu = (SELECT Smena
FROM Z_Primer
WHERE DatumVreme = ---PrethodnoVreme
(SELECT MAX(DatumVreme)
FROM Z_Primer AS B
WHERE B.DatumVreme < A.DatumVreme
)
)
-- Promena smene<=> tekuca smena nije ista kao i prethodna smena
, BreakPoint = CASE
WHEN Smena = (SELECT Smena
FROM Z_Primer C
WHERE C.DatumVreme = --- PrethodnoVreme
(SELECT MAX(DatumVreme)
FROM Z_Primer AS B
WHERE B.DatumVreme < A.DatumVreme
)
) THEN 0
ELSE 1
END
, Iznos
FROM Z_Primer AS A
GO
Odradite SELECT * FROM V_Breakpoints i videcete da daje isti rezultat kao prethodni SELECT izraz.
Sada oradimo ovo:
Code:
SELECT
DatumVreme
, Smena
, BreakPoint
, Grupa = (SELECT SUM(BreakPoint)
FROM V_BreakPoints AS B
WHERE A.DatumVreme >= B.datumVreme)
, Iznos
FROM V_BreakPoints AS A
DatumVreme Smena BreakPoint Grupa Iznos
----------------------- ----------- ----------- ----------- ---------------------
2007-08-25 16:11:19.527 1 1 1 200.00
2007-08-25 20:11:19.527 1 0 1 100.00
2007-08-25 22:11:19.527 1 0 1 400.00
2007-08-26 06:11:19.527 1 0 1 150.00
2007-08-26 08:11:19.527 1 0 1 150.00
2007-08-26 10:11:19.527 2 1 2 250.00
2007-08-26 11:11:19.527 2 0 2 150.00
2007-08-26 12:11:19.527 3 1 3 250.00
2007-08-26 14:11:19.527 3 0 3 175.00
2007-08-26 16:11:19.527 3 0 3 250.00
2007-08-26 19:11:19.527 3 0 3 100.00
2007-08-26 21:11:19.527 2 1 4 150.00
2007-08-26 22:11:19.527 2 0 4 250.00
2007-08-26 23:11:19.527 2 0 4 100.00
2007-08-27 02:11:19.527 2 0 4 300.00
(15 row(s) affected)
Iz poslednjeg rezultata moze se uraditi GROUP BY Grupa i dobiti resenje. Na primer ovako:
Code:
SELECT
Grupa
, UkupanIznos = SUM(Iznos)
, BrojNarudzbi = COUNT(*)
FROM
(
SELECT
DatumVreme
, Smena
, BreakPoint
, Grupa = (SELECT SUM(BreakPoint)
FROM V_BreakPoints AS B
WHERE A.DatumVreme >= B.datumVreme)
, Iznos
FROM V_BreakPoints AS A
) AS X
GROUP BY Grupa
Grupa UkupanIznos BrojNarudzbi
----------- --------------------- ------------
1 1000.00 5
2 400.00 2
3 775.00 4
4 800.00 4
(4 row(s) affected)
Izracunali smo dakle i vise nego sto se trazilo zadatkom - sume za sve smene, ne samo za poslednju. Ako ste uvek izracunavali samo poslednju smenu, pa to upisvali u neku tabelu, vise nema potrebe za tim - poslednji SELECT vam to daje svaki put. Izracunate vrednosti ionako ne treba cuvati u tabelama :-)
U praksi, sve moze da se strpa u jedan jedini SELECT i da se to cuva ko VIEW, a moze i u delovima (Ako koristite Access, morace u delovima )
Moze li ovo nekako jednostavnije?