Access Query ported to SQL

  • Here is the "inner" part of a query in Access.

    UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess'

    WHERE ((([Vehicle Detail].SerialNumber) In (SELECT TOP 15 [SerialNumber] FROM [Vehicle Detail] WHERE ((([Vehicle Detail].ProgYear)='12') AND (([Vehicle Detail].LeaseCycle)='1') And (([Vehicle Detail].ModelName)='Focus') AND (([Vehicle Detail].RiskNonRiskFlag)='R' AND (([Vehicle Detail].region)='SouthEast') And ReturnDate Is Null))

    ORDER BY [Vehicle Detail].DeliveryDate,[Vehicle Detail].SerialNumber)));

    All of the values after the "equals" signs are populated using variables. The outer part is a looping VBA program that takes grouping variables (example: ModelName and ProgranYear) to reference from the query above. Even the value for the "top" clause is a variable.

    This VBA process takes 3 hours in Access. I have the process in SQL using dynamic SQL and a while loop to fill variables and it shaves an hour off. My question is this: Could this same type of functionality be written using the "merge" statement with an update? My goal is to run the query in an hour or less. Would merge save me time?

  • Hard to give anything resembling specifics with not much detail. I think it would be far better if you can get rid of the looping and just do an update. If you can post some ddl and sample data along with the logic of what the update is supposed to do I bet we can make this run WAY faster than you ever though possible. I am talking a few seconds instead of a couple hours. 🙂

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks for your encouragement! Sorry, I have been on vacation. I will include the two tables and some code now.

    Vehicle Detail:

    VinSerialNumberProgYearLeaseCycleModelNameRiskNonriskFlagRegionReturnDate

    ZL1529831FADP3K22ZL152983131FOCUSRSOUTHEAST

    ZL1852231FADP3K20ZL185223131FOCUSRSOUTHEAST

    ZL1965361FADP3F27ZL196536131FOCUSRSOUTHEAST

    ZL2652731FADP3F27ZL265273131FOCUSRSOUTHEAST

    ZL1965801FADP3F2XZL196580131FOCUSRSOUTHEAST

    ZL1965901FADP3F22ZL196590131FOCUSRSOUTHEAST

    ZL1711981FADP3F29ZL171198131FOCUSRSOUTHEAST

    ZL1712581FADP3F21ZL171258131FOCUSRSOUTHEAST

    ZL1712681FADP3K27ZL171268131FOCUSRSOUTHEAST

    ZL2115291FADP3F2XZL211529131FOCUSRSOUTHEAST

    ZL2115321FADP3F2XZL211532131FOCUSRSOUTHEAST

    ZL2116171FADP3F27ZL211617131FOCUSRSOUTHEAST

    Returns Pros:

    RegionProgYearRiskNonRiskFlagMFGLeaseCycleModelNameReturnDateUnitCount

    Southeast13RFRD1FOCUS1/4/201412

    I agree with you about the looping, but have not been able to figure out a way to do it without looping. The problem is that the UnitCount column from the "Returns Pros" table becomes the top value in the query - hence the need for a "dynamic" (concatenated) SQL query.

    The following looping code is what shaved an hour:

    DECLARE @RowCount INT

    SET @RowCount = (SELECT COUNT(MFG) FROM [Returns Pros])

    CREATE TABLE #tmpReturns(ID INT IDENTITY (1,1) PRIMARY KEY, MFG VARCHAR(20), ModelName VARCHAR(20), ProgYear VARCHAR(12),ReturnDate DATETIME,RiskNonRiskFlag VARCHAR(12), LeaseCycle VARCHAR(12), Region VARCHAR(50), UnitCount INT)

    --CREATE UNIQUE CLUSTERED INDEX Idx1 ON #tmpReturns(ID);

    INSERT INTO #tmpReturns SELECT MFG, ModelName, ProgYear, ReturnDate, RiskNonRiskFlag, LeaseCycle, Region, UnitCount FROM [Returns Pros]

    -- Declare an iterator

    DECLARE @I INT

    -- Initialize the iterator

    SET @I = 1

    SET NOCOUNT ON

    -- Loop through the rows of a table @myTable

    WHILE (@I <= @RowCount)

    BEGIN

    -- Declare variables to hold the data which we get after looping each record

    DECLARE @mfg VARCHAR(50), @ModelName VARCHAR(50), @ProgYear VARCHAR(50)

    DECLARE @ReturnDate DateTime, @RiskFlag VarChar(25), @LeaseCycle VarChar(2)

    DECLARE @Region VARCHAR(50), @UnitCount VarChar(20), @SQL VarChar(450)

    -- Get the data from table and set to variables

    SELECT @mfg=mfg, @ModelName=ModelName, @ProgYear=ProgYear, @ReturnDate=ReturnDate, @RiskFlag=RiskNonRiskFlag,

    @LeaseCycle=LeaseCycle, @Region=Region, @UnitCount=UnitCount

    FROM #tmpReturns WHERE ID=@I

    -- Display the looped data

    Set @SQL ='UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate=''' + Cast(@ReturnDate as VarChar(20)) +

    ''' WHERE SerialNumber in (Select Top ' + @UnitCount + ' SerialNumber From [Vehicle Detail] WHERE MFG='''

    + @MFG + ''' And ModelName=''' + @ModelName + ''' And ProgYear=''' + @ProgYear + ''' And RiskNonRiskFlag='''

    + @RiskFlag + ''' And LeaseCycle=''' + @LeaseCycle + ''' And Region=''' + @Region + ''' And ReturnDate Is Null '

    + 'ORDER BY [Vehicle Detail].DeliveryDate, [Vehicle Detail].[SerialNumber])'

    Exec (@sql)

    -- Increment the iterator

    SET @I = @I + 1

    End

  • The code is much as I expected. It is better if you can post the ddl for your tables and the data as inserts. That way we are not left guessing on datatypes and it makes it a lot easier to work with. It would also help if you could post at least a few rows from the table that you are currently looping. I am 99.9% certain we can do this with no looping 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sorry, I totally missed the best practices the first time around. I will work on that this afternoon. It's exciting to think of accomplishing my goal without loops. That should be MUCH faster. I take it that "merge" is not the best way to go. I'll test my table creations and inserts before posting. Thanks.

  • realvilla (6/17/2013)


    Sorry, I totally missed the best practices the first time around. I will work on that this afternoon. It's exciting to think of accomplishing my goal without loops. That should be MUCH faster. I take it that "merge" is not the best way to go. I'll test my table creations and inserts before posting. Thanks.

    No problem. It is hard to know what to post and how to make it easy until you have done it a couple times. 😀

    I don't think MERGE is what you want here. From BOL:

    MERGE - Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

    You are always doing an update and are not concerned about the results of a join to make a decision.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Here you go. Now you can test your query (I hope) The VINs are not real for obvioius reasons.

    --===== Create the test table with

    CREATE TABLE [Vehicle Detail]

    (

    VIN Varchar PRIMARY KEY CLUSTERED,

    SerialNumber Varchar,

    ProgYear Varchar,

    LeaseCycle Varchar,

    ModelName Varchar,

    RiskNonRiskFlag Varchar,

    Region Varchar,

    MFG Varchar,

    ReturnDate Datetime

    )

    CREATE TABLE [Returns Pros]

    (

    Region Varchar

    ProgYear Varchar,

    RiskNonRiskFlag Varchar,

    MFG Varchar,

    LeaseCycle Varchar,

    ModelName Varchar,

    ReturnDate Datetime,

    UnitCount Float

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT (Returns Pros) MMDDYYYY ???

    --===== Insert the test data into the test table

    INSERT INTO [Vehicle Detail]

    (VIN, SerialNumber, ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG, ReturnDate)

    Select 'ZL152983', '1FADP3K22ZL152983', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL185223', '1FADP3K20ZL185223', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL196536', '1FADP3F27ZL196536', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL265273', '1FADP3F27ZL265273', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL196580', '1FADP3F2XZL196580', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL196590', '1FADP3F22ZL196590', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL171198', '1FADP3F29ZL171198', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL171258', '1FADP3F21ZL171258', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL171268', '1FADP3K27ZL171268', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL211529', '1FADP3F2XZL211529', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL211532', '1FADP3F2XZL211532', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'ZL211617', '1FADP3F27ZL211617', '13', '1', 'FOCUS', 'R', 'SOUTHEAST', 'FRD', NULL UNION ALL

    Select 'C5216918', '1ZVBP8DM2C5216918', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', NULL UNION ALL

    Select 'C5216923', '1ZVBP8DM2C5216923', '11', '2', 'MUSTANG CONV', 'R', 'ATLANTIC', FRD', NULL UNION ALL

    Select 'D1220647', '2G1WC6E30D1220647', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', NULL UNION ALL

    Select 'D1221984', '2G1WC6E31D1221984', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', NULL UNION ALL

    Select 'D1259402', '2G1WC6E30D1259402', '13', '1', 'IMPALA', 'R' , 'CENTRAL', 'GMC', 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, NULL UNION ALL

    Select 'Central' '13', 'N', 'GMC', '1', 'IMPALA', '6/3/2013', 3, NULL UNION ALL

    Select 'Southeast', '13', 'R', 'FRD', '1', 'FOCUS', '1/4/2014', 12

  • The ddl is pretty rough but I can make it work. You should always specify a size for varchar columns. Also, it seems like you could be using ints for some of these columns (ProgYear, LeaseCycle) and I think UnitCount is a count of items so it should probably be an int instead of a float.

    Regardless I think this is close enough. What I don't quite understand is what the business logic here? What are we trying to accomplish?

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'm sorry about the "roughness" but chalk it off to my first experience on this site. I was going to test the inserts but ran out of time here at work and wanted to get it in. The nulls for the [Returns Pros] table shouldn't have been there.

    Int would be fine for UnitCount since the numbers will never exceed 1,000. As for the business logic, every month we are sent "deletes" (returns) that have to be updated in the [Vehicle Detail] (VD) table. There is a specified number of vehicle returns for each grouping (example: FOCUS, Program Year 12, Region Central and so forth.) The return date is the critical component. There are a set number of return dates that must be filled in for each grouping using the top clause.

    If you will notice, there are the same number of records in the VD table as the specified return units (according to group) in Returns Pros. That isn't always the case in the real world, but there will never be more than the records for that group.

    I'm afraid the Program Year and the Lease Cycle will have to be text. I didn't set that up, somebody else did, but for the joins to work, (I assume that's what you will use) they must be text in both tables. I know you are probably going for speed, but hopefully it won't make that much difference. You could probably accomplish this without many variables using joins.

    I just noticed that we need to add DeliveryDate to the Vehicle Detail table for sorting. All of the Varchars can be 50 characters for simplicity sake except for ProgYear and LeaseCycle which can both be 2.

  • OK there are a few thing that we still missing but I pieced together. First thing is in your original logic you updated the returns in order of delivery date but this was missing in your ddl. No biggie.

    Also, since dealing with table names with spaces is such a pain I removed them on my side.

    alter table VehicleDetail

    add DeliveryDate datetime

    --now we need to have some sort of delivery date. I just used some existing data to generate some randomish dates.

    update VehicleDetail

    set DeliveryDate = dateadd(dd, datediff(dd, 0, DATEADD(day, -1 * cast(right(VIN,2) as int), GETDATE())), 0)

    from VehicleDetail

    OK so now we have a table that should be pretty close to your actual table. I did make a few adjustments to the returns to make sure this works when the return amount is less than the amount delivered.

    update ReturnsPros set UnitCount = 1 where Region = 'Atlantic'

    update ReturnsPros set UnitCount = 2 where Region = 'Central'

    update ReturnsPros set UnitCount = 9 where Region = 'Southeast'

    Alright so now we have a setup that is somewhat realistic...but we still need to solve the actual issue. Instead of a cursor or a while loop I am using the ROW_NUMBER() windowed function to add row numbers. This let us know which rows to update based on the values in the return table. The remaining comments are in the code.

    --first we need to get the RowNumber

    with Vehicles as

    (

    select *, ROW_NUMBER() over(partition by ProgYear, LeaseCycle, ModelName, RiskNonRiskFlag, Region, MFG order by DeliveryDate) 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 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

    )

    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

    That should produce the results as I understand them from your posts.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • I meant to say non-risk on the IMPALAs. Sorry.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 1 through 15 (of 58 total)

You must be logged in to reply to this topic. Login to reply