3 month visit member and no visit member

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

3 month visit member and no visit member

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-09 : 23:23:51

Who can help me give a sql query ?
I have two table save member data and sales record.Now I want to count which member within 3 month have visit our shop to bought service and count which member no visit shop over 3 month. please give me a sample query.

Table 1 - sales_header
Data field
member_id
trx_date
trx_type='S'

Table 2 - member master
data field
member_id
member_name

desire output
member-id , 3 month last visited date shop, > 3 month no visit
001 3 times 30 Jan 2015
002 11-Aug-2013
003 1 times 02-jan-2012

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-10 : 03:16:57

[code]
select
member_id
, sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) + ' times'
, max(case when trx_date < dateadd(month, -3, getdate()) then trx_date else NULL end)
from sales_header
group by member_id
[code]

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-10 : 04:19:25

Hi Waterduck,

I try to compile your query, and prompt error message in below '
conversion failed when converting the varchar value 'times' to data type int .Error code 245.
--------------------------------------------------
select
user_member
, sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) + ' times'
, max(case when trx_date < dateadd(month, -3, getdate()) then trx_date else NULL end)
from trx_hdr
group by user_member

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-12 : 02:11:04

select
member_id
, cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) as varchar(10)) + ' times'
, max(case when trx_date < dateadd(month, -3, getdate()) then trx_date else NULL end)
from sales_header
group by member_id

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 05:48:30

Thanks your help !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources