Grouping issue in SP

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

, +

Hi,

my SP returns the result like this

Doc_Id   Doc_No        Rev    Qty   Category  Material_Type
1        xxx           0       1      A         RCC
1        xxx           1       2      A         RCC

Now, i want one more column for TotalQty

for same Doc_no, same Category,same Material having different Rev then i want to add the qty and display in another column

EX:

Doc_Id   Doc_No        Rev    Qty   Category  Material_Type      Total
1        xxx           0       1      A         RCC                1
1        xxx           1       2      A         RCC                1+2=3

this is the part of my SP

SQL

SELECT   D.Doc_Id,D.Doc_No, D.Dwg_Rev_No,                            
 CONVERT(DECIMAL(16,3), ROUND (tq.Material_Quantity,3)) AS Material_Quantity, D.Dwg_Category_Short_Desc, DED.DED_Description As Material_Type

FROM #Dwg_Rev_Details D                         
  LEFT OUTER JOIN #Client_DCI C ON D.Doc_Id=C.Doc_Id AND D.Dwg_Rev_No=C.Dwg_Rev_No                                      
  LEFT OUTER JOIN #Site_DCI S ON D.Doc_Id=S.Doc_Id AND D.Dwg_Rev_No=S.Dwg_Rev_No                                      
  LEFT OUTER JOIN PTS_T_Drawing_Revision PDR ON D.Doc_Id=PDR.Doc_Id AND D.Dwg_Rev_No=PDR.Dwg_Rev_No              
  LEFT OUTER JOIN PTS_T_Drawing_Transmittals_Quantity TQ ON TQ.Doc_Id=D.Doc_Id AND D.Dwg_Rev_No=TQ.Dwg_Rev_No 
  LEFT OUTER JOIN   DMS_D_Drawing_Element_Details DED ON DED.DED_ID=TQ.MATERIAL_TYPE ,              
   DMS_D_Drawing_Schedule_Details DSD,              
   DMS_M_Company_Schedule_Element_Details SB
               
  WHERE D.Doc_Id=DSD.DDSD_Doc_Id              
  AND DSD.DDSD_COM_Code=SB.MCSED_COM_Code      
  AND DSD.DDSD_COM_Code=2       
  AND DSD.DDSD_SE_Code=@SE_Code      
  AND DSD.DDSD_SED_Code=SB.MCSED_SED_Code      
  AND SB.MCSED_SED_Short_Description=@SED_Code       
 GROUP BY D.Doc_Id,D.Doc_No, D.Dwg_Rev_No,                                       
 tq.Material_Quantity, D.Dwg_Category_Short_Desc, DED.DED_Description
  ORDER BY D.Doc_No

can any one help me how to resolve this issue...

Posted 10-Feb-13 20:06pm

Naveen.Sanagasetti

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Irbaz Haider Hashmi · Answer 1 · 2013-02-10T22:14:00

This is how to Update the Total. You can change it according to your requirements.

SQL

DECLARE @temp TABLE(ID int identity(1,1),empno int,Name varchar(50),sal numeric(18,2),total numeric(18,2))

// INSERT INTO TEMP TABLE YOUR LOGIC HERE


DECLARE @currentrow int
DECLARE @totalrows int
DECLARE @totalsum numeric(18,2)


SET @currentrow = 1
SET @totalsum = 0
SELECT @totalrows = COUNT(1) from @temp


WHILE(@currentrow <= @totalrows)
BEGIN


SELECT @totalsum = @totalsum + sal FROM @temp WHERE ID = @currentrow


UPDATE @temp
SET total = @totalsum
WHERE ID = @currentrow


SET @currentrow = currentrow + 1;


END