Query to show dates in columns

Question

Query to show dates in columns

aslowmo

SSC Rookie

Points: 44
More actions
November 3, 2009 at 5:44 am

#142875

Hello SqlServerCentrals Users
I have this question/request.
Id | Name | StartDate. | Enddate | Type
---------------------------------------------------------------------------------
2 | Marie | 11/2/2009 | 11/2/2009 | A1
3 | Hans | 11/3/2009 | 11/6/2009 | A4
4 | Marie | 11/4/2009 | 11/4/2009 | A1
5 | Marie | 11/5/2009 | 11/5/2009 | A4
6 | Marie | 11/6/2009 | 11/6/2009 | A3
And I would like to display if a startdate and the enddate not are the same like this.
Name | Monday | Thusday | Wednesday | Thursday | Friday
---------------------------------------------------------------------------------
Hans | | A4 | A4 | A4 | A4
Marie | A1 | | A1 | A4 | A3
And the final request is how can I exspand the procedure so i can view 5 or 6 weeke at one time?
So far I this query but it only works with startdate:
SELECT Name,
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday]
FROM (SELECT Name, type, Datename(Weekday, Startdate) Myweekname
FROM wp_Vacation) Src PIVOT (max(type) FOR Myweekname
IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt
I was helped by some people on asp.net. I'm pretty new to the sql syntax.
So if somebody also can explain the syntax I would be glad.

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

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

I see what you are trying to do. Along with pivot u got to expand the number of days between start date and enddate and get your values for that. Look at this post for only that part and try and see if you can club both pivot and this number table thing to get your result.

http://www.sqlservercentral.com/Forums/Topic812461-338-1.aspx

Just a kind suggestion also. If you provide your test data (in INSERT INTO VALUES format) and create table script, you would get better quicker reply and lot of folks would be trying to help.

---------------------------------------------------------------------------------

aslowmo SSC Rookie Points: 44 More actions · Answer 2

Thank you for your reply nabha, I'll check out the link you provided

And here is the test data:

declare @t table ( id int, name nvarchar(10),StartDate datetime, EndDate datetime,Type nvarchar(10))

insert into @t values (2,'Marie','11/2/2009','11/2/2009', 'A1')

insert into @t values (3,'Hans','11/3/2009','11/6/2009', 'A2')

insert into @t values (4,'Marie','11/2/2009','11/2/2009', 'A3')

insert into @t values (5,'Marie','11/2/2009','11/2/2009', 'A2')

insert into @t values (6,'Hans','11/6/2009','11/6/2009', 'A1')

SELECT Name,

[Monday],

[Tuesday],

[Wednesday],

[Thursday],

[Friday]

FROM (SELECT Name, type, Datename(Weekday, Startdate) Myweekname

FROM @t) Src PIVOT (max(type) FOR Myweekname

IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt

Infact, I have one more problem. How do I combine two records when the are on the same day.

right now then I run the query I get:

Name Monday Tuesday Wednesday Thursday Friday

---------- ---------- ---------- ---------- ---------- ----------

Hans NULL A2 NULL NULL A1

Marie A3 NULL NULL NULL NULL

But should have been:

Name Monday Tuesday Wednesday Thursday Friday

---------- ---------- ---------- ---------- ---------- ----------

Hans NULL A2 A2 A2 A1

Marie A1A2A3 NULL NULL NULL NULL

I'm really new to sql. I can do basic update select insert. But nothing like this.

So if you have some other approach to this please let me know.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

See if this helps, i am using max function now. You might need to use some concatenation function to get all the three values. Thats next step. I cant think about it as of now.

I'm using cross tab now!

For the tally table part follow the link that I provided earlier. Thanks.

Create table #t ( id int, name nvarchar(10),StartDate datetime,

EndDate datetime,Type nvarchar(10))

insert into #t values (2,'Marie','11/2/2009','11/2/2009', 'A1')

insert into #t values (3,'Hans','11/3/2009','11/6/2009', 'A2')

insert into #t values (4,'Marie','11/2/2009','11/2/2009', 'A3')

insert into #t values (5,'Marie','11/2/2009','11/2/2009', 'A2')

insert into #t values (6,'Hans','11/6/2009','11/6/2009', 'A1')

SELECT Name, type, Datename(Weekday, Startdate) Myweekname

FROM #t) Src PIVOT (max([type]) FOR Myweekname

IN ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) Pvt

Select

name,

max(CASE WHEN MyWeekname = 'MONDAY' THEN [Type] END) as

[Monday],

max(CASE WHEN MyWeekname = 'TUESDAY' THEN [Type] END) as

[Tuesday],

max(CASE WHEN MyWeekname = 'Wednesday' THEN [Type] END) as

[Wednesday],

max(CASE WHEN MyWeekname = 'Thursday' THEN [Type] END) as

[Thursday],

max(CASE WHEN MyWeekname = 'Friday' THEN [Type] END) as [Friday]

from

(Select id,name,N, [Type], datename(weekday,dateadd(dd, N- datepart

(dd,startdate), startdate)) as MyWeekname

from #t

JOIN dbo.Tally

ON N Between datepart(dd, StartDate) and datepart(dd, enddate)) src

Group by name

---------------------------------------------------------------------------------

aslowmo SSC Rookie Points: 44 More actions · Answer 4

Thank you nabha for your time.

A person on the msdn forum cracked it.

Just to close this quistion with the answer, go here to see it.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 5

Before you trot off, answer a question for me, please. Why did you need to format data in this manner?

I also noticed that you didn't get your dynamic date problem answered. 😉

--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)

aslowmo SSC Rookie Points: 44 More actions · Answer 6

Hi.

I was making a booking calendar.

Like this

And regarding the dynamic dates.

The way I found was just to build the string ex, [12/1/2009],[12/1/2009],[12/1/2009] in c# then pass it in as a variable to the storedprocedure.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 7

Thanks for the explanation.

--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)