sql server - Repeated rows status based on given rule -

sql server - Repeated rows status based on given rule -

i have table below

refno | amount | id | billed 1 20 1 1 1 20 2 1 2 10 3 0 2 10 4 0 2 10 5 0 3 30 6 1 3 30 7 0 3 30 8 1 3 30 9 0

i want out set below

refno | amount | id | billed | status 1 20 1 1 singlebilled 1 20 2 1 singlebilled 2 10 3 0 singleunbilled 2 10 4 0 singleunbilled 2 10 5 0 singleunbilled 3 30 6 1 multiplebilled 3 30 7 0 multiplebilled 3 30 8 1 multiplebilled 3 30 9 0 multiplebilled

here status need com base of operations on refno, billed flag please help me

something along these lines based on info have given far... see fiddle: http://sqlfiddle.com/#!3/156c1/8

select a.refno, a.amount, a.id, a.billed, case when b.bill_total = 0 'singleunbilled' when b.bill_count > b.bill_total 'multiplebilled' when b.bill_count = b.bill_total 'singlebilled' else '' end [status] test inner bring together ( select refno, count(billed) bill_count, sum(cast(billed int)) bill_total test grouping refno ) b on a.refno = b.refno

sql-server sql-server-2008 sql-server-2008-r2 sql-server-2012