[MSSQL] Ingewikkelde query

[TereZz]

Beste forumleden,

Deze query heeft me al meerdere dagen gekost. Ben er bijna, maar het laatste stukje wil maar niet.

Zie hier een uitkomst van onderstaande querie (total zit er nog niet bij)



En hier de query:

SQL:

select sCon.CONSIGNMENT_SEGNR AS ConsignmentID,
    lCon.PICKUP_DISTRICT_NAME_SEGNR AS PickupDistrictID, 
    lCon.DELIVERY_DISTRICT_NAME_SEGNR AS DeliveryDistrictID, 
    IIF(DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME, 'Y', 'N') AS OnTime, 
    DeliveryTypeID = 
        CASE 
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 1 THEN '1'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) > sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 1 THEN '2'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 0 THEN '3'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) > sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 0 THEN '4'
        END,
    sTimePickup.TimeID AS PickupTimeID,
    sTimeExp.TimeID AS Exp_DeliveryTimeID,
    sTimeDelivery.TimeID AS DeliveryTimeID,
    Amount = '1'
    Total = (SELECT Price FROM DELIVERY_PRICE WHERE FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS)
    --Total = (SELECT SUM(Price) FROM DELIVERY_PRICE WHERE FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS GROUP BY Price)    /*Total*/

from SAT_CONSIGNMENT AS sCon
INNER JOIN LINK_CONSIGNMENT AS lCon
    ON sCon.CONSIGNMENT_SEGNR=lCon.CONSIGNMENT_SEGNR
INNER JOIN ABC_Star.dbo.DimTime AS sTimePickup
    ON sTimePickup.Datetime=sCon.PICKUP_TIME
INNER JOIN ABC_Star.dbo.DimTime AS sTimeExp
    ON sTimeExp.Datetime=sCon.SCHEDULED_PICKUP_TIME
INNER JOIN LINK_PARCEL_OF_CONSIGNMENT AS lPoCon
    ON lPoCon.CONSIGNMENT_SEGNR=sCon.CONSIGNMENT_SEGNR
INNER JOIN SAT_PARCEL_OF_CONSIGNMENT AS sPoCon
    ON sPoCon.PARCEL_OF_CONSIGNMENT_SEGNR=lPoCon.PARCEL_OF_CONSIGNMENT_SEGNR
INNER JOIN ABC_Star.dbo.DimTime AS sTimeDelivery
    ON sTimeDelivery.Datetime=sPoCon.DELIVERY_TIME

--  inner join HUB_CONSIGMENT as hcon on hcon.CONSIGNMENT_NR = lcon.CONSIGNMENT_SEGNR
--GROUP BY sCon.CONSIGNMENT_SEGNR, lCon.PICKUP_DISTRICT_NAME_SEGNR, lCon.DELIVERY_DISTRICT_NAME_SEGNR, SAT_CONSIGNMENT.EXP_DURATION, 
--GROUP BY sCon.CONSIGNMENT_SEGNR, IIF(DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME, 'Y', 'N') , lCon.PICKUP_DISTRICT_NAME_SEGNR, lCon.DELIVERY_DISTRICT_NAME_SEGNR, sTimePickup.TimeID, sTimeExp.TimeID, sTimeDelivery.TimeID, sPoCon.WEIGHT_OF_CONSIGNMENT, sCon.EXPRESS_DELIVERY

ORDER BY ConsignmentID, OnTime

Regel 4(OnTime) hoeft niet weergegeven te worden, is enkel op het te versimpelen.

Probleem 1:
Het doel is om, indien er meerdere records met hetzelfde ConsigmentID is, de dubbele eruit gefilterd worden, en alleen de eerste zichtbaar is. Bij voorkeur ook diegene die de waarde 'N' heeft in het kolom OnTime. Hoe is dit te doen, want alles in een GROUP BY lukt me niet. (ook meerdere subqueries geprobeerd icm WITH).

Probleem 2:
Het totaal omzet moet per Consigment zichtbaar zijn, en niet per record. Dit is nu het geval met 'Total'.

Kunnen jullie me uit de brand helpen??

EDIT:
Het resultaat moet dus als volgt zijn:
Kolommen
ConsigmentID (UNIEK!), pickupDistrictID, DeliveryDistrictID, OnTime(optioneel), Deliverytype(optioneel), DeliveryTypeID, PickupTimeID, Exp_DeliveryTimeID, DeliveryTimeID(optioneel, bij voorkeur het hoogste nummer), Amount (Dit blijft '1' per record/consigmentID) en Total, wat het Total is per consigment (wat nu dubbele records geeft)

[ Voor 5% gewijzigd door TereZz op 01-11-2016 18:39 ]

[NMe]

Wat is "alleen de eerste?" Waarop baseer je dat? ID? Een datumveldje? Iets met een min/max?

Hoe dan ook: GROUP BY combineren met de juiste HAVING op basis van het antwoord op de vraag hierboven en je bent er, lijkt me?

[TereZz]

Heel lelijk. Ik sorteer op OnTime. Hierdoor zie ik als eerst, indien aanwezig, diegene met waarde 'N', en anders 'Y'. De rest kan weg.

Ik heb een GROUP BY geprobeerd, maar door de aggregate-functies moet ik zo'n beetj eheel de SELECT statement in de GROUP BY krijgen, wat me helaas niet lukt.

[MrMonkE]

distinct gaat niet helpen?

[NMe]

TereZz schreef op dinsdag 01 november 2016 @ 13:51:
Heel lelijk. Ik sorteer op OnTime. Hierdoor zie ik als eerst, indien aanwezig, diegene met waarde 'N', en anders 'Y'. De rest kan weg.

Ik heb een GROUP BY geprobeerd, maar door de aggregate-functies moet ik zo'n beetj eheel de SELECT statement in de GROUP BY krijgen, wat me helaas niet lukt.

...omdat?

Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

MrMonkE schreef op dinsdag 01 november 2016 @ 13:52:
distinct gaat niet helpen?

Hoe zie je dat voor je?

[ Voor 13% gewijzigd door NMe op 01-11-2016 13:53 ]

[Verwijderd]

MrMonkE schreef op dinsdag 01 november 2016 @ 13:52:
distinct gaat niet helpen?

Nee, die pakt willekeurig maar een enkel record.

[juhet]

je kunt ook ROW_NUMBER() OVER(PARTITION BY ConsigmentID ORDER BY OnTime) As RN toevoegen en vervolgens alles met RN = 1 selecteren

[ Voor 4% gewijzigd door juhet op 01-11-2016 13:55 ]

[RobIII]

Dus je zoekt een groupwise min/max?


Ander zet er even de gewenste output bij; dan zien we (beter) waar je naar toe wil.

[ Voor 79% gewijzigd door RobIII op 01-11-2016 13:57 ]

[damouzer]

Verwijderd schreef op dinsdag 01 november 2016 @ 13:54:
[...]


Nee, die pakt willekeurig maar een enkel record.

Eerst sorteren en dan unique of zoals hierboven distinct?

[NMe]

juhet schreef op dinsdag 01 november 2016 @ 13:55:
je kunt ook ROW_NUMBER() OVER(PARTITION BY ConsigmentID ORDER BY OnTime) As RN toevoegen en vervolgens alles met RN = 1 selecteren

....waarna je alsnog geen totalen hebt.

Verwijderd schreef op dinsdag 01 november 2016 @ 13:54:
[...]

Nee, die pakt willekeurig maar een enkel record.

Nee, distinct zorgt ervoor dat alle resultaten die exact hetzelfde zijn gecombineerd worden tot één rij in je result set. Is één kolom anders, dan krijg je dus ook met distinct gewoon twee verschillende rijen terug.

[ Voor 41% gewijzigd door NMe op 01-11-2016 13:58 ]

[juhet]

NMe schreef op dinsdag 01 november 2016 @ 13:56:
[...]

....waarna je alsnog geen totalen hebt.

dat hoeft ook niet toch?

TereZz schreef op dinsdag 01 november 2016 @ 13:51:
Heel lelijk. Ik sorteer op OnTime. Hierdoor zie ik als eerst, indien aanwezig, diegene met waarde 'N', en anders 'Y'. De rest kan weg.

[ Voor 15% gewijzigd door juhet op 01-11-2016 13:58 ]

[NMe]

juhet schreef op dinsdag 01 november 2016 @ 13:57:
[...]

dat hoeft ook niet toch?

[...]

Topicstart gelezen?

[juhet]

NMe schreef op dinsdag 01 november 2016 @ 13:59:
[...]

Topicstart gelezen?

Deels

Om probleem 1 en 2 tegelijk op te lossen is een group by inderdaad the way to go

[Wicked-Game]

Probleem 1 geeft sowieso een probleem omdat deliverytimeID bij een zelfde ConsigmentID verschillend is. Die zou ik dan ook weglaten in je query als die niet noodzakelijk is. Daardoor kan je puur op basis van N filter en Max het als het goed is recht zetten.

[TereZz]

Klopt, deliverytimeID is ook niet nodig (of alleen de MAX hiervan)

[NMe]

TereZz schreef op dinsdag 01 november 2016 @ 14:04:
Klopt, deliverytimeID is ook niet nodig (of alleen de MAX hiervan)

Simpel dus: niet deliverytimeID selecteren maar de MAX ervan, COUNT/SUM (doorstrepen wat niet van toepassing is) gebruiken voor je totalen en je bent er toch?

[TereZz]

NMe schreef op dinsdag 01 november 2016 @ 14:06:
[...]

Simpel dus: niet deliverytimeID selecteren maar de MAX ervan, COUNT/SUM (doorstrepen wat niet van toepassing is) gebruiken voor je totalen en je bent er toch?

Precies dit! Maar dan weer het probleem dat ik niet alles in de groupby krijg, door die CASE in de SELECT. Hoe moet de GROUP BY eruit zien?
Jammer dat ik de alliassen (AS) uit de SELECT niet kan gebruiken in de GROUP BY

[ Voor 8% gewijzigd door TereZz op 01-11-2016 14:09 ]

[KabouterSuper]

Volgens mij wil je gewoon per ConsignmentID de som van bepaalde kolommen (Amount) en de eerste waarde van andere kolommen (met een custom sortering, ik gok DeliveryTime en onTime).
De som-functie is eenvoudig en werkt in alle SQL dialecten. Let op dat je waarschijnlijk alleen per ConsignmentID wil grouperen; andere kolommen lijken mij niet logisch.

Voor de eerste waarde met custom sortering ben je erg afhankelijk van het sql-dialect. In oracle kan je dit met groepsfuncties gemakkelijk doen:

code:

SELECT MAX([column1]) KEEP (DENSE_RANK FIRST ORDER BY [column2/expression.etc])

Je kunt via de ORDER regelen dat onTime een rol heeft, door hier op te sorteren (N is alfabetisch eerder dan Y).
In mysql is het vrijwel onmogelijk (heeft klaarblijkelijk te maken met de manier waarop mysql intern via B-trees de data verzamelt, waardoor het custom sorteren niet kan). Een eventuele oplossing is om met group_concat kunnen gaan klooien, maar fraai is het niet.
In MS-Access heb je de FIRST of FIRST_VALUE functie, die precies doet wat je wil. Ik weet niet of deze functie ook in MSSQL bestaat.
MSSQL heeft wel analytical functions, waarmee je per ConsignmentID een rijnummer kunt querien, waarna je dit vervolgens kunt filteren, dus:

code:

with terezz as
    (
  select col1,col2,
    row_number() over(partition by a order by a) as rn
  from blahblah
    )    
    select  
col1,col2
from terezz 
where rn = 1

Met een analytische sum-functie kan je dan de totalen in de binnenste query verwerken, iets in de trant van:

code:

with terezz as
    (
  select col1,col2, sum(col2) over (partition by col1) sum_col2,
    row_number() over(partition by a order by a) as rn
  from blahblah
    )    
    select  
col1,sum_col2
from terezz 
where rn = 1

Tenslotte wat algemene tips bij het maken van ingewikkelde queries:
* Gebruik een GROUP BY alleen voor velden waar je op wilt groeperen. Technische trucs kunnen, maar maken je query moeilijk te lezen.
* Gebruik nooit een distinct op float of datetime kolommen. 99.9% kans dat je een denkfout maakt in je query. Stel je hebt een tabel met twee records, namelijk twee maal 100. Een distinct levert je 1 record op. Als je nu het tweede record aanpast naar 100.000000001, is het dan logisch dat je nu twee records terugkrijgt?
* Idem voor GROUP BY queries; groeperen op floats of datetime zou nooit hoeven.
* Wees niet te bang voor subqueries. Subqueries zien er iets moelijker uit, maar zijn gestructureerder. In veel gevallen: probeer in de binnenste query je logica per rij te verwerken, en in de buitenste query de groupering. Zo scheid je de verschillende elementen in je query. Nog beter, gebruik de WITH syntax als je sql-dialect dat ondersteunt.

[P_de_B]

TereZz schreef op dinsdag 01 november 2016 @ 14:09:
[...]

Precies dit! Maar dan weer het probleem dat ik niet alles in de groupby krijg, door die CASE in de SELECT. Hoe moet de GROUP BY eruit zien?
Jammer dat ik de alliassen (AS) uit de SELECT niet kan gebruiken in de GROUP BY

Je kunt de hele CASE gewoon herhalen in de GROUP BY

[NMe]

P_de_B schreef op dinsdag 01 november 2016 @ 14:41:
[...]

Je kunt de hele CASE gewoon herhalen in de GROUP BY

De vraag is of dat wenselijk is, ik zou het eerder naar de clientcode verplaatsen. Maar de hele case copy/pasten is inderdaad valide.

[P_de_B]

Ik had nog niet inhoudelijk naar de CASE gekeken om eerlijk te zijn, maar deze case zou inderdaad prima in de client code kunnen.

Overigens vind ik dat niet per definitie beter. Database levert de juiste data, client code zorgt voor weergave is wmb prima te verdedigen (met alle mogelijke nuanceringen, als dat bijvoorbeeld onleesbare code oplevert is het weer minder handig)

---

Ik denk ook dat het sneller zou kunnen zijn om de Total berekening niet via een subquery te doen maar via een JOIN. Dat zou je nog even kunnen testen door de verschillende executieplans te bekijken.

[TereZz]

KabouterSuper schreef op dinsdag 01 november 2016 @ 14:37:
Volgens mij wil je gewoon per ConsignmentID de som van bepaalde kolommen (Amount) en de eerste waarde van andere kolommen (met een custom sortering, ik gok DeliveryTime en onTime).
De som-functie is eenvoudig en werkt in alle SQL dialecten. Let op dat je waarschijnlijk alleen per ConsignmentID wil grouperen; andere kolommen lijken mij niet logisch.

Voor de eerste waarde met custom sortering ben je erg afhankelijk van het sql-dialect. In oracle kan je dit met groepsfuncties gemakkelijk doen:

code:

SELECT MAX([column1]) KEEP (DENSE_RANK FIRST ORDER BY [column2/expression.etc])

Je kunt via de ORDER regelen dat onTime een rol heeft, door hier op te sorteren (N is alfabetisch eerder dan Y).
In mysql is het vrijwel onmogelijk (heeft klaarblijkelijk te maken met de manier waarop mysql intern via B-trees de data verzamelt, waardoor het custom sorteren niet kan). Een eventuele oplossing is om met group_concat kunnen gaan klooien, maar fraai is het niet.
In MS-Access heb je de FIRST of FIRST_VALUE functie, die precies doet wat je wil. Ik weet niet of deze functie ook in MSSQL bestaat.
MSSQL heeft wel analytical functions, waarmee je per ConsignmentID een rijnummer kunt querien, waarna je dit vervolgens kunt filteren, dus:

code:

with terezz as
    (
  select col1,col2,
    row_number() over(partition by a order by a) as rn
  from blahblah
    )    
    select  
col1,col2
from terezz 
where rn = 1

Met een analytische sum-functie kan je dan de totalen in de binnenste query verwerken, iets in de trant van:

code:

with terezz as
    (
  select col1,col2, sum(col2) over (partition by col1) sum_col2,
    row_number() over(partition by a order by a) as rn
  from blahblah
    )    
    select  
col1,sum_col2
from terezz 
where rn = 1

Tenslotte wat algemene tips bij het maken van ingewikkelde queries:
* Gebruik een GROUP BY alleen voor velden waar je op wilt groeperen. Technische trucs kunnen, maar maken je query moeilijk te lezen.
* Gebruik nooit een distinct op float of datetime kolommen. 99.9% kans dat je een denkfout maakt in je query. Stel je hebt een tabel met twee records, namelijk twee maal 100. Een distinct levert je 1 record op. Als je nu het tweede record aanpast naar 100.000000001, is het dan logisch dat je nu twee records terugkrijgt?
* Idem voor GROUP BY queries; groeperen op floats of datetime zou nooit hoeven.
* Wees niet te bang voor subqueries. Subqueries zien er iets moelijker uit, maar zijn gestructureerder. In veel gevallen: probeer in de binnenste query je logica per rij te verwerken, en in de buitenste query de groupering. Zo scheid je de verschillende elementen in je query. Nog beter, gebruik de WITH syntax als je sql-dialect dat ondersteunt.

Ik probeer je methode, zou in theorie moeten werken. Maar:

SQL:

WITH test as
    (
    select scon.CONSIGNMENT_SEGNR, 
    Total = SUM((SELECT Price FROM DELIVERY_PRICE WHERE FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS)),
    row_number() over(partition by scon.CONSIGNMENT_SEGNR order by IIF(DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME, 'Y', 'N')) as rn

    from SAT_CONSIGNMENT AS sCon
    INNER JOIN LINK_CONSIGNMENT AS lCon
        ON sCon.CONSIGNMENT_SEGNR=lCon.CONSIGNMENT_SEGNR
    INNER JOIN ABC_Star.dbo.DimTime AS sTimePickup
        ON sTimePickup.Datetime=sCon.PICKUP_TIME
    INNER JOIN ABC_Star.dbo.DimTime AS sTimeExp
        ON sTimeExp.Datetime=sCon.SCHEDULED_PICKUP_TIME
    INNER JOIN LINK_PARCEL_OF_CONSIGNMENT AS lPoCon
        ON lPoCon.CONSIGNMENT_SEGNR=sCon.CONSIGNMENT_SEGNR
    INNER JOIN SAT_PARCEL_OF_CONSIGNMENT AS sPoCon
        ON sPoCon.PARCEL_OF_CONSIGNMENT_SEGNR=lPoCon.PARCEL_OF_CONSIGNMENT_SEGNR
    INNER JOIN ABC_Star.dbo.DimTime AS sTimeDelivery
        ON sTimeDelivery.Datetime=sPoCon.DELIVERY_TIME
        )
select *
from test
where rn=1

Mijn total wordt berekend door een subquery, wat niet kan in de functie SUM(). Wanneer ik de SUM() functie aanroep IN de subquery, krijg ik dezelfde waarden als zonder SUM, omdat hij maar enkele waarden krijgt.

[TereZz]

P_de_B schreef op dinsdag 01 november 2016 @ 14:51:
Ik had nog niet inhoudelijk naar de CASE gekeken om eerlijk te zijn, maar deze case zou inderdaad prima in de client code kunnen.

Overigens vind ik dat niet per definitie beter. Database levert de juiste data, client code zorgt voor weergave is wmb prima te verdedigen (met alle mogelijke nuanceringen, als dat bijvoorbeeld onleesbare code oplevert is het weer minder handig)

---

Ik denk ook dat het sneller zou kunnen zijn om de Total berekening niet via een subquery te doen maar via een JOIN. Dat zou je nog even kunnen testen door de verschillende executieplans te bekijken.

De hele CASE en de rest van de SELECT statement (zonder de SUM) werkt niet. Dan heb ik alsnog dubbele gegevens.

[NMe]

P_de_B schreef op dinsdag 01 november 2016 @ 14:51:
Overigens vind ik dat niet per definitie beter. Database levert de juiste data, client code zorgt voor weergave is wmb prima te verdedigen

Ik bedoelde inderdaad specifiek in dit geval. Voor de gemiddelde simpele CASE is het geen probleem, maar hier staat er zo veel in dat een stukje clientcode leesbaarder is.

TereZz schreef op dinsdag 01 november 2016 @ 18:27:
[...]

De hele CASE en de rest van de SELECT statement (zonder de SUM) werkt niet. Dan heb ik alsnog dubbele gegevens.

Dat kan niet, want zo werkt een group by niet. Je doet vast iets verkeerd.

Daarnaast: zelfs als dat wel zo is dan is er nog steeds die andere optie die ik noemde, de case naar je clientcode trekken.

[TereZz]

Het gaat helaas niet om dit in de clientcode op te lossen. Het is de bedoeling Visual Studio deze gegevens in een Sterschema zet.

[P_de_B]

Dan moet je duidelijker aangeven wat je geprobeerd hebt en wat het resultaat is. Wat gaat er fout? In principe moet je dit volgens mij met GROUP BY prima kunnen oplossen. Op welk onderdeel gaat het verkeerd? Probeer het eens uit te kleden en langzaam op te bouwen?

[TereZz]

Het 'Total' wordt berekend door middel van een subquery in de SELECT. Hierdoor kan ik dit niet SUMmen. Dit doe ik overigens omdat er geen relatie loopt vanaf het prijzentabel (DELIVERY_PRICE) naar overige tabellen.
Ik heb het geprobeerd zonder subquery, en de WHERE uit die subquery in de 'normale' query te stoppen, maar dan herkend hij dat tabel gewoonweg niet.

SQL:

select sCon.CONSIGNMENT_SEGNR AS ConsignmentID,
    lCon.PICKUP_DISTRICT_NAME_SEGNR AS PickupDistrictID, 
    lCon.DELIVERY_DISTRICT_NAME_SEGNR AS DeliveryDistrictID, 
    IIF(DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME, 'Y', 'N') AS OnTime, 
    IIF(sCon.EXPRESS_DELIVERY = 1, 'Express', 'Standard') AS Deliverytype,
    /*DeliveryTypeID = 
        CASE 
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 1 THEN '1'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) > sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 1 THEN '2'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) <= sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 0 THEN '3'
            WHEN DATEADD(mi, sCon.EXP_DURATION, sCon.PICKUP_TIME) > sPoCon.DELIVERY_TIME AND sCon.EXPRESS_DELIVERY = 0 THEN '4'
        END,*/
    sTimePickup.TimeID AS PickupTimeID,
    sTimeExp.TimeID AS Exp_DeliveryTimeID,
    --sTimeDelivery.TimeID AS DeliveryTimeID,
    Amount = '1',
    DELIVERY_PRICE.PRICE
    --Total = (SELECT SUM(Price) FROM DELIVERY_PRICE WHERE FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS)
    --Total = (SELECT SUM(Price) FROM DELIVERY_PRICE WHERE FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS GROUP BY Price)    /*Total*/

from SAT_CONSIGNMENT AS sCon
INNER JOIN LINK_CONSIGNMENT AS lCon
    ON sCon.CONSIGNMENT_SEGNR=lCon.CONSIGNMENT_SEGNR
INNER JOIN ABC_Star.dbo.DimTime AS sTimePickup
    ON sTimePickup.Datetime=sCon.PICKUP_TIME
INNER JOIN ABC_Star.dbo.DimTime AS sTimeExp
    ON sTimeExp.Datetime=sCon.SCHEDULED_PICKUP_TIME
INNER JOIN LINK_PARCEL_OF_CONSIGNMENT AS lPoCon
    ON lPoCon.CONSIGNMENT_SEGNR=sCon.CONSIGNMENT_SEGNR
INNER JOIN SAT_PARCEL_OF_CONSIGNMENT AS sPoCon
    ON sPoCon.PARCEL_OF_CONSIGNMENT_SEGNR=lPoCon.PARCEL_OF_CONSIGNMENT_SEGNR
INNER JOIN ABC_Star.dbo.DimTime AS sTimeDelivery
    ON sTimeDelivery.Datetime=sPoCon.DELIVERY_TIME

WHERE DELIVERY_PRICE.FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND DELIVERY_PRICE.TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS

Foutcode:
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "DELIVERY_PRICE.FROMWEIGHT" could not be bound.

[TereZz]

Je kan ook zonder een JOIN uit meerdere tabellen gegevens halen, door deze na een komma in de FROM te plaatsen
GROUP BY werkt nu. Ik heb nu nog wel wat dubbele gegevens, aangezien OnTime en DeliveryTypeID niet voor iedere record (/ConsigmentID) hetzelfde is.
Hier moeten de dubbele records dus uit.


Record 3, 17 enz. moeten er nog dus uit, maar wel opgeteld worden bij de laatste kolom van de record erboven.

Bedankt voor de hulp tot zover!

[ Voor 11% gewijzigd door TereZz op 01-11-2016 19:54 ]

[RobIII]

TereZz schreef op dinsdag 01 november 2016 @ 19:52:
Je kan ook zonder een JOIN uit meerdere tabellen gegevens halen, door deze na een komma in de FROM te plaatsen

Dat is een (impliciete) join

TereZz schreef op dinsdag 01 november 2016 @ 19:25:
Dit doe ik overigens omdat er geen relatie loopt vanaf het prijzentabel (DELIVERY_PRICE) naar overige tabellen.
[...]
Foutcode:
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "DELIVERY_PRICE.FROMWEIGHT" could not be bound.

Dus MSSQL moet maar, op magische wijze, snappen hoe die DELIVERY_PRICE ergens in je query erbij betrokken moet worden? Nogal wiedes dat je die foutmelding krijgt; de tabel wordt nergens in je joins noch where clause genoemd

P_de_B schreef op dinsdag 01 november 2016 @ 19:13:
Probeer het eens uit te kleden en langzaam op te bouwen?

^ Dat. Begin eens gewoon stapje voor stapje.

SQL:

select X
from Y
where ...

Dan een join erbij:

SQL:

select Y.X, Z.Q
from Y
inner join Z on ...
where ...

tot je alle betrokken/benodigde tabellen hebt en ga van daar uit verder. Overigens kun je ook in een join (een) extra conditie(s) opgeven:

SQL:

select Y.X, Z.Q
from Y
inner join Z on Z.Y_ID = Y.ID and Z.foo = 'bar' and Z.foobar = 123
where ...

[ Voor 77% gewijzigd door RobIII op 01-11-2016 20:10 ]

[TereZz]

Delivery_Price is niet te relationeel te joinen. Hij heeft geen gemeenschappelijke waarden. Daarom ook die laatste conditie.

[P_de_B]

Je kunt ook joinen op andere criteria


JOIN Delivery_Price ON FROMWEIGHT <= sPoCon.WEIGHT_OF_CONSIGNMENT AND TOWEIGHT > sPoCon.WEIGHT_OF_CONSIGNMENT AND sCon.EXPRESS_DELIVERY = DELIVERY_PRICE.EXPRESS

dan heb je die subselect er in ieder geval uit.

Record 3, 17 enz. moeten er nog dus uit, maar wel opgeteld worden bij de laatste kolom van de record erboven.

Dus ONTIME en DELIVERY_TYPEID niet meenemen in de SELECT (of daar ook een aggregate op toepassen, maar dat geeft onzinnige waarden denk ik?) en dan GROUP BY en SUM. Dat is het echt. Doe het nu eerst even zonder die subselect en die CASE construtie. Dan wordt het een stuk overzichtelijker.

[ Voor 8% gewijzigd door P_de_B op 01-11-2016 21:08 ]

[TereZz]

Yes! Dat werkt in ieder geval. De Total klopt dan.

Hoe kan ik dan alsnog een DeliveryTypeID meegeven?
1 staat voor Delivery= Express + OnTime=Y
2 voor Express + Ontime=N
3 voor Standard + Ontime=Y
4 is dan Standard+ Ontime=N

Subquery's?

[P_de_B]

Even terug naar je vraagstelling:

Probleem 1:
Het doel is om, indien er meerdere records met hetzelfde ConsigmentID is, de dubbele eruit gefilterd worden, en alleen de eerste zichtbaar is. Bij voorkeur ook diegene die de waarde 'N' heeft in het kolom OnTime. Hoe is dit te doen, want alles in een GROUP BY lukt me niet. (ook meerdere subqueries geprobeerd icm WITH).

Als we het even versimpelen,stel je hebt vier velden ConsigmentID, DeliveryTypeId, OnTime en Total. Zonder GROUP BY krijg je alle records terug, dus ook meerdere voor hetzelfde ConsignmentId. Je moet dus voor alle overige velden beslissen wat je er mee wilt doen. Als je ze wel nodig bent kun je een aggregate functie gebruiken, bijvoorbeeld SUM, AVG of MAX, dus

SELECT ConsigmentID, MAX(DeliveryTypeId), MAX(OnTIme), SUM(Total)
FROM..
GROUP BY ConsigmentId

Als dit geen zinnige informatie oplevert en je slimmer wilt kiezen welke records je terug wilt krijgen, moet je kiezen welke DeliveryTYpeId je wilt zien als deze per ConsigmentId vaker voorkomt.

Welke velden in je SELECT zijn echt nodig en welk record wil je terug zien als deze verschilt per ConsigmentId? Zie in je laatste voorbeeld rijen 2 en 3. Hier wijken veel gegevens voor hetzelfde ConsigmentId af. Zoals gezegd, jij moet dus zeggen welke records je dan wilt zien. Dat kan dus met:
- aggregate functies
- of als je een constructie wilt als "één consigmentId, total opgeteld voor alle regels en de andere records met de laagste DeliveryTime" moet je kijken naar juhet in "[MSSQL] Ingewikkelde query" of [google=groupwise max mssql]

[TereZz]

P_de_B schreef op dinsdag 01 november 2016 @ 22:20:
Even terug naar je vraagstelling:


[...]


Als we het even versimpelen,stel je hebt vier velden ConsigmentID, DeliveryTypeId, OnTime en Total. Zonder GROUP BY krijg je alle records terug, dus ook meerdere voor hetzelfde ConsignmentId. Je moet dus voor alle overige velden beslissen wat je er mee wilt doen. Als je ze wel nodig bent kun je een aggregate functie gebruiken, bijvoorbeeld SUM, AVG of MAX, dus

SELECT ConsigmentID, MAX(DeliveryTypeId), MAX(OnTIme), SUM(Total)
FROM..
GROUP BY ConsigmentId

Als dit geen zinnige informatie oplevert en je slimmer wilt kiezen welke records je terug wilt krijgen, moet je kiezen welke DeliveryTYpeId je wilt zien als deze per ConsigmentId vaker voorkomt.

Welke velden in je SELECT zijn echt nodig en welk record wil je terug zien als deze verschilt per ConsigmentId? Zie in je laatste voorbeeld rijen 2 en 3. Hier wijken veel gegevens voor hetzelfde ConsigmentId af. Zoals gezegd, jij moet dus zeggen welke records je dan wilt zien. Dat kan dus met:
- aggregate functies
- of als je een constructie wilt als "één consigmentId, total opgeteld voor alle regels en de andere records met de laagste DeliveryTime" moet je kijken naar juhet in "[MSSQL] Ingewikkelde query" of [google=groupwise max mssql]

Ik snap het eindelijk, bedankt!

P_de_B schreef op dinsdag 01 november 2016 @ 22:20:
Even terug naar je vraagstelling:


[...]


Als we het even versimpelen,stel je hebt vier velden ConsigmentID, DeliveryTypeId, OnTime en Total. Zonder GROUP BY krijg je alle records terug, dus ook meerdere voor hetzelfde ConsignmentId. Je moet dus voor alle overige velden beslissen wat je er mee wilt doen. Als je ze wel nodig bent kun je een aggregate functie gebruiken, bijvoorbeeld SUM, AVG of MAX, dus

SELECT ConsigmentID, MAX(DeliveryTypeId), MAX(OnTIme), SUM(Total)
FROM..
GROUP BY ConsigmentId

Als dit geen zinnige informatie oplevert en je slimmer wilt kiezen welke records je terug wilt krijgen, moet je kiezen welke DeliveryTYpeId je wilt zien als deze per ConsigmentId vaker voorkomt.

Welke velden in je SELECT zijn echt nodig en welk record wil je terug zien als deze verschilt per ConsigmentId? Zie in je laatste voorbeeld rijen 2 en 3. Hier wijken veel gegevens voor hetzelfde ConsigmentId af. Zoals gezegd, jij moet dus zeggen welke records je dan wilt zien. Dat kan dus met:
- aggregate functies
- of als je een constructie wilt als "één consigmentId, total opgeteld voor alle regels en de andere records met de laagste DeliveryTime" moet je kijken naar juhet in "[MSSQL] Ingewikkelde query" of [google=groupwise max mssql]

Geweldig! Toevallig is inderdaad de MAX van DeliveryTypeID perfect. De hele CASE mag blijkbaar in de MAX functie

Dankjullie wel allemaal! Wanneer ik tijd over heb zal ik eens een tweede poging doen naar het gebruik van WITH en groupwise max.

[KabouterSuper]

P_de_B schreef op dinsdag 01 november 2016 @ 22:20:
Als we het even versimpelen,stel je hebt vier velden ConsigmentID, DeliveryTypeId, OnTime en Total. Zonder GROUP BY krijg je alle records terug, dus ook meerdere voor hetzelfde ConsignmentId. Je moet dus voor alle overige velden beslissen wat je er mee wilt doen. Als je ze wel nodig bent kun je een aggregate functie gebruiken, bijvoorbeeld SUM, AVG of MAX, dus

SELECT ConsigmentID, MAX(DeliveryTypeId), MAX(OnTIme), SUM(Total)
FROM..
GROUP BY ConsigmentId

Je moet hierbij wel goed opletten....het zou kunnen zijn dat de MAX-waarden uit verschillende records komen (bijvoorbeeld DeliveryTypeId van het eerste record, en OnTime uit het tweede record). In dit geval heeft de TS blijkbaar het geluk dat de waarden overal oplopend zijn. In het algemeen wil je het eerste record in het geheel, niet de maximale waarde per kolom. Maar zoals ik al eerder benoemd heb zijn niet alle sql-dialecten hier even goed in.

Ik zou toch proberen om de price netjes in je join te krijgen, en dan met een analytische groepsfunctie (SUM(price) over (partition by ConsignmentId) ) de totalen te krijgen.

[P_de_B]

KabouterSuper schreef op woensdag 02 november 2016 @ 08:54:
[...]


Je moet hierbij wel goed opletten....het zou kunnen zijn dat de MAX-waarden uit verschillende records komen (bijvoorbeeld DeliveryTypeId van het eerste record, en OnTime uit het tweede record). In dit geval heeft de TS blijkbaar het geluk dat de waarden overal oplopend zijn. In het algemeen wil je het eerste record in het geheel, niet de maximale waarde per kolom. Maar zoals ik al eerder benoemd heb zijn niet alle sql-dialecten hier even goed in.

Helemaal eens natuurlijk, dat is hoe de aggregatefuncties werken. Daarom zei ik ook dat het afhankelijk is van de usecase of de data zinnig is. Volgens mij zit daarin per SQL dialect geen verschil