"Completing" a time phased table

Hi everyone,
I Need to display summarized, time phased (lets say wly), data in a
datagrid. Pretty straightforward except that my summarized data may or may
not be "complete". That is if I do a simple summarization query on my base
data I won't get a record for every w. In my DataGrid, if there are no
transactions, I need to show a '0' for that w.
Right now I'm planning doing the following...
1) Create another table that has the same layout as my base data table,
with one record per w and a zero value.
2) Summarizing my main data table by w, and then merging it (using
UNION) with my created "zero value" table.
3) Quering the merged tables and sumarizing by w. Where there is
overlap, the extra records from my created table add nothing to value. Wher
e
there is no base data for that w, the "zero value table" record will be
returned.
So it looks something like this (much simplified)...
SELECT SUM(Value), MAX(Period) FROM ((SELECT SUM(Value), MAX(Period) FROM
Base Group by Period) UNION ZeroTable) GROUP BY Period
Is there an easier way to do this?
Thanks.
BBMDo you have a table of the periods? If not, create one. Then you can do
this:
SELECT SUM(T.x), SUM(T.y), SUM(T.z)
FROM PeriodCalendar AS P
LEFT JOIN YourTable AS T
ON P.period = T.period
WHERE ...?
GROUP BY P.period ;
Turning the NULLs into zeros is just a matter of presentational
formatting. If you want to do it in the quer then wrap each sum with
COALESCE(..., 0).
David Portas
SQL Server MVP
--|||create table ws(date_from smalldatetime, date_to smalldatetime)
insert into ws values('7-31-2005','8-7-2005')
insert into ws values('8-7-2005','8-14-2005')
insert into ws values('8-14-2005','8-21-2005')
create table payments(pmt_date smalldatetime, amt money)
insert into payments values('8-2-2005',123.45)
insert into payments values('8-3-2005',12.5)
insert into payments values('8-20-2005',123.45)
insert into payments values('8-21-2005',123.45)
select convert(varchar(11), date_from) date_from,
convert(varchar(11), date_to) date_to,
(select sum(amt) from payments where pmt_date>=date_from and
pmt_date<date_to) wum_pmt
from ws
date_from date_to wum_pmt
-- -- --
Jul 31 2005 Aug 7 2005 135.9500
Aug 7 2005 Aug 14 2005 NULL
Aug 14 2005 Aug 21 2005 123.4500
drop table ws
drop table payments|||Thanks David.
Yes I do have a period table, but my problem is a little more complex than
in my original post. In actuality, there are multiple values in each period
by "category".
I do have control over the period table (I'm actually using a copy of the
real table with some extra fields in it) so I could add a record in my perio
d
table for each category (there are only six).
Is there an easier way to do this? (create a category table and use it to
create a cross product table as the left side of my JOIN? I'll try this)
COALESCE is VERY handy. Thanks for the tip.
Thanks again.
"David Portas" wrote:

> Do you have a table of the periods? If not, create one. Then you can do
> this:
> SELECT SUM(T.x), SUM(T.y), SUM(T.z)
> FROM PeriodCalendar AS P
> LEFT JOIN YourTable AS T
> ON P.period = T.period
> WHERE ...?
> GROUP BY P.period ;
> Turning the NULLs into zeros is just a matter of presentational
> formatting. If you want to do it in the quer then wrap each sum with
> COALESCE(..., 0).
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks AK. Very elegant. Using a Select statement to calculate the value
of the returned row is clever. Are there any performance implications?
BBM
"AK" wrote:

> create table ws(date_from smalldatetime, date_to smalldatetime)
> insert into ws values('7-31-2005','8-7-2005')
> insert into ws values('8-7-2005','8-14-2005')
> insert into ws values('8-14-2005','8-21-2005')
> create table payments(pmt_date smalldatetime, amt money)
> insert into payments values('8-2-2005',123.45)
> insert into payments values('8-3-2005',12.5)
> insert into payments values('8-20-2005',123.45)
> insert into payments values('8-21-2005',123.45)
> select convert(varchar(11), date_from) date_from,
> convert(varchar(11), date_to) date_to,
> (select sum(amt) from payments where pmt_date>=date_from and
> pmt_date<date_to) wum_pmt
> from ws
> date_from date_to wum_pmt
> -- -- --
> Jul 31 2005 Aug 7 2005 135.9500
> Aug 7 2005 Aug 14 2005 NULL
> Aug 14 2005 Aug 21 2005 123.4500
> drop table ws
> drop table payments
>|||It seems to me that David's solution and AK's solution are
basically equivalent, the only difference being that each of them
made different assumptions about the structure of your tables, which
you didn't provide. Can you be more specific about why David's
solution does not meet your requirements, and this one does?
Here is what David's solution would look like using the tables
AK provided:
select
convert(varchar(11), date_from) as date_from,
convert(varchar(11), date_to) as date_to,
coalesce(sum(amt),$0) as wum_pmt
from ws left outer join payments
on pmt_date >= date_from
and pmt_date < date_to
group by date_from, date_to
Steve kass
Drew University
BBM wrote:
>Thanks AK. Very elegant. Using a Select statement to calculate the value
>of the returned row is clever. Are there any performance implications?
>BBM
>"AK" wrote:
>
>