case ...when
-
Hi all,
I am trying to write a store procedure to group total sales for certain agents. Some agents have sub-agents so I need to group them together, i.e. for agent 190000, I need to group all agents whose agent ID start with 19XXXX to this agent's total sale. But the Sql analyzer keeps complain about the bold part. I just don't know if there is an other way around.
Thank you.
Betty
Create Proc ProducerSubProducerSales @AgentCode varchar(15), @StartDate Datetime, @EndDate Datetime
As
Insert into temptSalesSummary
select @agentCode agentCode, orgname, '', count(order_total) TotalNumTrxns,
Cast(sum(Order_total)/100.00 As Decimal(12,2)) totalSales,
month(@startDate),
month(@EndDate),
year(@startDate),
year(@endDate),
@startdate,
@Enddate
from vs v join agentDetails D
on v.agentCode=d.agentID
Where
convert(char(10),txndatetime, 126) <= convert(char(10), @EndDate,126)
and convert(char(10),txndatetime, 126) >=convert(char(10), @StartDate,126)
and Select Case substring(@agentcode, 1, 2)
when '17' then v.agentCode like '17%'
When '19' then v.agentCode like '19%'
When '13' then v.agentCode like '13%'
End
order by sum(order_total) desc
Return
-
try this :
change your case statement to
and v.agentCode like substring(@agentcode, 1, 2) + '%'
-
Do only Agents with codes begining with 13, 17 and 19 have sub-agents? (or was this just an example?).
If all agents may have subagents, then this SQL comparison might be easier and also might support usage of an index on agent code:
where vs.agentcode like substring(@agentcode, 1, 2) + '%'
Date comparisons do not require a conversion to strings, so:
where convert(char(10),txndatetime, 126) =convert(char(10), @StartDate,126)
can more simply be:
where VS.txndatetime between @StartDate and @EndDate
Regarding "count(order_total)", this will provide a count of the different order_totals but if in table vs there are two identical order_totals, this would only be counted as one not as two. "count(*)" may be what you need.
Summing table VS as a seperate subquery would be clearer and also would not require to explictly list all of the non-aggregated columns within the group by:
Try this:
select @agentCode agentCode
, orgname
, ''
, AgentSales.TotalNumTrxns
, Cast(AgentSales.totalSales / 100.00 As Decimal(12,2)) As totalSales
, month(@startDate)
, month(@EndDate)
, year(@startDate)
, year(@endDate)
,@startdate
,@Enddate
from agentDetails
JOIN(select substring(vs.agentcode, 1, 2) as AgentCodePrefix
, count(*) AS TotalNumTrxns
, sum(Order_total as totalSales
from VS
-- This where condition may allow usage of an index
where vs.agentcode like substring(@agentcode, 1, 2) + '%' -- This may allow usage of an index
andVS.txndatetime between @StartDate and @EndDate
group by substring(vs.agentcode, 1, 2)
) AS AgentSales
-- value LIKE with leading constants may allow usage of an index
on agentDetails.agentcode like AgentSales.AgentCodePrefix + '%'
SQL = Scarcely Qualifies as a Language
-
"count(order_total)" will count 2 identical values as 2, not 1.
It will not count rows where order_total IS NULL.
I believe idea of
where convert(char(10),txndatetime, 126) <= convert(char(10), @EndDate,126)
and convert(char(10),txndatetime, 126) >=convert(char(10), @StartDate,126)
was to compare dates excluding time part.
Proper way to do it:
SET @StartDate = convert(datetime, convert(int, @StartDate -0.5))
SET @EndDate = convert(datetime, convert(int, @EndDate -0.5)) + convert(datetime, '23:59:59.997'
-- This points @startDate to the start of the day, @EndDate to the end of the day
select ...
where VS.txndatetime between @StartDate and @EndDate
_____________
Code for TallyGenerator -
Hi all,
Thanks all your input, this gets me very excited since this is my first post. I noticed that this is a very active forum. I will take a look at your replies.
The one above is just an example. But I forgot one thing, the first two characters of subagents' ID for certain group is not always the same as the master agent ID. i.e. The master AgentID 141333 actually has subagents with agentID start with 12XXXX. What should I do with this case?
Betty
-
Hi all,
The initial store procedure I want to write is: I need a list of agent sales during a certain period, from most to least. But among those agents, only several agents have subagents. The real case is:
Master Agent 190000 has subagent 19XXXX, so need group by 19XXXX
Master Agent 160000 has subagent 16XXXX, so need group by 16XXXX
Master Agent 131222 has subagent 17XXXX, so need group by 131222+17XXXX
all other agents group by itself.
I just don't know how I can write only a store procedure and get the result in one execution.
Any other idea will be very helpful.
Betty
-
You must have the rule you've just described in table, not in code of SP.
After that you may use simple joins in SP, and rules may be added or changed without changing code.
_____________
Code for TallyGenerator -
Hi Sergiy,
How do I set up those rules in table. Can you give me an example?
Thanks.
Betty
-
Something like this:
Code AgentCode
190000 19XXXX
160000 16XXXX
131222 131222
131222 17XXXX
But you know the rule better, you can do it more close to the real case.
_____________
Code for TallyGenerator -
Sergiy,
I think this's a great idea. The the way I understand you is as follow:
I can add one more column masterAgentID in the VS table besides the origianl agentID.
It will looks like this:
totalsales AgentID MasterAgentID
100.00 190000 190000
200.00 163457 160000
300.00 160000 160000
350.00 191234 190000
400.00 171222 131222
400.00 173333 131222
400.00 131222 131222
500.00 123456 123456
So later on, as long as I group sales by MasterAgentID, then I will solve all the cases, right
Thank you, thank you.
-
If the logic of determining the agents/subagents gets too complicated for the actual stored procedure, consider packaging it in a user-defined function which takes in a master agent ID and returns a table of the sub-agent IDs and then changing the part in the SP that currently says
and Select Case substring(@agentcode, 1, 2)
when '17' then v.agentCode like '17%'
When '19' then v.agentCode like '19%'
When '13' then v.agentCode like '13%'
End
to something like
and v.agentCode IN (SELECT agentCode FROM dbo.fnMyFunctionName(MasterAgentID))
I might be off on the SQL syntax here, but hopefully you get the idea. Ought to work...
-
Hi milzs,
That's another good way to do it. I will try it too.
Thank you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply