Solved

Formatting data

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

19 Experts available now in Live!

Get 1:1 Help Now