CODEWITHSUNDEEP
sqlsql-server
jma
jma

Reputation: 3

Extracting time and convert to column then group by date

this is my first post. I wanted to ask some help from everyone as i have tried to find a solution to my problem.

I currently have this data

Date                    Status   
----------------------- -------------
2013-01-02 08:49:31.000 A
2013-01-02 18:03:29.000 B
2013-01-03 08:54:59.000 A
2013-01-03 18:04:43.000 B
2013-01-04 08:53:26.000 A
2013-01-04 18:08:43.000 B

I wanted to have an output like this

Date                     A              B   
----------------------- ------------    ------------
2013-01-02 00:00:00.000 08:49:31.000    18:03:29.000
2013-01-03 00:00:00.000 08:54:59.000    18:04:43.000
2013-01-04 00:00:00.000 08:53:26.000    18:08:43.000

What my current output is this

Date                    A               B   
----------------------  ------------    ------------------
2013-01-02 00:00:00.000 08:49:31.000    NULL
2013-01-02 00:00:00.000 NULL            18:03:29.000
2013-01-03 00:00:00.000 08:54:59.000    NULL
2013-01-03 00:00:00.000 NULL            18:04:43.000
2013-01-04 00:00:00.000 08:53:26.000    NULL
2013-01-04 00:00:00.000 NULL            18:08:43.000

I've done row to column query before but the data can SUM, so it was easy to make it as a column but this one contains the date and time on the same field.

Any will be appreciated, TIA.

Upvotes: 0

Views: 84

Answers (2)

darin
darin

Reputation: 498

You can give this a try, you may need a substring rather than convert time. Not quote sure if that conversion works correctly...

select convert(varchar(10),isnull(a.Date,b.Date),101),
   convert(time,a.Date) A,
   convert(time,b.Date) B
from table a
inner join table b 
on convert(varchar(10),a.Date,101) = convert(varchar(10),b.Date,101)
where a.Status = 'A'
and b.Status = 'B'

Upvotes: 0

rs.
rs.

Reputation: 27467

Try this

SELECT DATE_Field, 
MAX(CASE STATUS WHEN 'A' THEN Date_Time END) A,
MAX(CASE STATUS WHEN 'B' THEN Date_Time END) B
FROM 
(
SELECT CONVERT(Date,DateField,101) Date_Field, 
CONVERT(Time,DateField) Date_Time, Status
FROM YourTable
) v
GROUP BY DATE_Field

Upvotes: 1

Related Questions