How To convert case into packs in case statement mssql2000

Question

Hi Friends,

as example for table for references

create table inv_tab

(

inv_no varchar(20),

Model_ref varchar(20),

Item_type char(15),

Item_No int,

Inv_qty int,

Bill_type char(20)

)

insert into inv_tab values('tn/002/13-14','#','K','6000320','25','PACKS')

insert into inv_tab values('tn/002/13-14','6000320','X','500020','30','PACKS')

insert into inv_tab values('tn/002/13-14','6000320','X','500040','1','case')

insert into inv_tab values('tn/002/13-14','6000320','X','500010','3','PACKS')

MY expecting O/P:

INV_NO ITEM_NO SALES FREE

tn/002/13-14 500020 25 5

tn/002/13-14 500040 0 0.04 (I.e 1 / 25)

tn/002/13-14 500010 0 3

i had done the code which was free and sales now i need to convert case to packs (i.e item_qty/inv_qty billed )

here my code for sales and free kindly modify my code for case into packs

select

a.company_no,

a.invoice_no,

a.invoice_locn,

a.item_no as Kit_Ref_group,

b.invoice_litem_no,

b.item_no ,

max(CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales,

b.invoice_qty,

a.invoice_type,

b.invoice_qty - (CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty End) as Free

from

dms_t_invoice_item a

inner join

dms_t_invoice_item b

on

a.invoice_no= b.invoice_no

where

a.item_no=b.model_ref

and

a.company_no=b.company_no

and

a.invoice_type=b.invoice_type

and

a.invoice_locn=b.invoice_locn

and

a.invoice_litem_no=b.invoice_litem_no

and

a.invoice_no='HY/002273/10-11'

group by

a.company_no,

a.invoice_no,

a.item_no ,

b.item_no ,

a.invoice_qty,

b.invoice_qty,

a.invoice_type,

a.invoice_locn,

b.invoice_litem_no,

b.company_no,

B.tran_uom