Data Output - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Data Output

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-29 : 18:53:15

I have a small task that needs to be done quick.
A CEO has a spreadsheet that tracks measurables down the right column(A).
Budget column, Months Jan- Dec, average(month), actual are at the top as headers.
There are about 30 tabs that represent entities. Each tab links to a summary page with identical cells. Doesn't work great for multiple years.

Anyway this needs to be a DB.

I have put together tables as follows:

tblentity
[entityid]
[entityname]

tblmeasure
[measureid]
[measurename]

tbldata
[measureid]
[entityid]
[year]
[month]

tblbudget
[measureid]
[entityid]
[year]

I can structure anyway, I am most cocerned with the query to select the view output.

Just wondering if anyone has done something similar and had good/bad results. Thanks for the input.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-29 : 19:17:56

Not sure what the question is here.
Structure looks ok.
How much data are you talking about?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skillile
Posting Yak Master

208 Posts

Posted - 2003-11-29 : 19:27:07

I estimate the about of data to about 8,000 records.

I guess my question is if I store data like this. Will my Select statement look something like:

SELECT
a.measureid,
a.measurename
FROM tblmeasure a
...
LEFT JOIN tbldata .. ON ..JAN
LEFT JOIN tbldata .. ON ..FEB
LEFT JOIN tbldata .. ON ..MAR
LEFT JOIN tbldata .. ON ..APR
LEFT JOIN tbldata .. ON ..MAY
...

Not sure how to create this with efficiency.

measurename budget jan feb mar apr may jun jul .. avg total
m1 xx xx xx xx xx xx xx xx xx xx
m1 xx xx xx xx xx xx xx xx xx xx

Thanks for the input.

slow down to move faster...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-29 : 20:54:19

You will need an index to include the month - with about 8000 recs almost anything should be to quick to monitor the time.
You would probbaly want a table to hold the months so you can join to it.
Think you probbaly need an amy field on budget and measure

Query could be something like

SELECT
a.measurename ,
e.entityname ,
Budget = (select amt from tblBudget b where b.measureid = m.measureid) ,
jan = sum(case when m.month = 1 then amt else 0 end) ,
feb = sum(case when m.month = 2 then amt else 0 end) ,
....

FROM tblmeasure m
join tblentity e
on e.entityid = d.entityid
cross join tblMonth m
left outer join tbldata d
on d.measureid = m.measureid
and d.entityid = e.entityid
and d.month = m.month
where m.measureid = @measureid
and m.year = 2003
group by e.entityid, m.measureid, a.measurename, e.entityname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources