Return all rows with matching columns, different filter ...

With the data below, I would like to filter on a column where there is a non valid date on all rows that have the same values in cust_cd, subscriberid,ordernumber,appname,appflavor; however, if only one row with the above matching values does not meet the criteria, i would like to return all related rows even if only one of the rows doesn't have a valid date...based on the select query, I would only return the two rows that meet the filter in the where clause. When I'm identifying rows that meet bus rules, ie, have a valid date I would like to select all associated transactions related to that row, even if all the other rows have a valid date. So all related rows are valid or none.

if OBJECT_ID('tempdb..#table1') is not null
drop table #table1

CREATE TABLE #table1(
LDOrderDataID INTEGER NOT NULL
,Cust_Cd INTEGER NOT NULL
,SubscriberID INTEGER NOT NULL
,OrderNumber INTEGER NOT NULL
,AppName VARCHAR(9) NOT NULL
,APPFlavor VARCHAR(2) NOT NULL
,OrDate DATE
);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333117,2091833,217069,1,'APP1','BC','2014-09-02');
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333118,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333119,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333120,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333121,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333122,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333117,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333118,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333119,2091833,217069,1,'APP1','BC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333117,2091834,217068,1,'APP1','BC','2014-09-02');
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333118,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333119,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333120,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333121,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333122,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333117,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333118,2091834,217068,2,'APP1','AC',NULL);
INSERT INTO #table1(LDOrderDataID,Cust_Cd,SubscriberID,OrderNumber,AppName,APPFlavor,OrDate) VALUES (333119,2091834,217068,2,'APP1','AC',NULL);

SELECT *
FROM #table1
WHERE DATEPART(YY,OrDate) < 2015
Scarlett72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
I think you want to do this:
SELECT * 
FROM table1 t
where not exists (select 1 from table1 where cust_cd = t.cust_cd and subscriberID = t.subscriberID and ordernumber = t.ordernumber and appname = t.appname and appflavor = t.appflavor and DATEPART(YY,OrDate) < 2015)

Open in new window

Scarlett72Author Commented:
Hi Walter, I think that's close, want to return all rows for both orders, I think my table could have been populated better, because there would be rows that have valid dates that in this case I would want to omit because they are 'good'.  However, for both orders in the temp table I need to capture all rows.  So for instance the null dates were good dates ie, >= 2015 but because one of the dates were 'bad' ie, < 2015 I would like to capture all of the rows that have matching values in the custcd, subscriberid, appname, appflavor, ordernumber.
Walter RitzelSenior Software EngineerCommented:
So, you just need the condition of the query from not exists to exists.
SELECT * 
FROM table1 t
where exists (select 1 from table1 where cust_cd = t.cust_cd and subscriberID = t.subscriberID and ordernumber = t.ordernumber and appname = t.appname and appflavor = t.appflavor and DATEPART(YY,OrDate) < 2015)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.