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 «««12345»»»

Access Query ported to SQL Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 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 #1464872
Posted Wednesday, June 19, 2013 9:18 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
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
Post #1465246
Posted Wednesday, June 19, 2013 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 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 #1465247
Posted Wednesday, June 19, 2013 9:43 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
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.
Post #1465258
Posted Wednesday, June 19, 2013 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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.


_______________________________________________________________

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 #1465265
Posted Thursday, June 20, 2013 9:16 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
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?
Post #1465764
Posted Thursday, June 20, 2013 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 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 #1465771
Posted Thursday, June 20, 2013 2:58 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
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.
Post #1465952
Posted Thursday, June 20, 2013 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 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 #1465956
Posted Thursday, June 20, 2013 10:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:42 AM
Points: 103, Visits: 811
I'm well versed in Access but know very little T-SQL. What does "ROW_NUMBER() over(partition by" do?
Post #1466037
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse