One more about dataset relations

[Mr.Matthew.Smith]

Hi,

I have a data relationship which is 'orders' and 'order dates' mapped
out in a dataset with relations and viewed via two datagrids.

I have the dates broken down by week so the order dates grid shows how
many orders per week.

The weekly break down is performed in the sql query (datepart(week) etc
etc) but if there aren't any orders for that week it skips the week. So
for January it'll read

Week Month Total
1 Jan 5
2 Jan 6
4 Jan 2
....

what I would like to have is

Week Month Total
1 Jan 5
2 Jan 6
3 Jan 0
4 Jan 2
....


Now I was shown some sql code that accomplishes this via a cross join
but I noticed that it takes a little time to run and that's will a
small sampling of the data I intend to use it on.

So basically my question is, is there a better way to do this ?

Thanks

 

[Mr.Matthew.Smith]

I forgot to add that I'm wondering if there was a way to do this from
the c# side of things. Obviously some answers posted here may be to
optimize my query and use an outer join or something to that effect
rather than a cross join.

I have found I cannot use the outer join solution because my data is
mapped like so

table products (productid int, producttype int)
table sales (salesid int, datetime smalldatetime)
table saleshistory(productid int, salesid int)

and I wasn't able to create a working query using an outer join to
accomplish this thus the suggested cross join.

But mainly I was just wondering of there was a way to do this in c#?

Thanks