MOzgalica 12: Bankarske nevolje

MOzgalica 12: Bankarske nevolje 02.10.2008. u 19:22 · pre 220 meseci #1

Evo jedne bankarske mozgalice: u mesecu Oktobru 2008 godine, banka mora svaki dan da isplati odredjenu kolicinu para u kesu. Novac za isplatu stize ponedeljkom. Sta stigne ponedeljkom, podeli se te sedmice. Sedmica pocinje u nedelju a zavrsava se u subotu (default za MS SQL server, verovatno i za ostale). Uprava banke razmislja i o ideji da

Treba napisati dva kverija:

a) koliko novca treba dostaviti u ponedeljak za svaku sedmicu, ovako:

Code:

Ponedeljak              KolikoIsplata UkupanIZnos
----------------------- ------------- ---------------------
2008-09-29 00:00:00.000 4             62.50
2008-10-06 00:00:00.000 7             28.10
2008-10-13 00:00:00.000 7             14.26
2008-10-20 00:00:00.000 2             3.08

(4 row(s) affected)

b) isto, samo se novac dstavlja u petak prethodne sedmice:, rezultat treba da bude:

Code:

Petak                   KolikoIsplata UkupanIZnos
----------------------- ------------- ---------------------
2008-09-26 00:00:00.000 4             62.50
2008-10-03 00:00:00.000 7             28.10
2008-10-10 00:00:00.000 7             14.26
2008-10-17 00:00:00.000 2             3.08

(4 row(s) affected)

Ulazni podaci:

Code:

IF OBject_ID('tempdb..#Isplata') IS NOT NULL DROP TABLE #Isplata
GO
CREATE TABLE #Isplata
(
DatumIsplate datetime PRIMARY KEY
, IznosIsplate money NOT NULL CHECK (IznosIsplate>0)
)
GO
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/1/2008',30)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/2/2008',15)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/3/2008',10)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/4/2008',7.5)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/5/2008',6)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/6/2008',5)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/7/2008',4.29)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/8/2008',3.75)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/9/2008',3.33)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/10/2008',3)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/11/2008',2.73)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/12/2008',2.5)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/13/2008',2.31)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/14/2008',2.14)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/15/2008',2)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/16/2008',1.88)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/17/2008',1.76)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/18/2008',1.67)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/19/2008',1.58)
INSERT INTO #Isplata (DatumIsplate, IznosIsplate) VALUES ('10/20/2008',1.5)
GO

Izvolite :-)

Re: MOzgalica 12: Bankarske nevolje 03.10.2008. u 16:58 · pre 220 meseci #2

Da li niko nema vremena ili je isuvise jednostavno pitanje?

Re: MOzgalica 12: Bankarske nevolje 04.10.2008. u 02:19 · pre 220 meseci #3

Nije mnogo komplikovano.

Resenje je za MYSQL 5.0

Za ponedeljak:

Code:

SELECT  dan.ponedeljak AS Ponedeljak,
        COUNT(WEEK(isp.DatumIsplate)) AS KolikoIsplata,
        SUM(isp.IznosIsplate) AS UkupanIznos
FROM    isplata AS isp
        INNER JOIN
                (
                SELECT DATE_SUB(isp1.DatumIsplate, INTERVAL WEEKDAY((DATE_ADD(isp1.DatumIsplate, INTERVAL 1 DAY)))-1 DAY) AS ponedeljak,
                       WEEK(isp1.datumisplate) AS w
                FROM    isplata AS isp1
                GROUP BY Ponedeljak
                )
                AS dan
        ON      WEEK(isp.DatumIsplate) = dan.w
GROUP BY WEEK(isp.DatumIsplate)

Za petak:

Code:

SELECT  dan.petak AS Petak,
        COUNT(WEEK(isp.DatumIsplate)) AS KolikoIsplata,
        SUM(isp.IznosIsplate) AS UkupanIznos
FROM    isplata AS isp
        INNER JOIN
                (
                SELECT DATE_SUB(isp1.DatumIsplate, INTERVAL WEEKDAY((DATE_ADD(isp1.DatumIsplate, INTERVAL 1 DAY)))+2 DAY) AS petak,
                       WEEK(isp1.datumisplate) AS w
                FROM    isplata AS isp1
                GROUP BY petak
                )
                AS dan
        ON      WEEK(isp.DatumIsplate) = dan.w
GROUP BY WEEK(isp.DatumIsplate)

Jedino je malo tricky bilo naci datume za ponedeljke i petkove :-)

Reseno je sa malcice zapetljanom kalendarskom racunicom u JOIN subquery i
"stelovanjem" WEEKDAY() funkcije da indekse dana u nedelji vraca uvecane za 1.

WEEKDAY() funkcija vraca index 0 za ponedeljak pa dalje redom, pa je bilo potrebno da se
uskladi sa WEEK() funkcijom koja po defaultu krece on nedelje.

Ostalo ne bi trebalo da je tesko za shvatiti.

Pozdrav,
Bojan

Re: MOzgalica 12: Bankarske nevolje 06.10.2008. u 16:18 · pre 219 meseci #4

Bravo za Bojana!

Sve je lako kad uocis caku

Evo resenje za MS SQL, za Ponedeljak:

Code:

;WITH Pon AS
(
SELECT
    DatumIsplate
    , IznosIsplate
    , Dateadd(d, (2-DatePart(dw, DatumIsplate)),DatumIsplate ) AS Ponedeljak
FROM #Isplata
)
SELECT
    Ponedeljak
    , COUNT(*) AS KolikoIsplata
    , SUM(IznosIsplate) AS UkupanIZnos
FROM Pon
GROUP BY Ponedeljak
GO

Isti princip, za Petak:

Code:

;WITH Pet AS
(
SELECT
    DatumIsplate
    , IznosIsplate
    , Dateadd(d, (-7 + 6-DatePart(dw, DatumIsplate)),DatumIsplate ) AS Petak
FROM #Isplata
)
SELECT
    Petak
    , COUNT(*) AS KolikoIsplata
    , SUM(IznosIsplate) AS UkupanIZnos
FROM Pet
GROUP BY Petak

Kljuc je dakako u WITH delu, kao i kod Bojana. Problem moze da se definise i ovako: Za bilo koji dan, prikazati datum za Ponedeljek u istoj sedmici.
Ako posmatrate 7 dana u nekoj sedmici (nije vazno koji dan pocinje sedmicu, nedelja, ponedeljak ili nesto drugo),onda se danima dodeljuju neki redni brojevi. Redni brojevi zavise od vaseg sistema, i obicno je Nedelja = 0, Ponedeljak = 1 itd. Na mom sistemu je je za ponedelja doeljen broj 2. Otuda 2 u izrazu pod DateDiff

Code:

SELECT
    DatumIsplate
    , IznosIsplate
    , Dateadd(d, (2-DatePart(dw, DatumIsplate)),DatumIsplate ) AS Ponedeljak
FROM #Isplata

Za bolje razumevanje evo ovakav kveri (trebace vam tabela koju sam definisoa na pocetku) u MS SQL. Za ostale sisteme nadam se da cete shvatiti ideju pa mzoete da napisete svoj kod:

Code:

SELECT
    DatumIsplate
    , DatePart(dw, DatumIsplate) AS RedniBrojDAna
    , ImeDana = CASE
                    WHEN DatePart(dw, DatumIsplate) = 1 THEN 'Nedelja'
                    WHEN DatePart(dw, DatumIsplate) = 2 THEN 'Ponedeljak'
                    WHEN DatePart(dw, DatumIsplate) = 3 THEN 'Utorak'
                    WHEN DatePart(dw, DatumIsplate) = 4 THEN 'Sreda'
                    WHEN DatePart(dw, DatumIsplate) = 5 THEN 'Cetvrtak'
                    WHEN DatePart(dw, DatumIsplate) = 6 THEN 'Petak'
                    WHEN DatePart(dw, DatumIsplate) = 7 THEN 'Subota'
                END
FROM #Isplata
WHERE DatumIsplate Between '2008-10-05 00:00:00.000' AND '2008-10-11 00:00:00.000'

DatumIsplate (yy-mm-dd) RedniBrojDAna ImeDana
----------------------- ------------- ----------
2008-10-05 00:00:00.000 1             Nedelja
2008-10-06 00:00:00.000 2             Ponedeljak
2008-10-07 00:00:00.000 3             Utorak
2008-10-08 00:00:00.000 4             Sreda
2008-10-09 00:00:00.000 5             Cetvrtak
2008-10-10 00:00:00.000 6             Petak
2008-10-11 00:00:00.000 7             Subota

(7 row(s) affected)

Pitanje je sada, kako iskoristiti RedniBrojDana? Sta treba dodati ili oduzeti da se uvek dobije trazeni ponedeljak, 2008-10-06 00:00:00.000?
Bice lakse ako budemo trazili recimo sredu 2008-10-08 . Dajkle, sta treba dodati ili oduzeti od svakog datuma na lsiti da bi dobili 2008-10-08?

Ocigledno je sledece:
Za nedelju, 2008-10-05 treba dodati 3 dana da bi se dobila sreda 2008-10-08
za ponedeljak, 2008-10-06 treba dodati 2 dana
za utorak, 2008-10-07 treba dodati 1 dan
za sredu, 2008-10-08 treba dodati 0 dana
za cetvratk 2008-10-09 treba oduzeti 1 dan, dodati -1
za petak 2008-10-10 treba oduzeti 2 dana = dodati -2
za subotu 2008-10-11

Uz malo probanja dolazimo do formule za racunanje broja dana koje treba dodati:

dodatak = RedniBrojDanaZatrazenidatum - RedniBrojdana(DatumIsplate)

Ako trazimo Sredu onda je RedniBrojDana = 4 a RedniBrojdana(DatumIsplate) racunamo pomocu DatePart(dw, DatumIsplate). Sve u svemu:
Evo kveri koji korak po korak objasnjava opisani postupak:

Code:

-- Query A
SELECT
    DatumIsplate
    , DatePart(dw, DatumIsplate) AS RedniBrojDAna
    , ImeDana = CASE
                    WHEN DatePart(dw, DatumIsplate) = 1 THEN 'Nedelja'
                    WHEN DatePart(dw, DatumIsplate) = 2 THEN 'Ponedeljak'
                    WHEN DatePart(dw, DatumIsplate) = 3 THEN 'Utorak'
                    WHEN DatePart(dw, DatumIsplate) = 4 THEN 'Sreda'
                    WHEN DatePart(dw, DatumIsplate) = 5 THEN 'Cetvrtak'
                    WHEN DatePart(dw, DatumIsplate) = 6 THEN 'Petak'
                    WHEN DatePart(dw, DatumIsplate) = 7 THEN 'Subota'
                END
    , RedniBrojZaZadatiDatum = 4
    , RacunanjeDodataka = '4 - ' + '(' + CAST(DatePart(dw, DatumIsplate) AS varchar(1)) + ')'
    , Dodatak = 4 - DatePart(dw, DatumIsplate)
    , Sreda = Dateadd(d, (2-DatePart(dw, DatumIsplate)),DatumIsplate )
FROM #Isplata
WHERE DatumIsplate Between '2008-10-05 00:00:00.000' AND '2008-10-11 00:00:00.000'

sto daje rezultat:

Code:

DatumIsplate            RedniBrojDAna ImeDana    RedniBrojZaZadatiDatum RacunanjeDodataka Dodatak     Sreda
----------------------- ------------- ---------- ---------------------- ----------------- ----------- -----------------------
2008-10-05 00:00:00.000 1             Nedelja    4                      4 - (1)           3           2008-10-06 00:00:00.000
2008-10-06 00:00:00.000 2             Ponedeljak 4                      4 - (2)           2           2008-10-06 00:00:00.000
2008-10-07 00:00:00.000 3             Utorak     4                      4 - (3)           1           2008-10-06 00:00:00.000
2008-10-08 00:00:00.000 4             Sreda      4                      4 - (4)           0           2008-10-06 00:00:00.000
2008-10-09 00:00:00.000 5             Cetvrtak   4                      4 - (5)           -1          2008-10-06 00:00:00.000
2008-10-10 00:00:00.000 6             Petak      4                      4 - (6)           -2          2008-10-06 00:00:00.000
2008-10-11 00:00:00.000 7             Subota     4                      4 - (7)           -3          2008-10-06 00:00:00.000

(7 row(s) affected)

Ako u poslednjoj kolni kverija 'Query A' stavite broj za ponedeljak (=2), dobijete resenje mozgalice. To je ako je zadati dan u okviru iste sedmice u kojoj se nalaze ispitivani dani. Ako je zadati datum u prethodnoj sedmici (novac za isplate mora stici u petak prethodne sedmice za isplate naredne sedmice) onda u formuli za dodavanje imate jos -7 (minus sedam). Za dve nedelje unapred bilo bi -14 sto je u stvari -2*7. Otud ono -7 u resenju za Petak. Nisam siguran da je kod Bojana u pitanju petak

Ko razume ovu mozgalicu mozi ce da resi probleme tipa 'traze se sedmicini zbirovi za neki izvestaj'. Izaberete bilo koji dan, recimo ponedeljak, da predstavlja celu sedmicu. Citaj "zbir za sedmcu koja pocinje u ponedeljak dana tog i tog'. Svaka sedmica pocinje u neki ponedeljak, pa ponedeljak moze da opise sedmicu. Moze i bilo koji drugi dan, kako vam se svidja. Samo treba nekako znati koje redne brojeve dodeljuje danima u sedmici vas sistem.

Sva je lako kad znas caku

Resenaj u drugim sistemi i dalje su dobrodosla. Ovo nije nesto sto se lako pamti i reprodukuje. Imamo gotove izraze dakle za MySQL i MS SQL 2005. Treba nam za ORACLE, POStGress i tako dalje, cisto da imamo gotov primer koji mozemo da cut/paste ako nam ikad zatreba.

Re: MOzgalica 12: Bankarske nevolje 06.10.2008. u 16:24 · pre 219 meseci #5

Evo kako ide u Accessu:

Code:

SELECT kalendar.datum,
       kalendar.danunedelji,
       kalendar.dan,
       [datum] - 7 + (4 - [danunedelji]) AS SredaPrethodneSedmice,
       [datum] + (2 - [danunedelji])     AS PoonedeljakIsteSedmice
FROM   kalendar;

Tabela Kalendar je u zakacenom primeru.

BankarskeNevolje.zip129.69k

Re: MOzgalica 12: Bankarske nevolje 07.10.2008. u 09:10 · pre 219 meseci #6

a sta ako nije bilo isplata neke nedelje a ti hoces da prikazes da je bilo 0 isplata i da je 0 iznos? :-)

Re: MOzgalica 12: Bankarske nevolje 07.10.2008. u 14:52 · pre 219 meseci #7

Citat:

a sta ako nije bilo isplata neke nedelje a ti hoces da prikazes da je bilo 0 isplata i da je 0 iznos?

Svaka cast na pitanju. Razlika izmedju profesionalca i amatera je sto profesionalc vidi dalje od resenja konkretnog pitanja. Kao kad neko kaze 'ja bih bazu da kad kliknem na artikl pokaze mi se stanje na lageru'. Amateri onda dodju na Access forum i pitaju kako da naprave formu sa list boxom. Profesionalci vide iz tog pitanja citav sistem za pracenje inventara, ulaz, izlaz robe i sve ostalo.

U realnom svetu, veoma validno pitanje. Medjutim, ne, ovo dodatno pitanje bi zakomplikovalo mozgalicu. Mozgalica je postavljena da bi u stvari pokazali kako se odredjuje 'tekuca sedmica' i' 'prosla sedmica'. Pretpostavljam da ekipa koja je u stanju da resava mozgalica je isto u stanju da doda LEFT JOIN i pokrije i tvoje pitanje... Medjutim, ako se zamislim, ima par stvari na koje treba obratiti paznju.

Trebalo bi dakle u nezavisniom kveriju izlistati sve sedmice u posmatranom periodu. Ovo nisu fiskalne sedmice iz tabele Kalendar. U tabelama tipa Kalandar se nekad sedmica oko nove godine cepa na dva dela, pa na to treba paziti. Znaci, za svaki datum iz zadatog opsega a koji nije zadat u originalnoj pitalici) izracunali bi kojoj sedmici pripada, na nacin opisan u resenju mozgalice. Za ovo nam ipak treba ili tabela Kalendar (svaki datum) ili CTE koji se razvija u oblik tabele Kalendar. Onda bi uradili GROUP BY po sedmicama => jedan red za svaku sedmicu. U resenju mozgalice izracunamo sedmicu za dana kada je bilo uplata. Sada imamo sta nam treba za LEFT JOIN - na starni vise su sve sedmice, a na strani manje (gde cemo dobiti NULLs) je resenje mozgalice. I onda COALESCE(UkupnaIsplata, 0) ce nam dati nule za one sedmice u kojima nema isplata. Mnogo posla, ali se da uraditi bez upotrebe novih trikova.

Posto su mozgalice vise akadamskog tipa, da vidimo kako i ako moze u SQL nesto za sta deluje da zahteva proceduralno programiranje, onda ostavljamo realnu biznis loguiku po strani, za trenutak.

Forum kao aplikacija na telefonu

Srodne teme