Scheduling Product - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Scheduling Product

Author

Topic

Mizzium
Starting Member

1 Post

Posted - 2010-06-04 : 08:11:32

I'm trying to create an SQL database that will allow an input of a series of date ranges (i.e. 1 October 2010-25 October 2010, 5 October 2010-29 October 2010) along with a numerical value (2, 3, 4). I then want a report in calendar format where I can pull up a specific week and it will display a sum of all numerical values associated with that date on the calendar.

I am ridiculously new to SQL and have a basic understanding of creating tables to input data, but not how to manipulate the report format effectively.

Any pointers?

PackRat
Starting Member

26 Posts

Posted - 2010-06-04 : 17:57:35

not entirely certain where you're headed with all of this; but lets get some code out there for discussion;
let me know how this falls short of what you're trying to do.

-- example table
declare @tbl table (id int identity(1,1), dateStart smalldatetime, dateEnd smalldatetime, refNbr smallint);

-- some test data
insert @tbl
select '10/1/10', '10/31/10', 5 union all
select '10/5/10', '10/31/10', 7 union all
select '10/13/10', '10/31/10', 10 

-- simple report params; start-end
declare @rptDateStart smalldatetime, @rptDateEnd smalldatetime;
select @rptDateStart='10/10/10', @rptDateEnd='10/16/10';

-- sum of refNbr where range falls entirely within the specified report period (no partial credit for starting or ending in the window)
select sum(refNbr) total from @tbl where dateStart<=@rptDateStart and dateEnd>=@rptDateEnd

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources