Please help with query - same table transaction

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 2012 Forums

Transact-SQL (2012)

Please help with query - same table transaction

Author

Topic

ismailm
Starting Member

13 Posts

Posted - 2015-04-30 : 16:09:50

Hi guys,

I am trying to write a query to find all cases within a particular month (e.g. 1st April to 30th April) where a transaction by the same user has been captured with the second transaction needing to be no more than 60 minutes after the first transaction. The first transaction would need to have a value of '2' in the column 'Status'. The subsequent transaction(s) can be any 'Status'.
So:

Table: Table1
Fields: UserID (int), DateCaptured(date), Status (int)

I also need a second query to show number of subsequent transactions divided by number of transaction that had a Status=2. (I guess a way to explain this would be count(subsequent transactions)/count(transactions with Status=2 in April)

Your help would be much appreciated.

Let me know if this is not clear.

Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 16:21:02

Is DateCaptured really datetime? Otherwise, how would you get 60 minutes from two dates?

ismailm
Starting Member

13 Posts

Posted - 2015-04-30 : 16:23:11

quote:
Originally posted by gbritton

Is DateCaptured really datetime? Otherwise, how would you get 60 minutes from two dates?

Yes, you're right, my bad! :)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 16:29:24

You can use Windowing for that, I believe


select userid, datecaptured, status
from 
(
    select userid, datecaptured, status
         , First_Value(datecaptured) over(partition by userid order by datecaptured) as FirstDate
    from table1
) _
where datediff(minute, datecaptured, firstdate) <= 60

ismailm
Starting Member

13 Posts

Posted - 2015-04-30 : 16:48:54

quote:

Originally posted by gbritton

You can use Windowing for that, I believe


select userid, datecaptured, status
from 
(
    select userid, datecaptured, status
         , First_Value(datecaptured) over(partition by userid order by datecaptured) as FirstDate
    from table1
) _
where datediff(minute, datecaptured, firstdate) <= 60

Thanks for that, however I am not sure that will give me the desired results.

What I want to know is did these users have another transaction within 60 minutes of the datecaptured returned in this query:

Select userid, datecaptured
from table1
where status=2
and datecaptured between '2015-04-01 00:00:00' and '2015-04-30 23:59:59'

(and how many of them did?)
the newer transactions should also be within the same month.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-30 : 19:27:48

Try my query, add Where Status=2 to the subquery

ismailm
Starting Member

13 Posts

Posted - 2015-04-30 : 19:43:19

I have played around with your query and I believe it does work, which is great. So I moved on to trying the second part of my query:

select count (*)  from
(select userid, datecaptured, tmp.firstorder, status,tmp.firststatus
from 
(
    select userid, datecaptured, status
         , First_Value(datecaptured) over(partition by userid order by datecaptured) as firstdate
		 , First_Value(status) over(partition by userid order by datecaptured) as firststatus
    from table1
		where datecaptured between '2015-04-01 00:00:00' and '2015-04-30 23:59:59'		
) as tmp
where datediff(minute, datecaptured, tmp.firstdate) <= 60
and datecaptured between '2015-04-01 00:00:00' and '2015-04-30 23:59:59'
and userid is not null
and datecaptured>tmp.firstorder
and tmp.firstdate between '2015-04-01 00:00:00' and '2015-04-30 23:59:59'
and tmp.firststatus=2) subsequenttrns / select count (*) from 
(select userID from table1
 where status=2
  and datecaptured between '2015-04-01 00:00:00' and '2015-04-02 23:59:59'
  and userid is not null)  status2

Unfortunately, I keep getting an error (Incorrect syntax near '/'.)
Any idea? (when I run the two count queries individually without the '/' they are fine).

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources