troubleshooting Question

SQLServer 12 - need asssistance with sqlstmt

Avatar of Member_2_7964482
Member_2_7964482 asked on
Microsoft SQL ServerSQL
1 Comment1 Solution82 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros