November 1, 2014 at 1:15 am
What I'm trying to do is calculate the difference between the aliases DaysAged and new. Can this be done?
I've tried isnull(daysaged, 0) + isnull(new, 0) Aged.
I've tried coalesce (DaysAged, new,0) as aged
and neither of these work.
Thanks in advance,
Carl
select distinct top 100 filedate, transaction_date, duedate, event_instance, event_name, eventstatus, age,
(datediff(DD, duedate, filedate)+1) DaysAged,
-(DATEDIFF(WK, duedate, filedate) *2)
-(case when DATENAME(DW, duedate) = 'sunday' then 1 else 0 end)
-(case when DATENAME(DW, FileDate) = 'saturday' then 1 else 0 end) as new
from [My Table]
November 4, 2014 at 3:20 pm
You can use CROSS APPLY to assign alias names to expressions:
select distinct top 100 filedate, transaction_date, duedate,
event_instance, event_name, eventstatus, age,
DaysAged,
coalesce (DaysAged, new, 0) as aged
from [My Table]
cross apply (
select (datediff(DD, duedate, filedate)+1) AS DaysAged,
-(DATEDIFF(WK, duedate, filedate) *2)
-(case when DATENAME(DW, duedate) = 'sunday' then 1 else 0 end)
-(case when DATENAME(DW, FileDate) = 'saturday' then 1 else 0 end) as new
) as assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply