SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.05 seconds. Powered By: Snitz Forums 2000