t-sql 2012 orrder by

Question

Post reply

t-sql 2012 orrder by

wendy elizabeth

SSCoach

Points: 16915
More actions
September 25, 2015 at 11:59 am

#303450

In the following t-sql 2012, I have a question about what the 'ORDER BY bola.Bill DESC) a) > cw.Inv' sql means in the sql listed below. Basically what does the
'> cw.Inv' mean in the order by statement
CASE WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.Bill DESC) a) > cw.Inv
THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (
SELECT TOP 1 bolb.CasesDelivered, bolb.ProcessingCharge * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb
WHERE bolb.Active = 1
AND bolb.RFSCode = cw.RFSCode
AND bolb.ItemMonth <= CW.ItemMonth
ORDER BY bolb.Bill DESC) b)
WHEN (SELECT SUM(a.CasesDelivered) FROM (
SELECT TOP 2 bola.CasesDelivered FROM [NC].[Lad] bola
WHERE bola.Active = 1
AND bola.RFSCode = cw.RFSCode
AND bola.ItemMonth <= CW.ItemMonth
ORDER BY bola.Bill DESC) a) > cw.Inv

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It means that it's comparing the value returned by the subquery to check if it's greater than the value from cw.Inv.

It also indicates a bad query design, but there's not enough information to provide a better option.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

It's not part of the ORDER BY.

What you have there is a subquery.

CASE WHEN ( <subquery> ) > cw.Inv THEN ...

Comparing the result of the subquery to the column cw.Inv

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

Here is more of the sql that is listed below. Can you tell me what the logic is:

WHEN (SELECT SUM(a.CasesDelivered) FROM (

SELECT TOP 1 bola.CasesDelivered FROM [NC].[Lad] bola

WHERE bola.Active = 1

AND bola.RFSCode = cw.RFSCode

AND bola.ItemMonth <= CW.ItemMonth

ORDER BY bola.BillOfLadingDate DESC) a) <=cw.Inv

THEN (SELECT ROUND(SUM(b.ProcessingCharge)/NULLIF(SUM(b.CasesDelivered),0), 4) AS WeightedAvereage FROM (

SELECT TOP 1 bolb.CasesDelivered, (bolb.CaseRate + bolb.ProcessingCharge + bolb.USDAStorageCharge) * bolb.CasesDelivered AS ProcessingCharge FROM [NC].[Lad] bolb

WHERE bolb.Active = 1

AND bolb.RFSCode = cw.RFSCode

AND bolb.ItemMonth <= CW.ItemMonth

ORDER BY bolb.BillOfLadingDate DESC) b) * CASE WHEN MONTH(CW.ItemMonth) = 1 THEN ISNULL(ShippedJanuary,0)

WHEN MONTH(CW.ItemMonth) = 2 THEN ISNULL(ShippedFebruary,0)

WHEN MONTH(CW.ItemMonth) = 3 THEN ISNULL(ShippedMarch,0)

WHEN MONTH(CW.ItemMonth) = 4 THEN ISNULL(ShippedApril,0)

WHEN MONTH(CW.ItemMonth) = 5 THEN ISNULL(ShippedMay,0)

WHEN MONTH(CW.ItemMonth) = 6 THEN ISNULL(ShippedJune,0)

WHEN MONTH(CW.ItemMonth) = 7 THEN ISNULL(ShippedJuly,0)

WHEN MONTH(CW.ItemMonth) = 8 THEN ISNULL(ShippedAugust,0)

WHEN MONTH(CW.ItemMonth) = 9 THEN ISNULL(ShippedSeptember,0)

WHEN MONTH(CW.ItemMonth) = 10 THEN ISNULL(ShippedOctober,0)

WHEN MONTH(CW.ItemMonth) = 11 THEN ISNULL(ShippedNovember,0)

WHEN MONTH(CW.ItemMonth) = 12 THEN ISNULL(ShippedDecember,0)

END

END AS UsageInventoryExtension