January 25, 2012 at 11:17 am
I have a select statement that looks something like this - sorry it's so long:
/*ALTER VIEW [dbo].[vdvSalesOrder_OnTimeShip] AS*/
SELECT dbo.tsoSalesOrder.TranID,
dbo.timItem.ItemID,
dbo.vListValidationString.LocalText AS Status,
dbo.timItemDescription.ShortDesc AS ItemShortDesc,
(Select MAX(tsoShipment.TranDate)
FROM tsoShipLine
INNER JOIN tsoShipment
ON tsoShipLine.ShipKey = tsoShipment.ShipKey
WHERE tsoShipLine.SOLineKey = tsoSOLineDist.SOLineKey
) AS 'ShipDate',
dbo.tsoSOLineDist.ShipDate as 'Promise Date',
(SELECT 'ShippedOnTime' = CASE
WHEN DATEDIFF(day, ISNULL(tsosolinedist.shipdate, GETDATE()), ISNULL(MAX(tsoShipment.TranDate), GETDATE())) > 0 THEN 'Late'
WHEN DATEDIFF(day, ISNULL(tsosolinedist.shipdate, GETDATE()), ISNULL(MAX(tsoShipment.TranDate), GETDATE())) <= 0 THEN 'On-Time'
END
FROM tsoShipLine
INNER JOIN tsoShipment
ON tsoShipLine.ShipKey = tsoShipment.ShipKey
WHERE tsoShipLine.SOLineKey = tsoSOLineDist.SOLineKey
) AS 'ShippedOnTime',
--PSS END 15SEPT2010 SF
(Select DATEDIFF(day, ISNULL(tsosolinedist.shipdate, GETDATE()), ISNULL(MAX(tsoShipment.TranDate), GETDATE()))
FROM tsoShipLine
INNER JOIN tsoShipment
ON tsoShipLine.ShipKey = tsoShipment.ShipKey
WHERE tsoShipLine.SOLineKey = tsoSOLineDist.SOLineKey
) AS 'DaysLate'
FROM dbo.timSalesPromotion RIGHT OUTER JOIN
dbo.tmfWorkOrdHead_HAI LEFT OUTER JOIN
dbo.tmfWorkOrdProd_HAI ON dbo.tmfWorkOrdHead_HAI.WorkOrderKey = dbo.tmfWorkOrdProd_HAI.WorkOrderKey RIGHT OUTER JOIN
dbo.tsoSOLine INNER JOIN
dbo.vListValidationString AS vListValidationString_2 ON dbo.tsoSOLine.SystemPriceDetermination = vListValidationString_2.DBValue ON
dbo.tmfWorkOrdProd_HAI.SOLineKey = dbo.tsoSOLine.SOLineKey
There are many more joins, but I don't think you need to see all of it.
The Data comes out like:
TranID - ItemID - Status - ShipDate - PromiseDate - DaysLate
1234 56 Closed 1/2/2012 1/1/2011 1
65 Closed 1/1/2012 1/3/2012 -2
4321 89 Closed 1/10/2012 1/15/2012 -5
If DaysLate' is > 0 shipment is late, if <=0 shipment is on time.
My issus is that on a single order(TranID) 1 item could be shipped on time and another could be shipped late
as in TranID 1234.
I want to return only Data where TranID has an item that was shipped late. If a TranID had everything shipped on time, I don't want it showing up in the data set.
So, I can't just say where DaysLate > 0.
Again, sorry this was so long, but if anyone could point me in the right direction I would really appreciate it!!
January 25, 2012 at 11:22 am
A common way to test if there's a row that exists in a sub-table is with an EXISTS statement.
They usually look something like:
Select
case
when exists
(select *
from dbo.MySubTable
where
ParentTableID = MyParentTable.ID
and X > Y)
then 1
else 0
end as XY
from dbo.MyParentTable;
There are other ways. Which is best depends a lot on how your tables are built and the exact needs of the whole query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply