|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, January 30, 2012 11:29 PM
Points: 27,
Visits: 73
|
|
Hi,
Please let me know how to use ROW_NUMBER() function in sql server 200,
i know how to use in sql 2005..
It gives error in SQL server 2000
Looking for the quick and affirmative response.
Thanks and Regards, Pravin Kadam
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553,
Visits: 2,232
|
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, January 30, 2012 11:29 PM
Points: 27,
Visits: 73
|
|
Hi ,
I have onc date column in that I have transition date for each customer.
I have to find out the difference between the dates per customer.
eg :-
CUST_ID date XXX 12-05-2008 XXX 12-08-2008 XXX 12-25-2008 XXX 12-28-2008
If the difference between the dates (Pervious and next ) is greater than 4 then I have take the sum from the pervious date to last date.
Please suggest me the answer Thanks and Regards,
Pravin V. Kadam
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553,
Visits: 2,232
|
|
try something like this.
DECLARE @YourTable TABLE (Cust_ID INT,Date DATETIME)
INSERT INTO @YourTable SELECT 1,'2008-12-05' UNION ALL SELECT 1,'2008-12-08' UNION ALL SELECT 1,'2008-12-25' UNION ALL SELECT 1,'2008-12-28'
DECLARE @MyTable TABLE (id INT IDENTITY(1,1),Cust_ID INT,Date DATETIME)
INSERT INTO @MyTable (Cust_ID,Date) SELECT * FROM @YourTable
SELECT DISTINCT a.* FROM @MyTable a INNER JOIN @MyTable b ON b.id <= a.id WHERE DATEDIFF(dd,b.Date,a.Date) > 4
---------------------------------------------- Try to learn something about everything and everything about something. - Thomas Henry Huxley
 Posting Best Practices Numbers / Tally Tables
SQL-4-Life
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, January 30, 2012 11:29 PM
Points: 27,
Visits: 73
|
|
Thanks Christopher,
Let me run the code, i will get back to you.
Thanks for quick response
Pravin V. Kadam
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Thursday, May 17, 2012 6:16 AM
Points: 89,
Visits: 236
|
|
Christopher,
Its very nice query but here is my issue which is little bit different than above,
I am having same issue on SQL 03 where I can not use Row_Number()
What I have in my table is below: ID Fname Lname Amount 1 Smith Johnson $12.32 1 Smith Johnson $23.32 2 Melinda Ben $23.09 2 Melinda Ben $45.32 2 Melinda Ben $566.00
And here is what I am trying to accomplish: ID ID_Line_No Fname Lname Amount 1 1 Smith Johnson $12.32 1 2 Smith Johnson $23.32 2 1 Melinda Ben $23.09 2 2 Melinda Ben $45.32 2 3 Melinda Ben $566.00
I would like to get "ID_Line_No" column where number increse when ID number change. so, as you see above that when ID = 1 and I have two records for Mr.Smith so, my code should identify each row starting with 1,2,... It is easy in SQL 2005 but I have one client that use SQL 2003. Please advice,
|
|
|
|