SQLTeam.com | Weblogs | Forums

Join Table with Division


#1

Hi all
I have two tables

Screenshot_2

please help me how to make result like that


#2

Create table #A (id int, Unit char(1), Conv int)
Create table #B (id int, Qty int)

insert into #A values
(1,'A',1)
,(1,'B',10)
,(1,'C',50)

insert into #B values (1,223)
Select ID, Unit, Conv, IsNull(Remainder, Division) Division
from (
select a.ID, a.Unit, a.Conv, qty / Conv as division,
(lag(qty - (qty / Conv * Conv)) over (order by Conv desc)/Conv) as Remainder
from #A a
join #B b
on a.ID = b.ID
) x
order by Conv desc


#3

Thank bro....
but I am using sql server 2005 and lag function not support, please help me