September 11, 2011 at 10:56 pm
HI,
I have a customer table which stores transaction details of customers. Now i need to separate records based on the condition more than 2 times recharge with in 5 days.
i.e., if a customer done recharge for his card more than 2 times with in 5 days i need to retrieve that record..
Pls help me on this..
For example...
I have data like this
CustomerId RechargeAmt RechargeDate
1 100.00 2011-08-12
2 500.00 2011-08-15
1 300.00 2011-08-12
3 100.00 2011-08-12
3 700.00 2011-08-12
2 100.00 2011-08-15
4 200.00 2011-08-10
1 100.00 2011-08-13
1 400.00 2011-08-14
1 100.00 2011-08-15
2 300.00 2011-08-12
3 100.00 2011-08-15
now i need to get the out put for the customers 1,2 and 3.
please help on this..
thanks..
September 11, 2011 at 11:10 pm
Can you pls just re-format your question with the sql scripts of create table and Insert into table statements?
.. So that someone can help you quickly.
September 11, 2011 at 11:26 pm
Please find the script below..
create table customer_trans
(
customerid int,
rechargeamt varchar(50),
rechargedate varchar(30)
);
insert into customer_trans values(1,'100.00','2011-08-12');
insert into customer_trans values(2,'500.00','2011-08-15');
insert into customer_trans values(1,'300.00','2011-08-12');
insert into customer_trans values(3,'100.00','2011-08-12');
insert into customer_trans values(3,'700.00','2011-08-12');
insert into customer_trans values(2,'100.00','2011-08-15');
insert into customer_trans values(4,'200.00','2011-08-10');
insert into customer_trans values(1,'100.00','2011-08-13');
insert into customer_trans values(1,'400.00','2011-08-14');
insert into customer_trans values(1,'100.00','2011-08-15');
insert into customer_trans values(2,'300.00','2011-08-12');
insert into customer_trans values(3,'100.00','2011-08-15');
select * from customer_trans ;
Thanks..
September 12, 2011 at 12:09 am
Something like
with cte as
(select CustomerId, RechargeDate, Count(*) RechargeNr
from customer_trans
group by CustomerId, RechargeDate)
select c1.CustomerId, c1.RechargeDate, sum(c2.RechargeNr)
from cte c1
join cte c2 on c2.CustomerId = c1.CustomerId and c2.RechargeDate between c1.RechargeDate and DateAdd(d, 5, c1.RechargeDate)
group by c1.CustomerId, c1.RechargeDate
having sum(c2.RechargeNr) > 2
However there are a few things about the table that i dont like.
1. Amount shouldnt be varchar
2. Date shouldnt be varchar
3. There is no primary key
And to run the code above an index on CustomerId and RechargeDate would propably be useful.
Oh and there is no guarantee that the above code will not bring your server to its knees begging for mercy.
/T
September 12, 2011 at 12:21 am
Thanks for your reply..
The out put for the script is as below..
Can you please explain why customerid 1 is coming two times???
CustomerId RechargeDate (No column name)
1 2011-08-12 6
2 2011-08-12 3
3 2011-08-12 3
1 2011-08-13 3
And as the same customer can do multiple transactions to save all the records there is no primary key..
September 12, 2011 at 12:28 am
Try this in case you are just looking for the count and CustomerIds :
select CustomerId, count(*) RechargeNr from customer_trans
group by customerid
having Count(*)>2 and DATEDIFF(day, MIN(RechargeDate), MAX(RechargeDate))<=5
Regards,
Sudhir
September 12, 2011 at 1:02 am
Sudhir Dwivedi (9/12/2011)
Try this in case you are just looking for the count and CustomerIds :select CustomerId, count(*) RechargeNr from customer_trans
group by customerid
having Count(*)>2 and DATEDIFF(day, MIN(RechargeDate), MAX(RechargeDate))<=5
Regards,
Sudhir
Thank you Sudhir..
It helped me.. I have modified the query according to my requirement..
Thank you so much..
September 12, 2011 at 4:28 am
Can you post your end result so others may learn from it too and (as a plus for yourself) have your solution reviewed for any errors / options for improvements.
September 12, 2011 at 5:13 am
As the requirement is in real time confidential data base, i can't post the original table structure and data.
So i have given the sample data but the requirement is same.
The final query which I have written is
select vrt.CARD_SERIALNO CardSerialNumber,CONVERT(varchar,dbo.FormatDateTime (req_mechinertc),107) Date, substring(CONVERT(varchar,dbo.FormatDateTime (req_mechinertc),100),12,8) Time, vrt.Req_RechargeAmt Amount,vrt.merchant_name MerchantName,vrt.Req_MerchantID MerchantID from v_RechargeTransactions_new vrt
where Req_CustomerID in( select Req_CustomerID from v_RechargeTransactions_new
group by Req_CustomerID
having Count(*)>10 and DATEDIFF(day, MIN(dbo.formatdatetime(serverdatetime)), MAX(dbo.formatdatetime(serverdatetime)))<=5 )
order by dbo.FormatDateTime (req_mechinertc) desc
here dbo.formatdatetime() is UDF.
Thanks...
September 12, 2011 at 6:05 am
I'm slightly confused by something here...
I think the solution you provided will only return results where there have been 10 updates within 5 days if the customer in questio's first and last transactions occurred in tthe same five day period.
If the customer has topped up a month ago and then 34 times yesterday (for example) he would be excluded from the result set as the different between min and max would be too great?
The cte method would appear to be on the right track, but I'm not near an SQL server instance to take a peak so cannot confirm this!
Regards,
JC
September 12, 2011 at 6:53 am
Prasanthi,
After reading JC's comments I realize that my suggestion is not the right one.
Hence I am suggesting you this better query:
In case you are interested to know 2 or more recharges between last recharge date and maximum 5 days prior to that, you can phrase like this:
-----------------------------------------------
select CustTran.customerid
from customer_trans CustTran
where (DATEDIFF(day,CustTran.rechargedate,
(select MAX(rechargedate) from customer_trans
where CustomerId =CustTran.CustomerId ))<=5)
group by customerid
having Count(*)>2
-------------------------------------------------
Regards,
Sudhir
September 12, 2011 at 7:23 am
Your requirements are indeed not clear on the topic of the 5 days period. I was little afraid of that already (and that's why I asked you to post your query). Is it "5 days before the customer's last transaction", or is it "3 consecutive transactions for that customer within any 5 day period ever" or is it "3 transactions for the customer within the last 5 days"?
This question kept me from posting a solution before. There is however one important improvement that I would like to share with you already: Existence checks like these can often be solved more efficient than using a count(*) function. You need to realize that to match the condition set, you only need to "prove existence" of at least 3 consecutive transactions within the period defined. By calling count(*) however you make SQL server read all existing rows for this customer. Especially on larger data sets having to read just 3 instead of all rows can be a very big time (and resource) saver.
If you can provide the proper definition for the start/end of the 5 day period we can most likely provide you with a more efficient solution. And apart from this improvement there are some more that I can describe for you tonight (if nobody beats me to it).
And 2 more important questions for the improvements:
a) does your transactions table have a unique key column?
b) does your model have a customers table that defines the customerid uniquely?
September 13, 2011 at 11:21 pm
hi..
sorry for the late reply..
I will explain you clearly.
I have a view which stores the data of all the transactions of all the customers.
My requirement is I should get a report with all the customer's details who makes recharge more than 10 times with in 5 consecutive days..
here, from the front end user will select one date.. for that date we need to add 5 days and should check for the condition.
there is no primary key column in the view.
hope this clarifies..
Please revert back if anything is still confusing..
thanks.
September 14, 2011 at 12:28 am
How about this?
declare @NumberOfRechargesLimit int, -- initialize it to the number of recharges you want to view; in ur case it is 10
@DateChosenByUser date, -- date chosen by the user from front end
@CutOffDate date -- local variable to calculate the 5 day cut-off limit
set @DateChosenByUser = '2011-08-10'
set @NumberOfRechargesLimit = 10
select @CutOffDate = DATEADD( dd, 5 , @DateChosenByUser)
; with cte as
(
select *
-- get the count of rows per customerid in the given date range
, CountofRecharges = COUNT(*) over(PARTITION by customerid)
from customer_trans
where rechargedate >= @DateChosenByUser and rechargedate <= @CutOffDate
)
select *
from cte
where CountofRecharges >= @NumberOfRechargesLimit
September 14, 2011 at 12:47 am
it's useful..thank you..
can you please share some references or pdf's if you have to learn about CTEs and OVER clause..
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply