figure 2 returning all customers and their sales
set nocount on
declare @dtstartdate datetime,
@dtenddate datetime,
@dtdate datetime
set @dtenddate = ''5/5/1997''
set @dtenddate = dateadd(dd, -1, cast(cast((month(@dtenddate) + 1)
as varchar(2)) + ''/01/'' + cast(year(@dtenddate) as varchar(4)) + ''
23:59:59'' as datetime))
set @dtstartdate = dateadd(mm, -1 * 12, @dtenddate)
select cast(year(o.orderdate) as varchar(4)) + ''-'' +
case
when month(o.orderdate) < 10
then ''0'' + cast(month(o.orderdate) as varchar(2))
else cast(month(o.orderdate) as varchar(2))
end as smonth,
c.customerid,
c.companyname,
c.contactname,
sum(od.quantity * od.unitprice) as msales
from customers c
inner join orders o on c.customerid = o.customerid
inner join [order details] od on o.orderid = od.orderid
where o.orderdate between @dtstartdate and @dtenddate
group by
cast(year(o.orderdate) as varchar(4)) + ''-'' +
case
when month(o.orderdate) < 10
then ''0'' + cast(month(o.orderdate) as varchar(2))
else cast(month(o.orderdate) as varchar(2))
end,
c.customerid,
c.companyname,
c.contactname
order by
c.companyname,
smonth
___________________________________________________________________________
figure 3 cartesian product at work
declare @tblmonths table (smonth varchar(7))
declare @tblcustomers table ( customerid char(10),
companyname varchar(50),
contactname varchar(50))
declare @tblfinal table ( smonth varchar(7),
customerid char(10),
companyname varchar(50),
contactname varchar(50),
msales money)
declare @dtstartdate datetime,
@dtenddate datetime,
@dtdate datetime,
@i integer
set @dtenddate = ''5/5/1997''
set @dtenddate = dateadd(dd, -1, cast(cast((month(@dtenddate) + 1) as
varchar(2)) + ''/01/'' + cast(year(@dtenddate) as varchar(4)) + ''
23:59:59'' as datetime))
set @dtstartdate = dateadd(mm, -1 * 12, @dtenddate)
— get all months into the first table
set @i = 0
while (@i < 12)
begin
set @dtdate = dateadd(mm, -1 * @i, @dtenddate)
insert into @tblmonths select cast(year(@dtdate) as varchar(4)) + ''-'' +
case
when month(@dtdate) < 10
then ''0'' + cast(month(@dtdate) as varchar(2))
else cast(month(@dtdate) as varchar(2))
end as smonth
set @i = @i + 1
end
— get all clients who had sales during that period into the "y" table
insert into @tblcustomers
select distinct
c.customerid,
c.companyname,
c.contactname
from customers c
inner join orders o on c.customerid = o.customerid
where o.orderdate between @dtstartdate and @dtenddate
insert into @tblfinal
select m.smonth,
c.customerid,
c.companyname,
c.contactname,
0
from @tblmonths m cross join @tblcustomers c
update @tblfinal set
msales = mydata.msales
from @tblfinal f inner join
(
select c.customerid,
cast(year(o.orderdate) as varchar(4)) + ''-'' +
case when month(o.orderdate) < 10
then ''0'' + cast(month(o.orderdate) as varchar(2))
else cast(month(o.orderdate) as varchar(2))
end as smonth,
sum(od.quantity * od.unitprice) as msales
from customers c
inner join orders o on c.customerid = o.customerid
inner join [order details] od on o.orderid = od.orderid
where o.orderdate between @dtstartdate and @dtenddate
group by
c.customerid,
cast(year(o.orderdate) as varchar(4)) + ''-'' +
case when month(o.orderdate) < 10
then ''0'' + cast(month(o.orderdate) as varchar(2))
else cast(month(o.orderdate) as varchar(2))
end
) mydata on f.customerid = mydata.customerid and f.smonth =
mydata.smonth
select f.smonth,
f.customerid,
f.companyname,
f.contactname,
f.msales
from @tblfinal f
order by
f.companyname,
f.smonth
