Sql-server – How to combine these three queries into one result

sql serversql-server-2012

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:

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

Thanks in advance.

Best Answer

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.

Related Solutions

Sql-server – Can someone explain why select with nolock will query a potion of updated data

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

Select query reads value of row R1 and releases its S lock
Update query updates R2 and takes an X lock
Select query tries to read R2 and is blocked.
Update query updates R1 and takes an X lock.
Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)


INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T 
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T 
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A

Sql-server – How to calculate time spent at work in a single column

DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT 
  start = p, 
  [end] = e, 
  [minutes_elapsed] = m, 
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

If you need to do this on older versions (2005 -> 2008 R2):

;WITH cte1 AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT 
    x.r,
    [start] = x.e, 
    [end] = y.e, 
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

Suggested schema for recording time in/time out is pretty simple:

CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

Now your query is:

SELECT 
  EmployeeID, 
  ClockIn, 
  ClockOut, 
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE, 
    DATEDIFF(MINUTE, ClockIn,  
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...

Best Answer

Related Solutions

Sql-server – Can someone explain why select with nolock will query a potion of updated data

Sql-server – How to calculate time spent at work in a single column

Related Question