How to get total only for today's date for some columns in reslut set

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  •  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

  • So you're saying it's a good thing there isn't a BacklogUpdateTrackingTable?

    🙂

  • 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

  • gaurav wrote:

     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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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