SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Increment Column Value based on condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmsra79
Starting Member

24 Posts

Posted - 01/29/2013 :  11:28:28  Show Profile  Reply with Quote
I have a View like below:
FirstName,LastName,Createdate,CustomerID,Date1,Date2,SaleID
Kurt,Russel,15-Mar-06,201,,,
John,Mason,1-Jan-05,101,15-Jan-89,,5001
John,Mason,1-Jan-05,101,15-Jan-90,25-Feb-90,2001


Expected Output:
FirstName,LastName,Createdate,CustomerID,Date1,Date2,SaleID,Flag1,Flag2,
Kurt,Russel,15-Mar-06,201,,,,0,0,
John,Mason,1-Jan-05,101,15-Jan-89,,5001,1,0,
John,Mason,1-Jan-05,101,15-Jan-90,25-Feb-90,2001,2,1,


I want to create 2 Flags that increment the count based on recurrence of Date1, Date2 for same Customer.

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 01/29/2013 :  11:38:02  Show Profile  Visit russell's Homepage  Reply with Quote
something like this:

SELECT	firstname, lastname, date1, date2,
	row_number() over(partition by firstname, lastname, date1, date2
			order by firstname, lastname, date1, date2
		) flag1
FROM	yourTable;
Go to Top of Page

bmsra79
Starting Member

24 Posts

Posted - 01/29/2013 :  12:12:57  Show Profile  Reply with Quote
Works partially

Flag should be 0 for all customers where the SaleID is blank. Increment should start by 1 for each record having a SaleID and Date1 (or) SaleID and Date2
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.47 seconds. Powered By: Snitz Forums 2000