July 14, 2021 at 5:32 pm
Hello All,
I am getting a result set for some records after joining the tables. Below is my query
SELECT TOQ.order_id AS id,
TOQ.coated_bare AS Type,
TOQ.dia AS Dia,
TOQ.pipe_thickness AS Thickness,
TOQ.order_qty_no_pipe AS OrderNumber,
TOQ.order_qty_meter AS OrderLength,
CAST(ISNULL(SUM(CAST(TRQ.lenght AS DECIMAL(18, 2))), 0) * 100 / TOQ.order_qty_meter AS DECIMAL(10, 2)) AS Progress,
ISNULL(SUM(TRT.trailors), 0) AS FortheDayReceivedTrailors,
ISNULL(SUM(TRT.no_of_pipes), 0) AS FortheDayReceivedNumber,
ISNULL(SUM(CAST(TRT.length AS DECIMAL(18, 2))), 0) AS FortheDayReceivedLength,
ISNULL(SUM(TRQ.trailors), 0) AS ReceivedTrailors,
ISNULL(SUM(TRQ.no_of_pipes), 0) AS ReceivedNumber,
ISNULL(SUM(CAST(TRQ.lenght AS DECIMAL(18, 2))), 0) AS ReceivedLength,
ISNULL(SUM(TIT.trailors), 0) AS FortheDayIssuedTrailors,
ISNULL(SUM(TIT.no_of_pipes), 0) AS FortheDayIssuedNumber,
ISNULL(SUM(CAST(TIT.length AS DECIMAL(18, 2))), 0) AS FortheDayIssuedLength,
ISNULL(SUM(TIQ.trailors), 0) AS IssuedTrailors,
ISNULL(SUM(TIQ.no_of_pipes), 0) AS IssuedNumber,
ISNULL(SUM(CAST(TIQ.lenght AS DECIMAL(18, 2))), 0) AS IssuedLength,
(ISNULL(SUM(TRQ.no_of_pipes), 0) - ISNULL(SUM(TIQ.no_of_pipes), 0)) AS BalanceNumber,
CAST((ISNULL(SUM(TRQ.lenght), 0) - ISNULL(SUM(TIQ.lenght), 0)) AS DECIMAL(18, 2)) AS BalanceLength
FROM tblRecievedQuantity TRQ
LEFT JOIN tblOrderQuantity AS TOQ ON TOQ.order_id = TRQ.order_id
LEFT JOIN tblIssuedQuantity AS TIQ ON TOQ.order_id = TIQ.order_id
LEFT JOIN tblReceivedTrack AS TRT ON TOQ.order_id = TRT.order_id
LEFT JOIN tblIssuedTrack AS TIT ON TOQ.order_id = TIT.order_id
WHERE TRQ.location_id = @location_id
GROUP BY TOQ.coated_bare,
TOQ.dia,
TOQ.order_qty_meter,
TOQ.pipe_thickness,
TOQ.order_qty_no_pipe,
TOQ.order_id;
There is a column named date in tblReceivedTrack and tblIssuedTrack tables where I am storing the date.
Its is giving a result set, but I need get total for today's date only for ( FortheDayReceivedTrailors, FortheDayReceivedNumber, FortheDayReceivedLength, FortheDayIssuedTrailors, FortheDayIssuedNumber, FortheDayIssuedLength ) columns.
How can I put a where clause for the today's date. Kindly suggest.
July 15, 2021 at 1:10 am
You left out the name of the date column for the two tables you're talking about and you didn't include those names anywhere in the code. With that in mind, you need to make the appropriate substitution in the code below. Also, because of the Left Outer join and the nature of your request, the criteria needs to show up in the JOIN criteria rather than the filter criteria of a WHERE clause.
FROM tblRecievedQuantity TRQ
LEFT JOIN tblOrderQuantity AS TOQ ON TOQ.order_id = TRQ.order_id
LEFT JOIN tblIssuedQuantity AS TIQ ON TOQ.order_id = TIQ.order_id
LEFT JOIN tblReceivedTrack AS TRT ON TOQ.order_id = TRT.order_id
AND TRT.yourdatecol >= CONVERT(DATE,GETDATE())
LEFT JOIN tblIssuedTrack AS TIT ON TOQ.order_id = TIT.order_id
AND TIT.yourdatecol >= CONVERT(DATE,GETDATE())
Also, while I appreciate a good, rigid alias naming convention like you seem to have a grip on (all puns here are intended), you might want to consider making an exception for the "TIT" alias so as to not be the butt of such jokes. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 15, 2021 at 3:09 pm
FROM tblRecievedQuantity TRQLEFT JOIN tblOrderQuantity AS TOQ ON TOQ.order_id = TRQ.order_idLEFT JOIN tblIssuedQuantity AS TIQ ON TOQ.order_id = TIQ.order_idLEFT JOIN tblReceivedTrack AS TRT ON TOQ.order_id = TRT.order_idAND TRT.yourdatecol >= CONVERT(DATE,GETDATE())LEFT JOIN tblIssuedTrack AS TIT ON TOQ.order_id = TIT.order_idAND TIT.yourdatecol >= CONVERT(DATE,GETDATE())
Sorry, but your solution is taking all the columns, I only need that is should work only for (FortheDayReceivedTrailors, FortheDayReceivedNumber, FortheDayReceivedLength, FortheDayIssuedTrailors, FortheDayIssuedNumber, FortheDayIssuedLength ) columns.
Please suggest.
Thanks
July 15, 2021 at 3:58 pm
So you're saying it's a good thing there isn't a BacklogUpdateTrackingTable?
🙂
July 15, 2021 at 7:29 pm
You could try putting CASE logic inside the SUM functions to evaluate your youdatecol
...
ISNULL(SUM(case when youdatecol>=convert(date, getdate())
then TRT.trailors
else 0 end), 0) AS FortheDayReceivedTrailors,
...
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 15, 2021 at 10:12 pm
FROM tblRecievedQuantity TRQLEFT JOIN tblOrderQuantity AS TOQ ON TOQ.order_id = TRQ.order_idLEFT JOIN tblIssuedQuantity AS TIQ ON TOQ.order_id = TIQ.order_idLEFT JOIN tblReceivedTrack AS TRT ON TOQ.order_id = TRT.order_idAND TRT.yourdatecol >= CONVERT(DATE,GETDATE())LEFT JOIN tblIssuedTrack AS TIT ON TOQ.order_id = TIT.order_idAND TIT.yourdatecol >= CONVERT(DATE,GETDATE())Sorry, but your solution is taking all the columns, I only need that is should work only for (FortheDayReceivedTrailors, FortheDayReceivedNumber, FortheDayReceivedLength, FortheDayIssuedTrailors, FortheDayIssuedNumber, FortheDayIssuedLength ) columns.
Please suggest.
Thanks
Did you actually try the code or are you just evaluating the code? If you notice the two tables that I filtered in the FROM clause, the only columns they reference in all of the code are the columns you've identified.
Try the actual code with the correctly substituted column name.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply