October 8, 2008 at 10:20 am
hi guys, i have been working using PIVOT for the first time, i am going to create a view with the following query; but i can't get the max(CreatedDate), can anyone tell me why please?
CREATE TABLE [dbo].[tblDistributor](
[DistributorID_PK] [int] NULL,
[DistributorCode_FK] [int] NULL,
[CodeID] [int] NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
insert into tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values (11525,1731,1120,'2005-10-27 09:42:34.420')
insert into tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values(11526,1731,1120,'2006-10-27 09:42:34.733')
insert into tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values(11539,1731,2011,'2005-10-27 10:10:36.633')
Select DistributorCode_FK,[1120]as dateOpened, [2011]as dateClosed
FROM ( select DistributorCode_FK,CodeID,CreatedDate
FROM tblDistributor where DistributorCode_FK = 1731) as s
PIVOT (MAX(CreatedDate) FOR CodeID IN ([1120],[2011]))as p
ORDER BY DistributorCode_FK
This is how my original table looks like:
DistributorID_PK DistributorCode_FKCode IDCreatedDate
11525173111202005-10-27 9:42:34.420
11526173111202006-10-27 9:42:34.733
11539173120112005-10-2710:10:36.633
Results:
DistributorCode_FK dateCreated dateClosed
17312005-10-27 10:10:36.6332005-10-2710:10:36.633
Desired Results:
DistributorCode_FKdateCreated dateClosed
17312006-10-27 09:42:34.733 2005-10-27 10:10:36.633
October 8, 2008 at 12:29 pm
I pasted your code in and got your desired results... 🙁
I'm going to guess that the results you posted are not exactly from the code you posted - the column names in your select statement don't match up with the column names in the resultset (dateOpened vs. dateCreated). So I think the code you have here is right. Any chance your "real" select is larger than what you have here and some part of that might be further filtering your resultset?
Chad
October 8, 2008 at 3:33 pm
CREATE TABLE #tblDistributor(
[DistributorID_PK] [int] NULL,
[DistributorCode_FK] [int] NULL,
[CodeID] [int] NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
insert into #tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values (11525,1731,1120,'2005-10-27 09:42:34.420')
insert into #tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values(11526,1731,1120,'2006-10-27 09:42:34.733')
insert into #tblDistributor(DistributorID_PK,DistributorCode_FK,CodeID,CreatedDate)
values(11539,1731,2011,'2005-10-27 10:10:36.633')
Select DistributorCode_FK,[1120]as dateOpened, [2011]as dateClosed
FROM ( select DistributorCode_FK,CodeID,CreatedDate
FROM #tblDistributor where DistributorCode_FK = 1731) as s
PIVOT (MAX(CreatedDate) FOR CodeID IN ([1120],[2011]))as p
ORDER BY DistributorCode_FK
This is my result (SQL 2008)
DistributorCode_FKdateOpeneddateClosed
17312006-10-27 09:42:34.7332005-10-27 10:10:36.633
October 9, 2008 at 2:53 pm
thank you guys, my bad, i had an extra criteria in my query. :blush:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply