September 26, 2016 at 9:14 am
Hi All,
I have a query I have written where one of the columns could potentially have a null value, in which case I reference another date field in the record and add 21 days.
-- if no completion date shown this will add 3 weeks to the OPEN DATE of the workorder
(CASE
WHEN W.shopestimatedcompletiondate IS NULL THEN DATEADD(DD, DATEDIFF(DD, 0, W.opendate), 21)
ELSE DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)
END) as PromiseDate,
This part of my query works fine, however later in this same SELECT statement I need use results of this CASE statement to make additional calculations. I am trying to figure out to tell the query to use the results of that CASE, without having to reinsert every place else in the query, which seems really inefficient. This is an example of where I would want to use the result of the first case statement. In the following code, I don't really want to evaluate it based W. shopestimatedcompletiondate, but rather on the PromiseDate, which is defined in the first code segment.
-- schedule priority in DAYS = PromiseDate minus Currentdate minus QuotedHrs
(DATEDIFF(dd, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)) )
-(DATEDIFF(wk, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) * 2)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) = 7 THEN 1 ELSE 0 END) As No_Days,
It seems somehow that I should be able to set a local variable to the result of the first CASE statement, but that errors out. This is the complete query if that proves more informative.
Declare @CutoffDate datetime
set @CutoffDate=convert(datetime,convert(varchar(11),getdate())) /* strip to date only */ -- set @today = convert(datetime,'2008-02-15')
select W.wono,W.billto,W.shipto,W.shipname,convert(datetime,convert(varchar(11),W.opendate)) as opendate
,W.openby,W.salesman,
W.writer,W.shopstatus,W.shopquotehours,
-- if no completion date shown this will add 3 weeks to the OPEN DATE of the workorder
(CASE
WHEN W.shopestimatedcompletiondate IS NULL THEN DATEADD(DD, DATEDIFF(DD, 0, W.opendate), 21)
ELSE DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)
END) as PromiseDate,
-- schedule priority in DAYS = PromiseDate minus Currentdate minus QuotedHrs
(DATEDIFF(dd, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)) )
-(DATEDIFF(wk, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) * 2)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) = 7 THEN 1 ELSE 0 END) As No_Days,
--
-- schedule priority IN HOURS = PromiseDate minus Currentdate minus QuotedHrs
((DATEDIFF(dd, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)) )
-(DATEDIFF(wk, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) * 2)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) = 7 THEN 1 ELSE 0 END)) *8 No_Days2,
--
-- schedule priority IN HOURS PLUS ESTHRS = PromiseDate minus Currentdate minus QuotedHrs
((DATEDIFF(dd, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 0, W.shopestimatedcompletiondate), 0)) )
-(DATEDIFF(wk, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0), DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) * 2)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DATEPART(dw, DATEADD(DD, DATEDIFF(DD, 1, W.shopestimatedcompletiondate), 0)) = 7 THEN 1 ELSE 0 END)) *8 -ShopQuoteHours as SchedPriority,
--
C.name,round(L.sumofhours,1) as CurrHrs,L.sumofcost,L.sumofsell,
round ((case
when (W.shopquotehours -L.sumofhours) > 0 then (W.shopquotehours -L.sumofhours)
else null
end),1)
as HoursRemaining
,
(case
when (W.shopquotehours -L.sumofhours) >= 0 then ''
else '*'
end)
as OverHours
,
(case
when dateadd(day,1,W.shopestimatedcompletiondate) <= @CutoffDate+1 then 'Bucket1'
when (dateadd(day,1,W.shopestimatedcompletiondate) > @CutoffDate+1) and (dateadd(day,1,W.shopestimatedcompletiondate) <= (@CutoffDate + 8)) then 'Bucket2'
when (dateadd(day,1,W.shopestimatedcompletiondate) > @CutoffDate+8) and (dateadd(day,1,W.shopestimatedcompletiondate) <= (@CutoffDate + 15)) then 'Bucket3'
when (dateadd(day,1,W.shopestimatedcompletiondate) > @CutoffDate+15) and (dateadd(day,1,W.shopestimatedcompletiondate) <= (@CutoffDate + 22)) then 'Bucket4'
else 'Bucket5'
end) as AgeBucket
from wo W, customer C, wiplabor L
where W.wono=L.wono
and W.billto=C.number
and W.disposition = 1
and W.salebranch =1
and left(W.saledept,1) = 4
and ShopStatus<>'Complete'
and left(W.salecode,1)<>'Z'
thanks,
Joe
September 26, 2016 at 9:27 am
Just use CROSS APPLY to introduce the expression, and then you can just give it a name you can reference.
Here's a quick example illustrating it:
SELECT object_id,
case_result,
--can be referenced by name now
derived_from_case='Simple concat: '+case_result
--can also be referenced by name in other operations
FROM sys.objects O
CROSS APPLY
(SELECT CASE
WHEN O.object_id<100 THEN 'Less than 100'
WHEN O.object_id>=100 THEN 'Greater than or equal to 100'
END
)cross_applied(case_result)
In 2008 on, you could also CROSS APPLY VALUES instead of doing the SELECT.
Cheers!
September 26, 2016 at 10:20 am
Jacob,
Thank you!! I have been stuck on that problem for a week. Your solution cleared it in 5 minutes.
Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply