Solved

Formatting data

Posted on 2011-09-13
3
360 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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