July 25, 2003 at 9:57 am
I have recently inherited a program and have decided to modify the table structure. All of the data below (other than location) once existed in one table and I have extracted the ‘Purpose’ and the ‘Technology’ into separate tables.
Now what I need to do is get the data back into the original horizontal positioning so that my ASP pages can read the data.
Tables used: tblSchedule, tblLocation, tblPurpose, tblTechnology
Here is the report that is created in Excel (via an ASP page)
(tblSchedule) (tblLocation) (tblPurpose) (tblTechnology)
dtStartDate etc.. Location AC CBT CE CCC etc.. AG DVC etc..
----------- ---------- -- --- -- --- -- ---
01/01/2003 Room 1 X X X
01/02/2003 Room 1 X X
01/02/2003 Room 2 X X X X X X
I have read how to ‘shift’ vertical data to horizontal layout by using the case statement. All of the examples I have found use only a couple of fields and many times use numerical data. The problem I am having is that when I try to aggregate the data in the ‘Group By’ (to get all of my data on one line), it wants me to have ALL fields in an aggregate. Once I do that, my data layout isn’t all in one row.
Am I trying to fit too many things into one statement? How can I get the layout above?
Here is the statement I have thus far:
SELECT tblSchedule.dtDate as dtStartDate,
tblLocation.LocationDescription,
tblSchedule.short_description,
CASE tblPurpose.txtPurpose WHEN ‘AC’ THEN ‘X’ ELSE NULL END AS AC,
CASE tblPurpose.txtPurpose WHEN ‘CBT’ THEN ‘X’ ELSE NULL END AS CBT,
CASE tblPurpose.txtPurpose WHEN ‘CE’ THEN ‘X’ ELSE NULL END AS CE,
CASE tblPurpose.txtPurpose WHEN ‘CCC’ THEN ‘X’ ELSE NULL END AS CCC,
….continue with other CASE stmts
tblSchedule.people,
CASE tblTechnology.txtTechnology WHEN ‘AG’ THEN ‘X’ ELSE NULL END AS AG,
CASE tblTechnology.txtTechnology WHEN ‘DVC’ THEN ‘X’ ELSE NULL END AS DVCC,
CASE tblTechnology.txtTechnology WHEN ‘NM’ THEN ‘X’ ELSE NULL END AS NM,
….continue with other CASE stmts
tblSchedule.remarks
FROM tblSchedule
INNER JOIN tblLocation ON tblSchedule.intLocationID = tblLocation.intLocationID
INNER JOIN tblParticipantSites ON tblSchedule.intSchedID = tblParticipantSites.intSchedID
INNER JOIN tblPurpose ON tblSchedule.intSchedID = tblPurpose.intSchedID
INNER JOIN tblTechnology ON tblSchedule.intSchedID = tblTechnology.intSchedID
ORDER BY tblSchedule.dtDate
July 25, 2003 at 10:22 am
You would need to do LEFT JOINs to your lookup tables, otherwsie you will end up showing only those records that have records in both lookup tables. Plus, not quite sure why you are doing a crosstab this way. Wouldn't it be easier in your ASP page to do the logic in your ASP page. The CASE statement won't work because it won't know which record in the lookup table you are referring to, so you'd have to use the EXISTS clause along with CASE:
...
CASE
WHEN EXISTS(SELECT * FROM tblPurpose p WHERE txtPurpose = ‘AC’ AND p.intSchedID = tblSchedule.intSchedID)
THEN ‘X’
ELSE ''
END AS AC
...
I still think it's smarter to just return arrays to the ASP page and use procedural script to format the crosstab report.
Edited by - jpipes on 07/25/2003 10:24:40 AM
July 25, 2003 at 10:58 am
Thank you for your response.
Since the ASP code was already in place to extract the data from a Recordset with the 'Purpose' and the 'Technology' in the same row, I thought I could change the tables, then teh SQL statements and leave the ASP code the same.
However, I see your point and will look at changing the code.
I'm new to Stored Procs and waver between where to draw the line on having the stored procedure do the work or the code....
Thanks again for your help!
July 25, 2003 at 11:32 am
When you are stuck in a situation like that, choose to do the simplest stored procedure you can, returning the data to ASP, then using procedural code to format it. It will give you the most flexibility in the case where you need to make changes to the display of the report later (and they always make changes, don't they...). This should get you going:
CREATE PROCEDURE dbo.GetLocations
AS
BEGIN
-- Get the start date and room number
-- for all dates with a location in
-- tblLocation
SELECT
s.intSchedID
, s.dtDate
, l.LocationDescription
, s.short_description
, s.remarks
, s.people
FROM tblSchedule s
INNER JOIN tblLocation l
ON s.intLocationID = l.intLocationID
ORDER BY s.dtDate DESC
END
--
CREATE PROCEDURE dbo.GetPurposesBySchedID
@SchedID INT
AS
BEGIN
SELECT p.txtPurpose
FROM tblPurpose p
WHERE p.intSchedID = @SchedID
END
--
CREATE PROCEDURE dbo.GetTechnologiesBySchedID
@SchedID INT
AS
BEGIN
SELECT t.txtTechnology
FROM tblTechnology t
WHERE t.intSchedID = @SchedID
END
Then in your ASP, use these procedures like this pseudocode demonstrates:
Dim aSchedules
Dim aPurposes
Dim aTechnologies
Dim aConstPurposes
Dim iCountSchedules
Dim iCountPurposes, iCountTechnologies
Dim i, j, k, iScheduleID
' Set up the crosstab headers
aPurposes = Array("AC","CBT","CE","CCC")
iCountPurposes = UBound(aPurposes)
' Repeat above for technologies...
GetDBConnection() ' Get the connection
' GetSchedules returns array of
' schedules by executing GetSchedules
' procedure and doing ADODB.Recordset.GetArray()
aSchedules = GetSchedules()
' Loop through schedules and build
' row in crosstab report
'
iCountSchedules = UBound(aSchedules, 2)
For i=0 To iCountSchedules
' Print schedule date and location
' then get arrays of the purposes
' and technologies associated with
' this schedule.
iScheduleID = aSchedule(i, 0)
PrintSchedule() ' Just writes out some array values
' GetPurposes executes GetPurposes
' procedure and returns an delimited
' string of purposes (if any)
sPurposes = GetPurposes(iScheduleID)
If Len(Trim(sPurposes)) > 0 Then
' Loop through the constant purposes
' If found, print X, otherwise, space
For j=0 To iCountPurposes
If Instr(sPurposes, aPurposes(j)) Then
Response.Write "<td>X</td>"
Else
Response.Write "<td> </td>"
End If
Next
' Repeat above for technologies
Next
This is only pseudocode meant to get you started, but I hope it helps.
Edited by - jpipes on 07/25/2003 11:33:34 AM
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply