realvilla (7/2/2013)
Sean,I'm realizing something. Let's say that you have three records in the ReturnsPros table that have the same data in six columns:
ProgYear = '13'
RiskNonRiskFlag = 'N'
Region = 'Central'
ModelName = 'Mustang Conv'
MFG = 'FRD'
Cycle ='1'
However, the ReturnDate column will be unique in those three records. What I am getting at is that the first "set" of records represents a grouping in the VehicleDetail and the ReturnDates represent a "SUBSET" within the VehicleDetail.
Isn't a partition a bit like a "group by"? If that is so, what we need is a subpartition. Here's the rub. The ReturnDate column is null in most records before the process begins, so you can't group on nothing with your partition. If we could, we would have the problem solved.
You don't have to do anything. I just wanted to give you an update but if you have any ideas, I would appreciate it.
OK let's say you have two rows with the above criteria. The value for Row_Number() will increment for each of those rows based on the order by. When we add a partition to the ROW_NUMBER windowed function it will reset the number for each new partition. In other words, when any of the criteria changes the value of ROW_NUMBER will restart at 1. I am not quite sure I get what you mean about ReturnDate being NULL. My understanding was that was the only rows you wanted to be able to update.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/