asked on

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

DALSOM

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.

Kevin Cross

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.

DALSOM

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.

Mango-Man

ASKER

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

cbarr101

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;

Mango-Man

ASKER

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

Mango-Man

ASKER

(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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mango-Man

ASKER

Many thanks - sorry for the delay

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

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