find min. visittime of each customer use query

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

SQL Server 2008 Forums

Transact-SQL (2008)

find min. visittime of each customer use query

Author

Topic

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 05:03:02

My query is to display all purchase details of customers whose purchased within 30 days from his first visittime and also they purchased in between Nov-1, 2008 to Nov-30, 2008 and some other conditions also(u can see in my query). How to write sql query?
Tables - st_emailtracking (visittime)
op_order - (datepurchased)
I wrote one, how i will get minimum visittime , because some users have more than one visittimes. My query as follows,
1)select min(st.visittime) from alamyStatistics..st_emailTracking st
join alamyusers..up_visitor up on st.userid=up.userid
join alamyorders..op_order op on op.userid=st.userid
where ((op.datepurchased between '11-01-08' and '11-30-08')
and (order_total!=0) and (orderstatusid=100) and (up.isdistrib=0))
Here o/p is getting minimum of all visittimes under the column visittime. But i need visittime of each customer separately.
So i wrote another
2)select min(st.visittime),st.userid from alamyStatistics..st_emailTracking st
join alamyusers..up_visitor up on st.userid=up.userid
join alamyorders..op_order op on op.userid=st.userid
where ((op.datepurchased between '11-01-08' and '11-30-08')
and (order_total!=0) and (orderstatusid=100) and (up.isdistrib=0)) group by st.visittime,st.userid
But here displaying all visittimes of each customer separately.
I am totally confused, how to implement my logic?
pls help me.
Thanks in advance for any help

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 05:32:24

[code]
select *
from
(
select st.userid, st.visittime,
row_no = row_number() over (partition by st.userid order by st.visittime)
from alamyStatistics..st_emailTracking st
join alamyusers..up_visitor up on st.userid=up.userid
join alamyorders..op_order op on op.userid=st.userid
where ((op.datepurchased between '11-01-08' and '11-30-08')
and (order_total!=0) and (orderstatusid=100) and (up.isdistrib=0))
) d
where row_no = 1
[/code]

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

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 05:36:22

quote:

Originally posted by khtan


select *
from
(
select st.userid, st.visittime, 
       row_no = row_number() over (partition by st.userid order by st.visittime)
from   alamyStatistics..st_emailTracking st
join   alamyusers..up_visitor up on st.userid=up.userid
join   alamyorders..op_order op  on op.userid=st.userid
where ((op.datepurchased between '11-01-08' and '11-30-08')
and   (order_total!=0) and (orderstatusid=100) and (up.isdistrib=0))
) d
where row_no = 1

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

Hi,
Thanks for ur support.
But i tried ur query , showing an error as 'row_number' is not a recognized function name.Is it needed to declare?

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 06:05:14

What is the version of your SQL Server ?

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

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 06:11:02

quote:
Originally posted by khtan

What is the version of your SQL Server ?

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

Version,
Microsoft SQL Server 2000 - 8.00.2273

mike

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 06:14:47

I'm not sure about the version, how do i know which version of sql server i am running?

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 06:17:49

quote:
Version,
Microsoft SQL Server 2000 - 8.00.2273

SQL Server 2000. row_number() only available in SQL 2005 and 2008.

by the way, you posted your question in Transact-SQL (2008) forum.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 06:19:53

try this


SELECT st.userid, st.visittime
FROM   alamyStatistics..st_emailTracking st
JOIN   (
		SELECT	userid, min_visittime = MIN(visittime)
		FROM	alamyStatistics..st_emailTracking
		GROUP BY userid
       ) m	ON	st.userid	= m.userid
		AND	st.visittime	= m.min_visittime
JOIN   alamyusers..up_visitor up ON st.userid=up.userid
JOIN   alamyorders..op_order op  ON op.userid=st.userid
WHERE ((op.datepurchased BETWEEN '11-01-08' AND '11-30-08')
AND   (order_total!=0) AND (orderstatusid=100) AND (up.isdistrib=0))

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

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 06:35:04

the query i tried, its getting but showing all visittime of each user. i'll show u the sample data which i got.
userid visittime
7224111A 2008-07-22 11:00:16.000
7224111A 2008-07-22 11:00:16.000
946D9F9E 2008-05-21 11:20:28.000
946D9F9E 2008-05-21 11:20:28.000
98257FCE 2008-09-18 12:37:55.000
98257FCE 2008-09-18 12:37:55.000
98257FCE 2008-09-18 12:37:55.000

After get the minimum visittime of all users who purchased between 'Nov-1,2008 to Nov-30,2008' i have to check another condition is "Take the minimum visittime and check datepurchase within 30 days from minimum visittime.

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 06:57:39

do you have a primary key in the st_emailTracking table ?

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

mike09
Starting Member

8 Posts

Posted - 2009-05-28 : 07:02:00

quote:
Originally posted by khtan

do you have a primary key in the st_emailTracking table ?

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

yes, userid is the pkey

mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 08:10:49

use distinct


select distinct st.userid, st.visittime

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

mike09
Starting Member

8 Posts

Posted - 2009-05-29 : 00:35:28

quote:
Originally posted by khtan

use distinct

select distinct st.userid, st.visittime

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

Hi sir,
its getting without repetiton of visittime, fine .
That means now i am getting unique visittime of each user who purchased between 'Nov-1,2008 to Nov-30,2008',
My actual query is
"Display all purchase details of customers whose purchased within 30 days from his first visittime and also they purchased in between Nov-1, 2008 to Nov-30, 2008 and some other conditions also."
Now one part over.
Next i have to check another condition also "Take this minimum visittime and check that user who purchased(op.datepurchased) within 30 days from minimum visittime., then display who are the users?

ie, op.datepurchased between min.visittime and dateadd(dd,30,min.visittime)

Thanks a lot for your valuable support

mike

mike09
Starting Member

8 Posts

Posted - 2009-05-29 : 04:19:34

Hi, finally i got using ur query,

SELECT distinct st.userid, st.visittime,op.datepurchased,(up.firstname + ' ' + up.lastname) as username,up.email,c.countryid,up.whenregistered,(month(op.datepurchased)) as Month,(year(op.datepurchased)) as Year,(COALESCE(op.order_total,0) / COALESCE(op.ExchangeRate,1)) as Amountspent,right(st.emailcode,2),left(st.emailcode,8)
FROM alamyStatistics..st_emailTracking st
JOIN (SELECT st.userid,min_visittime = MIN(visittime)
FROM alamyStatistics..st_emailTracking st
JOIN alamyusers..up_visitor up on st.userid=up.userid
JOIN alamyorders..op_order op on op.userid=st.userid
JOIN alamyusers..up_contact c on op.userid = c.userid
WHERE ((op.datepurchased BETWEEN '11-01-08' AND '11-30-08')
AND (order_total!=0) AND (orderstatusid=100) AND (up.isdistrib=0)
AND (st.emailcode like '_%LB') or (st.emailcode like '_%NB')

)GROUP BY st.userid)m
ON st.userid = m.userid
AND st.visittime = m.min_visittime
JOIN alamyusers..up_visitor up ON st.userid=up.userid
JOIN alamyorders..op_order op ON op.userid=st.userid
JOIN alamyusers..up_contact c on op.userid = c.userid
WHERE ((op.datepurchased BETWEEN '11-01-08' AND '11-30-08')
AND (op.datepurchased between min_visittime and dateadd(dd,30,min_visittime))
AND (order_total!=0) AND (orderstatusid=100) AND (up.isdistrib=0)
AND (op.datepurchased > min_visittime))

Can I ask another doubt, my query is "Take each visittime of a user then check that this user has purchased within 30 days,
if he has no purchase then take his next visittime and check if there is any purchased within 30 days from this visittime and go on
When the user purchased within 30 days from any visittime have to display all records based some other conditions."
I think u got my qn. That is neede looping statements,
ie Take first visittime then
check
Take 2nd visittime then
check
Please can u help me this also .
I would be grateful for ur help and answer.

mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources