[T-SQL] count met meerdere voorwaarden

[jvaneijk]

Hoi allemaal,

Ik heb een vraagje. Ik wil een query maken welke een aantal en het verschil tussen 2 datums in dagen terug geeft.
Dit is niet zo'n probleem:

SQL:

SELECT TOP ( 100 ) PERCENT
        COUNT(*) AS Qty,
        dbo.GetWorkingDays(DateRec, DateShipping) AS Days
FROM    dbo.tbl_Orders
WHERE   ( Cust_ID = 4 )
        AND ( dbo.GetWorkingDays(DateRec, DateShipping) >= 0 )
        --AND ( dbo.GetWorkingDays(DateRec, DateShipping) <= 20 )
        AND ( DateRec >= CONVERT(DATETIME, '2008-10-01 00:00:00', 102) )
        AND ( ProductGroupID <> 22 )
GROUP BY dbo.GetWorkingDays(DateRec, DateShipping)
ORDER BY Days

dit geeft dus een tabel terug als onderstaand

code:

Qty           Days
1753    12
2150    13
1638    14
1356    15
783 16
1309    17
803 18

Nu wil ik dus expliciet kunnen zeggen aantal voor 1 dag, aantal voor 2 dagen, aantal voor 3dagen enz tot en met 20 en daarna moet hij het aantal weergeven wat meer dan 20 dagen in omloop is.

Ik heb gezocht op google maar kon niets vinden, ook mede doordat ik geen idee heb waarop ik moet zoeken. Mijn volgende en laatste idee is gebruik maken van een union of beter gezegd 20 unions
Daar zie ik een beetje tegenop aangezien het niet echt netjes en mooi is. Maar als er geen andere manier is is het natuurlijk wel te doen.

Nu is mijn vraag dus heeft iemand enig idee hoe dit op te lossen is zonder unions.

[Niemand_Anders]

Een top 100%. Die vind ik leuk ;-)

Wat betreft je probleem. Een simpele oplossing is door een teller tabel aan te maken. En aan records met id 1t/m 20 je resultaten te joinen.. (join on GetWorkingDays=Counters.ID).

Als je de join hebt verander dan 'WHERE' even in 'AND' zodat je filter clausule onderdeel wordt van de join restricties.

[ Voor 81% gewijzigd door Niemand_Anders op 23-03-2009 14:25 ]

[jvaneijk]

Niemand_Anders schreef op maandag 23 maart 2009 @ 14:19:
Een top 100%. Die vind ik leuk ;-)

I know moest even snel MS SQL management studio maakt die shit ervan heb geen zin gehad om dat weg te halen.

[P_de_B]

Niemand_Anders schreef op maandag 23 maart 2009 @ 14:19:
Een top 100%. Die vind ik leuk ;-)

Dat moet je doen als je een ORDER BY wilt hebben in de definitie van een view

---

Ik begrijp niet precies wat de gewenste output is, kun je daar een voorbeeldje van geven?

[jvaneijk]

P_de_B schreef op maandag 23 maart 2009 @ 14:24:
[...]

Dat moet je doen als je een ORDER BY wilt hebben in de definitie van een view

---

Ik begrijp niet precies wat de gewenste output is, kun je daar een voorbeeldje van geven?

Hij komt idd uit de sleur en pleur omgeving van de view Zoals ik zei het moest even snel gebeuren

code:

Aantal     Dagen
2             1
3             2
7             3
600          4
..             ..
4043        19
10456       20+

Dit is wat het moet worden. En dat gaat dan later in een grafiek. want kan het ook zelf doen maar het principe is om het geautomatiseerd te laten gaan in SSRS

[ Voor 28% gewijzigd door jvaneijk op 23-03-2009 14:48 ]

[jvaneijk]

Niemand_Anders schreef op maandag 23 maart 2009 @ 14:19:
Een top 100%. Die vind ik leuk ;-)

Wat betreft je probleem. Een simpele oplossing is door een teller tabel aan te maken. En aan records met id 1t/m 20 je resultaten te joinen.. (join on GetWorkingDays=Counters.ID).

Als je de join hebt verander dan 'WHERE' even in 'AND' zodat je filter clausule onderdeel wordt van de join restricties.

Ben ik nou een grote mongool of ben jij onduidelijk. Ik snap er eerlijk gezegt niets van wat je bedoeld

[Niemand_Anders]

Ik heb nog nooit bij een order by een 'top' hoeven te gebruiken. Een top 100% (ofwel alle resultaten) is erg zwaar. Wees dan verstandig en sorteer dan de resultaten van de view en niet de query in de view.
select * from WorkkingDaysView order by days is vele malen sneller dan een top 100%.

Op een development database zul je misschien het verschil niet echt merken. Maar als je order tabel miljoenen records bevat merk je het wel. Net zoals een join met een where clause gebruiken. De join where combinatie stampt uit de tijd dat join nog onbekend waren en men 'from user, orders where users.userid=orders.userid and ....' moest gebruiken. Echter een where wordt pas uitgevoerd nadat de twee tabellen zijn samen gevoegd, terwijl de restricties op de join zelf worden uitgevoerd voor de join waardoor de twee recordsets minder groot zijn en dus sneller te joinen.

[P_de_B]

offtopic:
Ik denk dat de reden van de top 100% wel duidelijk is, en ik denk dat de TS ook wel weet dat dit in 'normale' queries niet gebruikt gaat worden. Dit is gewoon standaard gedrag van de management studio

Op een development database zul je misschien het verschil niet echt merken. Maar als je order tabel miljoenen records bevat merk je het wel. Net zoals een join met een where clause gebruiken. De join where combinatie stampt uit de tijd dat join nog onbekend waren en men 'from user, orders where users.userid=orders.userid and ....' moest gebruiken. Echter een where wordt pas uitgevoerd nadat de twee tabellen zijn samen gevoegd, terwijl de restricties op de join zelf worden uitgevoerd voor de join waardoor de twee recordsets minder groot zijn en dus sneller te joinen.

Ik weet niet precies wat je hier wilt zeggen, maar dit soort optimalisaties wordt echt wel door de query governor uitgevoerd hoor.

---

@TS, kun je nog eens een voorbeeld van de gewenste output laten zien?

[ Voor 55% gewijzigd door P_de_B op 23-03-2009 14:43 ]

[jvaneijk]

Niemand_Anders schreef op maandag 23 maart 2009 @ 14:36:
Ik heb nog nooit bij een order by een 'top' hoeven te gebruiken. Een top 100% (ofwel alle resultaten) is erg zwaar. Wees dan verstandig en sorteer dan de resultaten van de view en niet de query in de view.
select * from WorkkingDaysView order by days is vele malen sneller dan een top 100%.

Op een development database zul je misschien het verschil niet echt merken. Maar als je order tabel miljoenen records bevat merk je het wel. Net zoals een join met een where clause gebruiken. De join where combinatie stampt uit de tijd dat join nog onbekend waren en men 'from user, orders where users.userid=orders.userid and ....' moest gebruiken. Echter een where wordt pas uitgevoerd nadat de twee tabellen zijn samen gevoegd, terwijl de restricties op de join zelf worden uitgevoerd voor de join waardoor de twee recordsets minder groot zijn en dus sneller te joinen.

Tis maar goed dan dat er geen join inzit of inkomt. Wat betreft de top 100% die gaat er netjes uit als ik hem optimaliseer. Het voornaamste is nu mijn probleem op zien te lossen zodat ik niet 20 unions hoef te gebruiken want als we het over performance hebben kan die er ook nog wel even bij.

[jvaneijk]

P_de_B schreef op maandag 23 maart 2009 @ 14:41:

offtopic:
Ik denk dat de reden van de top 100% wel duidelijk is, en ik denk dat de TS ook wel weet dat dit in 'normale' queries niet gebruikt gaat worden. Dit is gewoon standaard gedrag van de management studio

@TS, kun je nog eens een voorbeeld van de gewenste output laten zien?

Zo goed als jij hebt geedit in jouw bericht zo heb ik dat ook gedaan check hierboven. ^^^^

code:

Aantal     Dagen
2          1
3          2
7          3
600        4
..         ..
4043       19
10456      20+

[ Voor 14% gewijzigd door jvaneijk op 23-03-2009 14:49 ]

[P_de_B]

Ik denk dat je toch 1 union nodig hebt

SELECT COUNT(*), CountWorkingDays as Days
[..]
WHERE CountWorkingDays <= 20
UNION

SELECT COUNT(*), '20+' as Days
[..]
WHERE CountWorkingDays > 20

[jvaneijk]

P_de_B schreef op maandag 23 maart 2009 @ 14:49:
Ik denk dat je toch 1 union nodig hebt

SELECT COUNT(*), CountWorkingDays as Days
[..]
WHERE CountWorkingDays <= 20
UNION

SELECT COUNT(*), '20+' as Days
[..]
WHERE CountWorkingDays > 20

Das idd ook een oplossing. Maar het probleem is dus het zou voor kunnen komen dat 5dagen geen aantal heeft. Die moet ik toch tonen maar dan met aantal 0
Daarom dat ik ook mijn blik al zag dwalen en dat ik 20 unions voor ogen zag

Maar er is dus geen andere makkelijkere manier om dit op te lossen neem ik aan?
want dan kunnen ze me wat en doe ik het gewoon zo.

[P_de_B]

Als je wel records wilt opnemen voor aantallen waarvoor eigenlijk geen records zijn is daar wel een handig trucje voor. Je moet joinen met een tabel die maar 1 kolom heeft: 'Counter'. Deze kolom vul je met waarde 1 t/m 20 (of meer, deze tabel kun je anders ook wel gebruiken) en vanaf deze tabel join je naar bovenstaande query

Zoek maar eens op Tally table.

[Acid__Burn]

Wat je ook kunt doen is een Sub Select:

SQL:

select tt.*
from
(select t1.orderid, t1.days
from t1
group by days) tt

Daar krijg je een lijstje uit:

1 5
2 5
3 10
4 15
5 20

Daarna doe je ongeveer dit:

SQL:

select tt.days, count(tt.days)
from
(select t1.orderid, t1.days
from t1
group by t1.days) tt
group by tt.days
order by tt.days asc

Is dit ongeveer wat je bedoelde?

[ Voor 8% gewijzigd door Acid__Burn op 23-03-2009 15:17 . Reden: code ]

[Niemand_Anders]

jvaneijk schreef op maandag 23 maart 2009 @ 14:41:

Tis maar goed dan dat er geen join inzit of inkomt. Wat betreft de top 100% die gaat er netjes uit als ik hem optimaliseer. Het voornaamste is nu mijn probleem op zien te lossen zodat ik niet 20 unions hoef te gebruiken want als we het over performance hebben kan die er ook nog wel even bij.

Je maakt een tabel 'Counters'. Dit tabel heeft slechts 1 veld te weten ID. Vervolgens plaats je in deze tabel de sequence 1 t/m 100.

Behalve de top 100 veranderd je query verder niet zoveel. In feite krijg je dan een query als:

SQL:

SELECT c.ID, Count(o.*) FROM Counters C LEFT OUTER JOIN tbl_Orders o on c.ID=GetWorkingDays 
AND (c.ID BETWEEN 1 AND 20) AND (o.Cust_id=4) AND (o.ProductGroupID <> 22) 
AND ... GROUP BY ...

Je hebt dan altijd een lijst met dagen van 1 t/m 20. Op dagen dat er niets terug wordt gegevens is de count 0.


Hopelijk is het nu mijn oplossing iets duidelijker.

[ Voor 1% gewijzigd door Niemand_Anders op 23-03-2009 15:05 . Reden: Query formatting aangepast zodat deze niet als 1 lange regel wordt getoond. ]

[P_de_B]

Niemand_Anders schreef op maandag 23 maart 2009 @ 15:00:
[...]


Je maakt een tabel 'Counters'. Dit tabel heeft slechts 1 veld te weten ID. Vervolgens plaats je in deze tabel de sequence 1 t/m 100.

Behalve de top 100 veranderd je query verder niet zoveel. In feite krijg je dan een query als:

SQL:

SELECT GetWorkingDays, Count(o.*) FROM Counters C LEFT OUTER JOIN tbl_Orders o on c.ID=GetWorkingDays AND (c.ID BETWEEN 1 AND 20)
AND (o.Cust_id=4) AND (o.ProductGroupID <> 22) AND ...
GROUP BY ...

Je hebt dan altijd een lijst met dagen van 1 t/m 20. Op dagen dat er niets terug wordt gegevens is de count 0.


Hopelijk is het nu mijn oplossing iets duidelijker.

Maar dan heb je 20+ dagen nog niet, daarvoor is denk ik de oplossing met een union de meest handige.

[jvaneijk]

Niemand_Anders schreef op maandag 23 maart 2009 @ 15:00:
[...]


Je maakt een tabel 'Counters'. Dit tabel heeft slechts 1 veld te weten ID. Vervolgens plaats je in deze tabel de sequence 1 t/m 100.

Behalve de top 100 veranderd je query verder niet zoveel. In feite krijg je dan een query als:

SQL:

SELECT GetWorkingDays, Count(o.*) FROM Counters C LEFT OUTER JOIN tbl_Orders o on c.ID=GetWorkingDays AND (c.ID BETWEEN 1 AND 20)
AND (o.Cust_id=4) AND (o.ProductGroupID <> 22) AND ...
GROUP BY ...

Je hebt dan altijd een lijst met dagen van 1 t/m 20. Op dagen dat er niets terug wordt gegevens is de count 0.


Hopelijk is het nu mijn oplossing iets duidelijker.

Hartelijk dank voor de verduidelijking bovenstaande poster had het ook al verduidelijkt. Ik ga even kijken wat ik ga nemen. Die counter is leuk maar die enkele unions is het makkelijkst en dan zoeken ze het maar uit met die lege dagen. Komt toch nooit voor.. tenminste in de geschiedenis teruggaand tot 2001 is het nog nooit voorgekomen