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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
realvilla (6/18/2013)


Oh and by the way, this line has to be what replaced the top clause correct?

where v.RowNum <= r.UnitCount


Yes that is correct. Instead of getting the top we just get the ones we want by leveraging RowNum.

_______________________________________________________________

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

Group: General Forum Members
Points: 37 Visits: 49
OK, I am set up now with rights on the database which is 2008. I run the following code and get 0 records. Notice the "select COUNT(*) from VehiclesToUpdate" line. I noticed that the table (I went ahead and took out the spaces in the file name thinking it would correct what I am about to tell you) has a RED UNDERLINE under the table name whether or not I place a "dbo" in front of it. The fields names have red underlines as well. This is probably something very simple, but I am not as familiar with 2008. I placed underlines under the new stuff I added. Anyway, here is the code:


with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from VehicleDetail

)
, VehiclesToUpdate as
(
--Now we only want to see the rows we want to update
select v.VIN,
v.SerialNumber,
r.ReturnDate,
v.ProgYear,
v.LeaseCycle,
v.ModelName,
v.RiskNonRiskFlag,
v.Region,
v.MFG,
v.ReturnDate as NewReturnDate --needs to have a unique name so we can update it.
from dbo.ReturnsPros r
join Vehicles v 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
where v.RowNum <= r.UnitCount and v.ReturnDate is null)

select COUNT(*) from VehiclesToUpdate
--Update VehiclesToUpdate
--set NewReturnDate = ReturnDate
--since NewReturnDate is actually ReturnDate from VehicleDetail
--we know which column we are updating
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
realvilla (6/19/2013)
OK, I am set up now with rights on the database which is 2008. I run the following code and get 0 records. Notice the "select COUNT(*) from VehiclesToUpdate" line. I noticed that the table (I went ahead and took out the spaces in the file name thinking it would correct what I am about to tell you) has a RED UNDERLINE under the table name whether or not I place a "dbo" in front of it. The fields names have red underlines as well. This is probably something very simple, but I am not as familiar with 2008. I placed underlines under the new stuff I added. Anyway, here is the code:



The red underlines are most likely just because intellisense is not up to date. When you rename, create or drop objects intellisense is not updated. Just hit ctrl + shift + r and it will update.

As far as no rows being returned, it is impossible for me to determine because I don't have the same data you do. Change your join to a left join. If that now returns rows but the other side is NULL then there is something in your join not right. About the best I can do remotely is get you close. You will have to do the last little bit of tweaking and debugging from where you have access.

_______________________________________________________________

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

Group: General Forum Members
Points: 37 Visits: 49
I so appreciate your help and you have already gone way above and beyond the call of duty. I'll try the left joins and guy more experienced in SQL should be here after lunch. I have seen the "promised land" so I am not giving up.

To go from 3 hours to a few seconds was so amazing that the co-worker who will be using this still may not be totally convinced. It will be difficult to test each piece of your code but I still think we're close.

At this point I will just notify you of the progress since your work is done. Like you said, you got me close and the rest is up to us. Thanks again.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
realvilla (6/19/2013)
I so appreciate your help and you have already gone way above and beyond the call of duty. I'll try the left joins and guy more experienced in SQL should be here after lunch. I have seen the "promised land" so I am not giving up.

To go from 3 hours to a few seconds was so amazing that the co-worker who will be using this still may not be totally convinced. It will be difficult to test each piece of your code but I still think we're close.

At this point I will just notify you of the progress since your work is done. Like you said, you got me close and the rest is up to us. Thanks again.


You are quite welcome. It is amazing what a difference it makes when you don't loop and just deal your data as a set. I hope you figure it out and please post back and let me know what happens. It is nice to hear when things like this work out. :-D

_______________________________________________________________

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

Group: General Forum Members
Points: 37 Visits: 49
I am SO close. I have a question for you. Before I ask it, I will show you the SQL Code and it is now.

with Vehicles as
(
select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate, SerialNumber) as RowNum
from dbo.VehicleDetail
)
, VehiclesToUpdate as
(
--Now we only want to see the rows we want to update
select v.VIN,
v.SerialNumber,
r.ReturnDate,
v.ProgYear,
v.LeaseCycle,
v.ModelName,
v.RiskNonRiskFlag,
v.Region,
v.MFG,
v.ReturnDate as NewReturnDate --needs to have a unique name so we can update it.
from dbo.ReturnsPros r
join Vehicles v 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
where v.RowNum <= r.UnitCount And v.ReturnDate Is Null
)

Update VehiclesToUpdate
Set NewReturnDate = ReturnDate
--since NewReturnDate is actually ReturnDate from VehicleDetail
--we know which column we are updating

--did it actually work?
select * from VehicleDetail where ReturnDate is not null

The question is, where is the best place to put the "ReturnDate is null" statement? Currently, this query generates 73,061 records when it should generate 205,165. Would it be better to put the ReturnDate where clause in the first select?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
That would probably help. It is pretty tough for me to say for certainty though.

_______________________________________________________________

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

Group: General Forum Members
Points: 37 Visits: 49
I'm trying not to bother you any more but you did ask me to give you a progress update. I am stumped. I have working on this the better part of the day and I haven't made much progress. I placed the following code at the end of your code:

SELECT VehicleDetail.SerialNumber, VehicleDetail.ProgYear, VehicleDetail.ModelName, VehicleDetail.Region, VehicleDetail.MFG
FROM VehicleDetail LEFT JOIN VehiclesToUpdate ON (VehicleDetail.SerialNumber = VehiclesToUpdate.SerialNumber) AND (VehicleDetail.Vin = VehiclesToUpdate.Vin)
Where VehicleDetail.SerialNumber is null

This returned no records which means that all of the cars are accounted for between VehiclesToUpdate and VehicleDetail. However, the number of records to update is still far short. I told you that I'm not giving up but I'm not sure what to try next.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
realvilla (6/20/2013)
I'm trying not to bother you any more but you did ask me to give you a progress update. I am stumped. I have working on this the better part of the day and I haven't made much progress. I placed the following code at the end of your code:

SELECT VehicleDetail.SerialNumber, VehicleDetail.ProgYear, VehicleDetail.ModelName, VehicleDetail.Region, VehicleDetail.MFG
FROM VehicleDetail LEFT JOIN VehiclesToUpdate ON (VehicleDetail.SerialNumber = VehiclesToUpdate.SerialNumber) AND (VehicleDetail.Vin = VehiclesToUpdate.Vin)
Where VehicleDetail.SerialNumber is null

This returned no records which means that all of the cars are accounted for between VehiclesToUpdate and VehicleDetail. However, the number of records to update is still far short. I told you that I'm not giving up but I'm not sure what to try next.


No problem.

The above query returning now rows means there are no rows in VehicleDetail that don't have a serial number.

Does this also return 0?


SELECT count(*)
FROM VehicleDetail
Where VehicleDetail.SerialNumber is null



How many vehicles do you think should be updated? How many rows are in VehiclesToUpdate?

_______________________________________________________________

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)
grovelli-262555
grovelli-262555
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 885
I'm well versed in Access but know very little T-SQL. What does "ROW_NUMBER() over(partition by" do?
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