Max and Pivot based on Codes - SQL Server Forums

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.....

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
*/

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;

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..

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

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....

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-12 : 11:17:11

Have you tried my privous post with time?

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

sql_chaser
Starting Member

33 Posts

Posted - 2014-12-12 : 16:11:27

Changed and its working..Thanks a lot

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources