getting sum of units sold in preceeding weeks

i have a table where daywise i have details of units sold..i want to have a query which takes today (getdate()) and then calculates the no.ofunits sold in 7 preceeding days as week1, 8-14 preceeding days as week2, and so on for 3 preceding months (approx
12 preceeding weeks)..
can anyone plz suggest how i go about with this..i guess...i have either to use the while syntax...
plz respond asap
regards
Nikhil
hi nikhil,
pls post sample data, table structure to get correct results. However you
can have query as follows, replace dt_column with the name of the date
columns you have and sold_units with the "units sold" column of your table.
select
select sum(case when dt_column >= dateadd(dd, -7, {fn current_date()} )
and dt_column <= getdate() then sold_units else 0 end) 'last week',
sum(case when dt_column >= dateadd(dd, -14, {fn current_date()} ) and
dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
end) '2nd last week'
/*
... so on, add sum(CASE... statements to above query till the required
date.
*/
from
<table_name>
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Dear Vishal..
thanks for the response...however i still do have a problem...when it calculates for the (say) 2nd preceeding week..it should not add the 1st preceeding week unitssold...
e.g : previous week is all dates between (current date) and (current date -7)
and 2nd previous week is all dates between (current date - 8) and (current date - 15)
could you plz once again help me out...
regards
Nikhil
|||>>it should not add the 1st preceeding week unitssold...<<
yes, it will not add 1st preceding week's unitssold, All you have to do is
pass right parameter to dateadd function in the query i've posted.
ex:
select sum(case when dt_column >= dateadd(dd, -7, {fn current_date()} )and
dt_column <= getdate() then sold_units else 0 end) 'last week',
sum(case when dt_column >= dateadd(dd, -15, {fn current_date()} ) and
dt_column < dateadd(dd, -8, {fn current_date()} ) then sold_units else 0
end) '2nd last week'
from
<table_name>
If this doesn't satisfy your requirement , pls post table structure and
sample records and expected result set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Well i did get that query..right sort off...
want one more help...
can i have the same in diff columns...e.g. the table column heading would be
item - week1 - week2 - week3
regards
Nikhil
-- Vishal Parkar wrote: --
hi nikhil,
pls post sample data, table structure to get correct results. However you
can have query as follows, replace dt_column with the name of the date
columns you have and sold_units with the "units sold" column of your table.
select
select sum(case when dt_column >= dateadd(dd, -7, {fn current_date()} )
and dt_column <= getdate() then sold_units else 0 end) 'last week',
sum(case when dt_column >= dateadd(dd, -14, {fn current_date()} ) and
dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
end) '2nd last week'
/*
... so on, add sum(CASE... statements to above query till the required
date.
*/
from
<table_name>
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||[vbcol=seagreen]
be
yes possible, you only have to add group by clause. See following example
--sample data
create table tableA
(dt_column datetime,
sold_units int,
itemid int)
insert into tableA values(getdate() ,3, 1)
insert into tableA values(getdate() -1 ,3, 1)
insert into tableA values(getdate() -15,3, 1)
insert into tableA values(getdate() ,3, 2)
insert into tableA values(getdate() -15, 43, 2)
--required query
select itemid,sum(case when dt_column >= dateadd(dd, -7, {fn
current_date()} )
and dt_column <= getdate() then sold_units else 0 end) 'week1',
sum(case when dt_column >= dateadd(dd, -15, {fn current_date()} ) and
dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
end) 'week2'
from
tableA
group by itemid
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||hi there...thanks for the help so far...
wosh..i finally got it....the query does give me week wise sale...for all items for the past 12 weeks..the way i wanted...but now in addition to this..i want two more columns...
1. average
2. standard deviation
for each item...i want the average of all 12 weeks and the standard deviation of all 12 weeks in two adjoining columns...
pray tell me how do i go about with this...
regards
Nikhil
-- Vishal Parkar wrote: --
[vbcol=seagreen]
be
yes possible, you only have to add group by clause. See following example
--sample data
create table tableA
(dt_column datetime,
sold_units int,
itemid int)
insert into tableA values(getdate() ,3, 1)
insert into tableA values(getdate() -1 ,3, 1)
insert into tableA values(getdate() -15,3, 1)
insert into tableA values(getdate() ,3, 2)
insert into tableA values(getdate() -15, 43, 2)
--required query
select itemid,sum(case when dt_column >= dateadd(dd, -7, {fn
current_date()} )
and dt_column <= getdate() then sold_units else 0 end) 'week1',
sum(case when dt_column >= dateadd(dd, -15, {fn current_date()} ) and
dt_column < dateadd(dd, -7, {fn current_date()} ) then sold_units else 0
end) 'week2'
from
tableA
group by itemid
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||similar to the SUM function you can make use of STDDEV and AVG functions.
look for more help on them in books online.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com