January 20, 2012 at 10:59 am
Populate this Contract Number field in the database...
Fields are SSN, Contract_Number, Effective_Date, Term_Date...
Here is the breakdown:
Think of this process as a Contract...For each contract, we have a SSN, the date it became effective, and the date it termed...After their first contract terms, they get another contract with a new effective date and a new term date, but still the same SSN number...So we have this table that holds each contract and have added this field contract number that tells which contract was the first, second, and so on...Example:
As Table sits now:
SSN | Contract_Number | Effective_Date | Term_Date
111-11-1111 | NULL | 1-1-2012 | 3-31-2012
111-11-1111 | NULL | 4-1-2012 | 6-30-2012
111-11-1111 | NULL | 7-1-2012 | 10-31-2012
How I need it to look:
SSN | Contract_Number | Effective_Date | Term_Date
111-11-1111 | 1 | 1-1-2012 | 3-31-2012
111-11-1111 | 2 | 4-1-2012 | 6-30-2012
111-11-1111 | 3 | 7-1-2012 | 10-31-2012
Well I cant figure out how to get the first contract (1) in the Contract_Number field...
I know I want to get the MIN(Effective_Date) for each SSN and set that to 1 but when I run my query SELECT MIN(Effective_Date) FROM TableA WHERE SSN = SSN HAVING MIN(Effective_Date) = Effective_Date, I get the error of Effective_Date is invalid in the HAVING clause b/c its not contained in either an aggregate function or the GROUP BY clasue...So I add GROUP BY to my query and GROUP BY SSN, Effective_Date...Well b/c the Effective_Dates are different, it puts a 1 for each record of the SSN and I cant just put SSN in the GROUP BY b/c I get the same error!
Can someone help me out?
January 20, 2012 at 11:03 am
SELECT SSN, Effective_Date, Row_Number() OVER (Partition by SSN ORDER BY Effective_Date) AS ContractNumber
FROM <some table>
You can use that as the basis of an update
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2012 at 11:13 am
Thank you...That is exactly how I need it to look...
However Im not very familiar with the ROW NUMBER (), OVER PARTITION BY statement and how would I use that in an UPDATE statement
UPDATE TableA
SET Contract_Number = (SELECT ROW_NUMBER() OVER(PARTITION BY SSN ORDER BY Effective_Date) FROM TableA) ????
January 20, 2012 at 11:15 am
I just tried the above UPDATE statement...
But got the error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, so on or when the subquery is used as an expression???
January 20, 2012 at 11:20 am
UPDATE <someTable>
SET <whatever>
FROM <SomeTable> INNER JOIN (<subquery containing the row number>) ON <join condition>
If you want real code, post your table definitions in a usable state, I'd rather not try and type up and guess data types.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2012 at 11:42 am
Again I thank you for your response!
This all was getting put into a temp table so when I modified the INSERT INTO my temp table to add the ROW_NUMBER() OVER PARTITION BY statment and it worked perfectly!
Much appreciation (I have been fighting this the past day and half)
February 5, 2012 at 4:24 pm
asm1212 (1/20/2012)
Populate this Contract Number field in the database...Fields are SSN, Contract_Number, Effective_Date, Term_Date...
Here is the breakdown:
Think of this process as a Contract...For each contract, we have a SSN, the date it became effective, and the date it termed...After their first contract terms, they get another contract with a new effective date and a new term date, but still the same SSN number...So we have this table that holds each contract and have added this field contract number that tells which contract was the first, second, and so on...Example:
As Table sits now:
SSN | Contract_Number | Effective_Date | Term_Date
111-11-1111 | NULL | 1-1-2012 | 3-31-2012
111-11-1111 | NULL | 4-1-2012 | 6-30-2012
111-11-1111 | NULL | 7-1-2012 | 10-31-2012
How I need it to look:
SSN | Contract_Number | Effective_Date | Term_Date
111-11-1111 | 1 | 1-1-2012 | 3-31-2012
111-11-1111 | 2 | 4-1-2012 | 6-30-2012
111-11-1111 | 3 | 7-1-2012 | 10-31-2012
Well I cant figure out how to get the first contract (1) in the Contract_Number field...
I know I want to get the MIN(Effective_Date) for each SSN and set that to 1 but when I run my query SELECT MIN(Effective_Date) FROM TableA WHERE SSN = SSN HAVING MIN(Effective_Date) = Effective_Date, I get the error of Effective_Date is invalid in the HAVING clause b/c its not contained in either an aggregate function or the GROUP BY clasue...So I add GROUP BY to my query and GROUP BY SSN, Effective_Date...Well b/c the Effective_Dates are different, it puts a 1 for each record of the SSN and I cant just put SSN in the GROUP BY b/c I get the same error!
Can someone help me out?
As a sidebar, please tell me the name of your company so I can avoid it for contract work. I wouldn't take kindly to having my SSN being used in such an unencrypted manner. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply