Query for specific duplicated values

[dbrewerton]

Ok, let me see if I can explain this clearly. What I'm trying to do is generate a query that looks for duplicate start times. The caveat here is there are certain character strings that have to be present for it to be considered a duplicate. Here is the existing query I have:

SELECT AL2.PS, AL2.ASSETS, AL2.PT, AL2.NP, AL2.NN, AL2.AS, AL2.PR, AL2.CH1, AL2.AT, AL2.AD, AL2.PC,  AL2.PE, AL2.STAT, AL2.SYS, AL2.BD, AL2.LOC, AL2.IR FROM MYDB AL2
WHERE (PS >= CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)+1) AND (PS < CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)+14) AND ST <> 'CL';

Ok, now the duplication will be based on AL2.PS and the AL2.ASSETS field must contain certain codes in it to be considered a duplicate.

Example:
If record 1 has a PS = 2010-11-15 15:30:00 and has an ASSET code of JPB, find any records that match that time in this table. The idea is I'm looking for duplicated records in the same table. I have been working on this and I am stumped.

[amitpatel66]

How about:

SQL> ed
Wrote file afiedt.buf
 
  1  with t as (SELECT '2010-11-15 15:30:00' ps,'JPB' asset from dual
  2  union all select '2010-11-15 15:30:00','JPB' from dual
  3  union all select '2010-11-15 15:30:32','JPB' from dual)
  4  select ps,asset,COUNT(*) cnt
  5  FROM   t
  6  GROUP BY ps,asset
  7* HAVING COUNT(*) > 1
SQL> /
 
PS                  ASS        CNT
------------------- --- ----------
2010-11-15 15:30:00 JPB          2
 
SQL> 
 
 

[dbrewerton]

Thank you, I will give that a shot! :)

[dbrewerton]

What is Dual supposed to be? Got a large result dating back to 2009. I want only 14 days of data into the future.

[amitpatel66]

Can you post what you are trying?

Btw, Dual is a dummy table that I used to create dummy data becauase I dont have a table that you are using.

[dbrewerton]

Ok, what I am trying to accomplish is as follows:
based on a sub query that finds certain items that must run without interruption, I want to have a query that will return results that would cause a time conflict. The subquery I am using is:

SELECT START, END, NUMBER, CHAR1, PROJECT, DEPT, CATEGORY 
from MyDB 
WHERE START between  (CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)+1) 
AND (CAST(FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME)+7)
AND CATEGORY = 'MAIN'
AND CHAR1 = 'YES'
 

Now, the prime difference is the ones I'm looking for would be CHAR1 as NO or NULL. The conflict would be related to the duration. None of the non-char1 tasks can be ran during the duration. Here is a sample result set:

START                    END                NUMBER    CHAR1    PROJECT     DEPT    CATEGORY
2010-11-20 23:00:00    2010-11-21 04:00:00    18688    YES    Project1    DEPT1    MAIN
2010-11-21 23:00:00    2010-11-22 00:00:00    19933    YES    Project2    DEPT2    MAIN
2010-11-19 00:00:00    2010-12-17 00:00:00    25652    YES    Project3    DEPT3    MAIN