HELLO, this query returns 2 records(rows), i just want to insert 2 new rows with respect to their months (#w.M)
declare @liNum decimal(19,6)
declare @PrCl dec(19,6)
declare @de dec(19,6)
declare @cr dec(19,6)
declare @acc char(20)
declare @Pracc char(20)
Create table #W
(
liNum dec(19,6),sname nvarchar(100),Acc char(20), M Datetime, Op dec(19,6),De dec(19,6),Cr dec(19,6),Cl dec(19,6), Ct char(50),GrpName nvarchar(50),
CustType varchar(30), DealrName varchar(30), MrktCode varchar(30), SPO varchar(30), Tertry varchar(30), TertryMnger varchar(30),TertryZne nvarchar(50), Active nvarchar(50),
Addrs nvarchar(200),city varchar(30), CrLimit numeric(9), PayTerms varchar(30), Zne varchar(12),CustCatgry varchar(11)
)
Insert into #W
(
liNum, sname, Acc,M,Op,De,Cr,Cl ,Ct,GrpName,CustType,DealrName,MrktCode,SPO,Tertry,TertryMnger,TertryZne,Active,Addrs,city,CrLimit,PayTerms,Zne,CustCatgry
)
SELECT t1.TransId, t1.ShortName , T1.[Account],
Convert(Datetime,t0.RefDate,103) , 0,
T1.[Debit] De ,T1.[Credit] Cr,0 ,d2.CardCode,d2.GroupName, d2.CustomerType, d2.DealerName, d2.MarketingCode, d2.SPO, d2.Territory, d2.TerritoryManager, d2.TerritoryZone, d2.Active,
d2.Address, d2.City, d2.CreditLimit, d2.PaymentTerms,d2.Zone, d2.CustomerCatergory
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN DIMBUSINESSPARTNERS D2 ON D2.CARDCODE = T1.SHORTNAME
where d2.CardType='C'
and t1.ShortName in ('101267','107286','107755','2109294','4101340')
Order By T1.[ShortName] ,T1.[RefDate],t1.TransId asc
Declare cu cursor for
Select #w.sname,de,cr from #W
for update set @PrAcc=''
Open cu
Fetch next from cu into @acc,@de,@cr
While @@FETCH_STATUS = 0
Begin
If @acc!=@PrAcc set @PrCl=0
Update #W
set op=@PrCl,
cl=@PrCl-@Cr+@de
where current of cu
set @PrCl=@PrCl-@Cr+@de
set @PrAcc=@acc
Fetch next from cu into @acc,@de,@cr
End
deallocate cu
--select #w.M from #w
--insert into #W (M)values('2013-01-01')---------- I WANT TO INSERT A NEW ROW HERE...-----------
select *, #w.Cl as Balance, YEAR(#w.M) AS Year , convert(char(3), '2013-02-28', 0) as MName , MONTH('2013-02-28') AS Month , '2013-01-01' AS FromDate, '2013-02-28' AS Todate
from #w
inner join (
select Max(liNum) as transid from #W
where #w.sname not in (select sname from #w where #w.M between '2013-01-01' and '2013-02-28')
and #w.m <'2013-01-01'
group by #w.sname )
#z on #z.transid = #w.liNum
where #w.M <'2013-02-28'
--union
--select *, #w.Cl as Balance, YEAR(#w.M) AS Year , convert(char(3), #w.m, 0) as MName , MONTH(#w.m) AS Month , @FromDate AS FromDate, @Todate AS Todate
--from #W
--inner join
--(
-- Select MAX(liNum) as rownum
-- from #w
-- where #w.M between @Fromdate and @Todate
-- group by year(#w.m), month(#w.M), #W.sname
--) #z on #z.rownum = #w.liNum
--where #w.M between @Fromdate and @Todate
Drop table #W