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

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Grouping Ordered & Received Qty On PO (Conditional

Author  Topic 

goenkavishal1
Starting Member

1 Post

Posted - 2012-11-07 : 06:23:08
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
   

- Advertisement -