• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Formatting data


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

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

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..)

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 Nordberg
Peter Nordberg
  • 2
1 Solution
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.


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
SELECT etc etc etc

Just remember to have the same number of columns for both queries.
Peter NordbergIT ManagerAuthor Commented:
It led me to the right solution. Thanks!
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.
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.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now