• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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
0
fjkilken
Asked:
fjkilken
  • 8
  • 4
  • 2
2 Solutions
 
fjkilkenAuthor Commented:
file embedded also:

example.xls
0
 
DultonCommented:
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]

Open in new window

0
 
fjkilkenAuthor Commented:
thanks Dulton but sorry - but that only gives 2 rows of data, 4 rows are expected
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
DultonCommented:
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.




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]

Open in new window

0
 
fjkilkenAuthor Commented:
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
0
 
fjkilkenAuthor Commented:
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
0
 
DultonCommented:
The end date didn't need wrapped in a case statement.

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] 

Open in new window

0
 
fjkilkenAuthor Commented:
ok - upgraded ny tools - now running 11.0.3368 and all is good - thanks!
0
 
fjkilkenAuthor Commented:
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
0
 
fjkilkenAuthor Commented:
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','RETIRED','8/26/2012','9/29/2012')
                                ,('D25538-402','JAPAN','RETIRED','9/30/2012','11/02/2012')
                                                ,('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]
0
 
Olaf DoschkeSoftware DeveloperCommented:
>LAG and LEAD functions look really cool! ...any idea of how I may enable them?
Well, not without SQL2012, but the article shows an alternative using CTE, which is similar to what the others here already do.

Bye, Olaf.
0
 
DultonCommented:
your original example data doesn't show multiple site data. that's why the code didn't consider it.

try the below for the sample data you showed in post: 40006559

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','RETIRED','8/26/2012','9/29/2012')
                                ,('D25538-402','JAPAN','RETIRED','9/30/2012','11/02/2012')
                                ,('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]
                                ,case when dc.[site] <> pl.[site] then pl.[site] else dc.[site] end as [site]
,case when dc.[status] <> pl.[status] or dc.[site] <> pl.[site] then pl.[status] else dc.[status] end as  [status]
                                , case  when dc.[status] <> pl.[status] or dc.[site] <> pl.[site] 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.[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] 

Open in new window

0
 
fjkilkenAuthor Commented:
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now