Link to home
Create AccountLog in
Avatar of Member_2_7964482
Member_2_7964482

asked on

SQLServer 12 - need asssistance with sqlstmt

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer