May 11, 2011 at 1:26 pm
Can you provide a sample of what your expecting for a result set?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 1:46 pm
3Marketplace Order ID 2Marketplace Product wps.QA1Start wps.QA1Closed wps.QA2Start wps.QA2Closed
WS849369 Wintel Package Request 2010/03/29 03:55:36.000 2010/03/30 18:57:50.000 2010/03/30 18:57:50.000 2010/03/30 19:57:50.000
The Closed is the Start date / time of the next step.
May 11, 2011 at 2:00 pm
I had to play around with the Item names to label the Start and End Dates. I haven't had a chance to fully test it, but here's a start for you. I just want to caution you again about using CTEs on large volumes of data; it can cause huge performance problems. With that said:
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item] + '_EndDate'
FROM #t
union all
SELECT DISTINCT [1Request Item] + '_StartDate'
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head Desc
EXEC (';with cte as
(select [3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [3Marketplace Order ID], [1Request Item] order by [3Marketplace Order ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
union all
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_StartDate'', NextRow.[11Item Date Closed] StartDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 2:24 pm
WOW!!! Do you need a room mate 🙂 Personal assistant 🙂 Been able to add more to it and it's pulling back 5 k pretty qucik, faster that my 1k lines of CASE :w00t:
If it bogs them down then they will just have to buy more hardware:-D
Thank You thank you thank you!!!
I did notice that some of the start dates are not coming through but with some work I should be able to get it. On the sample data it worked though.
May 11, 2011 at 2:40 pm
here is the Full code
DROP TABLE #t
Create TABLE #t
([3Marketplace Order ID] [nvarchar](138) NULL,
[1Request Item] [nvarchar](127) NULL,
[2Marketplace Product] [nvarchar](138) NULL,
[11Item Date Closed] [datetime] NULL,
[10Date Due] [datetime] NULL
)
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:57:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA1','Wintel Package Request','2010/03/29 03:58:50.000','2009/10/14 09:25:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA2','Wintel Package Request','2010/03/29 03:58:36.000','2009/10/15 09:15:43.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item] + '_EndDate'
FROM #t
union all
SELECT DISTINCT [1Request Item] + '_StartDate'
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head Desc
EXEC (';with cte as
(select [3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [3Marketplace Order ID], [1Request Item] order by [3Marketplace Order ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
union all
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_StartDate'', NextRow.[11Item Date Closed] StartDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
I thought maybe the dates were to close since I just copy and pasted some values in but the start dates on only a few are coming through.
May 12, 2011 at 6:04 am
tictoc (5/11/2011)
WOW!!! Do you need a room mate 🙂 Personal assistant 🙂
Depends on if you're male or female 🙂
Glad I could help
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 6:45 am
LOL nope. But if you were female 😉
The code I posted using the example data has dates missing and I cannot figure out why. I am thinking that the dates / column being used are random depending on the selection. True? The only way I see would be to hard code the steps to the one being used..
([3Marketplace Order ID] [nvarchar](138) NULL,
[1Request Item] [nvarchar](127) NULL,
[2Marketplace Product] [nvarchar](138) NULL,
[11Item Date Closed] [datetime] NULL,
[10Date Due] [datetime] NULL
)
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:57:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA1','Wintel Package Request','2010/03/29 03:58:50.000','2009/10/14 09:25:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA2','Wintel Package Request','2010/03/29 03:58:36.000','2009/10/15 09:15:43.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:59:14.000','2009/10/16 13:00:00.000')
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT [1Request Item] + '_EndDate'
FROM #t
union all
SELECT DISTINCT [1Request Item] + '_StartDate'
FROM #t
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head Desc
EXEC (';with cte as
(select [3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [3Marketplace Order ID], [1Request Item] order by [3Marketplace Order ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
union all
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_StartDate'', NextRow.[11Item Date Closed] StartDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
May 12, 2011 at 7:06 am
That's because of the Max(EndDate) that we are using in the pivot. You need to use an aggregate for the pivot function, so I used that field. Are you expecting each [3Marketplace Order ID] to show with Start and End dates? Based on the data you provided, what are the results you are expecting?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 8:03 am
Mike01 (5/12/2011)
That's because of the Max(EndDate) that we are using in the pivot. You need to use an aggregate for the pivot function, so I used that field. Are you expecting each [3Marketplace Order ID] to show with Start and End dates? Based on the data you provided, what are the results you are expecting?
Ahh I see ! I will post up the order of the flow, there is a specific order they travel in, in a few but first I am going to try a few things and learn some 🙂 Begging for code is not cool!
So far I can COALESCE at a pretty decent level thanks to all of your help! It's also a huge help on another part I am working on.
May 12, 2011 at 9:39 am
This may help or muddy things up 🙂
If [2Marketplace Product] = Wintel Pre Packaged Request for DSI/OVCM Packages(Desktops)
Then this is the workflow
WPS.PrePackageFileUpload.Auto
WPS.PrePackageValidation
WPS.UserConfirmation
WPS.SystemCheck.Auto
WPS.DeviationQA.Auto
WPS.PrePackageQA.Auto
WPS.AdvancedParameter.Auto
WPS.PublishToDev.Auto
WPS.PublishToUAT.Auto
WPS.PrePackageUATApproval
WPS.IntegrationTesting.Auto
WPS.PublishToProd.Auto
WPS.Prod Entitlement
WPS.Notify.Auto
which would breakdown to
WPS.PrePackageFileUpload.Auto_StartDate --- Probably would never be there since there is no date to use
WPS.PrePackageFileUpload.Auto_EndDate -- This should be the Start Date for the workflow
WPS.PrePackageValidation_StartDate -- Should use the Upload EndDate as the start date then roll forward
WPS.PrePackageValidation_StartEnd
WPS.UserConfirmation_StartDate - Uses Validation EndDate as start date
WPS.UserConfirmation_EndDate
WPS.SystemCheck.Auto_StartDate - Uses Confirmation EndDate as start date
WPS.SystemCheck.Auto_EndDate
WPS.DeviationQA.Auto_StartDate - Uses Check EndDate as start date
WPS.DeviationQA.Auto_EndDate
WPS.PrePackageQA.Auto_StartDate - Uses DeviationQA EndDate as start date
WPS.PrePackageQA.Auto_EndDate
WPS.AdvancedParameter.Auto_StartDate - Uses PrePackageQA EndDate as start date
WPS.AdvancedParameter.Auto_EndDate
WPS.PublishToDev.Auto_StartDate - Uses AdvancedParameter EndDate as start date
WPS.PublishToDev.Auto_EndDate
WPS.PublishToUAT.Auto_StartDate - Uses PublishToDev EndDate as start date
WPS.PublishToUAT.Auto_EndDate
WPS.PrePackageUATApproval_StartDate - Uses PublishToUAT EndDate as start date
WPS.PrePackageUATApproval_EndDate
WPS.IntegrationTesting.Auto_StartDate - Uses PrePackageUATApproval EndDate as start date
WPS.IntegrationTesting.Auto_EndDate
WPS.PublishToProd.Auto_StartDate - Uses IntegrationTesting EndDate as start date
WPS.PublishToProd.Auto_EndDate
WPS.Prod Entitlement_StartDate - Uses PublishToProd.Auto EndDate as start date
WPS.Prod Entitlement_EndDate
WPS.Notify.Auto_StartDate - Uses Prod Entitlement EndDate as start date
WPS.Notify.Auto_EndDate
As a thought - I was thinking about creating another select and use CASE to breakdown the different Products /workflows but that's a huge ammount of CASE..
May 12, 2011 at 11:45 am
One thing I am trying to figure out is how to put the results in a specific order
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by Head Desc
You can adjust the ol ACS or DESC BUT what if you want A B C D and it's D C B A. I read that CTE does not allow ORDER BY or Group BY but I have read some on Dynamic Ordering.
If this = X then use this List ,, make sense or on your inside voice your hearing Chris Rock Say "What is this !@$$#$ trying to do to us!"
Gotta have some hummor in all of this 🙂
thanks
May 12, 2011 at 12:15 pm
I guess it depends. The code you have will give the columns in desc order. If you remove the desc, then the columns ascending order. If you want a mix (I.e ABDC), then you need to define the @Cols table differently and then put the records in. For example, we are putting all the distinct [1Request Item] records into the @Cols table in one step but using a Union All. If you wanted all the Auto colums first, then the Quarter columns, you could define the @Cols table to have an identity column and then a sort order column. Then you would get
DECLARE @Cols TABLE (ID int identity(1,1), SortOrder int, Head VARCHAR(MAX))
--Start and End Dates For Auto Fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%Auto%'
union all
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] like '%Auto%'
-- Start and End Dates For Quarter fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%Quarter%'
and [1Request Item] not like '%Auto%'
union all
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] like '%Quarter%'
and [1Request Item] not like '%Auto%'
Now when you go to get your ColumnList, you change the sort order
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by SortOrder, ID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 1:43 pm
ahh making sense, kind of like CASE which I am used to except this you got more control. I added everthing in and I keepgetting a syntax error unless I remove the _StartDate below the Union All - marked with stops here.
So building on this I could infact add each step in order using = and the value name. Cool! Where am I wrong on the syntax?
DROP TABLE #t
Create TABLE #t
([3Marketplace Order ID] [nvarchar](138) NULL,
[1Request Item] [nvarchar](127) NULL,
[2Marketplace Product] [nvarchar](138) NULL,
[11Item Date Closed] [datetime] NULL,
[10Date Due] [datetime] NULL
)
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA1','Wintel Package Request','2010/03/29 03:57:50.000','2009/12/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.QA2','Wintel Package Request','2010/03/29 03:55:36.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:55:31.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:57:51.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:19.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849369','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:12:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA1','Wintel Package Request','2010/03/29 03:54:50.000','2009/10/14 09:15:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.QA2','Wintel Package Request','2010/03/29 03:55:39.000','2009/10/15 09:15:33.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:15:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:55:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 16:57:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:29:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Prod Entitlement','Wintel Package Request','2010/03/20 18:59:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849370','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:12:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA1','Wintel Package Request','2010/03/29 03:58:50.000','2009/10/14 09:25:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.QA2','Wintel Package Request','2010/03/29 03:58:36.000','2009/10/15 09:15:43.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/03/29 03:35:38.000','2009/10/15 10:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:25:41.000','2009/10/15 11:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 18:47:50.000','2009/10/14 11:15:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:20:59.000','2009/10/15 11:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 04:21:01.000','2009/10/15 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Prod Entitlement','Wintel Package Request','2010/03/31 18:49:13.000','2009/10/14 12:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849371','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:13:14.000','2009/10/16 13:00:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.QA1','Wintel Package Request','2010/03/29 03:52:50.000','2009/10/14 09:35:32.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.QA2','Wintel Package Request','2010/03/29 02:58:36.000','2009/10/15 07:15:43.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.GenerateWrapID.Auto','Wintel Package Request','2010/11/29 03:35:38.000','2009/10/15 10:05:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 01:25:41.000','2009/10/15 12:15:34.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.Prod Entitlement','Wintel Package Request','2010/03/30 14:47:50.000','2009/10/14 11:11:35.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.GenerateWrapID.Auto','Wintel Package Request',' 2010/03/29 04:22:09.000','2009/10/15 11:11:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.PublishToProd.Auto','Wintel Package Request','2010/03/29 03:11:01.000','2009/10/15 12:00:11.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.Prod Entitlement','Wintel Package Request','2010/03/31 16:49:13.000','2009/10/14 12:30:00.000')
insert INTO #t ([3Marketplace Order ID], [1Request Item], [2Marketplace Product], [11Item Date Closed],[10Date Due])
VALUES ('WS849373','WPS.Notify.Auto','Wintel Package Request','2010/03/30 18:32:10.000','2009/10/16 13:12:25.000')
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (ID int identity(1,1), SortOrder int, Head VARCHAR(MAX))
--Start and End Dates For Auto Fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%Auto%'
union ALL
--KEEPS STOPPING HERE
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] LIKE '%Auto%'
-- Start and End Dates For Quarter fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%CreateUploadFolder%'
and [1Request Item] not like '%Auto%'
union ALL
--KEEPS STOPPING HERE
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] like '%CreateUploadFolder.Auto%'
and [1Request Item] not like '%Auto%'
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
order by SortOrder, ID
EXEC (';with cte as
(select [3Marketplace Order ID], [1Request Item], [11Item Date Closed],
Row_Number() over (partition by [3Marketplace Order ID], [1Request Item] order by [3Marketplace Order ID], [1Request Item], [11Item Date Closed]) RowNum
from #t)
SELECT *
FROM
(
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_EndDate'' [1Request Item], CurRow.[11Item Date Closed] EndDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
union all
SELECT CurRow.[3Marketplace Order ID], CurRow.[1Request Item] + ''_StartDate'', NextRow.[11Item Date Closed] StartDate
from cte CurRow left outer join cte NextRow
on CurRow.[3Marketplace Order ID] = NextRow.[3Marketplace Order ID]
and CurRow.[1Request Item] = NextRow.[1Request Item]
and CurRow.RowNum = NextRow.RowNum + 1
) t
PIVOT (max([EndDate]) FOR [1Request Item] IN (' + @ColsList + ')) PVT')
May 13, 2011 at 6:07 am
When you do the insert and have a union all, the Insert into statement only needs to appear once
--Start and End Dates For Auto Fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%Auto%'
union ALL
--KEEPS STOPPING HERE
--INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 1, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] LIKE '%Auto%'
-- Start and End Dates For Quarter fields
INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_EndDate'
FROM #t where [1Request Item] like '%CreateUploadFolder%'
and [1Request Item] not like '%Auto%'
union ALL
--KEEPS STOPPING HERE
--INSERT @Cols (SortOrder, Head)
SELECT DISTINCT 2, [1Request Item] + '_StartDate'
FROM #t where [1Request Item] like '%CreateUploadFolder.Auto%'
and [1Request Item] not like '%Auto%'
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2011 at 8:53 am
Thank You! I was p[laying with it late last night and realized the double tap there (insert) and felt pretty stupid asking an obvious question. Been staring at this for too long if I am allowed to use any excuse:-)
I am going through and working out the list. One thing though is if I use a SUBSTRING on the SUBSTRING([1Request Item], 5, 25) AS [1Request Item],
I can remove the WPS. and leave the rest of the name intact then build my list. Seems to be working so far but I am sure there is a performance hit in there.
thanks
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply