Using T-sql in a store procedure
-
Hi Everyone,
I am trying to use T-Sql in a store procedure to return a group of data
instead of using a simple select statement for two times.
The two select statements are:
--get data for an agent whose id is 12345
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, agentCode from vs
where year(txndatetime)='2004' and AgentCode='12345'
group by product, AgentCode
order by Product
--get data for all agents including agentcode is 12345
--and use 666666 to represent all agents' sale
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, agentCode='666666' from vs
where year(txndatetime)=@year
group by product
order by Product
Can you help?
Thank you.
Betty
-
Hi,
I got it. I just forgot union.
(select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, agent='66666' from vs
where year(txndatetime)='2004'
group by productUNION
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, agentCode from vs
where year(txndatetime)='2004' and agentCode='12345'
group by product, agentCode)
order by product
and that works.
Thank you!
-
Why not do
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End AS Agent from vs
where year(txndatetime) = 2004
group by product, agentCode
order by product
-
whoops Or even
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End AS Agent from vs
where year(txndatetime) = 2004
group by product, CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End
order by product
-
Hi Andrew,
Thanks for the posting, that code looks good to me.
But for '66666', it should include the sales from agent '12345' too.
What I like to have is
total sales for agent '12345'
Total sales for all agents including '12345'.
Can you still do it?
Betty
-
The UNION approach is good if you want the result in two rows.
If having the two answer as two fields in a single row will work, use:
SELECT COUNT(product) numPol, product,
cast(sum(order_total)/100.00 as Decimal(12,2)) txnSale
cast(sum(case when agentCode='12345' then order_total else 0 end)/100.00 as Decimal(12,2)) agentSales
FROM vs
WHERE year(txndatetime)=2004
GROUP BY product
ORDER BY product
In general, it is better to perform conversions and formatting on the result of SUM (or other aggregate function), rather than converting the value in each row and aggregating the result. Unless SUM(order_total) will result in a numeric overflow.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply