query help

I have a table with accounts and bills.   each account gets 1 bill a month.
How can i construct a query that returns the last 3 bills for each account only if all 3 bills equal zero
jamesmetcalf74Asked:
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.

Scott PletcherSenior DBACommented:
SELECT ca1.*
FROM (
    SELECT account, SUM(bill_amount) AS total_bill_amount
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY account ORDER BY bill_date DESC) AS row_num
        FROM dbo.table_name
    ) AS derived1
    WHERE row_num BETWEEN 1 AND 3
    GROUP BY account
    HAVING COUNT(*) = 3 AND SUM(bill_amount) = 0
) AS derived2
CROSS APPLY (
    SELECT TOP (3) *
    FROM dbo.table_name tn2
    WHERE tn2.account = derived2.account
    ORDER BY bill_date DESC
) AS ca1

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
SujithData ArchitectCommented:
with [data] as (
SELECT 	1000 as account, CAST('2018-03-31' AS datetime) as dt, 0 as bill_amt
union all
SELECT 	1000 as account, CAST('2018-02-28' AS datetime) as dt, 0 as bill_amt
union all
SELECT 	1000 as account, CAST('2018-01-31' AS datetime) as dt, 0 as bill_amt
union all
SELECT 	1000 as account, CAST('2017-12-31' AS datetime) as dt, 200 as bill_amt
union all
SELECT 	2000 as account, CAST('2018-02-28' AS datetime) as dt, 0 as bill_amt
union all
SELECT 	2000 as account, CAST('2018-01-31' AS datetime) as dt, 100 as bill_amt
union all
SELECT 	2000 as account, CAST('2017-12-31' AS datetime) as dt, 0 as bill_amt
union all
SELECT 	2000 as account, CAST('2017-11-30' AS datetime) as dt, 0 as bill_amt
), [data2] as (
select 	account, dt, bill_amt
, row_number() over(partition by account order by dt desc) rn
from	[data]
), [data_final] as (
select	account, dt, bill_amt
, sum(bill_amt) over(partition by account) sm
,max(bill_amt) over(partition by account) mm
,min(bill_amt) over(partition by account) mn
from 	[data2] where rn <=3
)
select	account, dt, bill_amt
from	[data_final]
where	sm = 0 and mm =0 and mn = 0
;

Open in new window

Scott PletcherSenior DBACommented:
Correction, just in case a negative bill amount could appear (?):

...
SELECT ..., SUM(ABS(bill_amount)) AS total_bill_amount
...
HAVING AND SUM(ABS(bill_amount)) = 0
...
jamesmetcalf74Author Commented:
Worked like a charm
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
Query Syntax

From novice to tech pro — start learning today.