Advertisement

04.03.2008 at 02:44PM PDT, ID: 23294369
[x]
Attachment Details

Create Crosstab/Pivot table

Asked by SWRO in MS SQL Server

Tags: Microsoft, SQL Server, T-SQL

Hello, I am trying to create the output in the attached code snippet:

Here's the setup:
Within a date range (say 11/15/2007 thru 2/10/2008) a promotion will run. I need to track bookings on a daily basis so every date should have a value (either 0 or some number > 0). One table has all of the date values for that range. I need to join that to a table that has all of the bookings.

Essentially, every ID (represents a sailing) needs to have a value for every day of that promotion regardless of whether anyone booked or not. If no bookings occurred on that day, it should be a zero.
So, if a promotion runs for 87 days then there should be at least 87 records for a specific sailing regardless of whether or not there were no bookings.

Does that make sense? Is that a CROSS JOIN or a crazy Crosstab?

BTW - This is SQL 2000.
Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
Line	Sailing	Duration	Dest.	Ship	ID	Week1
Alpha	5/1/2008	7	Caribbean	Ship1	123456	0
Alpha	6/5/2008	9	Caribbean	Ship1	123457	1
Alpha	7/5/2008	12	Caribbean	Ship1	123458	1
Alpha	9/1/2008	12	Caribbean	Ship2	123459	0
Beta	2/2/2008	3	Caribbean	Ship3	155555	0
Beta	3/3/2008	5	Caribbean	Ship8	155556	1
Gamma	1/2/2008	8	Caribbean	Ship9	1675211	0
 
-----------------------------------------------------
Line	Sailing	Dest.	Ship	ID	Date	Bookings
Alpha	5/1/2008	Caribbean	Ship1	123456	11/15/2007	0
Alpha	5/1/2008	Caribbean	Ship1	123456	11/16/2007	1
Alpha	5/1/2008	Caribbean	Ship1	123456	11/17/2007	1
Alpha	5/1/2008	Caribbean	Ship1	123456	11/18/2007	2
Alpha	5/1/2008	Caribbean	Ship1	123456	11/19/2007	1
Alpha	5/1/2008	Caribbean	Ship1	123456	11/20/2007	0
[+][-]04.03.2008 at 06:53PM PDT, ID: 21278528

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.05.2008 at 09:49PM PDT, ID: 21290774

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: MS SQL Server
Tags: Microsoft, SQL Server, T-SQL
Sign Up Now!
Solution Provided By: yuching
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628