Showing all week data irrespective of a week's data existing or not in the table.

Question

Showing all week data irrespective of a week's data existing or not in the table.

descentflower

Ten Centuries

Points: 1227
More actions
June 4, 2009 at 9:43 pm

#205502

Hi ,
i have a data from 1 june 2008 till todays date.
Date Name Price
----------------------------
06-01-2008 A 1
06-01-2008 B 2
06-01-2008 C 3
06-01-2008 D 4
06-02-2008 A 5
06-02-2008 B 6
06-02-2008 C 7
06-02-2008 D 8
.
.
.
.
06-01-2009 A 11
06-01-2009 B 21
06-01-2009 C 31
06-01-2009 D 41
.
.
.
.
I need to show data yearly and weekly
like for 2008 and 2009 . As data is not present for first few weeks for 2008 i still want 0 to showed there. Like this,
Year Week Name Price
------------------------------
2008 1 A 0
2008 1 B 0
2008 1 C 0
2008 1 D 0
.
.
.
.
2008 22 A 1.1
2008 22 B 1.1
2008 22 C 1.1
2008 22 D 1.1
.
.
.
2008 52 A 0
2008 52 B 0
2008 52 C 0
2008 52 D 0
.
.
.
.
2009 1 A 10
2009 1 B 20
2009 1 C 30
2009 1 D 40
Same for year 2009 .. (If data present then show else show 0 for every week).
I am using Avg as an aggregate function.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

I would create a weeks table containing all the weeks you want to report on then use that with a LEFT OUTER JOIN onto your results where there are NULL results use COALESCE or ISNULL to return a 0 (zero)

Hope that helps.

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 2

Here is some basic work,

create table #weekData (date datetime, sname varchar(5), price int)

insert into #weekData

select '06-01-2008', 'A', 1 union all

select '06-01-2008', 'B', 2 union all

select '06-01-2008', 'C', 3 union all

select '06-01-2008', 'D', 4 union all

select '06-02-2008', 'A', 5 union all

select '06-02-2008', 'B', 6 union all

select '06-02-2008', 'C', 7 union all

select '06-02-2008', 'D', 8 union all

select '06-01-2009', 'A', 11 union all

select '06-01-2009', 'B', 21 union all

select '06-01-2009', 'C', 31 union all

select '06-01-2009', 'D', 41

select datepart(year,date), datepart(wk,date), sname, count(price)

from #weekData

group by datepart(year,date), datepart(wk,date), sname

order by datepart(year,date), datepart(wk,date), sname

drop table #weekData

then you can improved it further as suggested by allen davidson.

"Don't limit your challenges, challenge your limits"