How do you create Valid_From and Valid_To dates

Question

How do you create Valid_From and Valid_To dates

OptimusPrime

Ten Centuries

Points: 1379
More actions
March 8, 2011 at 5:20 pm

#248018

Hello experts,
Need some help once again please. I'm not sure I know how to group by on the sample data below to get the desired result. As always, really appreciate the help. --Michael
key productdate
1 A2010-01-01
1 A2010-01-02
1 B2010-01-03
1 A2010-01-04
Desired Result:
KeyProduct from_dateto_date
1A 2010-01-012010-01-02
1B 2010-01-032010-01-03
1A 2010-01-049999-01-01

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003947 More actions · Answer 1

Deee -Daah! (3/8/2011)
Hello experts,
Need some help once again please. I'm not sure I know how to group by on the sample data below to get the desired result. As always, really appreciate the help. --Michael
key productdate
1 A2010-01-01
1 A2010-01-02
1 B2010-01-03
1 A2010-01-04
Desired Result:
KeyProduct from_dateto_date
1A 2010-01-012010-01-02
1B 2010-01-032010-01-03
1A 2010-01-049999-01-01

This problem is easily solved by the classic "Enumerate, Pivot, and Display" technique.

--===== Conditionally drop the test table to make reruns easier.

-- This is NOT a part of the solution.

IF OBJECT_ID('TempDB..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create and populate a test table

-- This is NOT a part of the solution.

SELECT [Key],

Product,

[Date] = CAST([Date] AS DATETIME)

INTO #MyHead

FROM (

SELECT 1,'A','2010-01-01' UNION ALL

SELECT 1,'A','2010-01-02' UNION ALL

SELECT 1,'B','2010-01-03' UNION ALL

SELECT 1,'A','2010-01-04'

) d ([Key], Product, [Date])

;

--===== Using the test data we just created, solve the problem

WITH

cteEnumerate AS

(

SELECT GroupNum = (ROW_NUMBER() OVER (PARTITION BY [Key], Product ORDER BY [Date])-1)/2,

RowNum = (ROW_NUMBER() OVER (PARTITION BY [Key], Product ORDER BY [Date])-1)%2,

[Key], Product, [Date]

FROM #MyHead

),

ctePivot AS

(

SELECT [Key], Product, GroupNum,

From_Date = MAX(CASE WHEN RowNum = 0 THEN [Date] ELSE NULL END),

To_Date = MAX(CASE WHEN RowNum = 1 THEN Date ElSE NULL END)

FROM cteEnumerate

GROUP BY [Key], Product, GroupNum

) --=== Display

SELECT [Key],

Product,

From_Date,

To_Date = ISNULL(To_Date, CASE

WHEN GroupNum > 0

THEN CAST('9999' AS DATETIME)

ELSE From_Date

END)

FROM ctePivot

ORDER BY From_Date

;

As a bit of a side bar, the likely reason why you waited almost 4 hours for a solution to such a simple problem is simply because lots of people like to test their coded solutions before posting them. In order to do that, they have to have data but they just don't want to take the time to change the data you posted into something that can be used to populate a test table.

Before you post another problem, I recommend you study and use the methods to post data found in the article at the first link in my signature line below. 😉

My other recommendation is that you get out of the habit of using SQL Keywords as column names. And, yes, as of SQL Server 2008, "Date" is a reserved word. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OptimusPrime Ten Centuries Points: 1379 More actions · Answer 2

Dear Jeff, thanks very much. Also, lots of thanks for the recommendations. I'll post better next time.

scurrp Grasshopper Points: 12 More actions · Answer 3

Jeff would it be easy to modify your code if the

Desired Result was:

Key Product from_date to_date

1 A 2010-01-01 2010-01-01 --different end date

1 A 2010-01-02 2010-01-03 --This is a new record

1 A 2010-01-04 9999-01-01 --same

1 B 2010-01-03 9999-01-01 --different end date

Cheers

Pete

Jeff Moden SSC Guru Points: 1003947 More actions · Answer 4

Allow me to ask a question in return before I respond, please. Why are we playing musical chairs with the requirements? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)