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.
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.