October 31, 2009 at 5:53 am
Dear Gurus in MS SQL I have the following need.Please help me with this query please.
I have a table as follows
CREATE TABLE XX_MSTEST ( CID VARCHAR(10), VDATE datetime)
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1001', '01/12/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1001', '02/23/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1001', '03/03/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1002', '05/04/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1002', '05/20/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1002', '10/02/2009 12:00:00 AM')
GO
INSERT INTO XX_MSTEST ( CID, VDATE ) VALUES ('1002', '10/29/2009 12:00:00 AM')
GO
What I would like to have is the number of counts where the customer has re-visted in less than 30 days with respect to the last visit.For example in the customer 1001 first re-visit is after 42 days which will not be included in my count . I need 2 outputs one is plain count and other is the details.
So in the example I gave the plain count will fetch 3 and details of the cout will be
100123 Feb 09,100220 May 09,100229 Oct 09.
Thanks in advnce.
October 31, 2009 at 6:26 am
Thanks for supplying the test data. However, your expected results seem to be slightly inconsistent with your described requirements. Is it the first or the second date of a pair of visits less than 30 days apart that you wish to return? I've assumed below that it is the second date that you want. The number of rows returned by the query gives your required count.
;WITH cteSEQ AS (
SELECT CID, VDATE,
ROW_NUMBER() OVER (PARTITION BY CID ORDER BY VDATE) AS rn
FROM XX_MSTEST
)
SELECT SEQ2.CID, SEQ2.VDATE
FROM cteSEQ SEQ1
INNER JOIN cteSEQ SEQ2 ON (SEQ1.CID = SEQ2.CID AND SEQ1.rn = SEQ2.rn - 1)
WHERE DATEDIFF(day, SEQ1.VDATE, SEQ2.VDATE) < 30
ORDER BY SEQ2.CID, SEQ2.VDATE
SELECT @@ROWCOUNT AS RevisitCount
October 31, 2009 at 6:33 am
dear andrew each date for a customer is to be considered with the previous visit of that customer.for example if there are 5 visits A,B,C,D,E the E is condered with the date of E and so also D's date with C's date and so on for a given customer.
October 31, 2009 at 6:49 am
My query gives the following results, where the single entry for CID=1001 returns the visit date 2009-03-03 rather than 2009-02-23, but I believe that it returns what you want.
CID VDATE
---- -----------------------
1001 2009-03-03 00:00:00.000
1002 2009-05-20 00:00:00.000
1002 2009-10-29 00:00:00.000
RevisitCount
------------
3
October 31, 2009 at 10:21 pm
Yes,Andrew.Thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply