May 11, 2010 at 7:11 pm
Hello All,
I have a table that has data in below format:
UserID PackageID DateCreated
ABC 1111 2010-05-01
ABC 1112 2010-05-02
ABC 1116 2010-05-06
DEF 1254 2009-04-01
DEF 1456 2009-08-23
GHI 9819 2010-02-21
GHI 9845 2010-02-23
...
...
I need to query out only those records where the difference in days(datecreated column) for each user is less than 3. For the above data, my output must be the following rows
UserID PackageID DateCreated
ABC 1111 2010-05-01
ABC 1112 2010-05-02
GHI 9819 2010-02-21
GHI 9845 2010-02-23
Do anyone know how to produce this kind of result?
May 11, 2010 at 8:17 pm
Yes... Self Joined CTE with ROW_NUMBER() so you can join the rows with an offset of 1. Since you're brand new, you might want to take a look at the article at the first link in my signature line below. Using those methods to post have people tripping over each other to help you with a coded answer.
And welcome aboard.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2010 at 10:13 am
Hi Jeff,
I tried to solve this by using CTE, but no luck.. . Can I get some more ideas to approach this.
Below is code what I tried.
create table Test (UserID varchar(30),PackageID int,DateCreated datetime)
go
insert into Test
values('ABC',1111,'2010-05-01'),
('ABC',1112,'2010-05-02'),
('ABC',1116,'2010-05-06'),
('DEF',1254,'2009-04-01'),
('DEF',1456,'2009-08-23'),
('GHI',9819,'2010-02-21'),
('GHI',9845,'2010-02-23')
go
With ct
as
(
select UserID,PackageID,DateCreated,Row_number()Over(partition by UserID order by UserID)as RN from test
)
select t.UserID,t.PackageID,t.DateCreated
from ct as c inner join test t on c.UserID = t.UserID
where c.RN in (select MIN(RN) from ct ) and DATEDIFF(DD,c.DateCreated,t.DateCreated)< 3
Thanks & Regards,
MC
May 13, 2010 at 11:12 am
SELECT a.UserID,a.PackageID,a.DateCreated
FROM Test a
WHERE EXISTS (SELECT * FROM Test b
WHERE b.UserID=a.UserID
AND b.PackageID<>a.PackageID
AND ABS(DATEDIFF(day,a.DateCreated,b.DateCreated))<=2)
ORDER BY a.UserID,a.PackageID,a.DateCreated;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537May 13, 2010 at 11:54 am
Hi Jeff, Thanks for the reply. It worked well.
May 13, 2010 at 11:57 am
Sample data:
declare @t_table table
(
UserID VARCHAR(10),
PackageID INT,
DateCreated DATETIME
);
insert into @t_table (UserID, PackageID, DateCreated)
select 'ABC', 1111, '2010-05-01' union
select 'ABC', 1112, '2010-05-02' union
select 'ABC', 1116, '2010-05-06' union
select 'DEF', 1254, '2009-04-01' union
select 'DEF', 1456, '2009-08-23' union
select 'GHI', 9819, '2010-02-21' union
select 'GHI', 9845, '2010-02-23';
This is the CTE method:
with cteTemp (RowID, UserID, PackageID, DateCreated)
as
(
select ROW_NUMBER() OVER (ORDER BY UserID, DateCreated),
UserID,
PackageID,
DateCreated
from @t_table
)
select ct.UserID,
ct.PackageID,
ct.DateCreated
from cteTemp ct
join cteTemp ct_2
on ct_2.UserID = ct.UserID
and abs(ct_2.RowID - ct.RowID) = 1
and ABS(DATEDIFF(dd, ct_2.DateCreated, ct.DateCreated)) < 3
order by UserID, DateCreated
May 13, 2010 at 12:07 pm
Hi MC,
Try this
With ct
as
(
select UserID,PackageID,DateCreated,Row_number()Over(partition by UserID order by UserID)as RN from test
) ,
WITH ct2 AS (
SELECT A.*
FROM
(select c.userID, C.packageID, c.datecreated, c1.packageID AS PackageID1, C1.datecreated AS Datecreated1,
CASE WHEN DATEDIFF(dd,c.datecreated, c1.datecreated) < 3 THEN 'YES' ELSE 'NO' END AS Duplicate
from ct as c inner join ct c1 on c.UserID = c1.UserID AND c.RN = C1.RN-1
) A
WHERE A.duplicate = 'YES'
)
SELECT E.*
FROM (
SELECT userID, PackageID, datecreated FROM ct2
UNION
SELECT UserID, packageID1, datecreated1 FROM ct2 ) E
May 13, 2010 at 2:07 pm
nmalepati (5/13/2010)
Hi Jeff, Thanks for the reply. It worked well.
Actually, that was Mark. Heh... I pass your thanks along.
Nice job, Mark!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2010 at 2:23 pm
Jeff Moden (5/13/2010)
nmalepati (5/13/2010)
Hi Jeff, Thanks for the reply. It worked well.Actually, that was Mark. Heh... I pass your thanks along.
Nice job, Mark!
Cheers Jeff.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537May 13, 2010 at 9:18 pm
Hi all,
Many thanks for the reply...
Thanks & Regards,
MC
May 14, 2010 at 3:34 am
nmalepati (5/11/2010)
Hello All,I have a table that has data in below format:
UserID PackageID DateCreated
ABC 1111 2010-05-01
ABC 1112 2010-05-02
ABC 1116 2010-05-06
DEF 1254 2009-04-01
DEF 1456 2009-08-23
GHI 9819 2010-02-21
GHI 9845 2010-02-23
...
...
I need to query out only those records where the difference in days(datecreated column) for each user is less than 3. For the above data, my output must be the following rows
UserID PackageID DateCreated
ABC 1111 2010-05-01
ABC 1112 2010-05-02
GHI 9819 2010-02-21
GHI 9845 2010-02-23
Do anyone know how to produce this kind of result?
select a.* from table1 a
inner join table1 b
on (a.userid = b.userid )
where datediff(day,a.datecreated,b.datecreated) < 3
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy