Avatar billede mzane Nybegynder
14. november 2007 - 09:13 Der er 11 kommentarer og
1 løsning

Ligge beløb sammen

Kære Eksperter,

Jeg har 3 tabeller:

"Dim_Date"
Date_ID
MonthNumber    (Indeholder månedens nr... f.eks. jan=1)
Year        (f.eks. Year=2007)
Year&Month    (Indeholder både år og månedens navn... f.eks. "2007 - Juli")

"Customer"
Customer_ID   
CustomerNumber    (Indeholder et kundenr... f.eks. "08243")
CustomerName    (Indeholder kundensnavn... f.eks. "Mortens Bilvask")

"Customer_Entry"
Entry_Date_ID
Customer_ID
OriginalAmount    (Indeholder beløb)

Det jeg ønsker er at få vist den løbende samlede sum (OriginalAmount) for hver måned...

F.eks. hvis "Customer_Entry" tabellen indeholder følgende:

Entry_Date_ID    Customer_ID    OriginalAmount
1        33        150
2        33        100
2        33        50
3        33        200
4        33        300
4        33        200
6        65        120
6        65        140
7        65        170

Så kunne jeg godt tænke mig at få følgende output, for et firma der har en bestemt CustomerNumber (ikke ID):

CustomerName    Year&Month    RunningSum
Mortens Bilvask    2007 - Jan    150
Mortens Bilvask    2007 - Feb    300    (150 + 100 + 50)
Mortens Bilvask    2007 - Mar    500    (150 + 100 + 50 + 200)
Mortens Bilvask    2007 - Apr    1000    (150 + 100 + 50 + 200 + 300 + 200)

Selv var jeg ude i noget med en subquery. Men får hele tiden dobbelt poster pga. flere beløb fra den samme Entry_Date_ID.
Jeg håber I kan komme med en løsning?
Avatar billede lorentsnv Nybegynder
14. november 2007 - 13:42 #1
Hvis du bruger SQL Server 2005, ville jeg først lavet en SQL der pivoter måneder ud som kolonner. Udenpå denne SQL, ville jeg lave en forespørgsel som akkumulerer YearToDate. Har ikke tid til at vise det nu, men ovenstående kan måske bruges som inspirationi. Hår måske tid til at se på det senere.
Avatar billede mzane Nybegynder
14. november 2007 - 14:07 #2
Det er en 2005 version jeg kører på. Har dog aldrig prøvet Pivot-funktionen... det ville være dejligt med et eksempel, for jeg er ikke helt sikker på hvad du mener med at pivoter måneder ud som kolonner?

/Mzane
Avatar billede lorentsnv Nybegynder
14. november 2007 - 14:42 #3
Jeg har lige lavet et hurtig eksempel på en tabel der indeholder fire felter:
Customer_id, Year, Month, Amount

SELECT Customer_id, Year,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM
(    SELECT Customer_id, Year, Month, Amount
    FROM dsa.dbo.Test
) p
PIVOT
(
    SUM(Amount)
    FOR Month IN
    ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt
ORDER BY customer_id, Year


Et hurtig eksempel på hvordan denne kan udbygges (foreløbig kun lavet YTD for jan, feb og marts):

SELECT Customer_id
    , Year
    , Jan = [1]
    , Feb = [1]+[2]
    , Mar = [1]+[2]+[3]
FROM
(   
    SELECT Customer_id, Year,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
    FROM
    (    SELECT Customer_id, Year, Month, Amount
        FROM dsa.dbo.Test
    ) p
    PIVOT
    (
        SUM(Amount)
        FOR Month IN
        ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS pvt
) a
Avatar billede mzane Nybegynder
14. november 2007 - 14:53 #4
Hej Lorentsnv, tusinde tak for et pænt bud/eksempel...

Men bliver der ikke et problem hvis det skal vises over f.eks. 7 år?
(Antallet af år kan variere)

Desuden skal der vel stadig laves et join eller to til Dim_Date og Customer?
Avatar billede lorentsnv Nybegynder
14. november 2007 - 14:53 #5
OBS!
Du må bruge IsNull i summeringen, ellers vil NULL values ødelegge runningsum.

SELECT Customer_id
    , Year
    , Jan = IsNull([1],0)
    , Feb = IsNull([1],0)+IsNull([2],0)
    , Mar = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)
    , Apr = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)+IsNull([4],0)
    , May = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)+IsNull([4],0)+IsNull([5],0)
--    , Jun = IsNull([1],0),IsNull([2],0),IsNull([3],0),IsNull([4],0),IsNull([5],0),IsNull([6],0)
FROM
(   
    SELECT Customer_id, Year,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
    FROM
    (    SELECT Customer_id, Year, Month, Amount
        FROM dsa.dbo.Test
    ) p
    PIVOT
    (
        SUM(Amount)
        FOR Month IN
        ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS pvt
) a
Avatar billede lorentsnv Nybegynder
14. november 2007 - 15:58 #6
Det giver ikke problem at der er flere år, dadu vil få en række for hvert år.
Det kræves at du linker/joiner sammen alle tre tabeller, noget i retning af:

SELECT CustomerName
    , Year
    , Jan = IsNull([1],0)
    , Feb = IsNull([1],0)+IsNull([2],0)
    , Mar = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)
    , Apr = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)+IsNull([4],0)
    , May = IsNull([1],0)+IsNull([2],0)+IsNull([3],0)+IsNull([4],0)+IsNull([5],0)
    , Jun = IsNull([1],0),IsNull([2],0),IsNull([3],0),IsNull([4],0),IsNull([5],0),IsNull([6],0)
FROM
(   
    SELECT CustomerName, Year,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
    FROM
    (  SELECT c.CustomerName, d.Year, d.MonthNumber, ce.OriginalAmount
        FROM Customer_Entry ce
        LEFT JOIN Dim_Date d
        ON ce.Entry_Date_ID = d.Date_ID
        LEFT JOIN Customer c
        ON ce.Customer_ID = c.Customer_ID
    ) p
    PIVOT
    (
        SUM(OriginalAmount)
        FOR MonthNumber IN
        ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS pvt
) a
Avatar billede lorentsnv Nybegynder
14. november 2007 - 15:59 #7
Rækken med ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]), forventer at MonthNumber indeholder ovenstående nummre for måneder. Ellers må du justere ovenstående.
Avatar billede mzane Nybegynder
14. november 2007 - 17:47 #8
Jeg tjekker det så snart jeg kan... er desværre ikke hjemme i aften har bare lige lånt en pc... var jo spændt på om der var noget godt og det ser sådan ud. :o)

Tak indtil videre!
Avatar billede lorentsnv Nybegynder
20. november 2007 - 09:45 #9
Har du fundet løsning på dit problem?
Avatar billede lorentsnv Nybegynder
12. december 2007 - 13:53 #10
På tide at lukke spørgsmålet?
Avatar billede mzane Nybegynder
01. oktober 2008 - 21:06 #11
Øh... det er for længe siden jeg sidst har været her... kan ikke huske hvad det blev til, men det kom da til at virke...

Lorentsnv, hvis du giver et svar, giver jeg point
Avatar billede mzane Nybegynder
19. januar 2011 - 10:20 #12
Okay... lukker tråd
Tak for Lorentsnv for forsøg på at hjælpe. Du må skrive, hvis du alligevel ønsker point, som tilbudt for 2 år siden, må du skrive.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester