Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Grouping Ordered & Received Qty On PO (Conditional
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goenkavishal1
Starting Member

India
1 Posts

Posted - 11/07/2012 :  06:23:08  Show Profile  Reply with Quote
Need to group the results on basis of vendor or rate change.
My Sorted Table result :-
ALTER PROC PURCHASE_HISTORY1          
(          
@item_code char(16)          
)          
AS          
BEGIN          
      
CREATE TABLE #PUR_HIS1        
(        
SL_NUM  INT,        
VENDOR_CODE CHAR(18),        
VENDOR_DESC VARCHAR(55),        
PO_NO  CHAR(18),      
RATE  NUMERIC(28,8),        
ORD_QTY  NUMERIC(28,8),        
RECV_QTY NUMERIC(28,8),        
CR_DATE  DATETIME      
)        
        
INSERT INTO #PUR_HIS1(SL_NUM,VENDOR_CODE,PO_NO,RATE,ORD_QTY,RECV_QTY,CR_DATE)        
        
SELECT ROW_NUMBER() OVER (ORDER BY HDR.PO_DATE desc) AS NUMBER,      
HDR.VENDOR_CODE, DTL.PO_NO, DTL.RATE, DTL.ORDERED_QTY_PUOM, DTL.RECEIVED_QTY_PUOM, HDR.PO_DATE     
FROM PUR_PO_DETAIL DTL, PUR_PO_HEADER HDR          
WHERE DTL.STOCK_NO = @ITEM_CODE          
AND DTL.PO_NO = HDR.PO_NO          
ORDER BY HDR.PO_DATE desc        
          
UPDATE #PUR_HIS1          
SET VENDOR_DESC = a.VENDOR_NAME        
FROM COMMON..PUR_COMPANY_VENDOR_MASTER a, #PUR_HIS1 b        
WHERE a.VENDOR_CODE = b.VENDOR_CODE     
    
SELECT * FROM #PUR_HIS1
--Sorted Table

CREATE TABLE #PUR_HIS_FNL        
(       
VENDOR_CODE CHAR(18),        
VENDOR_DESC VARCHAR(55),        
RATE  NUMERIC(28,8),        
DATE  DATETIME,    
ORD_QTY  NUMERIC(28,8),        
RECV_QTY NUMERIC(28,8)    
)        
    
INSERT INTO #PUR_HIS_FNL(VENDOR_CODE, VENDOR_DESC, RATE, DATE, ORD_QTY, RECV_QTY)     
    
SELECT a.VENDOR_CODE, a.VENDOR_DESC, a.RATE, a.CR_DATE, 0, 0
FROM #PUR_HIS1 a LEFT OUTER JOIN #PUR_HIS1 b ON a.SL_NUM = b.SL_NUM + 1    
WHERE b.SL_NUM IS NULL OR b.RATE <> a.RATE    
    
SELECT * FROM #PUR_HIS_FNL     
     
END
--Result should be like this 2 table result, but with grouped ordered & received quantities.
---------------------------------------------------------Sorted Table
SL_NUM VENDOR_CODE VENDOR_DESC PO_NO RATE ORD_QTY RECV_QTY CR_DATE
1 ORM000038 MEENA ENTERPRISES KSPO/000027/12-13 17.00000000 10.00000000 10.00000000 2012-04-13 00:00:00.000
2 ORM000828 MAHADEO PRASAD KSPO/000634/11-12 24.00000000 4.00000000 4.00000000 2011-10-24 00:00:00.000
3 ORM000038 MEENA ENTERPRISES KSPO/000453/11-12 17.00000000 10.00000000 10.00000000 2011-09-01 00:00:00.000
4 ORM000038 MEENA ENTERPRISES KSPO/000081/11-12 17.00000000 10.00000000 10.00000000 2011-04-29 00:00:00.000
5 ORM000038 MEENA ENTERPRISES KSPO/001059/10-11 17.00000000 20.00000000 20.00000000 2010-11-22 00:00:00.000
6 ORM000038 MEENA ENTERPRISES KSPO/000400/10-11 17.00000000 20.00000000 20.00000000 2010-06-15 00:00:00.000
7 ORM000038 MEENA ENTERPRISES KSPO/001505/09-10 23.00000000 10.00000000 10.00000000 2010-03-22 00:00:00.000
8 ORM000038 MEENA ENTERPRISES KSPO/000395/09-10 25.00000000 10.00000000 10.00000000 2009-10-03 00:00:00.000
9 ORM000038 MEENA ENTERPRISES KSPO/000100/09-10 25.00000000 10.00000000 10.00000000 2009-08-01 00:00:00.000
10 ORM000038 MEENA ENTERPRISES KS/BRO/PO/6778 25.00000000 10.00000000 10.00000000 2009-06-26 00:00:00.000
--------------------------------------------------Result Table
VENDOR_CODE VENDOR_DESC RATE DATE ORD_QTY RECV_QTY
ORM000038 MEENA ENTERPRISES 17.00000000 2012-04-13 00:00:00.000 0.00000000 0.00000000
ORM000828 MAHADEO PRASAD 24.00000000 2011-10-24 00:00:00.000 0.00000000 0.00000000
ORM000038 MEENA ENTERPRISES 17.00000000 2011-09-01 00:00:00.000 0.00000000 0.00000000
ORM000038 MEENA ENTERPRISES 23.00000000 2010-03-22 00:00:00.000 0.00000000 0.00000000
ORM000038 MEENA ENTERPRISES 25.00000000 2009-10-03 00:00:00.000 0.00000000 0.00000000

Edited by - goenkavishal1 on 11/07/2012 07:18:52
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000