SQL To Have Matrix (Three Tables Producing Results Set with Variable Rows & Columns)

Hi all,

Let me preface this by saying that I'm certainley not after free work and if this subject turns out to be an in depth complex answer, I'm happy to hire someone!

I'm developing a website for boat charters and the rates for charter vary based on time (e.g. Spring, Summer, Xmas, etc).  Typically I have simple had a table like this:

BOATID (INT)
BOAT NAME (NVARCHAR)
SPRINGRATE (MONEY)
SUMMERRATE (MONEY)

with a row for each boat which contained the rates for each period.

Obviously this has limitations and so I'd like the time periods to be editable by the customer so I created three tables:

BOATS
----------
BOATID (INT)
BOAT NAME (NVARCHAR)

DATE RANGES
---------------------
RANGEID (INT)
STARTDATE (SMALLDATETIME)
FINISHDATE (SMALLDATETIME)

RATES
----------
BOATID (INT)
RANGEID (INT)
RATECOST (MONEY)

I then loop through these in my scripting language to generate a table but I'm not going to be able to sort these results by different time ranges, etc.

So my question is, is there a better way?  If not, presumably I can create the result set in a SP and make it sortable on each column that way?

Thanks in advance for any help

Bob
LVL 1
Mango-ManAsked:
Who is Participating?
 
SharathData EngineerCommented:
Can you check this.
declare @CHARTER_vessels  table (vesselID int, vesselName nvarchar(50))
insert @CHARTER_vessels values
(1,           'The Titanic'),
(2,           'The Pinafore'),
(3,           'The Black Pearl')


declare @CHARTER_rateDateRange table (rateDateRangeID int, rateDateRangeName nvarchar(50))
insert @CHARTER_rateDateRange values 
(1,                   'Spring 2012'),
(2,                   'Summer 2012'),
(3,                   'Fall 2012')


declare @CHARTER_rates table (vesselID int, rateDateRangeID int, rateCost int)
insert @CHARTER_rates values 
(1,            1 ,                   434),
(1,            2 ,                   445),
(1,            3 ,                   231),
(2,            1 ,                   675),
(2,            2 ,                   545),
(2,            3 ,                   768),
(3,            1 ,                   543),
(3,            2 ,                   654),
(3,            3 ,                   658)

select * from (
select cv.vesselName,crd.rateDateRangeName,cr.rateCost
  from @CHARTER_rates cr
  join @CHARTER_vessels cv on cr.vesselID = cv.vesselID
  join @CHARTER_rateDateRange crd on cr.rateDateRangeID = crd.rateDateRangeID) t1
PIVOT (SUM(rateCost) FOR rateDateRangeName IN ([Spring 2012],[Summer 2012],[Fall 2012])) AS P
/*
vesselName	Spring 2012	Summer 2012	Fall 2012
The Black Pearl	543	654	658
The Pinafore	675	545	768
The Titanic	434	445	231
*/

Open in new window

0
 
DALSOMCommented:
Hi Bob.

Mainly, I see that you must to type for evrey range/boat/year a rate , probably a lot of times.
But, you can set a main price per boat, and then a percent of the boat price per temporade.

Boat No. 1  -- > $50 Normal Time.
   For Spring -- > 25% added
   For Winter -- > 50% on discount of original price.
   By San Valentin -- > 300 % added,
and so on...

So you won change every time range the price, just only you must change boat price!

I hope this help you,
Bye,
Dalsom.  
0
 
Kevin CrossChief Technology OfficerCommented:
I am not sure I am understanding what the challenge is...i.e., why can't you sort? It would probably be best to show the current result set and then your desired results in comparison. That way, we may be able to help you better. Without more information, I can say you can use PIVOT with SQL 2008 to get from the normalized tables to a display that is denormalized back to one row per boat with the rates going horizontally. You can just use a rank of the ranges, like range 1, 2, 3, ..., n. So range 1 for one boat can be different dates from another boat, but the rate for each is in the same column to signify that is the first range. Or you can explode the ranges back to individual months and have the PIVOT columns be January, February, ..., December.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DALSOMCommented:
Okidoki, look this query, and check the alias and column location (1,2,3...n)

select R.* ,B.[Boat Name],D.[Start Date], D.[Finish Date]
from Rates R inner join
        Boats B on R.boatid = B.boatid  inner join
       [Date Ranges] D on R.rangeid = D.rangeid
-- Then , you can order by any column of any table in the next line.
-- Just use the alias.columnName, or the query column display order number.

order by R.RangeID
-- or use :  order by 1,3
-- or use : order by Year(d.[start date]) desc

Just you must pick what column you need from your from block.

Hope this clear your dudes!
Bye ,
Dalsom.
0
 
Mango-ManAuthor Commented:
Hi both,

Many thanks for the responses.  To answer your immediate questions:

1) The customer definately wants to enter a boat price for every boat / time period (they cannot simply offer a discount for specific time periods).

2) With my current solution I cannot sort because the resulting HTML table is built up using the scripting language from nested recordsets, here is the VBSCRIPT to build the table:

SET vesselsRS = readOnlyConn.execute("SELECT * FROM CHARTER_vessels ORDER BY vesselName",,adCmdText)
SET rateDateRangesRS = readOnlyConn.execute("SELECT * FROM CHARTER_rateDateRange ORDER BY startDate",,adCmdText)
RESPONSE.WRITE(" 	<table border=""0"" cellspacing=""0"" class=""dataTbl"">")
	RESPONSE.WRITE(" 	<tr>")
	RESPONSE.WRITE(" 	<th>Vessel Name</th>")
	WHILE NOT rateDateRangesRS.EOF
		RESPONSE.WRITE(" 	<td>" & rateDateRangesRS("rateDateRangeName") & "</td>" & vbCrLf)
		rateDateRangesRS.MOVENEXT
	WEND
	RESPONSE.WRITE(" 	</tr>")

	WHILE NOT vesselsRS.EOF
		RESPONSE.WRITE(" 	<tr>")
		RESPONSE.WRITE(" 	<th>" & vesselsRS("vesselName") & "</th>" & vbCrLf)
		SET rateDateRangesRS = readOnlyConn.execute("SELECT * FROM CHARTER_rateDateRange ORDER BY startDate",,adCmdText)
		WHILE NOT rateDateRangesRS.EOF
			RESPONSE.WRITE(" 	<td>")
			SET ratesRS = readOnlyConn.execute("SELECT rateCost, rateName FROM CHARTER_rates WHERE vesselID = " & vesselsRS("vesselID") & " AND rateDateRangeID = " & rateDateRangesRS("rateDateRangeID"),,adCmdText)
			IF NOT ratesRS.EOF THEN RESPONSE.WRITE(ratesRS("rateCost"))
			RESPONSE.WRITE("</td>" & vbCrLf)
			rateDateRangesRS.MOVENEXT
		WEND
		RESPONSE.WRITE(" 	</tr>" & vbCrLf)
	vesselsRS.MOVENEXT
WEND
RESPONSE.WRITE(" 	</table>")

Open in new window


If I can instead build the entire result set in an SP that would be great.

Dalsom - many thanks for the code, I will check it out tomorrow!  (late here now)

Bob
0
 
cbarr101Commented:
This solution adds a view which will
- simplify wriiting queries
- enable you to tweak results of queries by modifying the view instead of each query


create view boats_dates_rates as
select b.boat_name,
       d.startdate,
       d.finishdate,
       r.ratecost
from boats b,
     date_ranges d,
     rates r
where b.boatid = r.boatid
  and r.rangeid = d.rangeid;

-- sample query for start dates Jan. - Feb.
select * from boats_dates_rates
where startdate between to_date('01/01/12','mmddyy')
                and to_date('02/27/12','mmddyy')
order by startdate, ratecost;

0
 
Mango-ManAuthor Commented:
Hi everyone,

Apologies for the long delay, I must also apologize for not being clear in what I'm trying to achieve.  I suspect that PIVOT may be the answer but I can't find examples of PIVOT that don't use an aggregate function, so to try and make it clear

I have three tables:

CHARTER_vessels (the boats)
===============

vesselID    vesselName
--------    ----------
1           The Titanic
2           The Pinafore
3           The Black Pearl


CHARTER_rateDateRange (seasons basically)
=====================

rateDateRangeID     rateDateRangeName
---------------     -----------------
1                   Spring 2012
2                   Summer 2012
3                   Fall 2012


CHARTER_rates (the price for each boat in each season)
=============

vesselID     rateDateRangeID      rateCost
--------     ---------------      --------
1            1                    434
1            2                    445
1            3                    231
2            1                    675
2            2                    545
2            3                    768
3            1                    543
3            2                    654
3            3                    658

Open in new window


And the output I'm trying to achieve is that the rates for each boat appear in the column for each season, like this:

vesselName         Spring 2012     Summer 2012     Fall 2012
----------         -----------     -----------     ---------
The Titanic        434             445             231
The Pinafore       675             545             768
The Black Pearl    543             654             658

Open in new window


Really sorry if I wasted anyone's time with my lack of clarity (and lack of knowledge!)

Thanks again

Bob
0
 
Mango-ManAuthor Commented:
(and obviously I would like to be able to sort the result set by the different columns if possible!)

Any points are much appreciated

Bob
0
 
Mango-ManAuthor Commented:
Many thanks - sorry for the delay
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.