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.
5 1443
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>
-
-
Thank you, I will give that a shot! :)
What is Dual supposed to be? Got a large result dating back to 2009. I want only 14 days of data into the future.
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.
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jerry |
last post by:
Hi,
Access 2003. I would like to query specific dates or years. I need to
be able to selct the dates in a query, ie aug 12 2004, aug 11th 2004,
or 2001, 2002 and 2003.
Any help is most appreciated
|
by: carrionk |
last post by:
Hi,
I have created a Subform which SourceObject is a parameter query.
This is the Query:
Qry Name:80IsscomProduct
SELECT *
FROM Isscomp28
WHERE Like ;
|
by: YuanYuan |
last post by:
Thank you so much, guys. I really appreciate that.
>-----Original Message-----
>Hi, I am wondering if there is a nice way to elimate
>duplicated values in an array, just like the "unique"...
|
by: mkjets |
last post by:
I have worked for hours on trying to find a solution and have not
figured it out. I am working in Access 2003. I need to create a query
that takes values from 1 table and displays them in...
|
by: bubbles |
last post by:
Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:
Need help to do this:
Open Table_A
WITH TableA
DO UNTIL .EOF
Read value from TableA.ColumnA
Run SQL Statement on...
| |
by: Pete |
last post by:
I need to create a single query (Not a SQL query) against a single
table that counts the number of records in the table, where the single
field "tmp" contains specific string values
If the field...
|
by: gerrybytes |
last post by:
Is the following a vaild query, or does any one have any suggestion on how to change it to get the correct info i'm looking for?
$Engineer = $_POST;
$Ref_No = $_POST;
$query = "INSERT INTO...
|
by: asp2 |
last post by:
I have a cursor inside a program unit in Oracle Forms 6
and its supposed to get one row from this cursor ..
but in some values (only some and others not) it gets the same row twice (duplicated) in...
|
by: Suevans |
last post by:
I've written a basic query which has a calculated field
Amount: -
The query works fine. Sometimes it will not have anything to show, this is correct, it is for unreconciled transactions. ...
|
by: Marcin W |
last post by:
Hi,
I have following problem. I have to run a query where one of the result fields should be within range of values I got from different table.
Query 1
select column_x from table1 where...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |