WITH ROLLUP sums ormula components, not results
-
I have the following query:
SELECT
COUNT(jsl.jslid) AS totalleads,
CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar)) AS datetime) AS slsDate,
SUM(CASE WHEN ls.lsAction = 4 THEN 1 ELSE 0 END) AS leadsFed,
SUM(CASE WHEN ls.lsAction = 3 THEN 1 ELSE 0 END) AS leadsErrored,
SUM(CASE WHEN ls.lsAction = 2 THEN 1 ELSE 0 END) AS leadsDeleted,
SUM(CASE WHEN ls.lsAction = 1 THEN 1 ELSE 0 END) AS leadsPending,
CONVERT(money, (SUM(CASE WHEN ls.lsAction = 4 THEN lpr.lpRate ELSE 0 END) + SUM(CASE WHEN ls.lsAction = 1 THEN lpr.lpRate ELSE 0 END)), 1) AS leadsRevenue,
CONVERT(money, ISNULL(((SUM(jst.Amount) - SUM(jst.ChargeBackAmt))/COUNT(jsl.jslid)), 0), 1) AS repSales,
CONVERT(money, ((SUM(CASE WHEN ls.lsAction = 4 THEN lpr.lpRate ELSE 0 END) + SUM(CASE WHEN ls.lsAction = 1 THEN lpr.lpRate ELSE 0 END)) + ISNULL(((SUM(jst.Amount) - SUM(jst.ChargeBackAmt))/COUNT(jsl.jslid)), 0)), 1) AS totalRevenue,
GROUPING(CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar)) AS datetime)) AS 'Primary'
FROM tblJobSeekerLeads jsl
INNER JOIN [USJobBoard System Database].dbo.valLeadStatus ls
ON ls.lsId = jsl.jslStatus
INNER JOIN [USJobBoard System Database].dbo.tblLeadPartner lpr
ON lpr.lpId = jsl.JSLLink
INNER JOIN [USJobBoard].dbo.xRelJobSeekerSales jss
ON jss.jsID = jsl.JSLJSId
INNER JOIN [USJSales].dbo.tblSLSUsers u
ON u.slsID = jss.jsSalesRepID AND u.slsID = 40
LEFT JOIN (SELECT CAST((CAST(YEAR(jstr.DateSubmitted) AS varchar) + CHAR(47) + CAST(MONTH(jstr.DateSubmitted) AS varchar) + CHAR(47) + CAST(DAY(jstr.DateSubmitted) AS varchar)) AS datetime) AS DateSubmitted, jstr.jsSalesRep, SUM(jstr.Amount)AS Amount, SUM(jstr.ChargeBackAmt) AS ChargeBackAmt
FROM [USJobBoard System Database].dbo.tblJSTransactions jstr
GROUP BY CAST((CAST(YEAR(jstr.DateSubmitted) AS varchar) + CHAR(47) + CAST(MONTH(jstr.DateSubmitted) AS varchar) + CHAR(47) + CAST(DAY(jstr.DateSubmitted) AS varchar)) AS datetime), jstr.jsSalesRep) jst
ON jst.jsSalesRep = jss.jsSalesRepID
AND jst.DateSubmitted >= CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar) + ' 00:00:00') AS datetime)
AND jst.DateSubmitted <= CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar) + ' 23:59:59') AS datetime)
WHERE jsl.jsldate >= {ts '2004-05-01 00:00:00'}
AND jsl.jsldate <= {ts '2004-05-31 23:59:59'}
GROUP BY CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar)) AS datetime)
WITH ROLLUP
The problem I am having is the repSales coumn (and apparently all columns, it just doesn't make a difference in most cases) ROLLUP is computed by aggregating each of the column values and then applying the formula. Unfortunately, in the majority of cases, Amount - ChargeBackAmt = 0 but totalleads is not. While this computes very well for individual datarecords, it skews the ROLLUP results.
Handling the ROLLUP in client-side code is not an option as this is a dynamically constructed query so the columns included are not known at program time.
Is there a way of getting ROLLUP or SQL to sum the formula results instead of the formula components and then applying the formula.
Thanks,
Robert
-
This would be a perfect example when it's a good thing to provide the table DDL's involved and a few rows of data showing the output along with the desired output.
I just have to ask one thing, though... Is this a query written by someone or is it generated code?
/Kenneth
-
I apologize. You are right, I should have provided results. Here they are:
table.cfdump_wddx,table.cfdump_xml,table.cfdump_struct,table.cfdump_array,table.cfdump_query,table.cfdump_cfc,table.cfdump_object,table.cfdump_binary,table.cfdump_udf,table.cfdump_udfbody,table.cfdump_udfarguments {font-size: xx-small;font-family: verdana, arial, helvetica, sans-serif;cell-spacing: 2;}table.cfdump_wddx th,table.cfdump_xml th,table.cfdump_struct th,table.cfdump_array th,table.cfdump_query th,table.cfdump_cfc th,table.cfdump_object th,table.cfdump_binary th,table.cfdump_udf th,table.cfdump_udfbody th,table.cfdump_udfarguments th {text-align: left;color: white;padding: 5;}table.cfdump_wddx td,table.cfdump_xml td,table.cfdump_struct td,table.cfdump_array td,table.cfdump_query td,table.cfdump_cfc td,table.cfdump_object td,table.cfdump_binary td,table.cfdump_udf td,table.cfdump_udfbody td,table.cfdump_udfarguments td {padding: 3;background-color: ffffff;vertical-align : top;}table.cfdump_wddx {background-color: 000000;}table.cfdump_wddx th.wddx {background-color: 444444;}table.cfdump_xml {background-color: 888888;}table.cfdump_xml th.xml {background-color: aaaaaa;}table.cfdump_xml td.xml {background-color: dddddd;}table.cfdump_struct {background-color: 0000cc ;}table.cfdump_struct th.struct {background-color: 4444cc ;}table.cfdump_struct td.struct {background-color: ccddff;}table.cfdump_array {background-color: 006600 ;}table.cfdump_array th.array {background-color: 009900 ;}table.cfdump_array td.array {background-color: ccffcc ;}table.cfdump_query {background-color: 884488 ;}table.cfdump_query th.query {background-color: aa66aa ;}table.cfdump_query td.query {background-color: ffddff ;}table.cfdump_cfc {background-color: ff0000;}table.cfdump_cfc th.cfc{background-color: ff4444;}table.cfdump_cfc td.cfc {background-color: ffcccc;}table.cfdump_object {background-color : ff0000;}table.cfdump_object th.object{background-color: ff4444;}table.cfdump_binary {background-color : eebb00;}table.cfdump_binary th.binary {background-color: ffcc44;}table.cfdump_binary td {font-size: x-small;}table.cfdump_udf {background-color: aa4400;}table.cfdump_udf th.udf {background-color: cc6600;}table.cfdump_udfarguments {background-color: dddddd;cell-spacing: 3;}table.cfdump_udfarguments th {background-color: eeeeee;color: 000000;}<script language=JavaScript>function dump ( obj ) {var out = '' ;if ( typeof obj == "object" ) {for ( key in obj ) {if ( typeof obj != "function" ) out += key + ': ' + obj + '
' ;}}}function cfdump_toggleRow(source) {//target is the right cellif(document.all) target = source.parentElement.cells[1];else target = source.parentNode.lastChild ;cfdump_toggleTarget( target, cfdump_toggleSource( source ) ) ;}function cfdump_toggleXmlDoc(source) {var caption = source.innerHTML.split( ' [' ) ;// toggle source (header)if ( source.style.fontStyle == 'italic' ) {// closed -> shortsource.style.fontStyle = 'normal' ;source.innerHTML = caption[0] + ' [short version]' ;source.title = 'click to maximize' ;switchShortToState = 'open' ;} else if ( source.innerHTML.indexOf('[short version]') != -1 ) {// short -> fullsource.innerHTML = caption[0] + ' [long version]' ;source.title = 'click to collapse' ;switchLongToState = 'open' ;switchShortToState = 'closed' ;} else {// full -> closedsource.style.fontStyle = 'italic' ;source.title = 'click to expand' ;source.innerHTML = caption[0] ;switchLongToState = 'closed' ;}// Toggle the target (everething below the header row).// First two rows are XMLComment and XMLRoot - they are part// of the long dump, the rest are direct children - part of the// short dumpif(document.all) {var table = source.parentElement.parentElement ;for ( var i = 1; i < table.rows.length; i++ ) {target = table.rows ;if ( i < 3 ) cfdump_toggleTarget( target, switchLongToState ) ;else cfdump_toggleTarget( target, switchShortToState ) ;}}else {var table = source.parentNode.parentNode ;var row = 1;for ( var i = 1; i < table.childNodes.length; i++ ) {target = table.childNodes ;if( target.style ) {if ( row < 3 ) {cfdump_toggleTarget( target, switchLongToState ) ;} else {cfdump_toggleTarget( target, switchShortToState ) ;}row++;}}}}function cfdump_toggleTable(source) {var switchToState = cfdump_toggleSource( source ) ;if(document.all) {var table = source.parentElement.parentElement ;for ( var i = 1; i < table.rows.length; i++ ) {target = table.rows ;cfdump_toggleTarget( target, switchToState ) ;}}else {var table = source.parentNode.parentNode ;for ( var i = 1; i < table.childNodes.length; i++ ) {target = table.childNodes ;if(target.style) {cfdump_toggleTarget( target, switchToState ) ;}}}}function cfdump_toggleSource ( source ) {if ( source.style.fontStyle == 'italic' ) {source.style.fontStyle = 'normal' ;source.title = 'click to collapse' ;return 'open' ;} else {source.style.fontStyle = 'italic' ;source.title = 'click to expand' ;return 'closed' ;}}function cfdump_toggleTarget ( target, switchToState ) {if ( switchToState == 'open' )target.style.display = '' ;else target.style.display = 'none' ;}</script>
query LEADSDELETED LEADSERRORED LEADSFED LEADSPENDING LEADSREVENUE PRIMARY REPSALES SLSDATE TOTALLEADS TOTALREVENUE 1 0 0 1 0 26.5000 0 0.0000 2004-05-01 00:00:00.0 1 26.5000 2 2 0 33 0 496.5000 0 0.0000 2004-05-03 00:00:00.0 35 496.5000 3 4 0 31 0 491.0000 0 59.9900 2004-05-04 00:00:00.0 35 550.9900 4 1 0 17 0 254.0000 0 0.0000 2004-05-05 00:00:00.0 18 254.0000 5 0 0 12 0 208.5000 0 59.9900 2004-05-06 00:00:00.0 12 268.4900 6 0 0 27 0 395.5000 0 0.0000 2004-05-07 00:00:00.0 27 395.5000 7 8 0 299 0 317.0000 0 0.0000 2004-05-10 00:00:00.0 307 317.0000 8 0 0 22 0 338.5000 0 0.0000 2004-05-11 00:00:00.0 22 338.5000 9 2 0 32 0 480.0000 0 0.0000 2004-05-12 00:00:00.0 34 480.0000 10 3 0 49 0 648.0000 0 189.9700 2004-05-13 00:00:00.0 52 837.9700 11 2 0 50 0 229.0000 0 0.0000 2004-05-14 00:00:00.0 52 229.0000 12 6 0 152 0 185.5000 0 0.0000 2004-05-15 00:00:00.0 158 185.5000 13 1 0 180 0 235.5000 0 0.0000 2004-05-16 00:00:00.0 181 235.5000 14 3 0 25 0 422.0000 0 0.0000 2004-05-17 00:00:00.0 28 422.0000 15 2 0 29 0 321.5000 0 0.0000 2004-05-18 00:00:00.0 31 321.5000 16 1 0 23 0 294.0000 0 0.0000 2004-05-19 00:00:00.0 24 294.0000 17 1 0 34 0 410.5000 0 0.0000 2004-05-20 00:00:00.0 35 410.5000 18 0 0 28 0 344.5000 0 0.0000 2004-05-21 00:00:00.0 28 344.5000 19 0 0 1 0 37.0000 0 0.0000 2004-05-22 00:00:00.0 1 37.0000 20 0 0 0 2 29.4900 0 0.0000 2004-05-24 00:00:00.0 2 29.4900 21 36 0 1045 2 6164.4900 1 11.7248 [empty string] 1083 6176.2148 The query is generated by a user selection. This particular query my be combined with other queries or replaced by other queries in the same page dpending on the users selections. Hence the reason the rollup has to work in the query because at program time I have no idea what the end query will be.
Thanks you,
Robert
-
Results are nice, but what also is needed is what the tables look like and some sample data to work on.. (not that I'm promising anything
)Well, I had to ask if it was generated code, because - frankly, it stinks.
(no offense to anyone though)
It's very hard to see what's going on, and at a first glance, there's some unnecessary things going on as well, which probably would only make things slower - never better, and perhaps even making results doubtful.
I mean, statistics is a sensitive subject - it requires that stuff gets grouped and calculated as intended - which basically is your problem right now.
Like this, which I can't make out why it's done this way...
(taken from the GROUP BY clause)
CAST((CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar)) AS datetime)
If I run this on my box, I get a date in the following format:
2004-06-09 00:00:00.000
I assume this is some kind of attempt to format the jslDate column (which I assume is datetime), but I wonder if this really fills any function...
If you take out the outermost CAST, you have this long concatenation;
(CAST(YEAR(jsl.jslDate) AS varchar) + CHAR(47) + CAST(MONTH(jsl.jslDate) AS varchar) + CHAR(47) + CAST(DAY(jsl.jslDate) AS varchar))
On my box this yields this format:
2004/6/9
However, when I cast '2004/6/9' as datetime, I end up with '2004-06-09 00:00:00.000', which is the default display style for my tool and server I happen to use right now...
So, if I really want a nice date format to work with, I'd suggest using CONVERT instead.
Assume that '2004-06-09 00:00:00.000' is what we want from jslDate - ie the date with all zeroes as time...
This would do the job then (instead of the looong multifunction concatenating thingy;
cast(convert(char(10), jsl.jslDate, 121) as datetime)
Oh well, enough ranting.
I guess that there's not much you can do about it anyway. 'Genereated' code is most of the times near impossible to tweak - one of the main reasons not to use 'generators' imo... 😐
To be constructive then...
Well, you know how the result should look...
I would probably take this query - rewrite it some (to make it more readable) and that way try to figure out what is happening and why (regarding how stuff is calculated).
If insight strikes, you then know what and why the problem is, and then also how to fix it.
(which is not the same thing as can be fixed, though)....
/Kenneth
-
Kenneth,
Thank you for your constructive comments. That long concatenation string was more of an evolution trying to get around some issues than real design. Your suggestions do really make it more readable. While it does not speak to the issue, which is how to get around the ROLLUP behavior of calculating the totals by determining the aggregate of the individual columns before applying the calculation rather than aggregating the results of each calculation. Anyway, after playing with it some more, the query now looks like this:
SELECT
COUNT(jsl.jslid) AS totalleads,
cast(convert(char(10), jsl.jslDate, 121) as datetime) AS slsDate,
SUM(CASE WHEN ls.lsAction = 4 THEN 1 ELSE 0 END) AS leadsFed,
SUM(CASE WHEN ls.lsAction = 3 THEN 1 ELSE 0 END) AS leadsErrored,
SUM(CASE WHEN ls.lsAction = 2 THEN 1 ELSE 0 END) AS leadsDeleted,
SUM(CASE WHEN ls.lsAction = 1 THEN 1 ELSE 0 END) AS leadsPending,
CONVERT(money, (SUM(CASE WHEN ls.lsAction = 4 THEN lpr.lpRate ELSE 0 END) + SUM(CASE WHEN ls.lsAction = 1 THEN lpr.lpRate ELSE 0 END)), 1) AS leadsRevenue,
CONVERT(money, ISNULL(((SUM(jst.Amount) - SUM(jst.ChargeBackAmt))/(CASE WHEN GROUPING(cast(convert(char(10), jsl.jslDate, 121) as datetime)) = 0 THEN COUNT(jsl.jslid) ELSE 1 END)), 0), 1) AS repSales,
CONVERT(money, ((SUM(CASE WHEN ls.lsAction = 4 THEN lpr.lpRate ELSE 0 END) + SUM(CASE WHEN ls.lsAction = 1 THEN lpr.lpRate ELSE 0 END)) + ISNULL(((SUM(jst.Amount) - SUM(jst.ChargeBackAmt))/(CASE WHEN GROUPING(cast(convert(char(10), jsl.jslDate, 121) as datetime)) = 0 THEN COUNT(jsl.jslid) ELSE 1 END)), 0)), 1) AS totalRevenue,
GROUPING(cast(convert(char(10), jsl.jslDate, 121) as datetime)) AS 'Primary'
FROM tblJobSeekerLeads jsl
INNER JOIN [USJobBoard System Database].dbo.valLeadStatus ls
ON ls.lsId = jsl.jslStatus
INNER JOIN [USJobBoard System Database].dbo.tblLeadPartner lpr
ON lpr.lpId = jsl.JSLLink
INNER JOIN [USJobBoard].dbo.xRelJobSeekerSales jss
ON jss.jsID = jsl.JSLJSId
INNER JOIN [USJSales].dbo.tblSLSUsers u
ON u.slsID = jss.jsSalesRepID AND u.slsID = 40
LEFT JOIN (SELECT cast(convert(char(10), jstr.DateSubmitted, 121) as datetime) AS DateSubmitted, jstr.jsSalesRep, SUM(jstr.Amount)AS Amount, SUM(jstr.ChargeBackAmt) AS ChargeBackAmt
FROM [USJobBoard System Database].dbo.tblJSTransactions jstr
GROUP BY cast(convert(char(10), jstr.DateSubmitted, 121) as datetime), jstr.jsSalesRep) jst
ON jst.jsSalesRep = jss.jsSalesRepID
AND jst.DateSubmitted >= cast(convert(char(10), jsl.jslDate, 121) as datetime)
AND jst.DateSubmitted <= cast((convert(char(10), jsl.jslDate, 121) + ' 23:59:59') as datetime)
WHERE jsl.jsldate >= {ts '2004-05-01 00:00:00'}
AND jsl.jsldate <= {ts '2004-05-31 23:59:59'}
GROUP BY (cast(convert(char(10), jsl.jslDate, 121) as datetime))
WITH ROLLUP
And provides these results:
query LEADSDELETED LEADSERRORED LEADSFED LEADSPENDING LEADSREVENUE PRIMARY REPSALES SLSDATE TOTALLEADS TOTALREVENUE 1 0 0 1 0 26.5000 0 0.0000 2004-05-01 00:00:00.0 1 26.5000 2 2 0 33 0 496.5000 0 0.0000 2004-05-03 00:00:00.0 35 496.5000 3 4 0 31 0 491.0000 0 59.9900 2004-05-04 00:00:00.0 35 550.9900 4 1 0 17 0 254.0000 0 0.0000 2004-05-05 00:00:00.0 18 254.0000 5 0 0 12 0 208.5000 0 59.9900 2004-05-06 00:00:00.0 12 268.4900 6 0 0 27 0 395.5000 0 0.0000 2004-05-07 00:00:00.0 27 395.5000 7 8 0 299 0 317.0000 0 0.0000 2004-05-10 00:00:00.0 307 317.0000 8 0 0 22 0 338.5000 0 0.0000 2004-05-11 00:00:00.0 22 338.5000 9 2 0 32 0 480.0000 0 0.0000 2004-05-12 00:00:00.0 34 480.0000 10 3 0 49 0 648.0000 0 189.9700 2004-05-13 00:00:00.0 52 837.9700 11 2 0 50 0 229.0000 0 0.0000 2004-05-14 00:00:00.0 52 229.0000 12 6 0 152 0 185.5000 0 0.0000 2004-05-15 00:00:00.0 158 185.5000 13 1 0 180 0 235.5000 0 0.0000 2004-05-16 00:00:00.0 181 235.5000 14 3 0 25 0 422.0000 0 0.0000 2004-05-17 00:00:00.0 28 422.0000 15 2 0 29 0 321.5000 0 0.0000 2004-05-18 00:00:00.0 31 321.5000 16 1 0 23 0 294.0000 0 0.0000 2004-05-19 00:00:00.0 24 294.0000 17 1 0 34 0 410.5000 0 0.0000 2004-05-20 00:00:00.0 35 410.5000 18 0 0 28 0 344.5000 0 0.0000 2004-05-21 00:00:00.0 28 344.5000 19 0 0 1 0 37.0000 0 0.0000 2004-05-22 00:00:00.0 1 37.0000 20 0 0 0 2 29.4900 0 0.0000 2004-05-24 00:00:00.0 2 29.4900 21 36 0 1045 2 6164.4900 1 12697.9700 [empty string] 1083 18862.4600 The issue now becomes to derive a value by which to divide the total repSales by that will yield the correct result. This may be more of a mathematical problem than a SQL problem. The overriding issue is caused by the entire query being driven by the call to tblJobSeekerLeads for which there may be multiple records for a given day. (This is not changeable). The tblJSTransactions data being used to calculate the repsales is not related to the tblJobSeekerLeads data other than that a given sales rep will produce leads and sales on a given day. Therefore the calculating of sales for a given day is done in it's own query, but the results are then duplicated for however many leads there are for that day. Hence the need for the dividing of the repSales by the jslead count. Such is the dillemma of a programmer when they are the servants of marketers and accountants.
Thank you again for you comments.
VR,
Robert
-
I agree with what Kenneth wrote.
Without looking deeply into the query, it looks like you want to rollup a grouping of a calc based on the count of that grouping.
The only thing I can suggest is to group the first level within a subquery (ie get values and counts) and then group the results with rollup.
Since it is generated code this is probably not possible, that is the rub, catch 22 and all that.
Far away is close at hand in the images of elsewhere.
Anon. -
I'm beginning to get the feel that the problem is basically that some values are calculated 'too many times'...
On way to get around such anomalies is to start from the 'inside' or 'the core' of details, grouping by the element that will produce the correct calculations for that attribute in a virtual table style, then gradually expand on that (several times if may be) joining yourself out, so to speak.
Here's a pseudoexample (with no meaning), just trying to show the construct.
select z.someOtherGrouping,
x.someGroup,
x.someGroupTotal
sum(z.somethingElse)
from
(
select someGroup, sum(someValue) as someGroupTotal
from myTable
group by someGroup
)x
join myTable z
on x.someGroup = z.someOtherGrouping
group by z.someOtherGrouping,
x.someGroup,
x.someGroupTotal
Admittedly this kind of stuff isn't all trivial to get together.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply