Assigning variables in an sql statement

[sid_2]

Hello, I am dealing with SQL server 2005 and i have data from a system where i am tracking an application at each stage in the system. i have data in a tabular form as follows and the dates are derived from an sql statement. how best can i write a select statement to calculate the duration at each stage of the application.

Name |Telephone|ApplicationDate|InitialSurvey|ApprovalA|ApprovalB|ApprovalC|ApprovalD|ApprovalE

FRED |772507857|8/6/2013       |8/9/2013     |8/9/2013 |8/21/2013|8/29/2013|9/5/2013 |8/21/2013
ALI  |712956901|8/10/2013      |8/12/2013    |8/12/2013|8/21/2013|8/30/2013|9/5/2013 |8/21/2013

I would therefroe like to display in the last 6 columns the duration for instance under initial survey, i would have 0 which is initial survey date minus application date then the next column would be Approval A date minus initial survey date.
* Please note that the last 6 columns are logs in one table which i am retrieving using cases

I will appreciate any assistance accorded to me. Thank you.

Swit

[Reverend Jim]

I can't give you a complete query without more information but to calculate durations you can use DATEDIFF as in

SELECT Name,Interval=DATEDIFF(DAY,ApplicationDate,InitialSurvey) 
  FROM mytable

Details of DATEDIFF here

[sid_2]

Thank you Jim. i tried using Datediff but the challenge is that all my dates are coming from the same table so here is the sql statement i am using to display that information;

1. select B.ApplicationCode, B.FullName,Telephone,B.Area,
2. case when B.ConnectionOption = 1 then 'New Connection'else 'Separation' end 'Connection Type',
3. B.ApplicationDate,
4. max (case when C.StatusID = 2 then C.CreationDate end) 'Initial Survey',
5. max (case when C.StatusID in (3,4,6) then C.CreationDate end) 'Approval by Manager',
6. max (case when C.StatusID = 7 then C.CreationDate end) 'Approval by Operations',
7. max (case when C.StatusID = 9 then C.CreationDate end) 'Payment made and confirmed',
8. max (case when C.StatusID = 10 then C.CreationDate end) 'Connected & forwarded to Billing',
9. max (case when C.StatusID = 11 then C.CreationDate end) 'Customer Created',
10.max (case when C.StatusID = 11 then DateDiff(D,B.ApplicationDate,C.CreationDate) end) 'Duration' 
11.     
12. from dbo.Vw_ApplicationDetails B
13. inner join dbo.Nc_ApplicationStatusLogs C on c.ApplicationID = B.ApplicationID
14. group by B.ApplicationCode,B.Area, B.FullName,Telephone,B.ApplicationDate,B.ConnectionOption
15. order by B.ApplicationDate desc,B.ConnectionOption 

If i am to use DateDiff, i would have to store the date in the first case statement so i can subtract it from the the one in the second case. That is where the challenge is.

Thanx in advance

[Reverend Jim]

I don't see the problem. You are doing a DATEDIFF on two specific columns. What does that have to do with the first CASE statement?

[sid_2]

Thanx Jim for your response. That query is ok but i have to get a duration between the dates now. For example the duration between when the application was at status 2 and status 3.

If i had;

@dt1 = max (case when C.StatusID = 2 then C.CreationDate end) 'Initial Survey',

then the next line would be;

max (case when C.StatusID in (3,4,6) then DateDiff(D,C.CreationDate,@dt1 end) 'Approval by Manager'

However, the challenge is how to get the above two lines of code in the same sql statement.

Thank you

[Reverend Jim]

Using the following table

CREATE TABLE [dbo].[test](
    [Name]  [varchar](50) NOT NULL,
    [type]  [varchar](50) NOT NULL,
    [adate] [date]        NOT NULL,
    [bdate] [date]        NOT NULL,
    [cdate] [date]        NOT NULL
) ON [PRIMARY]

and the data

Jim     A   2013-02-19  2013-04-12  2013-09-04
George  B   2013-02-19  2013-04-12  2013-09-04

and the query

SELECT 
    name,
    type=CASE WHEN type='A' THEN 'TYPE A' ELSE 'TYPE B' END,
    diff=DATEDIFF(DAY,CASE WHEN type='A' THEN adate ELSE bdate END,cdate)
  FROM test

I get the results

Jim     TYPE A  197
George  TYPE B  145

which I think is what you are looking for. Just take the example and apply it to your specific case. When you plug it into your query it will look convoluted so you should add comments to clearly explain what you are doing so that the next programmer who has to change it will not feel the urge to hunt you down and kill you while you sleep. Trust me. I've been that guy.

[sid_2]

Thank you Jim. This is very helpful. Plus i will be sure to add those comments.