question

abu_mu2004 avatar image
abu_mu2004 asked

How to extract Dates from table based on the value of other column in the same table?

In my situation, I want to extract Start Date and Stop Date from following table as you can see i have only 1 Date column which contain Start/Change/Stop Date which depends on ACTION column : 01 - Start (New drug start) 02 - Change (Change of dose) 03 - Stop ( Drug Stopped) The tricky bit is that When Action=02 dose is changed so the Change date should become Start date for the current dose and stop date for the previouse dose. I am really confused with it,, CREATE TABLE TEST(ID VARCHAR(10), [TherapyAction] INT, [Drug] INT, [Dose] FLOAT, [TherapyDate] DATETIME) INSERT INTO [dbo].[TEST] VALUES ('XXX' ,1, 1, 60, '01/09/2009 '), ('57A' ,3, 1, 60, '09/07/2011'), ('57A' ,1, 3, 5, '25/06/2010'), ('57A' ,3, 3, 5, '09/07/2011' ), ('57A' ,1, 4, 187.5, '19/02/2010'), ('57A' ,2, 4, 250, '01/06/2010' ), ('57A' ,3, 4, 250, '09/07/2011' ), ('A5B' ,1, 1, 12.5, '26/01/2007' ), ('A5B' ,2, 1, 25, '06/02/2007' ), ('A5B' ,2, 1, 225, '20/08/2009'), ('A5B' ,1, 4, 62.5, '04/07/2006'), ('A5B' ,2, 4, 125, '12/07/2006'), ('A5B' ,2, 4, 250, '01/05/2008'), ('A5B' ,1, 7, 7.5, '11/09/2006'), ('A5B' ,3, 7, 7.5, '26/01/2007'), ('A5B' ,1, 7, 9, '09/04/2010'), ('A5B', 3, 7, 9, '19/07/2010') SELECT * FROM [dbo].[TEST] Any help will be greatly appreciated
sqlsql-server-2012querydatabase
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you add what version of sql server you are working with please?
1 Like 1 ·
Hi.. I'm using sql server 2012 enterprise edition
0 Likes 0 ·
Sql server 2012 enterprise edition
0 Likes 0 ·
KenJ avatar image
KenJ answered
You can use a case statement - [ http://msdn.microsoft.com/en-us/library/ms181765.aspx][1] select case when TherapyAction = '01' then TherapyDate else null as StartDate, case when TherapyAction = '02' then TherapyDate else null as ChangeDate, &c... [1]: http://msdn.microsoft.com/en-us/library/ms181765.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sujit1103 avatar image
sujit1103 answered
Hi, I want to present the output of my query to a tabular form.. Table columns: jobname date starttime endtime I want to show it in output in following format: jobnum 01aug 02aug 03aug job1 10:00 10:15 10:10 job2 11:00 11:15 11:10 job3 11:30 11:45 11:35 Can someone please provide me query to get output in this format? Really Appreciate your response on this.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You added this as an answer to several questions, which I've deleted, please ask as a separate question
1 Like 1 ·
Please ask this as a new question, not as an answer to an old question
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.