Click here to Skip to main content
15,893,381 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
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
Posted
Updated 12-Dec-13 21:29pm
v2
Comments
Ashish_Agrawal 13-Dec-13 3:44am    
What error you are getting?
♥…ЯҠ…♥ 13-Dec-13 3:50am    
So you got two records super, but why you have given this query here? since you got the record, it will be helpful if you would have given output records and your requirement..... so try to improve your question

1 solution

just enter new insert code and other fill allow null then its works otherwise not!

or

you can do update query after that update that place all field null and give only one value
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900