How to use Cursor to get desired result.

Question

How to use Cursor to get desired result.

amitsingh308

Ten Centuries

Points: 1287
More actions
July 15, 2011 at 6:20 am

#254567

Hi
I have a query like :
with abc as (select Row_number() over (order by Op.master_Code,Q.issuedate)[r],
Convert(varchar(20),Q.issuedate,106)[Date],Op.master_Code,Op.Item_Name,
isnull(Sum(Stock),0)[Opening Stock],Isnull(Sum(Q.RecQty),0)[Receive Qty],
Convert(decimal(18,3),isnull(Sum(Q.UseQty),0))[Used Qty],(isnull(Sum(Stock),0) +
Isnull(Sum(Q.RecQty),0) )-(isnull(Sum(Q.UseQty),0))[Total],
isnull(Sum(Q.AdjQty),0)[Adjustment] ,(isnull(Sum(Q.AdjQty),0))+(
isnull(Sum(Stock),0) + Isnull(Sum(Q.RecQty),0) + isnull(Sum(Q.AdjQty),0) )-(isnull(Sum(Q.UseQty),0))Balance
from ( select distinct i.Master_Code,i.Item_Name,pb.Stock from internal_issue i
left outer join (select PB.Item_Code, isnull(Sum(pb.Stock),0)[Stock] from
(select Isnull(Sum(QtyIssued),0)[Stock],Master_code[Item_Code]
from internal_issue where issuedate<'07-01-2011 00:00:00' and Department='CONFECTIONARY'
Group by master_code
union all
select isnull(sum(Adjusted),0),Item_Code from RawStockAdjustment
where Ref_date<'07-01-2011 00:00:00' Group by Item_Code
union all
select - isnull(Sum(dt.Qty * dprod.Qty),0)[RQty],dt.master_Code
from FG_RECEIPEdet Dt,FG_RECEIPEMst Mst,Raw_DailyTransfer DProd
Where dt.Receipe_No = mst.Receipe_No And mst.Item_Code = dprod.Item_Code
and dprod.Date <'07-01-2011 00:00:00' group by dt.master_Code
)pb group by Pb.Item_Code ) pb on pb.Item_Code=i.master_code
left outer Join (select Item_Code,isnull(Sum(Qty),0)[Qty] from rawphysical_stock
where tag=0 and phy_date ='07-13-2011 00:00:00' group by Item_Code) Ph on
ph.Item_Code=I.Master_Code group by I.Master_Code,i.Item_Name,pb.Stock) OP,
(Select issuedate, Item_Code,Sum(RecQTy)[RecQty],Sum(UseQty)[UseQty],Sum(AdjQty)[AdjQty]
from (select issuedate, Isnull(Sum(QtyIssued),0)[RECQTY],Master_code[Item_Code],0[UseQTy],
0[AdjQty] from internal_issue
where issuedate between '07-01-2011 00:00:00' and '07-13-2011 00:00:00'
and Department='CONFECTIONARY' group by Master_code ,issuedate
union all
select dprod.Date ,0,dt.master_Code ,isnull(Sum(dt.Qty * dprod.Qty),0)[UsedQty],0
from FG_RECEIPEdet Dt,FG_RECEIPEMst Mst,Raw_DailyTransfer DProd
Where dt.Receipe_No = mst.Receipe_No And mst.Item_Code = dprod.Item_Code
and dprod.Date between '07-01-2011 00:00:00' and '07-13-2011 00:00:00'
group by dt.master_Code,dprod.Date
union all
select Ref_date,0,Item_Code,0,isnull(Sum(Adjusted),0)[AdQty]
from RawStockAdjustment where Ref_date between '07-01-2011 00:00:00'
and '07-13-2011 00:00:00' group by Item_Code,Ref_date) Q group by Item_Code,issuedate )
Q where Q.Item_Code = Op.master_Code
group by Op.master_Code,Op.Item_Name,Q.issuedate)
Select * into ##temp from abc
Now I am applying following query for to get desired result
select Date,Master_Code,Item_Name,[Opening Stock],[Receive Qty],[Used Qty],Total,
Adjustment,Balance from ##temp where r =1
union all
select Q.date,Q.master_Code,Q.item_Name,
Q.[Opening Stock]
,Q.[Receive Qty],Q.[Used Qty],Q.Total,
Q.Adjustment,Q.Balance from(
select row_number() over (order by t1.master_code,t1.date)[r], t1.Date,t1.Master_Code,t1.Item_Name,
case when t.master_code=t1.master_code then
t.balance
else t1.[Opening Stock] end as [Opening Stock] , t1.[Receive Qty],t1.[Used Qty],
case when t.master_code=t1.master_code then (t.Balance + t1.[Receive Qty] - t1.[Used Qty]) else (t1.[Opening Stock] + t1.[Receive Qty] - t1.[Used Qty] + t1.Adjustment)end as [Total] ,t1.Adjustment, case when t.master_code=t1.master_code then ((t.Balance + t1.[Receive Qty] - t1.[Used Qty]))+(t1.Adjustment) else t1.Balance end as[Balance] from ##temp t ,##temp t1 where t1.r = t.r +1 ) Q
the out put of this query is
Date Master_Code Item_Name Opening Stock Receive Qty Used Qty Total Adjustment Balance
-------------------- -------------------- ---------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
07 Jul 2011 1102 ALMOND 0.000 50.000 0.134 49.866 0.000 49.866
08 Jul 2011 1102 ALMOND 49.866 22.000 0.335 71.531 0.000 71.531
13 Jul 2011 1102 ALMOND 21.665 6.000 0.000 27.665 0.000 27.665
07 Jul 2011 1166 CHERRY TIN 0.000 0.000 0.600 -0.600 0.000 -0.600
08 Jul 2011 1166 CHERRY TIN -0.600 50.000 3.000 46.400 0.000 46.400
13 Jul 2011 1166 CHERRY TIN 47.000 10.000 0.300 56.700 0.000 56.700
But I Need output as follow.
Date Master_Code Item_Name Opening Stock Receive Qty Used Qty Total Adjustment Balance
-------------------- -------------------- ---------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
07 Jul 2011 1102 ALMOND 0.000 50.000 0.134 49.866 0.000 49.866
08 Jul 2011 1102 ALMOND 49.866 22.000 0.335 71.531 0.000 71.531
13 Jul 2011 1102 ALMOND 71.531 6.000 0.000 27.665 0.000 27.665
07 Jul 2011 1166 CHERRY TIN 0.000 0.000 0.600 -0.600 0.000 -0.600
08 Jul 2011 1166 CHERRY TIN -0.600 50.000 3.000 46.400 0.000 46.400
13 Jul 2011 1166 CHERRY TIN 47.000 10.000 0.300 56.700 0.000 56.700
Can Any one tell me how can i get this result

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

amitsingh308 (7/15/2011)
Can Any one tell me how can i get this result

No. Both outputs look exactly the same to me. Most people don't have the time or patience to compare the two and see what the difference is, so please describe what it is you're trying to do, and provide some table DDL and sample data.

Thanks

John

LutzM SSC Guru Points: 107049 More actions · Answer 2

At a first glance it looks like for some values in Item_Name the Opening values are running totals but for other values those are plain values.

For instance, the opening value for ALMOND changed from 21.665 to 71.531 (seems to be arunning total) but for CHERRY TIN it is identical (47.000).

Hard to tell what the business logic requires...

I second Johns request for ready to use sample data. For a detailed description of how to do it please have a look at the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

It looks like "Opening" is simply the difference between the previous "Opening" and the previous "Balance". Is that correct?

I also suggest that you read the article at the first link in my signature line below to post some readily consumable data to emulate what you store in the ##Temp table. Don't assume that you know how to do that... read the article, please. You'll get help a whole lot quicker and it might even be in the form of tested code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)