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
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60216 Visits: 17952
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 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 (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60216 Visits: 17952
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 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 (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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?
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 49
Correction:

WHERE NewReturnDate is null
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60216 Visits: 17952
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 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 (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
realvilla
realvilla
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60216 Visits: 17952
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 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)
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