mercredi 23 mars 2016

SQL server selecting date in a case statement

I have a table

DECLARE @t TABLE(
passengerID varchar(100),
itemA VARCHAR(100),
itemB VARCHAR(100),
timevar date
)
insert into @t values
('1','1','0','2015-01-01 00:00:00 '),
('1','0','1','2015-02-01 00:00:00')

and the goal is

DECLARE @goal TABLE(
passengerID varchar(100),
itemA VARCHAR(100),
itemB VARCHAR(100),
timevarA date,
timevarB date
)
insert into @goal values
('1','1','1','2015-01-01','2015-02-01 00:00:00')

I used this to obtain the goal...but the time variable ends up as a number that messes up the exact time (it has seconds etc.)

SELECT PassengerID,
       max(itemA) itemA,
       max(itemB) itemB,
       max(case when ItemA = 1 then Time end) as timevarA,
       max(case when ItemB = 1 then Time end) as timevarB
FROM @t
GROUP BY PassengerID;

Is there a way to do this max on a datetime variable without it converting? It seems to cut off the precision of the datetime if I later try to convert it back to datetime using R.

Aucun commentaire:

Enregistrer un commentaire