SQLServer 12 - need asssistance with sqlstmt

Member_2_7964482
Member_2_7964482 used Ask the Experts™
on
Need assistance in creating a SQL statement for SQLServer 2012 –

Facts:
Table BILLING – main fields in queston: ID,TranDate,Status  

ID      Trandate      Status
1      1/1/2017      P
1      1/1/2017      P
1      1/1/2017      P
1      2/1/2017      O
1      2/1/2017      P
1      2/1/2017      P
1      3/1/2017      P
1      4/1/2017      O
2      1/1/2017      O
(Note BILLING does have a unique index for ID/Trandate/Linenbr – but linenbr does not play a part in the sqlstmt)


Issue: For ID #1 need to return the earliest trandate from BILLING that is >= @trandate where all status for that trandate = ‘P’

For example:
select  min(trandate) from Billing where
id = ‘1’
and   trandate >= ‘@trandate                         (@trandate = 1/1/17)
and status = ‘P’

here’s the issue –
only want  1/1/2017 to be returned if all statuses for 1/1/2017 = ‘P’ (For trandate 1/1/17 that would be true)

But if @trandate = ‘2/1/2017’  would not be true.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Give this a whirl..
Line 3 allows you to conditionally COUNT based on a value, which can be compared to the overall COUNT 
;WITH cte AS (
	SELECT ID, Trandate, 
	  SUM(CASE WHEN Status='P' THEN 1 ELSE 0 END) AS status_p_count, 
	  COUNT(Status) AS status_count
	FROM YourTable
	GROUP BY ID, Trandate) 
SELECT ID, Trandate
FROM cte
WHERE status_p_count = status_count

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial