November 6, 2012 at 12:13 am
--Table
CREATE TABLE #PUR_HIS1
(
SL_NUM INT,
VENDOR_CODE CHAR(18),
VENDOR_DESC VARCHAR(55),
PO_NO CHAR(18),
--PO_SERIAL INT,
RATE NUMERIC(28,8),
ORD_QTY NUMERIC(28,8),
RECV_QTY NUMERIC(28,8),
CR_DATE DATETIME ,
FLAG CHAR(1)
)
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
Now without changing the order any further should do the grouping on change in vendor or the rate and sum order and received quantity.
November 6, 2012 at 3:04 am
Can you provide some sample data in a readily consumable form and expected results?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2012 at 3:37 am
SL_NUMVENDOR_CODEVENDOR_DESC PO_NORATEORD_QTYRECV_QTYCR_DATE
1 38 MEENA ENTERPRISES 271710 10 4/13/2012
2 828 MAHADEO PRASAD 634244 4 10/24/2011
3 38 MEENA ENTERPRISES 4531710 10 9/1/2011
4 38 MEENA ENTERPRISES 811710 10 4/29/2011
5 38 MEENA ENTERPRISES 10591720 20 11/22/2010
6 38 MEENA ENTERPRISES 4001720 20 6/15/2010
7 38 MEENA ENTERPRISES 15052310 10 3/22/2010
8 38 MEENA ENTERPRISES 3952510 10 10/3/2009
9 38 MEENA ENTERPRISES 1002510 10 8/1/2009
10 38 MEENA ENTERPRISES 67782510 10 6/26/2009
This is the data with cr_date sorted desc.
----Any the result should be :--
Sl Vendor Description Rate Ord Rcvd Date
138MEENA ENTERPRISES1710104/13/2012
2828MAHADEO PRASAD244410/24/2011
338MEENA ENTERPRISES1760609/1/2011
738MEENA ENTERPRISES2310103/22/2010
838MEENA ENTERPRISES25303010/3/2009
--Data is to be grouped on Vendor or rate change only.
November 6, 2012 at 6:33 am
goenka.vishal (11/6/2012)
SL_NUMVENDOR_CODEVENDOR_DESC PO_NORATEORD_QTYRECV_QTYCR_DATE1 38 MEENA ENTERPRISES 271710 10 4/13/2012
2 828 MAHADEO PRASAD 634244 4 10/24/2011
3 38 MEENA ENTERPRISES 4531710 10 9/1/2011
4 38 MEENA ENTERPRISES 811710 10 4/29/2011
5 38 MEENA ENTERPRISES 10591720 20 11/22/2010
6 38 MEENA ENTERPRISES 4001720 20 6/15/2010
7 38 MEENA ENTERPRISES 15052310 10 3/22/2010
8 38 MEENA ENTERPRISES 3952510 10 10/3/2009
9 38 MEENA ENTERPRISES 1002510 10 8/1/2009
10 38 MEENA ENTERPRISES 67782510 10 6/26/2009
This is the data with cr_date sorted desc.
----Any the result should be :--
Sl Vendor Description Rate Ord Rcvd Date
138MEENA ENTERPRISES1710104/13/2012
2828MAHADEO PRASAD244410/24/2011
338MEENA ENTERPRISES1760609/1/2011
738MEENA ENTERPRISES2310103/22/2010
838MEENA ENTERPRISES25303010/3/2009
--Data is to be grouped on Vendor or rate change only.
Actually what I meant was sample data from these 2 tables (and DDL for both):
PUR_PO_DETAIL DTL
PUR_PO_HEADER HDR
It doesn't matter what your current query produces. It probably should be changed from a (antiquated format) CROSS JOIN to an INNER JOIN anyway.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply