Getting Dates

Question

Post reply

Getting Dates

harag

SSC Enthusiast

Points: 117
More actions
September 25, 2010 at 3:41 pm

#228745

Hi
I need to pull the first 5 dates (earliest) dates from a table for each item. so If I have the following data in a table
ITEM|DateCreated
------------------
item1, date1
item1, date2
item1, date3
item1, date4
item1, date5
item1, date6
item1, date7
item2, date1
item2, date2
item2, date3
item3, date1
What I need to return in a recordset is this:
Item | Date1 | Date2 | Date3 | Date4 | Date5
-----------------------------------------------
item1, date1, date2, date3, date4, date5
item2, date1, date2, date3, NULL, NULL
item3, date1, NULL, NULL, NULL, NULL
As you can see if the item has fewer than 5 dates then the rest needs to be filled out with NULL
Can anyone please help me with this?
Thanks
H.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

harag

Read these 2 articles byJeff Moden:

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

another article

http://www.sqlservercentral.com/scripts/T-SQL/70819/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

harag SSC Enthusiast Points: 117 More actions · Answer 2

Hi

Thanks for the links to the articals, I'll certainly look over them tomorrow, but I'm not sure pivot tables can be used for what I need as it requires an "aggregate" function.

Would it be possible for you to do write some code for me to do what I need?

Cheers

H.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

PIVOT requires aggregation - read Jeff Moden article

Will I write it for you - I respectfully decline for doing someone else work I am normally paid my consulting fee.

Now, that said, read the articles, you will pleasantly surprised at how easy it will be for you to write the code.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

I definitely agree with Ron here ; you have not tried anything, then why would people consider working for you when u haven't even tried, or atleast that's what u have said..Hmmm... anyways, i have ample free time this weekend, so i made the query for you.

DECLARE @tab TABLE

(

Item VARCHAR(15),

DateCreated DATETIME

)

INSERT INTO @tab ( Item , DateCreated )

SELECT 'item1',GETDATE()

UNION ALL SELECT 'item1',DATEADD( DD,1, GETDATE())

UNION ALL SELECT 'item1',DATEADD( DD,5, GETDATE())

UNION ALL SELECT 'item1',DATEADD( DD,4, GETDATE())

UNION ALL SELECT 'item1',DATEADD( DD,7, GETDATE())

UNION ALL SELECT 'item1',DATEADD( DD,3, GETDATE())

UNION ALL SELECT 'item1',DATEADD( DD,2, GETDATE())

UNION ALL SELECT 'item2',DATEADD( DD,6, GETDATE())

UNION ALL SELECT 'item2',DATEADD( DD,1, GETDATE())

UNION ALL SELECT 'item2',DATEADD( DD,2, GETDATE())

UNION ALL SELECT 'item3',GETDATE()

; WITH CTE0 AS

(

SELECT RN = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY DateCreated )

, Item

, DateCreated

FROM @tab

) -- select * from CTE0

SELECT

Item ,

[1] AS Date1 ,

[2] AS Date2 ,

[3] AS Date3 ,

[4] AS Date4 ,

[5] AS Date5

FROM

(

SELECT RN,Item, DateCreated FROM CTE0 WHERE RN <= 5

) PIVOT_TABLE

PIVOT

(

MAX(DateCreated) FOR RN IN ( [1] ,[2], [3] , [4] , [5] )

) PIVOT_HANDLE

Just look at how i have create sample data.. why don't people give ready-to-use samples for others to work 🙁 (sigh)..

Hope the query helps you; if u don't understand any part of it, tel us, we will help you out..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Harag, you seem to be very new to the forum, so i recommend you going through this following Jeff Moden's article first ; this article will help you present your questions in such that it will invite the biggies on this forum to give them a shot at your question.

FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

harag SSC Enthusiast Points: 117 More actions · Answer 6

Hi

Yes, I'm very new to the forum, so will certainly read the Etiquette artical and follow it. Thanks for the help, PIVOTS are really new to me so still trying to understand them, but your little code really helps, thanks.

H.

harag SSC Enthusiast Points: 117 More actions · Answer 7

ColdCoffee,

Thanks for all the help I've spent this morning playing around with the code and it works great. I did have a go last night before joining the forum but my way was with several left joins, but it wasn't working as expected and in frustration I wrote to the forum.

Loving the WITH command as well, that's new to me as it makes the below code much clearer to read IMO. Which is the way I would have written it.

Thanks again for the help, much appreciated.

H.

SELECT RN, Item, DateCreated FROM

(

SELECT RN = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY DateCreated )

,Item

,DateCreated

FROM Test

) AS myTable

WHERE RN <= 4