Help! Select statement to display a schedule

[C0PP3R]

Hey,
I have a schedule table tha tholds an autonumber PK, a job number, Scheduled date adn scheduled hours.

The user needs to view this data in a two week period. The Start and end date will be passed ot the sp from VB

I need to return a list that looks like this:

In: Begin Date = '08/01/04' End Date = '08/14/04'

Out:

Job # | Aug 1 | Aug 2 | Aug 3 | Aug 4 | etc.... Aug 14|
12345 | 0 | 2 | 0 | 1 |

So job number 13245 has two records tha texist in m yschedule table, one that has 2 hours scheduled on Aug 2 and another with 1 hour scheduled for Aug 4th.

Any ideas and suggestions are appreciated!

Thanks

Copper

 

[skuhlman]

Assuming that you will always be dealing with a 14 day period, you need only pass the start date to the stored procedure. The SP can then look something like this:

CREATE PROCEDURE dbo.MyJobCounts
@StartDate datetime
AS

-- set the Starting Date to the FIRST SECOND of the day
set @StartDate = convert(DateTime,convert(char(10),@StartDate,121))

-- sum hours for account by day for selected 14 days (ignore times, only look at dates)
select JobNumber, convert(char(10),JobDate,121) as MyDate, sum(JobHours) as MyHours
	into #TempDetail
	from ChangeLog with (nolock)
	where JobDate >= @StartDate and JobDate < dateadd(d,14,@StartDate)
	group by JobNumber, convert(char(10),JobDate,121)
	order by JobNumber, MyDate

select JobNumber,
		sum(case when MyDate = @StartDate then MyHours else 0 end) as Day1Hours,
		sum(case when MyDate = dateadd(d,1,@StartDate) then MyHours else 0 end) as Day2Hours,
		sum(case when MyDate = dateadd(d,2,@StartDate) then MyHours else 0 end) as Day3Hours,
		sum(case when MyDate = dateadd(d,3,@StartDate) then MyHours else 0 end) as Day4Hours,
		sum(case when MyDate = dateadd(d,4,@StartDate) then MyHours else 0 end) as Day5Hours,
		sum(case when MyDate = dateadd(d,5,@StartDate) then MyHours else 0 end) as Day6Hours,
		sum(case when MyDate = dateadd(d,6,@StartDate) then MyHours else 0 end) as Day7Hours,
		sum(case when MyDate = dateadd(d,7,@StartDate) then MyHours else 0 end) as Day8Hours,
		sum(case when MyDate = dateadd(d,8,@StartDate) then MyHours else 0 end) as Day9Hours,
		sum(case when MyDate = dateadd(d,9,@StartDate) then MyHours else 0 end) as Day10Hours,
		sum(case when MyDate = dateadd(d,10,@StartDate) then MyHours else 0 end) as Day11Hours,
		sum(case when MyDate = dateadd(d,11,@StartDate) then MyHours else 0 end) as Day12Hours,
		sum(case when MyDate = dateadd(d,12,@StartDate) then MyHours else 0 end) as Day13Hours,
		sum(case when MyDate = dateadd(d,13,@StartDate) then MyHours else 0 end) as Day14Hours
	from #TempDetail
	group by JobNumber
	order by JobNumber

drop table #TempDetail