Seperate a eventdate into two columns

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

SQL Server 2000 Forums

SQL Server Development (2000)

Seperate a eventdate into two columns

Author

Topic

girisignin
Starting Member

11 Posts

Posted - 2009-09-29 : 08:55:43

This is my data table

ACD ID EventDate FailflagValue
111 10 june 2009 16:10 4
111 10 june 2009 17:30 0
111 11 june 2009 12:15 4
111 11 june 2009 13:20 0
222 10 june 2009 13:10 4
222 10 june 2009 14:00 0

Output should be like this

ACDID FailDate OKDate
111 10 june 2009 16:10 10 june 2009 17:30
111 11 june 2009 12:15 11 june 2009 13:20
222 10 june 2009 13:10 10 june 2009 14:00

The above query should be written in sql server 2000.
help please…
giri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 09:00:34

[code]select ACD,ID,
MAX(CASE WHEN FailFlagValue = 4 THEN EventDate ELSE NULL END) AS FailDate,
MAX(CASE WHEN FailFlagValue = 0 THEN EventDate ELSE NULL END) AS OkDate
from table
group by ACD,ID
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 09:02:19

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 09:02:42

Madhivanan

Failing to plan is Planning to fail

girisignin
Starting Member

11 Posts

Posted - 2009-09-30 : 00:17:55

hi visakh16
ACDID will be failed several times , so we can not take it into group by
giri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 00:25:36

then show some data to illustrate your real scenario and output you need.

girisignin
Starting Member

11 Posts

Posted - 2009-09-30 : 01:06:39

ACDID EventDate FailflagValue
111 - 10 june 2009 16:10 - 4
111 - 10 june 2009 17:30 - 0
111 - 11 june 2009 12:15 - 4
111 - 11 june 2009 13:20 - 0
222 - 10 june 2009 13:10 - 4
222 | 10 june 2009 14:00 | 0

Output should be like this

ACDID FailDate OKDate
111 - 10 june 2009 16:10 - 10 june 2009 17:30
111 - 11 june 2009 12:15 - 11 june 2009 13:20
222 - 10 june 2009 13:10 - 10 june 2009 14:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 01:10:25

[code]select ACD,
MAX(CASE WHEN FailFlagValue = 4 THEN EventDate ELSE NULL END) AS FailDate,
MAX(CASE WHEN FailFlagValue = 0 THEN EventDate ELSE NULL END) AS OkDate
from table
group by ACD,DATEADD(dd,DATEDIFF(dd,0,EventDate),0)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources