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:
[code=oracle]
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>

[/code]

[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