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 Wednesday, June 26, 2013 1:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
I can't seem to find in this thread now but iirc you said that the returns was data from an outside source? How do you validate that the returns they are sending are realistic? Seems to me that the returns data is more than went out the door.

_______________________________________________________________

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 #1467839
Posted Thursday, June 27, 2013 2:26 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, here is the "unsolved mystery". When I run your program pretty much as written, I get about 70,000 rows that don't update (null Return dates). Included in those rows is the following grouping.

Yet when I tag this code right after where v.RowNum <= r.UnitCount, I get 623 records for VehiclesToUdate, ReturnsPros and VehicleDetail the way I should.
How can this be?

Select count(*) as UpdateCount, 'Update' as SQLName from VehiclesToUpdate Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO
Select Sum(UnitCount) as ReturnsCount, 'RCount' as SQLName from ReturnsPros Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO
Select count(*) as VDCount, 'Detail' as SQLName from VehicleDetail Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO
Post #1468346
Posted Thursday, June 27, 2013 2:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
realvilla (6/27/2013)
Sean, here is the "unsolved mystery". When I run your program pretty much as written, I get about 70,000 rows that don't update (null Return dates). Included in those rows is the following grouping.

Yet when I tag this code right after where v.RowNum <= r.UnitCount, I get 623 records for VehiclesToUdate, ReturnsPros and VehicleDetail the way I should.
How can this be?

Select count(*) as UpdateCount, 'Update' as SQLName from VehiclesToUpdate Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO
Select Sum(UnitCount) as ReturnsCount, 'RCount' as SQLName from ReturnsPros Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO
Select count(*) as VDCount, 'Detail' as SQLName from VehicleDetail Where Region='Central' And ProgYear='13' And LeaseCycle='1'And ModelName='FUSION I4 SE' And MFG='FRD' And RiskNonRiskFlag='R'
GO


Wow this is going to be tough to figure out from here without a sample dataset where we can reproduce this. Can you make a smaller dataset where this issue occurs? It has been several days since I looked this in detail and don't really remember all the details. It will take me a bit to get back up to speed and I am swamped at work right now. I will try to get back here early next week and see what is happening. Sorry I can't help much more right now.


_______________________________________________________________

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 #1468347
Posted Friday, June 28, 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, I know you're busy so any help you give me is appreciated. It doesn't go with the territory that you have to support something you gave me as a sample but maybe you're intrigued enough to at least want to discover what the problem is.

I have tried so many things. The latest attempt was to try the code on a smaller sample of data. So I named a table with only FUSION I4 SE cars as "VehicleDetail" and renamed the original table as VehicleDetailTemp.

I ran your original code only changing the following (new line boldface):

Update VehiclesToUpdate
Set NewReturnDate = ReturnDate
Where ReturnDate Is Null

There were 3728 records with a null return date. Only 683 of them actually updated. The funny thing is that VehiclesToUpdate is 3728 before the update occurs. So why would the update query be selective? Is it because of duplicate records in VehiclesToUpdate?

I think your idea about sending you a data set is great. However, isn't there a limit as to how many records can be sent? What size dataset would you need?
Post #1468598
Posted Friday, June 28, 2013 9:49 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
Correction:

WHERE NewReturnDate is null
Post #1468600
Posted Friday, June 28, 2013 11:58 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 MAY have success finally!

If I run the same query below against VehiclesToUpdate I get 9. So the update can choose "X" number of VINs not caring if the VINs are unique. They MUST be unique. That is why it's ignoring so many rows to update.

Select COUNT(*)
From VehicleDetail
Where Vin='DN656102'

Correct me if I am wrong, but if I turn the VehiclesToUpdate section into a subquery to keep the VINs unique, this may solve the problem. I think it is worth a shot. I haven't tried this, but I bet if ran a select distinct VIN on VehiclesToUpdate after the Where v.RowNum <= r.UnitCount line, I would not get the correct number of records for whatever group I am focusing on.

I'll keep you posted even though you probably won't read this until Monday.
Post #1468663
Posted Friday, June 28, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
realvilla (6/28/2013)
I MAY have success finally!

If I run the same query below against VehiclesToUpdate I get 9. So the update can choose "X" number of VINs not caring if the VINs are unique. They MUST be unique. That is why it's ignoring so many rows to update.

Select COUNT(*)
From VehicleDetail
Where Vin='DN656102'

Correct me if I am wrong, but if I turn the VehiclesToUpdate section into a subquery to keep the VINs unique, this may solve the problem. I think it is worth a shot. I haven't tried this, but I bet if ran a select distinct VIN on VehiclesToUpdate after the Where v.RowNum <= r.UnitCount line, I would not get the correct number of records for whatever group I am focusing on.

I'll keep you posted even though you probably won't read this until Monday.


That is excellent!!! Been kind of nuts around here this week so my response time has been a bit slower than usual. Hope you get it nailed down and let me know. I enjoy hearing that I was able to kick start such a massive change for the better. This has probably been an awesome learning experience for you too.


_______________________________________________________________

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 #1468665
Posted Friday, June 28, 2013 2:52 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
This is a tough one but I think I have finally identified the problem. If you look at the ReturnsPros records (even the ones you had for this project) you will notice that the only thing unique in the rows is the ReturnDate. So if you try to inner join on the six columns between the two tables, you will get duplicates because the ReturnDate cannot be joined since it is null in VehicleDetail. For example, one grouping may have seven return dates, so joining will produce seven times the records that you need to update.

I assume that the partitions are unique but that doesn't help when joining. I haven't come up with a solution yet (the subquery idea hasn't worked so far).

I'll think on it this weekend. Thanks for all your help---and you were right. I HAVE learned a lot from this experience.
Post #1468709
Posted Tuesday, July 2, 2013 11:55 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'm realizing something. Let's say that you have three records in the ReturnsPros table that have the same data in six columns:

ProgYear = '13'
RiskNonRiskFlag = 'N'
Region = 'Central'
ModelName = 'Mustang Conv'
MFG = 'FRD'
Cycle ='1'

However, the ReturnDate column will be unique in those three records. What I am getting at is that the first "set" of records represents a grouping in the VehicleDetail and the ReturnDates represent a "SUBSET" within the VehicleDetail.

Isn't a partition a bit like a "group by"? If that is so, what we need is a subpartition. Here's the rub. The ReturnDate column is null in most records before the process begins, so you can't group on nothing with your partition. If we could, we would have the problem solved.

You don't have to do anything. I just wanted to give you an update but if you have any ideas, I would appreciate it.
Post #1469694
Posted Tuesday, July 2, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
realvilla (7/2/2013)
Sean,

I'm realizing something. Let's say that you have three records in the ReturnsPros table that have the same data in six columns:

ProgYear = '13'
RiskNonRiskFlag = 'N'
Region = 'Central'
ModelName = 'Mustang Conv'
MFG = 'FRD'
Cycle ='1'

However, the ReturnDate column will be unique in those three records. What I am getting at is that the first "set" of records represents a grouping in the VehicleDetail and the ReturnDates represent a "SUBSET" within the VehicleDetail.

Isn't a partition a bit like a "group by"? If that is so, what we need is a subpartition. Here's the rub. The ReturnDate column is null in most records before the process begins, so you can't group on nothing with your partition. If we could, we would have the problem solved.

You don't have to do anything. I just wanted to give you an update but if you have any ideas, I would appreciate it.


OK let's say you have two rows with the above criteria. The value for Row_Number() will increment for each of those rows based on the order by. When we add a partition to the ROW_NUMBER windowed function it will reset the number for each new partition. In other words, when any of the criteria changes the value of ROW_NUMBER will restart at 1. I am not quite sure I get what you mean about ReturnDate being NULL. My understanding was that was the only rows you wanted to be able to update.


_______________________________________________________________

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 #1469695
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse