none
Access DB 2007 - remove duplicate date rows RRS feed

  • Question

  • Hi,

    Below is cut and paste off a Select Query output. I'm trying to eliminate the duplicate dates. To provide some context, our company runs some data and then it's saved and loaded to Teradata. In the example below, you can see we have multiple duplicate dates (7/9/2019 shows up twice and et cetera) and the reason being is the data (awto_228) was loaded to Teradata, but, then the planner realized she made a mistake and loaded another corrected case (awto_243 - hence the duplicate dates). I can't have the other case (awto_228) scrubbed from the database so what I want is to take the max case name to get my unique row value. However, when I do a MAX in the select query on case name nothing happens. Could someone please help me with this?

    Thanks,
    Mike


    PlantName StartDateTime StreamCode StreamRow PETROStreamDescription NUmber1 Number2 Number3 MaxOfCaseName NAME
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.38032618 1091.12535 -72.5027321 awto_228 ESE 2019 July M1
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.88569597 1250.10222 -76.7584626 awto_243 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/13/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    Wednesday, October 9, 2019 4:52 PM

All replies

  • PlantName StartDateTime StreamCode StreamRow PETROStreamDescription NUmber1 Number2 Number3 MaxOfCaseName NAME
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.38032618 1091.12535 -72.5027321 awto_228 ESE 2019 July M1
    ABC 7/9/2019 abc.def LMNO..W Sour Grapes 3.88569597 1250.10222 -76.7584626 awto_243 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/10/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/11/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.774524 1214.32347 -76.7276515 awto_243 ESE 2019 July M1
    ABC 7/12/2019 abc.def LMNO..W Sour Grapes 3.27577072 1057.09121 -68.5873664 awto_228 ESE 2019 July M1
    ABC 7/13/2019 abc.def LMNO..W Sour Grapes 3.77450824 1214.32347 -76.7276515 awto_243 ESE 2019 July M1

    Hi Mike,

    You could also delete all records from the original load:  "WHERE MaxOfCaseName = 'awto_228'"

    or the records from the corrected load:  "WHERE MaxOfCaseName = 'awto_243'"

    Imb.

    Wednesday, October 9, 2019 5:37 PM
  • Hi,

    Thanks for the reply. The problem is that this happens all the time - the planners will load different cases pretty often which causes duplicated dates (with different quantitative values). Also, it's not always a mistake, if the posture of the business rapidly changes they'll have to do another optimized case and load it up again.

    Mike

    Wednesday, October 9, 2019 5:43 PM
  • I can't have the other case (awto_228) scrubbed from the database so what I want is to take the max case name to get my unique row value.
    You will need to return the MAX case names per date in a subquery and restrict the outer query to the rows in which the case name equals the value returned by the subquery, which should be correlated with the outer query on the date column.

    If you are uncertain how to do this, post the SQL statement for the current query we should be able to amend it appropriately.


    Ken Sheridan, Stafford, England

    Wednesday, October 9, 2019 6:01 PM
  • Thanks! I'll try.
    Wednesday, October 9, 2019 6:35 PM
  • just create an aggregate query of 2 fields:

    group on StartDate

    Max on CaseName

    ….save that with a name

    then add that query object into your current select query - and join on those 2 fields.....

    if there is more than 1 plant name you'll need a 3 field aggregate query.....

    Tuesday, October 15, 2019 10:30 PM