Solved

Formatting data

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now