Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Increment Column Value based on condition

Author  Topic 

bmsra79
Starting Member

24 Posts

Posted - 2013-01-29 : 11:28:28
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

5072 Posts

Posted - 2013-01-29 : 11:38:02
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 - 2013-01-29 : 12:12:57
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
   

- Advertisement -