Query to return all data that has same site, section and date

Question

Query to return all data that has same site, section and date

Nomvula

Hall of Fame

Points: 3778
More actions
September 25, 2024 at 9:33 am

#4462184

I have a query Select Id, WOCode, Site, Section, CompletionDate from work orders where type = 'BRE'. I want to be able to return list of all WOCode that has same Site,Section and CompletionDate. The date field will be date only with no time
September 25, 2024 at 1:08 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Nomvula wrote:

I have a query Select Id, WOCode, Site, Section, CompletionDate from work orders where type = 'BRE'. I want to be able to return list of all WOCode that has same Site,Section and CompletionDate. The date field will be date only with no time

same Site,Section and CompletionDate

Same as what?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

The list of work orders where work orders have the same Site, Section and same date

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

SELECT WOCode
FROM WorkOrders
WHERE Site = Section AND Section = CompletionDate

I'm fairly certain that this is not what you want, but that is what your post is asking for.

If you provide consumable DDL, sample data and desired results (and with >3,500 points, you should know how to do that), all will become clear.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 4

You didn't provide sample data and expected results.

This is my best guess:

WITH BRE AS
(
    SELECT Id, WOCode, Site, Section, CompletionDate
         , MAX(CASE WHEN [type] = 'BRE' THEN 'Y' ELSE 0 END) OVER(PARTITION BY Site, Section, CompletionDate) AS Is_BRE
    FROM [work orders]
)
SELECT Id, WOCode, Site, Section, CompletionDate
FROM BRE
WHERE Is_BRE = 'Y'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

My guess is to copy Drew's query but with DISTINCT in the outer SELECT

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

drew.allen SSC Guru Points: 76985 More actions · Answer 6

Steve Collins wrote:

My guess is to copy Drew's query but with DISTINCT in the outer SELECT

Presumably, the ID field is the primary/unique key, so adding DISTINCT will not change the results, but it will probably increase processing time. What is your reasoning for adding DISTINCT?

Also, the OP said they wanted to include a "list of ALL WOCode" (emphasis added). I would think that would exclude using DISTINCT.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

If the ID is unique then yes DISTINCT would do nothing. I was sort of thinking right-to-left if there were duplicate properties there could also be duplicate ID's. No data tho

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können