Avatar billede cjep Nybegynder
15. januar 2009 - 21:25 Der er 14 kommentarer og
2 løsninger

Problem med Sub total

Hej,
Jeg har lavet en kontoopgørelse, der sammentæller en subtotal pr. userid og laver en grand total. Det sidste virker ikke, og det kan jeg også undvære.

Det mærkelige er at subtotal fungerer fint - for det meste - noglegange bliver den dog sat til 0. Jeg kan simpelthen ikke se hvorfor. Fremgangsmåden er fundet efter noget research på nettet, men jeg er åben for alt der kan det samme...

SQL-script
----------
SELECT    id, username, is_deleted, is_freeOfCharge, is_invoiced, price, CASE WHEN id =
                          (SELECT    TOP 1 id
                            FROM          clj_calendar
                            WHERE      username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0
                            ORDER BY id DESC) THEN
                          (SELECT    SUM(price)
                            FROM          clj_calendar
                            WHERE      id <= O.id AND username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)
                                                  AND end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Sub Total', CASE WHEN id =
                          (SELECT    TOP 1 id
                            FROM          clj_calendar
                            ORDER BY username DESC) THEN
                          (SELECT    SUM(price)
                            FROM          clj_calendar
                            WHERE      is_FreeOfCharge = 0 AND is_invoiced =0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND end_time <= CONVERT(DATETIME,
                                                  '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Grand Total', location, start_time, end_time
FROM        clj_calendar AS O
WHERE    (is_freeOfCharge = 0 AND is_invoiced = 0) AND (start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (end_time <= CONVERT(DATETIME,
                      '2009-01-31 23:59:00', 102))
ORDER BY username, 'Sub Total'


RESULTAT (jeg har fjerne datofelterne, da de fylder for meget):

id            is_deleted    is_invoiced    Sub Total    Location
      username  is_freeOfcharge      price      Grand total
   
67    0004    0    0    0    0    0    0    3
68    0004    0    0    0    0    0    0    3
69    0004    0    0    0    0    0    0    3
70    0004    0    0    0    0    0    0    3
71    0004    0    0    0    0    0    0    3
72    0004    0    0    0    0    0    0    3
56    0015    0    0    0    40    40    0    1
109    0018    0    0    0    40    40    0    1
35    0023    0    0    0    50    0    0    1
36    0023    0    0    0    50    0    0    1
37    0023    0    0    0    50    150    0    1
38    0024    0    0    0    50    0    0    1
97    0024    0    0    0    0    50    0    3
14    0084    0    0    0    90    90    0    1
59    0091    0    0    0    60    0    0    1
60    0091    0    0    0    70    130    0    1
32    0095    0    0    0    40    0    0    1
108    0095    0    0    0    20    0    0    1
104    0114    0    0    0    40    0    0    1
10    0114    0    0    0    60    0    0    1
107    0114    0    0    0    50    150    0    1
61    0136    0    0    0    80    80    0    1
47    0145    0    0    0    30    0    0    1
92    0145    0    0    0    40    70    0    1
43    0165    0    0    0    60    0    0    1
45    0165    0    0    0    70    0    0    1
50    0173    0    0    0    40    0    0    1
51    0173    0    0    0    50    90    0    1
12    0184    0    0    0    50    50    0    1
93    0185    0    0    0    0    0    0    5
18    0187    0    0    0    60    0    0    1
19    0187    0    0    0    50    0    0    1
20    0187    0    0    0    50    0    0    1
21    0187    0    0    0    50    0    0    1
102    0187    0    0    0    20    0    0    1
105    0191    0    0    0    50    50    0    1
7    0192    0    0    0    60    60    0    1
106    0193    0    0    0    50    50    0    1
100    0194    0    0    0    40    0    0    1
27    0194    0    0    0    50    0    0    1
101    0194    0    0    0    40    130    0    1
58    0196    0    0    0    100    100    0    1
33    0205    0    0    0    40    40    0    1
63    0207    0    0    0    30    0    0    1
112    0207    0    0    0    40    70    0    1
54    0209    0    0    0    20    0    0    1
55    0209    0    0    0    70    90    0    1
52    0212    0    0    0    50    0    0    1
Avatar billede cjep Nybegynder
15. januar 2009 - 21:26 #1
Bemærk fx username 0187 og 0165 ikke giver nogen subtotal. Hvorfor?
Avatar billede cjep Nybegynder
15. januar 2009 - 21:27 #2
prøver lige at indsætte et eksempel med datofelter også:

59    0091    0    0    0    60    0    0    1    2009-01-10 12:00:00.000    2009-01-10 17:00:00.000
60    0091    0    0    0    70    130    0    1    2009-01-14 14:00:00.000    2009-01-14 20:00:00.000
32    0095    0    0    0    40    0    0    1    2009-01-08 19:00:00.000    2009-01-08 22:00:00.000
108    0095    0    0    0    20    0    0    1    2009-01-22 18:00:00.000    2009-01-22 19:00:00.000
104    0114    0    0    0    40    0    0    1    2009-01-14 20:00:00.000    2009-01-14 23:00:00.000
10    0114    0    0    0    60    0    0    1    2009-01-11 13:00:00.000    2009-01-11 18:00:00.000
107    0114    0    0    0    50    150    0    1    2009-01-23 12:00:00.000    2009-01-23 16:00:00.000
61    0136    0    0    0    80    80    0    1    2009-01-06 09:00:00.000    2009-01-06 16:00:00.000
47    0145    0    0    0    30    0    0    1    2009-01-18 17:00:00.000    2009-01-18 19:00:00.000
92    0145    0    0    0    40    70    0    1    2009-01-30 18:00:00.000    2009-01-30 21:00:00.000
43    0165    0    0    0    60    0    0    1    2009-01-18 12:00:00.000    2009-01-18 17:00:00.000
45    0165    0    0    0    70    0    0    1    2009-01-25 12:00:00.000    2009-01-25 18:00:00.000
50    0173    0    0    0    40    0    0    1    2009-01-18 09:00:00.000    2009-01-18 12:00:00.000
51    0173    0    0    0    50    90    0    1    2009-01-25 18:00:00.000    2009-01-25 22:00:00.000
12    0184    0    0    0    50    50    0    1    2009-01-04 17:00:00.000    2009-01-04 21:00:00.000
93    0185    0    0    0    0    0    0    5    2009-01-19 00:00:00.000    2009-01-26 01:00:00.000
Avatar billede janus_007 Nybegynder
15. januar 2009 - 23:20 #3
Findes ID'en her: SELECT    TOP 1 id
                            FROM          clj_calendar
                            WHERE      username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0
                            ORDER BY id DESC


Hvad får du hvis du blot beregner 0187 manuelt, altså blot som select sum(....
Avatar billede cjep Nybegynder
16. januar 2009 - 11:27 #4
En select sum(price) as total ... alene på 0187 giver totalen: 230. Det svarer til summen af de linier der også vises i min query, men subtotalen tælles blot ikke op.

Har lige slanket mit script ved at skære "Grand total" delen væk. Resultatet er det samme:

SELECT    id, username, is_deleted, is_freeOfCharge, is_invoiced, price, CASE WHEN id =
                          (SELECT    TOP 1 id
                            FROM          clj_calendar
                            WHERE      username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0
                            ORDER BY id DESC) THEN
                          (SELECT    SUM(price)
                            FROM          clj_calendar
                            WHERE      id <= O.id AND username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)
                                                  AND end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Sub Total'
                                               
FROM        clj_calendar AS O
WHERE    (is_freeOfCharge = 0 AND is_invoiced = 0) AND (start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (end_time <= CONVERT(DATETIME,
                      '2009-01-31 23:59:00', 102))
ORDER BY username, 'Sub Total'
Avatar billede berglund Nybegynder
23. januar 2009 - 12:37 #5
Hej cjep.

Jeg har lidt svært ved at følge med i dit script, så jeg håber at det er ok at jeg prøve at angribe det på ny? Jeg har lige et spørgsmål først.

1) Hvorfor denne CASE...END inden din sub-total-subquery?

CASE WHEN id =
                          (SELECT    TOP 1 id
                            FROM          clj_calendar
                            WHERE      username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0
                            ORDER BY id DESC) THEN
                          (SELECT    SUM(price)
                            ...
/thomas
Avatar billede berglund Nybegynder
23. januar 2009 - 12:46 #6
Jeg forstår det sådan at du er kun interesset i data som opfylder følgende krav:

WHERE    (is_freeOfCharge = 0 AND is_invoiced = 0) AND (start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (end_time <= CONVERT(DATETIME,
                      '2009-01-31 23:59:00', 102))

Dvs. virker følgende ikke (jeg ved godt at det her giver en subtotal på hver linie, men jeg skal bare vide at det her er ok, foreløbig)?

SELECT O.id, O.username, O.price (
SELECT SUM(price)
FROM
clj_calendar AS O2
WHERE
O2.username = O.username AND O2.is_freeOfCharge = 0 AND O2.is_invoiced = 0 AND O2.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)
                                                  AND O2.end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102))
   
) as 'subTotal'
FROM
clj_calendar AS O

WHERE    (O.is_freeOfCharge = 0 AND O.is_invoiced = 0) AND (O.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (O.end_time <= CONVERT(DATETIME,
                      '2009-01-31 23:59:00', 102))


Jeg gætter på at der er noget forvirring vedrørende de manglende tabel-aliasser. Kan du teste det her foreløbig?
/thomas
Avatar billede berglund Nybegynder
23. januar 2009 - 13:31 #7
Det slår mig lige at man også kan lave SQL-sætningen som et JOIN.
Altså først udregner med totalsummerne, inkl. maxId pr. bruger og så joiner man det til den oprindelige datatabel (her som tabel-alias "T"):

SELECT
    O.id,
    O.username,
    O.price,
    COALESCE(T.subtotal) as 'subtotal'
FROM
    clj_calendar O LEFT JOIN
    (


    SELECT
        MAX(o2.id) as 'MaxId', o2.username, SUM(o2.price) AS 'subtotal'
    FROM
      clj_calendar AS O2
    WHERE
      O2.is_freeOfCharge = 0
      AND O2.is_invoiced = 0
      AND O2.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)
      AND O2.end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102))
    GROUP BY o2.username

        ) T
    ON T.Maxid = O.id

WHERE   
    (O.is_freeOfCharge = 0 AND O.is_invoiced = 0)
    AND (O.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
    AND (O.end_time <= CONVERT(DATETIME,'2009-01-31 23:59:00', 102))

ORDER BY O.username ASC, O.id ASC

Jeg har ikke testet sætningen, så jeg kan ikke afvise at der er kommafejl el. lign.
/Thomas
Avatar billede berglund Nybegynder
23. januar 2009 - 13:32 #8
COALESCE(T.subtotal) as 'subtotal' skal være:
COALESCE(T.subtotal, 0) as 'subtotal'


sorry!
/thomas
Avatar billede cjep Nybegynder
24. januar 2009 - 14:31 #9
Hej Thomas .. jeg har prøvet dit første eksempel. Det fejler med:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.  ... den fejler ved anden SELECT sætning.

Det andet eksempel giver mig følgende (efter din rettelse):
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'GROUP'.
Avatar billede cjep Nybegynder
24. januar 2009 - 14:38 #10
1) Hvorfor denne CASE...END inden din sub-total-subquery?

Jeg har jo hentet scriptet på et site, men jeg forstår det sådan at for hver gang id'en skifter i første gennemløb, skal der afsluttes med en subtotal. Eller omvendt, for hvert id, gennemløbes tabellen igen og der summeres til subtotal hvis id'en er den samme.
Avatar billede berglund Nybegynder
24. januar 2009 - 19:03 #11
CASE .. END... igen.

Jeg er enig. Det opdagede jeg da jeg var ved at konstruere mit svar. Tror det er - performancemæssigt er meget dyrere end at lave den løsning som jeg har lavet. Det kan du jo lige tjekke.
Avatar billede berglund Nybegynder
24. januar 2009 - 19:04 #12
Jeg har rettet fejlen. Der var en parantes for meget inden "GROUP BY"

Prøv i stedet for:

SELECT
    O.id,
    O.username,
    O.price,
    COALESCE(T.subtotal,0) as 'subtotal'
FROM
    clj_calendar O LEFT JOIN
    (
    SELECT
        MAX(o2.id) as 'MaxId', o2.username, SUM(o2.price) AS 'subtotal'
    FROM
      clj_calendar AS O2
    WHERE
      O2.is_freeOfCharge = 0
      AND O2.is_invoiced = 0
      AND O2.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)
      AND O2.end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)
      GROUP BY o2.username

        ) T
    ON T.Maxid = O.id

WHERE   
    (O.is_freeOfCharge = 0 AND O.is_invoiced = 0)
    AND (O.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
    AND (O.end_time <= CONVERT(DATETIME,'2009-01-31 23:59:00', 102))
Avatar billede berglund Nybegynder
24. januar 2009 - 19:06 #13
Nå, nu ser jeg at jeg har rettet en "forkert" fejl. Jeg vil også helst have at du bruger det sidste forslag hvis det er brugbart.
Avatar billede cjep Nybegynder
24. januar 2009 - 22:35 #14
Den er der ikke endnu, scriptet kører fint, men giver følgende resultat hvor fx 0004, 0191, 0114
Avatar billede cjep Nybegynder
24. januar 2009 - 22:36 #15
Argh, kom til at afvise. Det var blot sorteringen der forvirrede mig. Tilføjede en ORDER BY O.username, 'subtotal' ... så kom det til at stå rigtigt.

Thomas, smid lige et svar igen så får du pointene - og tusind tak for hjælpen!

/Claus
Avatar billede cjep Nybegynder
24. januar 2009 - 22:38 #16
x
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