Solved

Formatting data

Posted on 2011-09-13
3
366 Views
Last Modified: 2012-05-12
Hi,

I have a couple of tables looking like this:
bsScheduleWeek
weekNo int  (34,35,36 etc.)
bsYearID int (25, 26 etc)

bsGrades
gradeID int (1,2,3,4 etc.)
grade nvarchar (Year 1, Year 2, Year 3 etc.)

bsSchedule.
scheduleID int
gradeID int
scheduledDate datetime
subjectID int
weekNo int
bsYearID int

 (this table, bsSchedule, holds all dates between a start and end date. Each gradeID has a reference to each date like this:
scheduleID  scheduledDate  gradeID  subjectID bskYearID  weekNo
1                 2011-09-11         1            1             25                 34
2                 2011-09-11          2           2             25                 34
3                2011-09-12           1            1            25                  34
4                2011-09-12            2            2           25                  34
and so on..)

bsScheduleSubject
subjectID int
subject nvarchar (Subject 1, Subject 2 etc.)

What I would like is to be able to format it like this:
Week no: 34
               Monday         Tuesday       Wednesday  Thursday     Friday           Saturday      Sunday
               2011-09-11   2011-09-12   2011-09-13  2011-09-14  2011-09-15  2011-09-16  2011-09-17
 Year 1   Subject 1       Subject 1       Subject 2      Subject 3...
Year 2    Subject 4        Subject 5      Subject 1      Subject 6....

WeekNo:35..... same procedure

IMy thought was to combine two datalists and relate them through week number but I'm not sure how to format the sql queris to accomplish this. If anyone can help in this scenario it would be great.

Peter




0
Comment
Question by:peternordberg
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 36527970
I had to do something similar to this. The way I did it was to generate a query for the header/dates and then use UNION with the normal query.

SET DATEFIRST 1

DECLARE @MondayDate smalldatetime
DECLARE @WeekNum int
DECLARE @Year int

SET @WeekNum = 35 --variable here, you can use it in a query or stored procedure
SET @Year = 2011 --same as above
SET @MondayDate = DATEADD(ww, @WeekNum, '01/01/' + @Year)
SET @MondayDate = DATEADD(d, 1-DATEPART(dw, @MondayDate), @MondayDate)

SELECT CONVERT(char(10),@MondayDate,103) Monday,
  CONVERT(char(10),DATEADD(day,1,@MondayDate),103) Tuesday,
  CONVERT(char(10),DATEADD(day,2,@MondayDate),103) Wednesday,
  CONVERT(char(10),DATEADD(day,3,@MondayDate),103) Thursday,
  CONVERT(char(10),DATEADD(day,4,@MondayDate),103) Friday,
  CONVERT(char(10),DATEADD(day,5,@MondayDate),103) Saturday,
  CONVERT(char(10),DATEADD(day,6,@MondayDate),103) Sunday
UNION ALL
SELECT etc etc etc

Just remember to have the same number of columns for both queries.
0
 

Author Closing Comment

by:peternordberg
ID: 36529838
It led me to the right solution. Thanks!
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36530391
Glad to help. Each case has its own peculiarities, but as long as you understand the logic behind it, you can usually adapt it to your own needs.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question