Monthly Repeat Users - SQL Server Forums

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

Monthly Repeat Users

Author

Topic

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-06 : 08:57:58

i have a table of customer orders
id order_date cust_id
1 2010-01-01 1
2 2010-01-01 2
3 2010-01-01 3
4 2010-01-02 1
5 2010-01-02 4
6 2010-01-02 5
7 2010-01-03 1
8 2010-01-03 2
9 2010-01-03 3
10 2010-01-03 4

I would like to find the monthly / weekly repeat customers
how do i do this?

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-06 : 09:00:09

hi,

just go thr this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137938

Regards,
Divya

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-06 : 09:09:18

this ?


select cust_id, dateadd(month, datediff(month, 0, order_date), 0)
from   customer_orders
group by cust_id, dateadd(month, datediff(month, 0, order_date), 0)
having count(*) > 1

KH
[spoiler]Time is always against us[/spoiler]

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-06 : 09:11:55

quote:
Originally posted by visakh16

just group by month(datefield) and take count(*)

Regards,
Divya

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-06 : 11:39:47

thanks for the answers.
let me explain what i mean in a little detail ( which i should have in my orginal post. so please pardon me. )

basically what i am trying to do is get monthly repeat customers which are unique
so in my example table the
cust_id = 1 will have count 3 ( meaning 3 orders in the Jan month from customer 1 )
cust_id = 2 will have count 2 ( meaning 2 orders in the Jan month from customer 2 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 11:44:29

quote:
Originally posted by andthereitgoes

thanks for the answers.
let me explain what i mean in a little detail ( which i should have in my orginal post. so please pardon me. )

basically what i am trying to do is get monthly repeat customers which are unique
so in my example table the
cust_id = 1 will have count 3 ( meaning 3 orders in the Jan month from customer 1 )
cust_id = 2 will have count 2 ( meaning 2 orders in the Jan month from customer 2 )

Didnt Tans suggestion work for you?

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-06 : 11:45:56

quote:
Originally posted by divyaram

quote:
Originally posted by visakh16

just group by month(datefield) and take count(*)

Regards,
Divya

that will only give me total orders in a particular month. i need total orders by EACH CUSTOMER in a month

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-06 : 11:47:21

quote:
Originally posted by visakh16

quote:
Originally posted by andthereitgoes

thanks for the answers.
let me explain what i mean in a little detail ( which i should have in my orginal post. so please pardon me. )

basically what i am trying to do is get monthly repeat customers which are unique
so in my example table the
cust_id = 1 will have count 3 ( meaning 3 orders in the Jan month from customer 1 )
cust_id = 2 will have count 2 ( meaning 2 orders in the Jan month from customer 2 )

Didnt Tans suggestion work for you?

am still trying to understand it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 11:49:38

it basically groups by cust_id and month date and takes count

dateadd(month, datediff(month, 0, order_date), 0) makes the datevalue to change to first day of the corresponding month.

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-06 : 11:53:48

quote:
Originally posted by visakh16

it basically groups by cust_id and month date and takes count

dateadd(month, datediff(month, 0, order_date), 0) makes the datevalue to change to first day of the corresponding month.

oh i see. thanks for the explanation. no it doesnt work for me.
what i need is list of total no. of orders in a month by a customer
eg:
Jan Customer-1 3 orders
Jan Customer-2 12 orders

I dont know whats the best way to represent this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 12:00:01

what about this?


select DATENAME(mm,dateadd(month, datediff(month, 0, order_date),0)),cust_id,count(*)
from   customer_orders
group by DATENAME(mm,dateadd(month, datediff(month, 0, order_date), 0)),cust_id

andthereitgoes
Starting Member

6 Posts

Posted - 2010-01-07 : 10:24:39

quote:

Originally posted by visakh16

what about this?


select DATENAME(mm,dateadd(month, datediff(month, 0, order_date),0)),cust_id,count(*)
from   customer_orders
group by DATENAME(mm,dateadd(month, datediff(month, 0, order_date), 0)),cust_id

Ahh, that might work. i am just going through it to see this is what i wanted :) thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:34:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources