June 26, 2013 at 10:58 am
Hi,
I have a table wherein I have customerID, transactionid, transactiontime, amount. My requirement is that I need to fetch only those customers who had 3 or more transactions within 3 hours and the total amount value should be greater than 1000 dollars. Can someone please help me with this.
June 26, 2013 at 11:10 am
we can probably help, but not without providing some DDL and sample data that represents the structure and data we need to look at;
if you can convert "customerID, transactionid, transactiontime, amount" into a CREATE table statement , and add a few INSERT INTO statements to give us sample data, we could really help.
then you need to define the "three hour" rule.
is it 3 specific hours, any rolling group of hours (ie 11-2, or 12-3, or 1-4) or something else?
is a transaction a transactionid, or any row in the table, on a per customer basis (ie is ther two rows for custoemr 42 with trnasacitonid 17?) for example, cashing a check might be adding $5,000, followed immediately by a withdrawl of the same new $5,000; so maybe you only want positive transactions?
Lowell
June 26, 2013 at 1:32 pm
pjrpjr7 (6/26/2013)
Hi,I have a table wherein I have customerID, transactionid, transactiontime, amount. My requirement is that I need to fetch only those customers who had 3 or more transactions within 3 hours and the total amount value should be greater than 1000 dollars. Can someone please help me with this.
You gave us essentially no information about your system, and you want code to do that? Not really helpful, but here is my take:
select customerid, sum(amount) as total, count(*) as trancount
from trantable
where trantime > dateadd(hh, -3, getdate())
group by customerid
having sum(amount) > 1000
and count(*) > 3
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 26, 2013 at 2:53 pm
CREATE TABLE [dbo].[SampleExcel2](
[CustID] tinyint NULL,
[TxnID] tinyint not NULL,
[TxnTime] datetime NULL,
[Amount] int NULL
) ON [PRIMARY]
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 1, '6/1/13 12:30 AM', 900)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 2, '6/1/13 2:00 AM', 150)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 3, '6/2/12 5:00 PM', 5)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 4, '6/2/12 5:15 PM', 2)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 5, '6/2/12 7:00 PM', 1500)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 6, '6/4/12 1:00 PM', 450)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 7, '6/4/12 5:00 PM', 700)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 8, '6/4/12 10:00 PM', 800)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (2, 9, '6/2/12 6:00 AM', 250)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (2, 10,'6/2/12 8:00 AM', 118)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (3, 11, '6/4/12 12:00 AM', 800)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 12, '6/3/12 2:00 PM', 1200)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 13, '6/10/12 10:00 PM', 2)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 14, '6/10/12 9:30 PM', 50)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 15, '6/11/13 12:00 AM', 621)
3 hour rule is rolling 3 hours. We can take any 3 hours and as long as there are 3 or more than 3 transactions or total amount is more than 1000 dollars, then such a record should qualify for the report.
June 26, 2013 at 3:08 pm
It might not be the best option but it should give you the correct result according to your test data.
WITH cte AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY CustID ORDER BY TxnTime) rn
FROM #SampleExcel2
),
Customers AS(
SELECT a.CustID, b.TxnTime StartTime, a.TxnTime EndTime
FROM cte a
JOIN cte b ON a.CustID = b.CustID
AND a.rn = b.rn + 2
AND a.TxnTime <= DATEADD(HH, 3, b.TxnTime)
)
SELECT s.CustID
FROM #SampleExcel2 s
JOIN Customers c ON s.CustID = c.CustID AND s.TxnTime BETWEEN c.StartTime AND c.EndTime
GROUP BY s.CustID
HAVING SUM( Amount) > 1000
June 26, 2013 at 5:55 pm
pjrpjr7 (6/26/2013)
CREATE TABLE [dbo].[SampleExcel2]([CustID] tinyint NULL,
[TxnID] tinyint not NULL,
[TxnTime] datetime NULL,
[Amount] int NULL
) ON [PRIMARY]
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 1, '6/1/13 12:30 AM', 900)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 2, '6/1/13 2:00 AM', 150)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 3, '6/2/12 5:00 PM', 5)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 4, '6/2/12 5:15 PM', 2)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 5, '6/2/12 7:00 PM', 1500)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 6, '6/4/12 1:00 PM', 450)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 7, '6/4/12 5:00 PM', 700)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (1, 8, '6/4/12 10:00 PM', 800)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (2, 9, '6/2/12 6:00 AM', 250)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (2, 10,'6/2/12 8:00 AM', 118)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (3, 11, '6/4/12 12:00 AM', 800)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 12, '6/3/12 2:00 PM', 1200)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 13, '6/10/12 10:00 PM', 2)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 14, '6/10/12 9:30 PM', 50)
INSERT [dbo].[SampleExcel2] ([CustID], [TxnID], [TxnTime], [Amount]) VALUES (4, 15, '6/11/13 12:00 AM', 621)
3 hour rule is rolling 3 hours. We can take any 3 hours and as long as there are 3 or more than 3 transactions or total amount is more than 1000 dollars, then such a record should qualify for the report.
Please ALWAYS show what you expect the INPUTS to be (if any) as well as the OUTPUTS when you ask for help. I ask for inputs here because I still don't understand the need. Are you going to pass in a datetime value and expect the query to go backwards from that point? Or are you looking for the system to somehow automagically iterate through every record and show a rolling-3-hour-back output (if any) for each new record? If the former, I think you need to lock it down to fixed time intervals (like on the hour). If not, I can't see how your output will be useful.
Say you had 200 contiguous minutes of records, starting at 0800, one record for the same single customer per minute, each 100 dollars. What output would you expect for that series of data?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply