MS SQL Server - Tricky Stored Procedure Code

Author

Tricky Stored Procedure Code

shaun

2006-03-31, 7:24 am

Hi
Ok here is a challenge to the group, I have a stored procedure that I
use to create a report, its based on the following scenario

A user calls in a raises a log in tblLog
They are then sold stuff in tblSales
We want to track what they are sold based on what they had so tblLog
has flags showing the variou7s offers they already had at the start of
the call
Each sale creates a record in tblSales

So heres the issue, we join tblLog to tblSales
To get the number of calles we do a Count(DISTINCT
vu_tblOffer_On_Card.Card_No) AS CountOfCard_No

But because of the join, if we do a Sum(CASE & #91;Direct_Debit_Ind
] WHEN
"Y" THEN 1 ELSE 0 END) AS DD_Already then we do not get the correct
figure as in effect the join gives us multiple rows and so the original
enrollment occurs many times. For example if we sell 4 thinsg to the
caller, we will get 4 rows back for that card member and the above
statement gives 4 when we want 1

The actual code is below, anyone any ideas???

Cheers

SELECT vu_tblCAU_USER.User_Nm,
Count(DISTINCT vu_tblOffer_On_Card.Card_No) AS CountOfCard_No,

Sum(CASE & #91;Direct_Debit_Ind
] WHEN "Y" THEN 1 ELSE 0 END) AS
DD_Already,
Sum(CASE & #91;Membership_Rewar
ds_Ind] WHEN "Y" THEN 1 ELSE 0 END) AS
MR_Already,
Sum(CASE & #91;Supplementary_Ca
rds_Ind] WHEN "Y"THEN 1 ELSE 0 END) AS
Supp_Already,

Sum(CASE & #91;Additional_Detai
ls_Required_Ind] WHEN "D" THEN CASE
& #91;Taken_Up_Ind]WHE
N "Y" THEN 1 ELSE 0 END ELSE 0 END) AS DD_Enrolled,
Sum(CASE & #91;Additional_Detai
ls_Required_Ind] WHEN "M" THEN CASE
& #91;Taken_Up_Ind]WHE
N "Y" THEN 1 ELSE 0 END ELSE 0 END) AS MR_Enrolled,
Sum(CASE & #91;Additional_Detai
ls_Required_Ind] WHEN "S" THEN CASE
& #91;Taken_Up_Ind]WHE
N "Y" THEN 1 ELSE 0 END ELSE 0 END) AS Supp_Enrolled,

FROM vu_tblCAU_USER
INNER JOIN vu_tblOffer_On_Card
INNER JOIN vu_tblCARD_LIST ON vu_tblOffer_On_Card.Card_No =
vu_tblCARD_LIST.Card_No
INNER JOIN vu_tblOffer ON vu_tblOffer_On_Card.Offer_Id =
vu_tblOffer.Offer_Id
INNER JOIN vu_tblCAU_Case ON vu_tblOffer_On_Card.Card_No =
vu_tblCAU_Case.Card_No
ON vu_tblCAU_USER.User_Id = vu_tblCAU_Case.Update_User_Id

WHERE vu_tblCAU_Case.Card_Market_Cd = @Card_Market_Cd AND
Report_Ts >= @From_Date AND
Report_Ts <= @To_Date AND
vu_tblCAU_USER.User_Id = CASE @Update_User_Id WHEN 0 THEN
vu_tblCAU_USER.User_Id ELSE @Update_User_Id END AND
Product_Line_Id = CASE @Product_Line_Id WHEN 0 THEN Product_Line_Id
ELSE @Product_Line_Id END AND
SUBSTRING([vu_tblCAU_Case].[Card_No],13,2) = '00' AND
vu_tblCAU_Case.Nonviable_Call_Ind="N" AND
vu_tblCAU_Case.Stop_Calls_Ind="A" AND
(vu_tblOffer. Additional_Details_R
equired_Ind="D" Or
vu_tblOffer. Additional_Details_R
equired_Ind="S" Or
vu_tblOffer. Additional_Details_R
equired_Ind="M")
GROUP BY vu_tblCAU_USER.User_Nm

Hugo Kornelis

2006-04-01, 8:23 pm

On 31 Mar 2006 05:08:46 -0800, shaun wrote:

(snip)
>So heres the issue, we join tblLog to tblSales
>To get the number of calles we do a Count(DISTINCT
>vu_tblOffer_On_Card.Card_No) AS CountOfCard_No
>
>But because of the join, if we do a Sum(CASE & #91;Direct_Debit_Ind
] WHEN
>"Y" THEN 1 ELSE 0 END) AS DD_Already then we do not get the correct
>figure as in effect the join gives us multiple rows and so the original
>enrollment occurs many times. For example if we sell 4 thinsg to the
>caller, we will get 4 rows back for that card member and the above
>statement gives 4 when we want 1
>
>The actual code is below, anyone any ideas???

Hi Shaun,

I do have some ideas, but I can't tell which of those ideas can be
applied in your sitaution. It's also hard to explain, since:

a) You didn't post the structure of the tables used (please include
CREATE TABLE statements in your followup),
b) You didn't include sample data (please post INSERT statements as
well),
c) The table names in your description don't match the table names in
the query, and the names in the query are too cryptic for me to
decipher,
d) The query uses no less than five tables, whereas your description
mentions only two (please try to trim down the query to a simplified
version that still shows the problem but uses as little tables as
possible, and that doesn't select data that's not related to the problem
at hand).

See www.aspfaq.com/5006 to find out what information you should include
with your request for help. Additionally, consider that the more you are
able to trim the problem down to its bare bones before posting, the
better your chances are to get a useful answer in little time.

--
Hugo Kornelis, SQL Server MVP

jcelko212@earthlink.net

2006-04-02, 1:23 pm

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Then you have some silly prefixes on data elements in violation of
ISO-11179 rules. I can figure out the "tbl-" , but is "vu_" a VIEW?
And did you mean to use double quotes, so that those single letter
references to scalar variables and not character strings? You also
seem to write with a lot of flags and miss all of the shorthand
predicates that make SQL readable. Your capitalization of reserved
words is irregular.

For a start, I just re-formatted your code, but I thnk you have some
real design problems under the covers.

SELECT CU.user_nm,
COUNT(DISTINCT CO.card_nbr) AS card_cnt,
SUM(CASE WHEN direct_debit_ind = 'Y'
THEN 1 ELSE 0 END) AS dd_already,
SUM(CASE WHEN membership_rewards_i
nd ='Y'
THEN 1 ELSE 0 END) AS mr_already,
SUM(CASE WHEN supplementary_cards_
ind = 'Y'
THEN 1 ELSE 0 END) AS supp_already,
SUM(CASE WHEN additional_details_r
equired_ind = 'D'
AND taken_up_ind = 'Y'
THEN 1 ELSE 0 END AS dd_enrolled,
SUM(CASE WHEN additional_details_r
equired_ind = 'M'
AND taken_up_ind = 'Y'
THEN 1 ELSE 0 END AS mr_enrolled,
SUM(CASE WHEN additional_details_r
equired_ind = 'S'
AND taken_up_ind = 'Y'
THEN 1 ELSE 0 END AS supp_enrolled
FROM Cau_Users AS CU,
Offer_On_Card AS CO.
Card_List AS L,
Cau_Case AS CC,
Offers AS O
WHERE CO.card_nbr = L.card_nbr
AND CO.offer_id = O.offer_id
AND CO.card_nbr = CC.card_nbr
AND CU.user_id = CC.update_user_id
AND CC.card_market_cd = @card_market_cd
AND report_ts BETWEEN @from_date AND @to_date
AND CU.user_id = COALESCE (@update_user_id, CU.user_id)
AND product_line_id
= COALESCE (@product_line_id, product_line_id)
AND SUBSTRING(CC.card_nbr,13, 2) = '00'
AND CC.nonviable_call_ind = 'N'
AND CC.stop_calls_ind = 'A'
AND O. additional_details_r
equired_ind
IN ('D', 'S', 'M')
GROUP BY CU.user_nm;

Sponsored Links