0

I'm still pretty new at SQL, so I apologize if this is a silly question. I took existing queries for three data points, and I'm not sure how to streamline them into a single output.

Clunky query:

DECLARE @contextdate as datetime
set @contextdate = '7/20/19 5am';

select
    'Total' [Type],
    cast(@contextdate as date) WEDate,
    sum(unitQty) as totalUnits,
    sum(packages) as totalPackages,
    sum(case when processingDay <= 2 then packages else 0 end) as OffPeakSL,
    sum(case when processingDay <= 5 then packages else 0 end) as PeakSL
from
(
select count(distinct hu_id) as packages, sum(tran_qty) unitQty, 
case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end processingDay
from
(
select
    torder.order_number,
    shipped_tl.hu_id,
    shipped_tl.tran_qty, 
    torder.actual_arrival_date,
    shipped_tl.start_tran_date + shipped_tl.start_tran_time as shippedDateTime,
    cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - '1900-01-01 05:00' as date) as opsDate, -- operational day starts at 5:00am and ends 4:59:59am the next day
    floor(cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - torder.actual_arrival_date as float)) as processingDay
    FROM
        t_tran_log shipped_tl WITH (NOLOCK)
        INNER JOIN t_order torder WITH (NOLOCK)
        ON shipped_tl.control_number = torder.order_number
    WHERE
        start_tran_date > '2019-05-20' 
        AND tran_type = 303 
        AND hu_id != 0000000
) shipped_tx
where opsDate between CAST(@contextDate - DATEPART(dw,@contextDate ) +1  AS DATE) and cast(@contextDate as date)
group by
    case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end
) shippedTotals;

select
    'West Coast' [Type],
    cast(@contextdate as date) WEDate,
    sum(unitQty) as totalUnits,
    sum(packages) as totalPackages,
    sum(case when processingDay < 1 then packages else 0 end) as OffPeakSL,
    sum(case when processingDay < 1 then packages else 0 end) as PeakSL
from
(
select count(distinct hu_id) as packages, sum(tran_qty) unitQty, 
case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end processingDay
from
(
select
    torder.order_number,
    shipped_tl.hu_id,
    shipped_tl.tran_qty, 
    torder.actual_arrival_date,
    shipped_tl.start_tran_date + shipped_tl.start_tran_time as shippedDateTime,
    cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - '1900-01-01 05:00' as date) as opsDate, -- operational day starts at 5:00am and ends 4:59:59am the next day
    floor(cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - torder.actual_arrival_date as float)) as processingDay
    FROM
        t_tran_log shipped_tl WITH (NOLOCK)
        INNER JOIN t_order torder WITH (NOLOCK)
        ON shipped_tl.control_number = torder.order_number
    WHERE
        start_tran_date > '2019-05-20' 
        AND tran_type = 303 
        AND hu_id != 0000000
        AND torder.ship_via NOT IN ('DFLT')
        AND torder.ship_to_state in ('WA','OR','CO','AZ','MT','UT','CA','TX','WY','ID','NV','NM')
) shipped_tx
where opsDate between CAST(@contextDate - DATEPART(dw,@contextDate ) +1  AS DATE) and cast(@contextDate as date)
group by
    case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end
) shippedTotals;


select
    'Expedited' [Type],
    cast(@contextdate as date) WEDate,
    sum(unitQty) as totalUnits,
    sum(packages) as totalPackages,
    sum(case when processingDay < 1 then packages else 0 end) as OffPeakSL,
    sum(case when processingDay < 1 then packages else 0 end) as PeakSL
from
(
select count(distinct hu_id) as packages, sum(tran_qty) unitQty, 
case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end processingDay
from
(
select
    torder.order_number,
    shipped_tl.hu_id,
    shipped_tl.tran_qty, 
    torder.actual_arrival_date,
    shipped_tl.start_tran_date + shipped_tl.start_tran_time as shippedDateTime,
    cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - '1900-01-01 05:00' as date) as opsDate, -- operational day starts at 5:00am and ends 4:59:59am the next day
    floor(cast(shipped_tl.start_tran_date + shipped_tl.start_tran_time - torder.actual_arrival_date as float)) as processingDay
    FROM
        t_tran_log shipped_tl WITH (NOLOCK)
        INNER JOIN t_order torder WITH (NOLOCK)
        ON shipped_tl.control_number = torder.order_number
    WHERE
        start_tran_date > '2019-05-20' 
        AND tran_type = 303 
        AND hu_id != 0000000
        AND torder.ship_via in ('ON')
) shipped_tx
where opsDate between CAST(@contextDate - DATEPART(dw,@contextDate ) +1  AS DATE) and cast(@contextDate as date)
group by
    case when floor(cast(shippedDateTime - actual_arrival_date as float)) > 5 then 5 else floor(cast(shippedDateTime - actual_arrival_date as float)) end
) shippedTotals;

Clunky output:

enter image description here

How can I clean up this query and have the results come out in a single table?

Thanks in advance.

  • Take a look at UNION – LowlyDBA Jul 19 at 14:05
  • 1
    ...or, more likely, UNION ALL, so you don't have an extra sort operator – John Eisbrener Jul 19 at 14:06
0

I did not analyze your query but the fastest way to get a single result set is to insert Union All In front of the second and third query. Rerun the query and you will get three rows in one result. All of the output columns have to be defined the same for Union All to work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.