Applying a calculation to a new column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Applying a calculation to a new column

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-05-19 : 22:37:15

Hi All - I have the following table.
It contains two columns with ship dates, which will only be populated with one or the other (not both):

ORDER # SHIP DATE #1 SHIP DATE #2 ARRIVAL DATE
12345 01/05/2011 NULL 01/10/2011
23456 NULL 01/10/2011 01/21/2011
45678 NULL 01/25/2011 02/01/2011
98765 03/01/2011 NULL 03/05/2011

My query (see code below) consists of two parts:
(1) Doing a CASE statement which consolidates both dates into one column
(2) Calculating the difference in business days between the ship and arrival date.

The problem is that I can only figure out how to caclcuate based on the new column I've outputted.

Any ideas on how to apply the business day calculation to the new "consolidated ship date" column?
THANKS!!!!!!!!!!!!!!!!!

SELECT

(CASE
WHEN [SHIP DATE #1] IS NOT NULL THEN [SHIP DATE #1]
WHEN [SHIP DATE #2] IS NOT NULL THEN [SHIP DATE #2]
) AS 'CONSOLIDATED SHIP DATE',

--CALCULATION OF BUSINESS DAYS BETWEEN SHIP AND ARRIVAL DATES

(DATEDIFF(dd, [SHIP DATE #1], [ARRIVAL DATE]) + 1)
-(DATEDIFF(wk, [SHIP DATE #1], [ARRIVAL DATE]) * 2)
-(CASE WHEN DATENAME(dw, [SHIP DATE #1]) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, [[SHIP DATE #1]) = 'Saturday' THEN 1 ELSE 0 END)
as [TURNAROUND TIME]

FROM ORDERS

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 22:48:40

You can use the coalesce function, as in:


coalesce([SHIP DATE #1],[SHIP DATE #2])

That would pick ship date 1 if it is not null, and ship date 2 if ship date 1 happens to be null. So your query would be:


SELECT

coalesce([SHIP DATE #1],[SHIP DATE #2]) AS 'CONSOLIDATED SHIP DATE',

(DATEDIFF(dd, coalesce([SHIP DATE #1],[SHIP DATE #2]), [ARRIVAL DATE]) + 1)
-(DATEDIFF(wk, coalesce([SHIP DATE #1],[SHIP DATE #2]), [ARRIVAL DATE]) * 2)
-(CASE WHEN DATENAME(dw, coalesce([SHIP DATE #1],[SHIP DATE #2])) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, coalesce([SHIP DATE #1],[SHIP DATE #2])) = 'Saturday' THEN 1 ELSE 0 END)
as [TURNAROUND TIME]

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-05-20 : 00:10:22

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU
IT WORKED !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources