Use TSQL to create a data matrix

[Andrew Chanter]

I am developing a SQL database to cover operations that were previously
handled in a spreadsheet, and need to create a view or procedure that
presents data into a matrix format similar to what the users are currently
working with. There must be a way I can create this using Transact SQL but
I cant figure it at this point. What the users want is for data to be
presented in 7 continuous columns where each column shows records for 1 day
of a week and each record is presented as a 'block'. Any tips or hints from
people who have achieved something similar would be gratefully accepted.

A sample of the format appears below. (Set with tabs. Hope this
translates)

1/2/2004 2/2/2004 3/2/2004 4/2/2004 5/2/2004
6/2/2004 7/2/2004
Person Smith Jones Green
Room 1A 2B 3c
Start 9:00AM 8:00AM 8:00AM
End 5:00 PM 5:00 PM 5:00 PM

Person Brown White
Room 1D 1D
Start 9:00AM 9:00AM
End 5:00 PM 5:00 PM

Person Green
Room 1M
Start 9:00AM
End 5:00 PM

Notes

1. As the diagram shows, there is likely to be a different number of records
for each day.
2. It is not necessary to have the field headings appear on the left of the
view.

[David Portas]

What client application are the users using to view this data? The client
application is a much more logical place to do this kind of presentational
stuff. What you have asked for is clearly something formatted for display on
screen or in a report. TSQL just isn't designed for report-writing. Sure,
you could write this in TSQL if you are masochistic enough to want to, but
Access and Visual Studio for example come with much better tools to do it.

--
David Portas
SQL Server MVP
--

[Andrew Chanter]

David, thanks for your reply,

Im actually using an Access ADP as the client interface and any output
created would be enhanced in Access for presentation to users. But I cant
think of how to get the base data easily in Access and I am something of an
Access expert. The closest Access has to what I want is a crosstab query,
but this falls well short of the solution I seek. I also experimented with
lining up 7 subforms, each containing records for a seperate day of the
week. This method kind of worked but had significant flaws. My impression
was that Transact SQL had some fancy commands not available in Access to
present data? I could write some ADO code in VBA to assemble the data at
runtime but if I was going to this trouble anyway I would be better off
biting the bullet and writing a procedure. But if you have some ideas for
MS Access I'm all ears!!!

"David Portas" <RE****************************@acm.org> wrote in message
news:45********************@giganews.com...

What client application are the users using to view this data? The client
application is a much more logical place to do this kind of presentational
stuff. What you have asked for is clearly something formatted for display on screen or in a report. TSQL just isn't designed for report-writing. Sure,
you could write this in TSQL if you are masochistic enough to want to, but
Access and Visual Studio for example come with much better tools to do it.

--
David Portas
SQL Server MVP
--

[David Portas]

It is a crosstab. You didn't specify any table structure but assuming
you have something that approximates this:

CREATE TABLE YourTable (room INTEGER NOT NULL, person VARCHAR(10) NOT
NULL, starttime DATETIME NOT NULL, endtime DATETIME NOT NULL, PRIMARY
KEY (room,person,starttime))

You could do use the following query. @dt is the starting date of the
week.

SELECT room, person,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=0 THEN starttime END) AS
start1,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=0 THEN endtime END) AS end1,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=1 THEN starttime END) AS
start2,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=1 THEN endtime END) AS end2,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=2 THEN starttime END) AS
start3,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=2 THEN endtime END) AS end3,
.... etc
FROM YourTable
WHERE starttime >= @dt
AND starttime < DATEADD(DAY,7,@dt)
GROUP BY room, person

The rest is just formatting you can do in an Access report.
--
David Portas
SQL Server MVP
--

[Andrew Chanter]

Many thanks David, I will give this a try.
"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

It is a crosstab. You didn't specify any table structure but assuming
you have something that approximates this:

CREATE TABLE YourTable (room INTEGER NOT NULL, person VARCHAR(10) NOT
NULL, starttime DATETIME NOT NULL, endtime DATETIME NOT NULL, PRIMARY
KEY (room,person,starttime))

You could do use the following query. @dt is the starting date of the
week.

SELECT room, person,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=0 THEN starttime END) AS
start1,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=0 THEN endtime END) AS end1,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=1 THEN starttime END) AS
start2,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=1 THEN endtime END) AS end2,
MIN(CASE WHEN DATEDIFF(DAY,@dt,starttime)=2 THEN starttime END) AS
start3,
MIN(CASE WHEN DATEDIFF(DAY,@dt,endtime)=2 THEN endtime END) AS end3,
... etc
FROM YourTable
WHERE starttime >= @dt
AND starttime < DATEADD(DAY,7,@dt)
GROUP BY room, person

The rest is just formatting you can do in an Access report.
--
David Portas
SQL Server MVP
--