Click here to monitor SSC
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 (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 49
All I can say is "wow"! You did this without variables, dynamic SQL or a top clause. That is amazing to me. I messed up on the RiskNonRisk column which you probably discovered wouldn't match between tables. The delivery dates were no big deal for such a small set. You could have placed almost anything in there that was not ahead of the returns dates in the VD table. As the Russians say, "Thank you a thousand times!"

I went back and changed what I sent and will post it, just to be sure everything is what is should be. But the important thing is the benchmark. I will test your code and let you know how much time it saved. That should be the fun part. This will be on approximately 250,000 records. Anyway, the following is what I should have sent:

--===== Create the test table with
CREATE TABLE [Vehicle Detail]
(
VIN Varchar PRIMARY KEY CLUSTERED,
SerialNumber Varchar 50,
ProgYear Varchar 2,
LeaseCycle Varchar 2,
ModelName Varchar 50,
RiskNonRiskFlag Varchar 2,
Region Varchar 50,
MFG Varchar 50,
ReturnDate Datetime,
)
CREATE TABLE [Returns Pros]
(
Region Varchar 50,
ProgYear Varchar 2,
RiskNonRiskFlag Varchar 2,
MFG Varchar 50,
LeaseCycle Varchar 2,
ModelName Varchar 50,
ReturnDate Datetime,
UnitCount Int
)


--===== Insert the test data into the test table
INSERT INTO [Vehicle Detail]
(VIN, SerialNumber, ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG, DeliveryDate, ReturnDate)
Select 'ZL152983', '1FADP3K22ZL152983', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL185223', '1FADP3K20ZL185223', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196536', '1FADP3F27ZL196536', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL265273', '1FADP3F27ZL265273', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196580', '1FADP3F2XZL196580', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL196590', '1FADP3F22ZL196590', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171198', '1FADP3F29ZL171198', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171258', '1FADP3F21ZL171258', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL171268', '1FADP3K27ZL171268', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211529', '1FADP3F2XZL211529', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211532', '1FADP3F2XZL211532', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'ZL211617', '1FADP3F27ZL211617', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', '07/04/2012', NULL UNION ALL
Select 'C5216918', '1ZVBP8DM2C5216918', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', '12/03/2011', NULL UNION ALL
Select 'C5216923', '1ZVBP8DM2C5216923', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', '12/03/2011', NULL UNION ALL
Select 'D1220647', '2G1WC6E30D1220647', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION ALL
Select 'D1221984', '2G1WC6E31D1221984', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION ALL
Select 'D1259402', '2G1WC6E30D1259402', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', '12/03/2011', NULL UNION

INSERT INTO [Returns Pros]
(Region, ProgYear, RiskNonRiskFlag, MFG, LeaseCycle, ModelName, ReturnDate, UnitCount)
Select 'Atlantic', '11', 'R', 'FRD', '2', 'MUSTANG CONV', '6/3/2013', 2, UNION ALL
Select 'Central' '13', 'N', 'GMC', '1', 'IMPALA', '6/3/2013', 3, UNION ALL
Select 'Southeast', '13', 'R', 'FRD', '1', 'FOCUS', '1/4/2014', 12
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
realvilla (6/18/2013)
All I can say is "wow"! You did this without variables, dynamic SQL or a top clause. That is amazing to me.


Hopefully you can understand what I did. It is a mental shift to think about changing the columns instead of the rows. Once you make that mental shift you are thinking about your data as sets instead of values.


I messed up on the RiskNonRisk column which you probably discovered wouldn't match between tables. The delivery dates were no big deal for such a small set. You could have placed almost anything in there that was not ahead of the returns dates in the VD table. As the Russians say, "Thank you a thousand times!"


Yeah the RiskNonRisk was kind of driving me nuts for a bit until I realized that it was excluded because of the value difference. And you are welcome.


I went back and changed what I sent and will post it, just to be sure everything is what is should be. But the important thing is the benchmark. I will test your code and let you know how much time it saved.


I look forward to hearing the results of your testing.

_______________________________________________________________

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

Group: General Forum Members
Points: 35 Visits: 49
I'm on version 2000 which is probably why I generated these errors when I tried to run your code. The good news is that SQL admin people are trying to get me access to another database that is 2008. Actually, I am running Management Studio 2008.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 4
'ROW_NUMBER' is not a recognized function name.

The data set I sent still does not have "N" for non-risk in the RiskNonRiskFlag column but I think you have already corrected that. I think we may have to sort on serial number as well as DeliveryDate but we are so close.
realvilla
realvilla
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 49
I meant to say non-risk on the IMPALAs. Sorry.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
realvilla (6/18/2013)
I'm on version 2000 which is probably why I generated these errors when I tried to run your code. The good news is that SQL admin people are trying to get me access to another database that is 2008. Actually, I am running Management Studio 2008.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 4
'ROW_NUMBER' is not a recognized function name.

The data set I sent still does not have "N" for non-risk in the RiskNonRiskFlag column but I think you have already corrected that. I think we may have to sort on serial number as well as DeliveryDate but we are so close.


Yeah if you are running against 2000 then you can't do this at all. I assumed you would not still be on 2000. w00t

You can add as many additional columns to the order by as you need to get your final results.

_______________________________________________________________

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

Group: General Forum Members
Points: 35 Visits: 49
I have some good news and some more good news and then some bad news. First, we found a 2008 database that we can use. Second, the query ran UNBELIEVABLY fast, a few seconds. The bad news is that it only updated about 30,000 records and it should have been over 200,000 records. If you sum the UnitCount column in Returns Pros, you will have the number of rows that should be updated in the VD table. It shouldn't make any difference whether it is a small or large set should it?

My first thought was the joins, but then I thought about the fact that it should only update rows that are blank. I don't think that should be a big deal, but there are some return dates in the VD that we don't want to overwrite.

So we're probably just looking at some minor tweaks. I'm thinking about taking your select statements to see how many rows are affected unless you have a better idea.

That recursive table idea is very elegant. They never taught me that in SQL class - probably because it wasn't available then.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
realvilla (6/18/2013)
I have some good news and some more good news and then some bad news. First, we found a 2008 database that we can use. Second, the query ran UNBELIEVABLY fast, a few seconds. The bad news is that it only updated about 30,000 records and it should have been over 200,000 records. If you sum the UnitCount column in Returns Pros, you will have the number of rows that should be updated in the VD table. It shouldn't make any difference whether it is a small or large set should it?

My first thought was the joins, but then I thought about the fact that it should only update rows that are blank. I don't think that should be a big deal, but there are some return dates in the VD that we don't want to overwrite.

So we're probably just looking at some minor tweaks. I'm thinking about taking your select statements to see how many rows are affected unless you have a better idea.

That recursive table idea is very elegant. They never taught me that in SQL class - probably because it wasn't available then.


Should be easy enough to add an extra condition in the where clause to exclude where the return date is not null. The easiest way to test it out is to comment out the update statement and add in a select * from VehiclesToUpdate. That will show you all the rows that would be updated. I am sure you will have to do some tweaking and fine tuning to get this to work with your real data.

Not sure what you mean about recursion, there isn't any in there at all.

_______________________________________________________________

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

Group: General Forum Members
Points: 35 Visits: 49
I will test it as you specified and get back with you. I will also try adding the "ReturnDate is null" and the SerialNumber to the Order By.

As for the recursion:

http://msdn.microsoft.com/en-us/library/ms175972.aspx

(Notice that it does say 2012 version)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
realvilla (6/18/2013)

As for the recursion:

http://msdn.microsoft.com/en-us/library/ms175972.aspx

(Notice that it does say 2012 version)


Here is the quote from the first paragraph there.


A common table expression can include references to itself. This is referred to as a recursive common table expression.


What I posted is NOT a recursive cte. It does not ever refer to itself (or a self join). A rCTE is similar but a very different animal entirely. :-)

_______________________________________________________________

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

Group: General Forum Members
Points: 35 Visits: 49
Oh and by the way, this line has to be what replaced the top clause correct?

where v.RowNum <= r.UnitCount
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