Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Subtotal when vendorcode changed. Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
Hi Experts,

i need to write a stored procedure for a table with below columns

for instance the table had 5 columns like

VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500


so am writing a Stored Proc to get all the table values like above ., but i need to write Stored Proc in such a way it returns
the above table like result along with subtotal when ever vendor code changed. as below

VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
subtoal 110 30 1200 ----- this subtoal should be displayed from Stored procedure
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
subtotal 25 105 600


please help me to acheive this

thank you.
Post #1536769
Posted Friday, January 31, 2014 9:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 592, Visits: 920
Please read the article in my signature on posting questions like this on the forum. Please provide the DDL statements along with the sample data inserts.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536825
Posted Friday, January 31, 2014 9:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 3,580, Visits: 8,035
Do you really need to do this in T-SQL? This is usually made in the front end.
If it's completely needed on T-SQL, you might need to union the detail with a query with subtotals adding a (non-visible) column to order the results so the subtotal will come after (or before if needed) the detail.
For a coded response, follow Keith's advice.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536839
Posted Friday, January 31, 2014 3:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
kishorefeb28 (1/31/2014)
Hi Experts,

i need to write a stored procedure for a table with below columns

for instance the table had 5 columns like

VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500


so am writing a Stored Proc to get all the table values like above ., but i need to write Stored Proc in such a way it returns
the above table like result along with subtotal when ever vendor code changed. as below

VendorCode | UnitPrice | Qty | Total(UnitPrice*Qty) |
--------------------------------------------------------
V001 | 100 | 10 | 1000
V001 | 10 | 20 | 200
subtoal 110 30 1200 ----- this subtoal should be displayed from Stored procedure
V222 | 20 | 5 | 100
V222 | 5 | 100 | 500
subtotal 25 105 600


please help me to acheive this

thank you.


Use GROUP BY WITH ROLLUP. See Books Online. As the others have suggested, if you want a coded answer, please see the first link in my signature line below under "Helpful Articles".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1536996
Posted Friday, January 31, 2014 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:51 PM
Points: 2, Visits: 17
Try
;with
T as (
SELECT VendorCode, 1 as Seq, UnitPrice, Qty, (UnitPrice * Qty) as Total
from Sales
union
SELECT VendorCode, 2, sum(UnitPrice), sum(Qty), sum(UnitPrice * Qty)
from Sales
group by VendorCode
)
SELECT case when Seq=1 then VendorCode else 'subtotal' end as VendorCode,
UnitPrice, Qty, Total
from T
order by T.VendorCode, Seq;

Post #1537020
Posted Friday, January 31, 2014 9:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
jdr.suporte (1/31/2014)
Try
;with
T as (
SELECT VendorCode, 1 as Seq, UnitPrice, Qty, (UnitPrice * Qty) as Total
from Sales
union
SELECT VendorCode, 2, sum(UnitPrice), sum(Qty), sum(UnitPrice * Qty)
from Sales
group by VendorCode
)
SELECT case when Seq=1 then VendorCode else 'subtotal' end as VendorCode,
UnitPrice, Qty, Total
from T
order by T.VendorCode, Seq;



There's just no need to hit the table twice. Seriously, lookup WITH ROLLUP.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1537054
Posted Tuesday, February 4, 2014 11:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
Thank you Luis

i found its not recommended so i have requested to handle it in the front end .
Post #1538060
Posted Wednesday, February 5, 2014 7:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
kishorefeb28 (2/4/2014)
i found its not recommended so i have requested to handle it in the front end .


You obviously didn't hear that from me. There's absolutely no reason why this can't be done in T-SQL and it's one less piece of managed code that you would have to resource, recompile, and repromote if you needed to make a change. Think of it as easy encapsulation.

First, you took more time trying to format your request than doing it the right way. If you had done it the right way, it wouldn't have deterred someone like me from actually giving it try for almost a week. The "Right Way" is documented in the in the first link in my signature line below under "Useful Links".

I'll do it for you this time only because you've been misled by someone into thinking that doing it in T-SQL is "Not Recommended". Yeah, there's a whole lot of people that will say that. I just don't happen to agree with them for the reasons previously mentioned.

Here's the test table setup if anyone else wants to play with some of the more advanced features of GROUP BY that came out in 2008. I kept this one at a 2005 (or up) level because it's a common question across all versions.

 SELECT *
INTO #TestTable
FROM (
SELECT 'V001',100, 10,1000 UNION ALL
SELECT 'V001', 10, 20, 200 UNION ALL
SELECT 'V222', 20, 5, 100 UNION ALL
SELECT 'V222', 5,100, 500
)d(VendorCode,UnitPrice,Qty,Total)
;

See? Not much more difficult than the formatting you tried to do.

Also, you wrote that you wanted sub-totals of the Unit Prices and the Qty's. Unless the parts (or whatever) are all identical, it makes no sense to sub-total or total those columns. With that in mind, I've written a CASE statement into the code for those two columns to output just a blank for sub-totals. That's easy to change, of course.

On to the code. The following produces the desired output (without the subtotals for 2 columns as per above). I personally don't believe you need the ORDER BY for this one but a lot of people get all bent out of shape if it's not there. Since it doesn't hurt anything by being there, I've included it.

WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER(ORDER BY VendorCode)
,VendorCode,UnitPrice,Qty,Total
FROM #TestTable
)
SELECT VendorCode = CASE
WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 0 THEN VendorCode
WHEN GROUPING(VendorCode) = 0 AND GROUPING(RowNum) = 1 THEN 'Sub Total'
WHEN GROUPING(VendorCode) = 1 AND GROUPING(RowNum) = 1 THEN 'Grand Total'
END
,UnitPrice = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(UnitPrice) AS VARCHAR(10)) ELSE '' END
,Qty = CASE WHEN GROUPING(RowNum) = 0 THEN CAST(SUM(Qty) AS VARCHAR(10)) ELSE '' END
,Total = SUM(Total)
--,MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum) --Uncomment this line to see how it all works
FROM cteEnumerate
GROUP BY VendorCode,RowNum WITH ROLLUP
ORDER BY MAX(RowNum),GROUPING(VendorCode),GROUPING(RowNum)
;

Here's the output from the above...

VendorCode  UnitPrice  Qty        Total
----------- ---------- ---------- -----------
V001 100 10 1000
V001 10 20 200
Sub Total 1200
V222 20 5 100
V222 5 100 500
Sub Total 600
Grand Total 1800

(7 row(s) affected)



I'd write a full blown explanation of the code for you, but you have to have some of the fun. I strongly recommend you lookup the things I used in Books Online so that you're not not so easily lured into believing in someone's recommendation that "it's not recommended" .


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538447
Posted Thursday, February 6, 2014 10:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 3,580, Visits: 8,035
Jeff Moden (2/5/2014)
kishorefeb28 (2/4/2014)
i found its not recommended so i have requested to handle it in the front end .


You obviously didn't hear that from me. There's absolutely no reason why this can't be done in T-SQL and it's one less piece of managed code that you would have to resource, recompile, and repromote if you needed to make a change. Think of it as easy encapsulation.

There's a simple reason of why would I do this on the front end. Formatting totals and subtotals is a lot simpler that way. That's, of course, depending on what the front end is.
As you say: "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." But it doesn't mean you should, what you need to do is to evaluate different options and now is time for the OP to decide.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1538765
Posted Thursday, February 13, 2014 5:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:18 AM
Points: 27, Visits: 96
THANK YOU.
Post #1541126
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse