DB Design question - 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

Database Design and Application Architecture

DB Design question

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2010-04-02 : 11:56:01

Hello everyone,

I have a question about the db I just designed.

A table with 5 fieldnames. Let's call the table tableOne:

TableName: TableOne
field1 int
Field2 int
field3 int
field4 int
StartDate dateTime
EndDate dateTime

The table layout is like this:


Item Name            Weekly              YTD
----------------------------------------------------
field1                  5                 8
Field2                  7                 10
field3                  2                 13
field4                  4                 11

We generate reports every week.

The weekly number is different each week. The user enters a date range for start date and end date and then plugs in the weekly numbers.

The YTD total is calculated by adding up all the weekly numbers.

This is currently done with an Excel spreadsheet.

Given the data structure, and above sample data, I need to find a way to generate YTD totals.

My thinking right now is that there is got to be a way to generate calculated values for each week.

Then I can add up these calculated values to generate YTD numbers but quite frankly, I am blank and I need your expert assistance.

Can you please help me look at the data structure and what needs to change in such that generating YTD totals won't be as challenging to me as it is right now?

Many thanks in advance.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 15:16:18

You could do something like this:


DECLARE @First DATETIME
SET @FIRST = CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01'
 
SELECT SUM(Field1) AS Field1YTD, SUM(Field2) AS Field2YTD, SUM(Field3) AS Field3YTD, SUM(Field4) AS Field4YTD
FROM tableOne
WHERE EndDate >= @FIRST

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources