checking to see if a store has activity.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

checking to see if a store has activity.

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 11:35:43

I would like to select from a database those stores who did not have any activity(no one did not shop) for 5 days or more in the last 6 months. e.g KFC didn't have any customers Monday,Tuesday, Wednesday,Thursday and Friday.

So the below code is what I came up for those customers who did not shop in the last 6 mths (Dec 2008 - 31st May 2009), for 5 days or more.

Does this look correct to you guys.


select ss.storeid,transactiondate,spend
from stores s inner join transactiontale ss on s.storeid=ss.storeid
where spend = 0
	and transactiondate >= '20081201' and transactiondate < '20090601'
group by ss.storeid,transactiondate,spend
having count(transactiondate) >= 5

I know that I have to do a check for those stores who did not have any transactions for 5 consecutive days first but I wanted to start small and then build.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:44:24

do you mean consecutive 5 days or cumulative 5 days?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-22 : 11:51:21

It looks right to me.

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 11:53:41

i meant consecutive 5 days

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 11:55:13

Okay so the difficulty for me comes when its time to do consecutive 5 days. Does anyone have any ideas on how you would get this done

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:55:30

and does your transactiontale table have records for all stores on all days? even when there was no activity?

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 12:14:16

yes it does have records for all stores on all days

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 12:17:38

are you using sql 2005?

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 12:19:55

sorry those days would not be present in the system

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 12:20:15

no im using 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 12:23:44

quote:
Originally posted by velvettiger

sorry those days would not be present in the system

ok. so do you have another calendar table storing all days info?

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 13:11:22

only those stores that would have had activity would be listed. So their dates woulds show up in the database but once a store didnt have a transaction on a particular date, that date for that store would not show

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 13:23:10

do u have any ideas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 13:28:24

[code]
select distinct s.storeid
from stores s
join (
select *,(select top 1 transactiondate from transactiontale where storeid=t.storeid and transactiondate <t.transactiondate order by transactiondate desc) as prevdate
from transactiontale t
)r
on r.storeid=s.storeid
where (transactiondate >= '20081201' and transactiondate < '20090601')
and r.transactiondate >= dateadd(dd,datediff(dd,0,prevdate)+5,0)
[/code]

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2009-06-22 : 13:37:06

Start with a table of "all" stores and two int fields
One for consecutive days and one as a 5 day flag (default 0).
Then do an outer join to your activity table.

Cycle through your activity by acending day and checking the activity.

Example
ConDay = case when Actdate is not null then 0 else Conday + 1 end
,Flag5day = case when ConDay > 4 then Flag5day + 1 else Flag5day end

Then Select from the allstore table where Flag5day > 1

Jim
Users <> Logic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-06-22 : 15:13:34

Hi visakh16,

Would you mind explaining to me what that query does?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:44:10

quote:
Originally posted by velvettiger

Hi visakh16,

Would you mind explaining to me what that query does?

i'm basically looking for each storeid whose differnece b/w any two transaction dates is > or = 5. this will give details of stores which had 5 days or more gap in activity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources