Does this need a cursor? - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Does this need a cursor?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-03 : 14:33:21

Here is the latest quandry:
I wrote up this query to for the sales department, but have run into a little problem. On execution I get "subquery returned more than 1..."
It is flagged for both subqueries. Does this need to be re-typed with a cursor, or is there a better way to do this?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 14:36:19

maybe you should show us the query.

Go with the flow & have fun! Else fight the flow

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-03 : 14:57:49

Oh!
What a Dumb A** I am!
here we go:

--- BELOW SELECTS LINES AND CALCULATES PROFIT AS A PERCENTAGE BY LINES ON AN INVOICE---
BEGIN
DECLARE 
@PCT NUMERIC,
@PROFIT_AMT NUMERIC,
@COMMISSION NUMERIC
SET @PCT=
(select case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)<>0
then cast(((reg_prc-cost)/cost)as numeric)*100
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost)/cost)as numeric)*100
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then (((prc-cost)/cost)*100)
end
FROM PS_TKT_HIST_LIN)
SET @PROFIT_AMT=
(SELECT
case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost))as numeric)
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then((prc-cost))
end
FROM PS_TKT_HIST_LIN)
SET @COMMISSION=
(SELECT
CASE
WHEN COALESCE (@PCT,0) BETWEEN 0 AND 20
THEN 0
WHEN COALESCE(@PCT,0) BETWEEN 21 AND 25
THEN (@PROFIT_AMT/100)
WHEN COALESCE(@PCT,0) BETWEEN 26 AND 28
THEN ((@PROFIT_AMT/100)*2)
WHEN COALESCE(@PCT,0)BETWEEN 29 AND 32
THEN ((@PROFIT_AMT/100)*3)
WHEN COALESCE(@PCT,0)>32
THEN ((@PROFIT_AMT/100)*3.5)
END
)
SELECT
PS_TKT_HIST.SLS_REP,ps_tkt_hist_lin.cost,ps_tkt_hist_lin.tkt_no,
ps_tkt_hist_lin.item_no, im_item.categ_cod,@PCT AS PCT,
@PROFIT_AMT AS PROFIT_AMT,@COMMISSION AS COMMISSION
from PS_TKT_HIST 
RIGHT OUTER JOIN PS_TKT_HIST_LIN
ON PS_TKT_HIST.STR_ID=PS_TKT_HIST_LIN.STR_ID
AND PS_TKT_HIST.STA_ID=PS_TKT_HIST_LIN.STA_ID
AND PS_TKT_HIST.TKT_NO=PS_TKT_HIST_LIN.TKT_NO
join im_item
on ps_tkt_hist_lin.item_no=im_item.item_no
and im_item.item_no<>'NOTE'
and im_item.categ_cod<>'WALAB'
and im_item.categ_cod<>'RELAB'
END

all this, and good looks, too!

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 15:01:38

just add those case statments to you main select instead of variables.
it should do the trick.

Go with the flow & have fun! Else fight the flow

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-03 : 15:24:12

I originally did it this way so I could do the commission amount calculation on the two variables:
@commission is calsculated off of @pct and @profit_amt. If I don't do them as a variable, how can I reference them? Or am I having a mycodis crappus moment?

There's never enough time to type code right,
but always enough time for a hotfix...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 15:40:04

how bout:
select pct*profit_amt as commission, *
from (your sql here) t1

Go with the flow & have fun! Else fight the flow

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-03 : 16:19:47

Ok,
Now that you've shown me that I like to do things twice, let me explain:
We need to expand this

SET @COMMISSION=
(SELECT
CASE
WHEN COALESCE (@PCT,0) BETWEEN 0 AND 20
THEN 0
WHEN COALESCE(@PCT,0) BETWEEN 21 AND 25
THEN (@PROFIT_AMT/100)
WHEN COALESCE(@PCT,0) BETWEEN 26 AND 28
THEN ((@PROFIT_AMT/100)*2)
WHEN COALESCE(@PCT,0)BETWEEN 29 AND 32
THEN ((@PROFIT_AMT/100)*3)
WHEN COALESCE(@PCT,0)>32
THEN ((@PROFIT_AMT/100)*3.5)
END

into about 15 different percentages. What the select is doing is first calculating the percentage of profit (The boss wants this in there, too) and then calculating the amount of commission based on the variables above. If I take out the above code, It works fine. When I add the above stuff, I get "invalid column name PCT" and invalid column name profit_amt".
What am I missing?

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-03 : 17:24:32

Another way to look at this:

--- BELOW SELECTS LINES AND CALCULATES PROFIT AS A PERCENTAGE BY LINES ON AN INVOICE---
select
case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)<>0
then cast(((reg_prc-cost)/cost)as numeric)*100
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost)/cost)as numeric)*100
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then (((prc-cost)/cost)*100)
end
as pct,
case
when coalesce(prc,0)=0 and coalesce(reg_prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(reg_prc,0)<>0 and coalesce(cost,0)<>0
then cast(-((reg_prc-cost))as numeric)
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then((prc-cost))
end
as profit_amt,
PS_TKT_HIST.SLS_REP,ps_tkt_hist_lin.cost,ps_tkt_hist_lin.tkt_no,
ps_tkt_hist_lin.item_no, im_item.categ_cod
from PS_TKT_HIST 
RIGHT OUTER JOIN PS_TKT_HIST_LIN
ON PS_TKT_HIST.STR_ID=PS_TKT_HIST_LIN.STR_ID
AND PS_TKT_HIST.STA_ID=PS_TKT_HIST_LIN.STA_ID
AND PS_TKT_HIST.TKT_NO=PS_TKT_HIST_LIN.TKT_NO
join im_item
on ps_tkt_hist_lin.item_no=im_item.item_no
and im_item.item_no<>'NOTE'
and im_item.categ_cod<>'WALAB'
and im_item.categ_cod<>'RELAB'

This works fine. How do I incorporate this:


SELECT
CASE
WHEN COALESCE (PCT,0) BETWEEN 0 AND 20
THEN 0
WHEN COALESCE(PCT,0) BETWEEN 21 AND 25
THEN (PROFIT_AMT/100)
WHEN COALESCE(PCT,0) BETWEEN 26 AND 28
THEN ((PROFIT_AMT/100)*2)
WHEN COALESCE(PCT,0)BETWEEN 29 AND 32
THEN ((PROFIT_AMT/100)*3)
WHEN COALESCE(PCT,0)>32
THEN ((PROFIT_AMT/100)*3.5)
END
AS COMMISSION

Into it? If I add it to the SELECT statement I keep getting 'invalid column' errors for PCT and PROFIT_AMT. If I add it as a variable, then I think it gets more complicated than need be.
WAAAH!!
(I myself vote for more coffee...)

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-04 : 10:06:40

The problem here is how to deal with the divide by zero error trying to get the COMMISSION column. We need to calculate the percentage of profit, then calculate the amount of commission off of that. Since we can't use derived columns in the commission calculation( That's the invalid column error) then we need to do the calculations for commission differently. Problem is, we keep hitting divide by zero errors (salespeople love to give things out at bizarre prices apparently).
SO...
Anybody have any ideas about how to go about this and avoid the dreaded divide by monster?

There's never enough time to type code right,
but always enough time for a hotfix...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-04 : 10:33:32

supply sample input data......and expected results....covering all situations...or even write it down for yourself and push each case through your SQL....

that should help you identify the cases where you are going to hit 'div by 0'...and to write out the formula for each scenario.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-04 : 13:13:10

Hey Andrew,
Ok, I got the divide by zero issue sorted out. NOW I have the last piece of this puzzle to sort out.
Here is the new, improved query:

--- BELOW SELECTS LINES AND CALCULATES PROFIT AS A PERCENTAGE BY LINES ON AN INVOICE---
select
-- First we show our profit as a percentage--
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
then ((prc-cost)/cost)*100
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then (((prc-cost)/cost)*100)
end
as pct,
-- show the dollar amount (+ or -) of profit--
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
then -((reg_prc-cost))
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then((prc-cost))
end
as profit_amt,
-- THIS STATEMENT CALCULATES COMMISION--
CASE
-- If the selling price is zero --
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
-- take potential sale away from commission total -- 
then (-(reg_prc-cost))
-- if the selling price is > zero --
-- an profit is 20% or less --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100)<20.00
-- pay no commission --
then 0
-- if profit is 20.01% to 25.00% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100) between 20.01 and 25.00
-- pay 1% commission --
then ((prc-cost)/100)
-- if profit is 25.01% to 30.00% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100) between 25.01 and 30.00
-- pay 2% commission --
then ((prc-cost)/50)
-- if profit is greater than 30.01% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100)>30.01
-- pay 3% commission --
then ((prc-cost)/33.33)
END
AS COMMISSION,
(po_ord_hdr.ord_misc_amt_1/max(po_ord_lin.seq_no))as frt,
--return our additional columns for reporting --
PS_TKT_HIST.SLS_REP,ps_tkt_hist_lin.reg_prc,ps_tkt_hist_lin.cost,
ps_tkt_hist_lin.prc,ps_tkt_hist_lin.tkt_no,ps_tkt_hist_lin.item_no, im_item.categ_cod
from PS_TKT_HIST 
RIGHT OUTER JOIN PS_TKT_HIST_LIN
ON PS_TKT_HIST.STR_ID=PS_TKT_HIST_LIN.STR_ID
AND PS_TKT_HIST.STA_ID=PS_TKT_HIST_LIN.STA_ID
AND PS_TKT_HIST.TKT_NO=PS_TKT_HIST_LIN.TKT_NO
join im_item
on ps_tkt_hist_lin.item_no=im_item.item_no
right outer join po_ord_lin
on po_ord_lin.commnt_1=ps_tkt_hist.tkt_no
right outer join po_ord_hdr
on po_ord_hdr.po_no=po_ord_lin.po_no
-- disallow notes,labor, and extended service plans from commission --
where im_item.item_no<>'NOTE'
and im_item.categ_cod<>'WALAB'
and im_item.categ_cod<>'RELAB'
and im_item.subcat_cod<>'ESPS'

group by PS_TKT_HIST_LIN.TKT_NO,PS_TKT_HIST.SLS_REP,PS_TKT_HIST_LIN.PRC,
PS_TKT_HIST_LIN.COST,PS_TKT_HIST_LIN.REG_PRC,
PS_TKT_HIST_LIN.ITEM_NO,im_item.CATEG_COD,po_ord_hdr.ord_misc_amt_1
order by ps_tkt_hist_lin.tkt_no

The frt calculation is messed up. What we have is this:
each line in PS_TKT_HIST_LIN may have an associated row in PO_ORD_LIN. What we want to do is: count up the number of lines on a given PO, divide the amount of freight charges on that po (PO_ORD_HDR.ORD_MISC_AMT_1) by the number of lines on that po, and return that amount for the item. Even if I do this:

select po_ord_hdr.po_no,po_ord_lin.item_no,
max(po_ord_lin.seq_no) as frt
from po_ord_hdr
right outer join po_ord_lin
on po_ord_hdr.po_no=po_ord_lin.po_no
group by po_ord_hdr.po_no,po_ord_lin.item_no

It returns something like this:
PO_NO ITEM_NO FRT
-------------------
123 abc 4
123 def 4
123 efg 2
123 ghi 1
123 jkl 5

What we need is for frt to be all '5' in order for the divide to work. We don't really need to return this in the select; what is ultimatley needed is for the freight calculation to be subtracted from the amount of profit on each line and also from the amount we calculate profit precentage and commission on (the green fields above)
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-05 : 06:03:40

1. Can you pre-calc and save the "freight" column....ie as a stage 1 of a 2-part "calculate our profit" process??...then you can just reference it direct?...otherwise you have to calculate it as per 2 below.

2. search here for running total and/or percentages....other solutions have been posted before.

3. your code is grand....but what about the future? what will happen if some fancy sales director wants to change the commision rules 2/3 months down the line? he/she will need to bring you back in....(great idea for ensuring permanency!!!)....however it might be neater/better in the long run if you could put the 'commission rules' (ie thresholds + pct's) into a (properly indexed) data table to which you could 'join' your raw data to derive your desired end result....

then by simply adding/amending the 'rules' you can change the end result...with no further programming....

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-01-05 : 11:14:46

Ok Andrew,
Check out the most recent draft:

--- BELOW SELECTS LINES AND CALCULATES PROFIT AS A PERCENTAGE BY LINES ON AN INVOICE---
select
-- First we show our gross profit as a percentage--
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
then ((prc-cost)/cost)*100
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then ((prc-cost)/cost)*100
end
as gross_pct,
-- now show the net profit percentage--
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)=0
then ((prc-cost)/cost)*100
when coalesce(prc,0)=0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)<>0
then ((prc-(cost+((po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no)))))/cost)*100
when coalesce(prc,0)<>0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)=0
then ((prc-cost)/cost)*100
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)<>0
then ((prc-(cost+((po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no)))))/cost)*100
end
as net_pct,
-- show the gross dollar amount (+ or -) of profit--
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
then -((reg_prc-cost))
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
then((prc-cost))
end
as gross_profit_amt,
-- now show our net dollar amount(+ or-) of profit --
case
when coalesce(prc,0)=0 and coalesce(cost,0)=0
then NULL
when coalesce (prc,0)=0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)=0
then -((reg_prc-cost))
when coalesce(prc,0)=0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)<>0
then -((reg_prc-cost)-(po_ord_hdr.ord_misc_amt_1/(select max (po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no)))
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)=0
then ((prc-cost))
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0 and coalesce(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no),0)<>0
then((prc-(cost-(po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no)))))
end
as net_profit_amt,
-- THIS STATEMENT CALCULATES COMMISSION--
CASE
-- If the selling price is zero --
when coalesce(prc,0)=0 and coalesce(cost,0)<>0
-- take potential sale away from commission total -- 
then (-(reg_prc-cost))
-- if the selling price is > zero --
-- an profit is 20% or less --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100)<20.00
-- pay no commission --
then 0
-- if profit is 20.01% to 25.00% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100) between 20.01 and 25.00
-- pay 1% commission --
then ((prc-cost)/100)
-- if profit is 25.01% to 30.00% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100) between 25.01 and 30.00
-- pay 2% commission --
then ((prc-cost)/50)
-- if profit is greater than 30.01% --
when coalesce (prc,0)<>0 and coalesce(cost,0)<>0
and (((prc-cost)/cost)*100)>30.01
-- pay 3% commission --
then ((prc-cost)/33.33)
END
AS COMMISSION,
--calculate the amount of freight per line item --
'frt'=
case
when coalesce(po_ord_hdr.ord_misc_amt_1,0)<>0 
and po_ord_lin.commnt_1=ps_tkt_hist.orig_ord_no
then po_ord_hdr.ord_misc_amt_1/(select max(po_ord_lin.seq_no)from po_ord_lin
where po_ord_lin.po_no=po_ord_hdr.po_no)
end,
--return our additional columns for reporting --
po_ord_hdr.po_no,PS_TKT_HIST.SLS_REP,ps_tkt_hist_lin.reg_prc,ps_tkt_hist_lin.cost,
ps_tkt_hist_lin.prc,ps_tkt_hist_lin.tkt_no,ps_tkt_hist.orig_ord_no,
ps_tkt_hist_lin.item_no, im_item.categ_cod
from PS_TKT_HIST 
RIGHT OUTER JOIN PS_TKT_HIST_LIN
ON PS_TKT_HIST.STR_ID=PS_TKT_HIST_LIN.STR_ID
AND PS_TKT_HIST.STA_ID=PS_TKT_HIST_LIN.STA_ID
AND PS_TKT_HIST.TKT_NO=PS_TKT_HIST_LIN.TKT_NO
left outer join po_ord_lin
on po_ord_lin.commnt_1=ps_tkt_hist.orig_ord_no
and po_ord_lin.item_no=ps_tkt_hist_lin.item_no
left outer join po_ord_hdr
on po_ord_hdr.po_no=po_ord_lin.po_no
join im_item
on ps_tkt_hist_lin.item_no=im_item.item_no
-- disallow notes,labor, extended service plans, warranty and service tickets from commission --
where ps_tkt_hist_lin.item_no<>'NOTE'
and ps_tkt_hist_lin.categ_cod<>'WALAB'
and ps_tkt_hist_lin.categ_cod<>'RELAB'
and ps_tkt_hist_lin.subcat_cod<>'ESPS'
and ps_tkt_hist.has_warr_items<>'Y'
and ps_tkt_hist.is_svc_call<>'Y'

group by PS_TKT_HIST_LIN.TKT_NO,PS_TKT_HIST.SLS_REP,PS_TKT_HIST_LIN.PRC,po_ord_hdr.po_no,
PS_TKT_HIST_LIN.COST,PS_TKT_HIST_LIN.REG_PRC,po_ord_lin.commnt_1,
PS_TKT_HIST_LIN.ITEM_NO,im_item.CATEG_COD,po_ord_hdr.ord_misc_amt_1,ps_tkt_hist.orig_ord_no,po_ord_lin.item_no
order by ps_tkt_hist_lin.tkt_no

This sure is a mouthfull! This query works as requested. In actuality this will be added to a procedure, and things like commission amounts and percentage breakpoints will be definable via the app. There is much concern about being able to 'tamper' with the math (I'm not really sure why everyone is concerned, but they are) and as such want everything formatted like this. Ugly, yes. but it looks like it will do the job..
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources