Date-ey kinda problem

[astrevens]

bit of a novice, so stay with me a minute......
What i need to get in a stored procedure is the equivilant of several access queries.

what im trying to get is a list of customers who havent bought anything in the current month.

Then for all of the customers who havent bought in the current month, show the last six months and their spending.

i have one Movements which shows all orders along with the account code, date and cost,sell.

so lets recap.....

I would like to search for all of the customers who have bought in the last year but not in the current month, and for all those customers show their spending over the previous 6 months.

This is going to called via Excel using ADO running the query then it will be dispayed in a pivot table.

Is this too long...... i think so.
any help would be much appreciated

 

[SQLBill]

I think will solve the first part of your query
"search for all of the customers who have bought in the
last year but not in the current month":

/* Determine current month and set as variable, using
** format mm/dd/yyyy */

declare @currentmonth smalldatetime

if month(getdate()) = 1
begin
select @currentmonth = '12/1/' + convert(char(4), year(getdate()) -1)
end
else
begin
select @currentmonth = convert(char(2), month(getdate()) -1 + '/1/' + convert(char(4), year(getdate()))
end

/* Retrieve accountcodes that appear in the movements table
** ONLY if they do not also appear during the current month */

select accountcode
from movements
where accountcode not in
(select accountcode
from movements
where date >= @currentmonth)

Let me know if that works....I'm still trying to figure the script to return the accountcode to a variable and then use that to do the rest of what you want.

-SQLBill

 

[SQLBill]

This is how I would retrieve the previous six months data if I was manually inputting the accountcode:

select *
from movements
where (date >= getdate()-6)
and (accountcode = '1234')

Then if I was using a variable I would change it to:

declare @t_accountcode int
set @t_accountcode = '1234'

select *
from movements
where (date >= getdate()-6)
and (accountcode = @t_accountcode)

But I'm still trying to get it so that it's automatic. I'm beginning to think that returning the original results into a temporary table and then using that to do the final select might be the best option.

Something like (untested)

/* creates new table in tempdb */

create table ##accountcodes
(
accountcodes int
)
go

/* Determine current month and set as variable, using
** format mm/dd/yyyy */

declare @currentmonth smalldatetime

if month(getdate()) = 1
begin
select @currentmonth = '12/1/' + convert(char(4), year(getdate()) -1)
end
else
begin
select @currentmonth = convert(char(2), month(getdate()) -1 + '/1/' + convert(char(4), year(getdate()))
end

/* populates the table using data from movements */

insert into ##accountcodes
select accountcode
from movements
where accountcode not in
(select accountcode
from movements
where date >= @currentmonth)

/* retrieves required data */

select *
from movements m, ##accountcodes ac
where (ac.accountcode = m.accountcode)
and (date >= getdate()-6)

-SQLBill

 

[bperry]

Hi there,
I'm thinking of an approach like this.
The date checking will still need a bit of fussing with, because we need to nail down what we exactly mean by '12 months ago' and '6 months ago.

Anyway, this is the start of what I am thinking:

Select * from
(
Select * from movements m1
where NOT EXISTS
(Select * from movements m2
where m2.AcctCode = m1.AcctCode
and YEAR(SaleDate) = YEAR(getdate())
and Month(SaleDate) = Month(getdate())
)
and SaleDate >= DATEADD(mm, -13, getdate())
) as yrSales

where SaleDate >= DATEADD(mm,-6,getdate())

Bold (in the middle) selects customers who have not bought this month.
From THAT group, Red Color selects people who have bought in the last year.
From That group, Blue Color selects Sales for last 6 months.


This works on my test data, but like I said we would need to fiddle with the dates. If you care to, you can try it and advise.

 

[bperry]

P.S.
Of course, in the very first Select, substitute * for whatever columsn you actually want.

Also, there's a bit of an anomoly with choosing the past purchases. You say:

"I would like to search for all of the customers who have bought in the last year"

But then when we go to report them, you say:

"and for all those customers show their spending over the previous 6 months"


In other words, if someone bought 9 months ago, we would initially select them, but then we wouldn't report any purchases since they have no purchases in last 6 months.

If we could resolve that, and agree that we are really in effect only reporting people who have bought in the last 6 months, then my suggested query would get a little easier:

Select * from movements m1
where NOT EXISTS
(Select * from movements m2
where m2.AcctCode = m1.AcctCode
and YEAR(SaleDate) = YEAR(getdate())
and Month(SaleDate) = Month(getdate())
)
and SaleDate >= DATEADD(mm, -6, getdate())

(But again, we still need to fiddle with the dates a little bit.)

-----------
bperry

 

[SQLBill]

by the way astrevens, these two requirements from you are NOT the same thing:

1.
"what im trying to get is a list of customers who havent bought anything in the current month.

Then for all of the customers who havent bought in the current month, show the last six months and their spending."

2.
"...so lets recap.....

I would like to search for all of the customers who have bought in the last year but not in the current month, and for all those customers show their spending over the previous 6 months."

The final result for each would be:
1. the last six months of spending for those customers who haven't bought anything this month.

2. the last six months of spending for all customers who bought any time in the past year BUT NOT in this month.

So, which result are you looking for?

-SQLBill

 

[bperry]

Hi astrevens and sqlbill:

Actually, i think this works pretty well, as least on my test data. As I mentioned earlier, substitute whatever columns you want for the first Select *.
--------------------------

SELECT * From movements m1
WHERE NOT EXISTS
(Select * from movements m2
where m2.AcctCode = m1.AcctCode
and YEAR(SaleDate) = YEAR(getdate())
and Month(SaleDate) = Month(getdate())
)
AND SaleDate >= DATEADD(mm, -6, getdate())
-------------------------------------
An issue with date still needs to be resolved.
Is the approach of 'subtracting 6 months from today' accurate enough for what you are doing? (I suspect it probably is......seems like you are looking for general sales data that doesn't have to be exact to the dollar)

(Also, I was wondering if your SaleDate column contained date+time, or just date? (i.e time=00:00:00)

 

[astrevens]

Thanx guys, just got into work, im gonna try this stuff now and give ya a shout later to see if it works. and i was planning on having an easy day today!!!!

 

[astrevens]

bperry, ur a life saver!
it works!!!
the sales date field is just date, there is a seperate field containing the time but like u said im just looking at general sales.
ok, ur really good at this so here is another im working on.
I would like to add into that query u just gave me, the product that over the six month period each customer has bought the most of. so it would be something like this:

account | cost | sell | Product bought most | month | year-----------------------------------------------------------
bob01 | £10 | £20 | plainpaper | 1 | 2002

the product field is mv_product.

 

[bperry]

I won't be able to look at it until tomorrow, but maybe SQL Bill will take a look at it with us.

Are you saying the requirements are all the same, but instead if listing every product the cust bought in last 6 months, we just want to print the single one that he bought the most?

 

[astrevens]

yep thats it all the same except include just the single product he bought the most of.

 

[bperry]

Re: the new (second) query.
Perhaps you'd like to try this for starters.
------------------------------
SELECT * From movements m1
WHERE NOT EXISTS
(Select * from movements m2
where m2.AcctCode = m1.AcctCode
and YEAR(SaleDate) = YEAR(getdate())
and Month(SaleDate) = Month(getdate())
)
AND SaleDate >= DATEADD(mm, -6, getdate())
AND Amount =
(Select TOP 1 Amount from movements m3
where m3.AcctCode = m1.AcctCode
Order By Amount Desc)
--------------------------------
But I might suggest something different if there was a primary key column in the table that you haven't mentioned.
Anyway, try it to see if it gives the results you want. Then we can look at finetuning it if it needs a performance boost.

 

[bperry]

Here's another version. This one should perform better, but just reports the AcctCode and Amount. But perhaps that's all you need for this particular query.
------------------------
SELECT AcctCode, MaxAmt
from
(Select AcctCode, MAX(Amount) as MaxAmt
From Movements
Where SaleDate >= DATEADD(mm, -6, getdate())
Group by AcctCode) dt

WHERE NOT EXISTS
(Select * from movements m2
where m2.AcctCode = dt.AcctCode
and YEAR(SaleDate) = YEAR(getdate())
and Month(SaleDate) = Month(getdate())
)
------------------------