stored Procedure to find out last years data

[astrevens]

I have the following stored procedure:


SELECT mv_rep as Rep,mv_account,aca_name as Name,sum(mv_cost)as Cost, sum(mv_sell_value)as Value,
sum(mv_sell_value - mv_cost)as profit,
datepart(month,[mv_date])as Month,datepart(year,[mv_date])as Year

From movements m1 INNER JOIN ADDRESSES ON( m1.mv_ACCOUNT = ADDRESSES.ACA_ACCOUNT)
WHERE NOT EXISTS
(Select * from movements m2
where m2.mv_Account = m1.mv_account
and YEAR(mv_Date) = YEAR(getdate())
and Month(mv_Date) = Month(getdate()))

AND mv_date >= DATEADD(mm, -6, getdate())and

and mv_rep in ("01","02","03","04","10&quot and
mv_type in ("no","eo","op&quot

What this does is find out who hasen't bought anything this month and show the previous 6 months spending for these customers.
It works fine but I would like to include in this the same previous 6 months data but for the previour year.

So at the end i will have a bunch accounts who havent speant this month, with their spend for the previous 6 months in this year and the previous 6 months for last year.

Many thanks to all the bods that can help me with this one.

 

[crystalized]

My first thought on this is that you might want to do a union of the stuff you are already getting and add in the stuff from the previous year. Now this is not tested but it would be something like this.

(SELECT mv_rep as Rep,mv_account,aca_name as Name,sum(mv_cost)as Cost, sum(mv_sell_value)as Value,
sum(mv_sell_value - mv_cost)as profit,
datepart(month,[mv_date])as Month,datepart(year,[mv_date])as Year

From movements m1 INNER JOIN ADDRESSES ON( m1.mv_ACCOUNT = ADDRESSES.ACA_ACCOUNT)
WHERE NOT EXISTS
(Select * from movements m2
where m2.mv_Account = m1.mv_account
and YEAR(mv_Date) = YEAR(getdate())
and Month(mv_Date) = Month(getdate()))

AND mv_date >= DATEADD(mm, -6, getdate())and

and mv_rep in ("01","02","03","04","10&quot and
mv_type in ("no","eo","op&quot
)
Union
(
SELECT mv_rep as Rep,mv_account,aca_name as Name,sum(mv_cost)as Cost, sum(mv_sell_value)as Value,
sum(mv_sell_value - mv_cost)as profit,
datepart(month,[mv_date])as Month,datepart(year,[mv_date])as Year

From movements m1 INNER JOIN ADDRESSES ON( m1.mv_ACCOUNT = ADDRESSES.ACA_ACCOUNT)
WHERE NOT EXISTS
(Select * from movements m2
where m2.mv_Account = m1.mv_account
and YEAR(mv_Date) = YEAR(getdate())
and Month(mv_Date) = Month(getdate()))

AND mv_date >= DATEADD(mm, -18, getdate())and
mv_date < DATEADD(mm, -12, getdate())

and mv_rep in (&quot;01&quot;,&quot;02&quot;,&quot;03&quot;,&quot;04&quot;,&quot;10&quot and
mv_type in (&quot;no&quot;,&quot;eo&quot;,&quot;op&quot
)



I hope this helps. Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde