Home Ignoring Duplicate Records SQL
Reply: 0

Ignoring Duplicate Records SQL

user1869
1#
user1869 Published in June 22, 2018, 11:32 am

In need of some help :)

So I have a table of records with the following columns:

Key (PK, FK, int) DT (smalldatetime) Value (real)

The DT is a datetime for every half hour of the day with an associated value

E.g.

Key       DT                       VALUE
1000      2010-01-01 08:00:00      80
1000      2010-01-01 08:30:00      75
1000      2010-01-01 09:00:00      100

I have a Query that finds the max value every 24 hour period and its associated time however, on one day the max value occurs twice and hence duplicates the date which is causing processing issues. I have tried using rownumber() which works but I can't use a calculated column in my where clause? Currently I have:

SELECT       cast(T1.DT as date) as 'Date',Cast(T1.DT as time(0)) as 'HH', ROW_NUMBER() over (PARTITION BY  cast(DT as date) ORDER BY DT) AS 'RowNumber'
FROM        TABLE_1 AS T1
INNER JOIN  (
                SELECT CAST([DT] as date) as 'DATE'
                ,       MAX([VALUE]) as 'MAX_HH'
                FROM    TABLE_1
                WHERE   DT > '6-nov-2016' and [KEY] = '1000'
                GROUP BY CAST([DT] as date)
            ) AS MAX_DT
        ON  MAX_DT.[DATE] = CAST(T1.[DT] as date)
        AND T1.VALUE = MAX_DT.MAX_HH
WHERE       DT > '6-nov-2016' and [KEY] = '1000'
ORDER BY DT

This results in

Key       DT               VALUE       HH
1000      2010-01-01       80          07:00:00
1000      2010-02-01       100         17:30:00
1000      2010-02-01       100         18:00:00

I need to remove the duplicate date (I Have no preference which HH it takes)

I think I've explained that terribly, let me know if it makes no sense and i'll try and re write

Any ideas?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323905 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO