Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Access Query ported to SQL Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 12:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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.
Post #1469718
Posted Tuesday, July 2, 2013 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's 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)
Post #1469721
Posted Wednesday, July 3, 2013 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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)


Post #1470139
Posted Monday, July 8, 2013 11:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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.
Post #1471299
Posted Monday, July 8, 2013 12:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's 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)
Post #1471316
Posted Tuesday, July 9, 2013 1:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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.

Thanks,
Bob
Post #1471835
Posted Tuesday, July 9, 2013 1:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
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 Moden's 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)
Post #1471839
Posted Tuesday, July 9, 2013 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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)));
Post #1471856
Posted Wednesday, July 17, 2013 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 9:08 AM
Points: 35, 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.
Post #1474673
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse