June 28, 2013 at 11:58 am
I MAY have success finally!
If I run the same query below against VehiclesToUpdate I get 9. So the update can choose "X" number of VINs not caring if the VINs are unique. They MUST be unique. That is why it's ignoring so many rows to update.
Select COUNT(*)
From VehicleDetail
Where Vin='DN656102'
Correct me if I am wrong, but if I turn the VehiclesToUpdate section into a subquery to keep the VINs unique, this may solve the problem. I think it is worth a shot. I haven't tried this, but I bet if ran a select distinct VIN on VehiclesToUpdate after the Where v.RowNum <= r.UnitCount line, I would not get the correct number of records for whatever group I am focusing on.
I'll keep you posted even though you probably won't read this until Monday.
June 28, 2013 at 12:06 pm
realvilla (6/28/2013)
I MAY have success finally!If I run the same query below against VehiclesToUpdate I get 9. So the update can choose "X" number of VINs not caring if the VINs are unique. They MUST be unique. That is why it's ignoring so many rows to update.
Select COUNT(*)
From VehicleDetail
Where Vin='DN656102'
Correct me if I am wrong, but if I turn the VehiclesToUpdate section into a subquery to keep the VINs unique, this may solve the problem. I think it is worth a shot. I haven't tried this, but I bet if ran a select distinct VIN on VehiclesToUpdate after the Where v.RowNum <= r.UnitCount line, I would not get the correct number of records for whatever group I am focusing on.
I'll keep you posted even though you probably won't read this until Monday.
That is excellent!!! Been kind of nuts around here this week so my response time has been a bit slower than usual. Hope you get it nailed down and let me know. I enjoy hearing that I was able to kick start such a massive change for the better. This has probably been an awesome learning experience for you too. 🙂
_______________________________________________________________
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/
June 28, 2013 at 2:52 pm
This is a tough one but I think I have finally identified the problem. If you look at the ReturnsPros records (even the ones you had for this project) you will notice that the only thing unique in the rows is the ReturnDate. So if you try to inner join on the six columns between the two tables, you will get duplicates because the ReturnDate cannot be joined since it is null in VehicleDetail. For example, one grouping may have seven return dates, so joining will produce seven times the records that you need to update.
I assume that the partitions are unique but that doesn't help when joining. I haven't come up with a solution yet (the subquery idea hasn't worked so far).
I'll think on it this weekend. Thanks for all your help---and you were right. I HAVE learned a lot from this experience.
July 2, 2013 at 11:55 am
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.
July 2, 2013 at 12:06 pm
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/
July 2, 2013 at 12:55 pm
Sean, I'm glad you brought the row number up. It will help me illustrate my point.
RegionProgYearRiskNonRiskFlagMFGLeaseCycleModelName
Southeast 12 R NIS 1 ALTIMA S
The way it is written now, the code will group by the six columns above but it needs to group on seven columns. The returndate is the seventh column. Even though your code partitions on returndate (I think), it will only number the grouping above starting with one. I will not number the grouping based on return date (since it is blank). For example:
Group1 (based on six columns)
1
2
3
Group2
1
2
3
as opposed to:
Group1 (based on seven columns)
returndate1 (sub group1)
1
2
3
returndate2
1
2
3
returndate3 (etc)
I hope it makes sense. If not, I'll send you some data to work with.
July 2, 2013 at 12:59 pm
I don't really understand the details as it pertains to your situation but it sounds like you need to look at the ROW_NUMBER partition and adjust it to fit your requirements.
_______________________________________________________________
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/
July 3, 2013 at 9:47 am
Sean, you must be a brilliant guy to come up with such elegant code. So I figured it must be my explanation. I racked my brain and came up with something that I think will get the point across. The following table should tell all. It is a sample VehicleDetail (actually "vehicles") table with a "ShouldBe" column added. Relate the ShouldBe to the ReturnDate and a light bulb should go off. This is what the row number should be.
That's what I want, but it is the ultimate "chicken before the egg" dilemma. The return dates are already there. They wouldn't be in an actual run. If they were there, it would be easy to partition on ReturnDate.
I only supplied the VD columns that are necessary. I couldn't use "Row_Number" (with underscore) because it was a keyword. The code is tested this time so it should be an easy test.
create table SampleVehDet
(
ProgYearVarChar(2),
LeaseCycleVarChar(2),
ModelNameVarChar(30),
RiskVarChar(2),
RegionVarChar(20),
MFGVarChar(5),
ReturnDateDateTime,
RowNumberInt,
ShouldBeInt
)
Insert Into SampleVehDet (ProgYear, LeaseCycle, ModelName, Risk, Region, MFG, ReturnDate, RowNumber, ShouldBe)
values('13','1','RIO 5DR LX','R','CENTRAL','KIA','4/21/2014',1, 1),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','4/21/2014',2, 2),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/9/2014',3, 1),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',4, 1),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',5, 2),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/16/2014',6, 3),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/23/2014',7, 1),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','6/23/2014',8, 2),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',9, 1),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',10, 2),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',11, 3),
('13','1','RIO 5DR LX','R','CENTRAL','KIA','7/21/2014',12, 4)
July 8, 2013 at 11:34 am
Sean, I haven't heard anything in a while so I assume you are on vacation. I don't know if you have had time to look at this.
July 8, 2013 at 12:26 pm
I see what you are saying about the row number but I am a bit confused about the process as a whole. Why would the rowNumber change based on return date? It seems that something is lost in translation. Probably due to the fact that this was something I looked at a few weeks ago and don't remember the nitty gritty details. It will take me some time to parse through all 50+ posts and wrap my head around this again. It seems that maybe all that is missing is including a RowNumber from the returns side or something like that. I will try to find a couple hours to dig through this again and see if I can see see what the issue is.
_______________________________________________________________
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/
July 9, 2013 at 1:49 pm
Sean, to answer your question, the row numbers must change (reinitialize) based on return dates so it will partition (group) correctly. If you'll look at the table I sent, you'll see that a certain group might have the same Program Year, Model Name, etc. but they will have different return dates. That's one of the reason the code bombed. The other reason was that the table we were updating had duplicates which threw the number of units specified off.
The business side of the equation is a little more difficult for someone not acquainted with the car business. However, let’s say you have a car rental business in three states. You are trying to project when and how many cars will be returned and eventually sold. (You obviously can’t keep cars forever). The cars that have the same delivery dates (coming in) will most likely have the same return dates (going out).
Let’s say you have 50 cars that need to be returned in the one state and another 50 in another state and so on. You don’t know the return date yet but you can project it using the delivery date.
In one of the states, you might want say 25 cars with one return date and 25 with another (since the delivery dates are different between aggregate groups). You can’t partition by model name because you are projecting different return dates. You don’t have the return dates in the system yet because something could have happened (stolen, damaged, etc.). But you project with the best knowledge you have at the time.
From your forecast department, you get a list of vehicles that list the return dates and the number of vehicles per group. Now you must project that on to the inventory list detail that has the ID numbers. If one group has 25 cars, then you must update 25 cars in inventory with the same return date. That’s the gist of it.
The good news is that we can partition on delivery date. That's an excellent idea that one of my co-workers gave me that elimiates the "cart before the horse" problem. The good news is that if you have an idea to eliminates duplicates on an inner join, we have the problem licked.
Sorry, I tried to make it shorter. 🙁
Thanks,
Bob
July 9, 2013 at 1:59 pm
LOL sounds like you are making good headway on this. Good thing...I am swamped at my actual job and don't have any time to dig back through this. 🙂
The good news is that if you have an idea to eliminates duplicates on an inner join, we have the problem licked.
If they are truly duplicates you could just add distinct to your query, or group by the columns.
_______________________________________________________________
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/
July 9, 2013 at 2:37 pm
Hmm. I thought you couldn't update a table using an aggregate query (ie. distinct keyword or group by). You have already gone way above and beyond the call of duty. I just appreciate the time you have given like I have said before.
Just for fun, here is an example of the way we used to do it in Access. It just shows the process for one group.
UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess'
WHERE ((([Vehicle Detail].SerialNumber) In (SELECT TOP 15 [SerialNumber] FROM [Vehicle Detail] WHERE ((([Vehicle Detail].ProgYear)='12') AND (([Vehicle Detail].LeaseCycle)='1') And (([Vehicle Detail].ModelName)='Focus') AND (([Vehicle Detail].RiskNonRiskFlag)='R' AND (([Vehicle Detail].region)='SouthEast') And ReturnDate Is Null))
ORDER BY [Vehicle Detail].DeliveryDate,[Vehicle Detail].SerialNumber)));
July 17, 2013 at 9:12 am
Sean, I told you I wasn't giving up. Here is my latest attempt.
with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName,
RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from dbo.VehicleDetail
where ReturnDate Is Null
)
MERGE Vehicles AS v
USING ReturnsPros AS r
ON (r.ProgYear = v.ProgYear
and r.LeaseCycle = v.LeaseCycle
and r.ModelName = v.ModelName
and r.RiskNonRiskFlag = v.RiskNonRiskFlag
and r.Region = v.Region
and r.MFG = v.MFG and v.RowNum <= r.UnitCount)
WHEN MATCHED
THEN UPDATE SET v.ReturnDate = r.ReturnDate;
--did it actually work?
select * from VehicleDetail where ReturnDate is not null
You will notice your code before the merge statement. I was hoping I could get around the duplicate problem using merge but here is the message I received.
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply