fjkilken
asked on
Need SQL query to yield dates range for part status
Hi
I have a table/query result set that needs to be consolidated. This is best explained via the attached file.
Essentially the table needs to be consolidated from it's current 23 rows to 4 rows, acheived by determining the date range for each part status change.
At present there is a row for every month regardless of whether the status changes or not, I need to remove these redundant rows and replace with single a row that shows the MAX and MIN date ranges for that status.
As I said, the file will best explain this - thanks a lot!!
Fergal
example.xls
I have a table/query result set that needs to be consolidated. This is best explained via the attached file.
Essentially the table needs to be consolidated from it's current 23 rows to 4 rows, acheived by determining the date range for each part status change.
At present there is a row for every month regardless of whether the status changes or not, I need to remove these redundant rows and replace with single a row that shows the MAX and MIN date ranges for that status.
As I said, the file will best explain this - thanks a lot!!
Fergal
example.xls
select [part num], [site], [status], Min([Status start date]) as [status start date]
,Max([status end date]) as [status end date]
from MyTable
Group by
[part num], [site], [status]ASKER
thanks Dulton but sorry - but that only gives 2 rows of data, 4 rows are expected
It's not that easy, because status can repeat. min/max queries would get you min date of period1 and max date of period2.
I doubt it can be done with simple query, as you can't simply group by status.
You may try to accomplish this using LAG() or LEAD().
See http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/
Bye, Olaf.
I doubt it can be done with simple query, as you can't simply group by status.
You may try to accomplish this using LAG() or LEAD().
See http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/
Bye, Olaf.
I didn't type your whole table, just enough to show the concept.
you can establish an initial order on this table by using row_number on some field that isnt' changing. I chose [part num]... that'll establish a unique incrementing integer on each row, preserving the original order.
from there, it's just a recursive CTE, looping through the dataset, incrementing by the row_number field and seeing when the status changes.
you can establish an initial order on this table by using row_number on some field that isnt' changing. I chose [part num]... that'll establish a unique incrementing integer on each row, preserving the original order.
from there, it's just a recursive CTE, looping through the dataset, incrementing by the row_number field and seeing when the status changes.
declare @MyTable table
([part num] varchar(10)
,[site] varchar(10)
,[status] varchar(10)
,[status start date] date
,[status end date] date
)
insert into @MyTable ([part num], [site], [status], [status start date], [status end date])
values ('D25538-402','USPSS','EOL','1/29/2012','2/25/2012')
,('D25538-402','USPSS','RETIRED','2/26/2012','3/31/2012')
,('D25538-402','USPSS','RETIRED','4/1/2012','4/28/2012')
,('D25538-402','USPSS','RETIRED','4/29/2012','5/26/2012')
,('D25538-402','USPSS','EOL','5/27/2012','6/30/2012')
,('D25538-402','USPSS','RETIRED','7/1/2012','7/28/2012')
,('D25538-402','USPSS','RETIRED','7/29/2012','8/25/2012')
,('D25538-402','USPSS','RETIRED','8/26/2012','9/29/2012')
,('D25538-402','USPSS','RETIRED','2/26/2012','12/28/2012')
;with partListing AS
(
SELECT [part num],[site],[status],[status start date], [status end date]
,row_number() OVER(ORDER BY [part num]) AS [InitialOrder]
FROM @MyTable
), DateCompile AS
(
SELECT [part num],[site],[status],[status start date], [status end date], [InitialOrder]
FROM PartListing where [initialorder] = 1
union all select
dc.[part num],dc.[site],case when dc.[status] <> pl.[status] then pl.[status] else dc.[status] end as [status]
, case when dc.[status] <> pl.[status] then pl.[status start date] else dc.[status start date] end as [status start date]
,case when dc.[status] = pl.[status] then pl.[status end date] else dc.[status end date] end as [status end date]
,pl.[initialorder]
from datecompile as dc
inner join partListing as pl on dc.[part num] = pl.[part num] and dc.[site] = pl.[site] and dc.[initialorder] + 1 = pl.[InitialOrder]
)
select [part num],[site],[status],[status start date], max([status end date]) AS [status end date]
from
(
select [part num],[site],[status],[status start date], [status end date]
,ROW_NUMBER() OVER(PARTITION BY [part num],[site],[status],[status start date] ORDER BY [status end date]) AS [FinalOrder]
from datecompile
) as t1
group by [part num],[site],[status],[status start date]
order by [status start date]ASKER
thanks for the link Olaf
LAG and LEAD functions look really cool! but although I believe I'm running SQL server 2012 (SQL Server 11.0.3000) - the functions are not available for me - any idea of how I may enable them? thanks
LAG and LEAD functions look really cool! but although I believe I'm running SQL server 2012 (SQL Server 11.0.3000) - the functions are not available for me - any idea of how I may enable them? thanks
ASKER
hi Dulton, thanks for the code - when I ran it I saw an unusual result:
For the second occurentce of "EOL" the status end date should be "2012-06-30" but it is resulting as "2012-05-26"
- any ideas why?
Fergal
part num site status status start date status end date
D25538-402 USPSS EOL 2012-01-29 2012-02-25
D25538-402 USPSS RETIRED 2012-02-26 2012-05-26
D25538-402 USPSS EOL 2012-05-27 2012-05-26
D25538-402 USPSS RETIRED 2012-07-01 2012-12-28
For the second occurentce of "EOL" the status end date should be "2012-06-30" but it is resulting as "2012-05-26"
- any ideas why?
Fergal
part num site status status start date status end date
D25538-402 USPSS EOL 2012-01-29 2012-02-25
D25538-402 USPSS RETIRED 2012-02-26 2012-05-26
D25538-402 USPSS EOL 2012-05-27 2012-05-26
D25538-402 USPSS RETIRED 2012-07-01 2012-12-28
The end date didn't need wrapped in a case statement.
this should work.
this should work.
declare @MyTable table
([part num] varchar(10)
,[site] varchar(10)
,[status] varchar(10)
,[status start date] date
,[status end date] date
)
insert into @MyTable ([part num], [site], [status], [status start date], [status end date])
values ('D25538-402','USPSS','EOL','1/29/2012','2/25/2012')
,('D25538-402','USPSS','RETIRED','2/26/2012','3/31/2012')
,('D25538-402','USPSS','RETIRED','4/1/2012','4/28/2012')
,('D25538-402','USPSS','RETIRED','4/29/2012','5/26/2012')
,('D25538-402','USPSS','EOL','5/27/2012','6/30/2012')
,('D25538-402','USPSS','RETIRED','7/1/2012','7/28/2012')
,('D25538-402','USPSS','RETIRED','7/29/2012','8/25/2012')
,('D25538-402','USPSS','RETIRED','8/26/2012','9/29/2012')
,('D25538-402','USPSS','RETIRED','2/26/2012','12/28/2012')
;with partListing AS
(
SELECT [part num],[site],[status],[status start date], [status end date]
,row_number() OVER(ORDER BY [part num]) AS [InitialOrder]
FROM @MyTable
), DateCompile AS
(
SELECT [part num],[site],[status],[status start date], [status end date], [InitialOrder]
FROM PartListing where [initialorder] = 1
union all select
dc.[part num],dc.[site],case when dc.[status] <> pl.[status] then pl.[status] else dc.[status] end as [status]
, case when dc.[status] <> pl.[status] then pl.[status start date] else dc.[status start date] end as [status start date]
,pl.[status end date] as [status end date]
,pl.[initialorder]
from datecompile as dc
inner join partListing as pl on dc.[part num] = pl.[part num] and dc.[site] = pl.[site] and dc.[initialorder] + 1 = pl.[InitialOrder]
)
select [part num],[site],[status],[status start date], max([status end date]) AS [status end date]
from
(
select [part num],[site],[status],[status start date], [status end date]
,ROW_NUMBER() OVER(PARTITION BY [part num],[site],[status],[status start date] ORDER BY [status end date]) AS [FinalOrder]
from datecompile
) as t1
group by [part num],[site],[status],[status start date]
order by [status start date] ASKER
ok - upgraded ny tools - now running 11.0.3368 and all is good - thanks!
ASKER
just expanding it now to take in other sites but having a problem that it's only bringing back 1 recrods - I'll take a closer look at the code and see what could be changed if possible
ASKER
here's an example of it failing: (returning just a single record)
declare @MyTable table
([part num] varchar(10)
,[site] varchar(10)
,[status] varchar(10)
,[status start date] date
,[status end date] date
)
insert into @MyTable ([part num], [site], [status], [status start date], [status end date])
values
('D25538-402','JAPAN','RET
,('D25538-402','JAPAN','RE
,('D25538-402','USPSS','EO
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','EO
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
;with partListing AS
(
SELECT [part num],[site],[status],[stat
,row_number() OVER(ORDER BY [part num]) AS [InitialOrder]
FROM @MyTable
), DateCompile AS
(
SELECT [part num],[site],[status],[stat
FROM PartListing where [initialorder] = 1
union all select
dc.[part num],dc.[site],case when dc.[status] <> pl.[status] then pl.[status] else dc.[status] end as [status]
, case when dc.[status] <> pl.[status] then pl.[status start date] else dc.[status start date] end as [status start date]
,pl.[status end date] as [status end date]
,pl.[initialorder]
from datecompile as dc
inner join partListing as pl on dc.[part num] = pl.[part num] and dc.[site] = pl.[site] and dc.[initialorder] + 1 = pl.[InitialOrder]
)
select [part num],[site],[status],[stat
from
(
select [part num],[site],[status],[stat
,ROW_NUMBER() OVER(PARTITION BY [part num],[site],[status],[stat
from datecompile
) as t1
group by [part num],[site],[status],[stat
order by [status start date]
declare @MyTable table
([part num] varchar(10)
,[site] varchar(10)
,[status] varchar(10)
,[status start date] date
,[status end date] date
)
insert into @MyTable ([part num], [site], [status], [status start date], [status end date])
values
('D25538-402','JAPAN','RET
,('D25538-402','JAPAN','RE
,('D25538-402','USPSS','EO
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','EO
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
,('D25538-402','USPSS','RE
;with partListing AS
(
SELECT [part num],[site],[status],[stat
,row_number() OVER(ORDER BY [part num]) AS [InitialOrder]
FROM @MyTable
), DateCompile AS
(
SELECT [part num],[site],[status],[stat
FROM PartListing where [initialorder] = 1
union all select
dc.[part num],dc.[site],case when dc.[status] <> pl.[status] then pl.[status] else dc.[status] end as [status]
, case when dc.[status] <> pl.[status] then pl.[status start date] else dc.[status start date] end as [status start date]
,pl.[status end date] as [status end date]
,pl.[initialorder]
from datecompile as dc
inner join partListing as pl on dc.[part num] = pl.[part num] and dc.[site] = pl.[site] and dc.[initialorder] + 1 = pl.[InitialOrder]
)
select [part num],[site],[status],[stat
from
(
select [part num],[site],[status],[stat
,ROW_NUMBER() OVER(PARTITION BY [part num],[site],[status],[stat
from datecompile
) as t1
group by [part num],[site],[status],[stat
order by [status start date]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for the help
I have split the points as Olaf got me pointed to LAG/LEAD which I have found to be very powerful, efficient and easy to use.
If I didn't have SQL 2012, then Dulton's solution is what I would use.
Thanks guys!
Fergal
I have split the points as Olaf got me pointed to LAG/LEAD which I have found to be very powerful, efficient and easy to use.
If I didn't have SQL 2012, then Dulton's solution is what I would use.
Thanks guys!
Fergal
ASKER
example.xls