extrapilate data - create many rows from 1 row

4 Nov 2005 1:19 AM

Paul the admin

I have records in a budget table that contain user startdate, enddate and
value.

I want to create a view, that contains a row (user, date, value) for each
week day (not week-end), and the proportion of the value from the table

Thanks

4 Nov 2005 8:56 AM

John Bell

Hi

Without DDL for your table this can only be a general answer! See
http://www.aspfaq.com/etiquette.asp?id=5006. You can use the datepart
function to determine if the date is a weekday or possibly use a calendar
table http://www.aspfaq.com/show.asp?id=2519 that contains a weekday
indicator. To get the proportion of the whole you will need a subquery to get
the total. e.g.

CREATE VIEW DailySum AS
SELECT CONVERT(datetime(CONVERT(char(8),datevalue,112)) as datevalue,
SUM(value) as value
FROM MyTable
GROUP BY CONVERT(datetime(CONVERT(char(8),datevalue,112))

CREATE VIEW WeekdayValues AS
SELECT datevalue, value, 100*(value/(select sum(value) FROM DailySum))
FROM DailySum
WERE datepart(dw,datevalue) between 2 and 6

The number returned by datepart will depend on what your datefirst is set to
see Books online for more.

John

Show quote

"Paul the admin" wrote:

> I have records in a budget table that contain user startdate, enddate and
> value.
>
> I want to create a view, that contains a row (user, date, value) for each
> week day (not week-end), and the proportion of the value from the table
>
> Thanks