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 2008 Forums
 Transact-SQL (2008)
 Max and Pivot based on Codes

Author  Topic 

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-11 : 19:12:01
I'm trying to find the max date for each code and then pivot it as Code date with the latest value.
I need to convert the date and time entry to make it a datetime field as well.
Create table CheckPunch
(
CheckNo Int,
PunchDate Int,
PunchTime Int,
PunchCode Char(5)
)
select 3453455,20140108,743,'SDC' UNION ALL
select 3453455,20131231,1539,'SCB' UNION ALL
select 3453455,20140108,1858,'SDC' UNION ALL
select 3453455,20140829,1535,'TDP' UNION ALL
select 3453455,20140829,809,'SSDT' UNION ALL
select 245411,20141031,1537,'SDC' UNION ALL
select 245411,20120424,1222,'SCB' UNION ALL
select 245411,20140820,1354,'GDT' UNION ALL
select 245411,20140418,841,'TDP' UNION ALL
select 245411,20140418,1442,'SCB'
select * from CheckPunch where PunchCode IN ('SDC','SCB','GDT')
Results :
CheckNo MaxSDC_DT MaxSCBDt MaxGDT
-------- --------- -------- ------
3453455 20140108 20131231 NULL
245411 20141031 20140418 20140820

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 08:12:02
Sorry please help if there are any conversion from date and time integer to normal date time stamp.....


Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-12 : 08:24:36
here you go :


Create table CheckPunch
(
CheckNo Int,
PunchDate Int,
PunchTime Int,
PunchCode Char(5)
)
insert into CheckPunch (CheckNo,PunchDate,PunchTime,PunchCode)
select 3453455,20140108,743,'SDC' UNION ALL
select 3453455,20131231,1539,'SCB' UNION ALL
select 3453455,20140108,1858,'SDC' UNION ALL
select 3453455,20140829,1535,'TDP' UNION ALL
select 3453455,20140829,809,'SSDT' UNION ALL
select 245411,20141031,1537,'SDC' UNION ALL
select 245411,20120424,1222,'SCB' UNION ALL
select 245411,20140820,1354,'GDT' UNION ALL
select 245411,20140418,841,'TDP' UNION ALL
select 245411,20140418,1442,'SCB'
select * from CheckPunch where PunchCode IN ('SDC','SCB','GDT')

select
CheckNo
,[SDC] as MaxSDC_DT
,[SCB] as MaxSCBDt
,[GDT] as MaxGDT
--,[SSDT] as MaxSSDT
--,[TDP] as MaxTDP

from ( select CheckNo,punchcode
,convert(datetime,convert(varchar(8),PunchDate)) as PunchDate
from CheckPunch where PunchCode IN ('SDC','SCB','GDT')
) p pivot ( max (PunchDate)
for punchcode
in (
[GDT]
,[SCB]
,[SDC]
--,[SSDT]
--,[TDP]
)
)as pvt

/*
DROP TABLE CheckPunch
*/
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-12-12 : 08:59:59
or with time as well:

SELECT CheckNo
,SDC AS MaxSDC_DT
,SCB AS MaxSCBDt
,GDT AS MaxGDT
FROM
(
SELECT CheckNo, PunchCode
,DATEADD(minute, PunchTime / 100 * 60 + PunchTime % 100, DATEADD(month, (PunchDate / 10000 - 1900) * 12 + PunchDate / 100 % 100 - 1, PunchDate % 100 - 1)) AS PunchDT
FROM CheckPunch
) S
PIVOT
(
MAX(PunchDT)
FOR PunchCode IN ([SDC],[SCB],[GDT])
) P;
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 10:36:44
Thanks a lot for the script..Looks like the dates are going to 2037 instead of 2013 and 2014..
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-12 : 10:56:58
If you want it with time and date then this:-

select
CheckNo
,[SDC] as MaxSDC_DT
,[SCB] as MaxSCBDt
,[GDT] as MaxGDT
--,[SSDT] as MaxSSDT
--,[TDP] as MaxTDP

from ( select CheckNo,punchcode
,Convert(varchar(10),PunchDate) + convert(datetime, left(right(('0' + convert(varchar(4), punchtime)),4),2) + ':' + right(('0' + convert(varchar(4),
punchtime)),2)) as PunchDate
from CheckPunch where PunchCode IN ('SDC','SCB','GDT')
) p pivot ( max (PunchDate)
for punchcode
in (
[GDT]
,[SCB]
,[SDC]
--,[SSDT]
--,[TDP]
)
)as pvt
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 11:13:31
Thanks a lot..Now the dates are coming out fine but there are cases where the dates are the same I will require to go with the time so if we can add the Integer time to the date that will solve the problem...but I'm not able to convert the integer time stamp with minute and second to the datetime format....
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-12 : 11:17:11
Have you tried my privous post with time?
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 12:46:46
This works excellent...Shouldn't I add one more convert to make it datetime..Also is there any where I can add the Date check along with this (Eg: For null and bad date default to min date "1753-01-01"

Convert(datetime,Convert(varchar(10),PunchDate) + convert(datetime, left(right(('0' + convert(varchar(4), punchtime)),4),2) + ':' + right(('0' + convert(varchar(4),
punchtime)),2))) as PunchDate
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 16:11:27
Changed and its working..Thanks a lot
Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-15 : 07:28:10
quote:
Originally posted by sql_chaser

This works excellent...Shouldn't I add one more convert to make it datetime..Also is there any where I can add the Date check along with this (Eg: For null and bad date default to min date "1753-01-01"

Convert(datetime,Convert(varchar(10),PunchDate) + convert(datetime, left(right(('0' + convert(varchar(4), punchtime)),4),2) + ':' + right(('0' + convert(varchar(4),
punchtime)),2))) as PunchDate




For null dates use case statement case when len(PunchDate) > 0 then "PunchDate" end and for date check in where statement use isdate(PunchDate) = 1 but in case of null you want your date then just change

case when len(PunchDate) > 0 then PunchDate else "1753-01-01" end

Go to Top of Page
   

- Advertisement -