SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access Query ported to SQL


Access Query ported to SQL

Author
Message
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
Sean, I'm glad you brought the row number up. It will help me illustrate my point.

Region ProgYear RiskNonRiskFlag MFG LeaseCycle ModelName
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60843 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
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
(

ProgYear VarChar (2),
LeaseCycle VarChar (2),
ModelName VarChar (30),
Risk VarChar (2),
Region VarChar (20),
MFG VarChar (5),
ReturnDate DateTime,
RowNumber Int,
ShouldBe Int
)

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)
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60843 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
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. Sad

Thanks,
Bob
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60843 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
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)));
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search