Assuming "No repair date" means the repairdate is blank
Something like this may give you what you are looking for
With step1fileb as
(select order, case when repairdate = ' ' then 1 else 2 end as datetype from fileb),
With step2fileb as
(select distinct order, datetype from step1fileb),
With step3fileb as
(select order, sum(datetype) as sumtype group by order order by order from step2fileb)
Select order, case when sumtype = 1 then 'OPEN'
When sumtype = 2 then 'REPAIRED'
When sumtype = 3 then 'OPEN'
Else 'UNDEFINED' end as finaltype
From step3fileb
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Smith, Mike
Sent: Thursday, February 23, 2012 11:58 AM
To: 'Midrange Systems Technical Discussion'
Subject: help needed with sql statement
I have 2 files.
FILEA and FILEB
Scenario. I am trying to create a view over 2 files that show me OPEN, REPAIRED or ALL.
Open is where a order in FILEB has at least 1 record that has no repair date.
Repaired is when a order in FILEB has ALL records with a Repair date.
The ALL part I got :)
Both have order number.
FILEA(ORDER)
FILEB (ORDER, REPAIRDATE)
FILEB can have multiple records per Order number
I've been struggling with this all morning.
Thanks for any help
Mike
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
