SQL Complex Query

CaptainGiblets
CaptainGiblets used Ask the Experts™
on
I'm struggling to write a query to return a very specific set of data and could do with some help in achieving the desired results.


I have four sets of data, structurally similar, in 4 different tables, each in their own database on the same SQLServer 2008 server.

Here's an example of the relevant fields from each table:
Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4

What I need to do is get the individual price (TotalPrice1 / Qty1) for each TotalPrice and fit it into a band based on the value:
0-150, 151-200, 201-250, 251+

Then I want to display a total of the Qty within each band grouped by the Name field, so I should have something like this:

Name  Band      Qty
John    0-150        5
John    151-200    12
John    201-250    19
John    251+         3
Alex    0-150....


I guess my first question would be - is this even possible?
If so, how do I get started and which commands do I need to look into?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
sounds "doable"

could we see the definitions of the 4 tables and samples of data from each - otherwise we assume stuff.

The more you give us, the more likely it will be usable
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
To get the ball rolling, it MIGHT look like this:
select
Name, Band, sum(Qty1) as Qty1_Sum
from (
        select
        Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 
        , case
            when  (TotalPrice1 / Qty1) >= 0   AND (TotalPrice1 / Qty1) < 151 then '0-150'
            when  (TotalPrice1 / Qty1) >= 151 AND (TotalPrice1 / Qty1) < 201 then '151-200'
            when  (TotalPrice1 / Qty1) >= 201 AND (TotalPrice1 / Qty1) < 251 then '201-250'
            when  (TotalPrice1 / Qty1) >= 251                                then '251+'
            else 'bad data'
          end as band
        from (
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 from tableA
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 from tableB
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 from tableC
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 from tableC
             )
group by Name, Band
order by Name, Band /* sorting on band "might" be weird */

Open in new window

is this somewhat close? please look carefully at the values used to decide the "bands" - may not be 100%
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
not sure how close the query above is, but with a bit more polish it might be like this:
select
  Name
, band_num
, case
    when band_num = -1 then 'data error'
    when band_num = 1  then '0-150'
    when band_num = 2  then '151-200'
    when band_num = 3  then '201-250'
    when band_num = 4  then '251+'
  end as Band
, sum(Qty1) as Qty1_Sum
from (
        select
-- more fields listed than needed perhaps?, reduce selection list to MINIMUM neede! 
        Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 
        , case
            when  (TotalPrice1 / Qty1) >= 0   AND (TotalPrice1 / Qty1) < 151 then 1
            when  (TotalPrice1 / Qty1) >= 151 AND (TotalPrice1 / Qty1) < 201 then 2
            when  (TotalPrice1 / Qty1) >= 201 AND (TotalPrice1 / Qty1) < 251 then 3
            when  (TotalPrice1 / Qty1) >= 251                                then 4
            else -1
          end as band_num
        from (
-- more fields listed than needed perhaps?, reduce selection list to MINIMUM neede! 
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4 
                from tableA
-- need a where here?
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4
                from tableB
-- need a where here?
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4
                from tableC
-- need a where here?
                union all
                select Name, Qty1, Qty2, Qty3, Qty4, TotalPrice1, TotalPrice2, TotalPrice3, TotalPrice4
                from tableD
-- need a where here?
             )
group by
  Name
, band_num
, case
    when band_num = -1 then 'data error'
    when band_num = 1  then '0-150'
    when band_num = 2  then '151-200'
    when band_num = 3  then '201-250'
    when band_num = 4  then '251+'
  end
order by
  Name
, band_num /* using a number may produce a more predictable sort sequence */

Open in new window

here, by using a band number instead of a string label, the output sort order is likely to be more predictable.

Please note the embedded questions, you can embed filtering on each of the 4 tables involved (if needed) and if you don't need a filed in the final output reduce the fields being selected in all parts of the query.

Hope this is helpful.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I'll try implementing these to get the results and come back with more information if I can't kick it into gear.

Thanks for the quick responses!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
From a performance standpoint, I think you'd be much better off summarizing each table separately, then join those summaries, as below:



SELECT
    COALESCE(t1.Name, t2.Name, t3.Name, t4.Name) AS Name,
    SUM(ISNULL(t1.[0-150], 0) + ISNULL(t2.[0-150], 0) + ISNULL(t3.[0-150], 0) + ISNULL(t4.[0-150], 0)) AS [0-150],
    SUM(ISNULL(t1.[151-200], 0) + ISNULL(t2.[151-200], 0) + ISNULL(t3.[151-200], 0) + ISNULL(t4.[151-200], 0)) AS [151-200],
    SUM(ISNULL(t1.[201-250], 0) + ISNULL(t2.[201-250], 0) + ISNULL(t3.[201-250], 0) + ISNULL(t4.[201-250], 0)) AS [201-250],
    SUM(ISNULL(t1.[250+], 0) + ISNULL(t2.[250+], 0) + ISNULL(t3.[250+], 0) + ISNULL(t4.[250+], 0)) AS [250+]
FROM (
    SELECT
        Name,
        SUM(CASE WHEN Qty1 = 0 THEN 1 WHEN TotalPrice1 / Qty1 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 1 WHEN TotalPrice2 / Qty2 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 1 WHEN TotalPrice3 / Qty3 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 1 WHEN TotalPrice4 / Qty4 <= 150 THEN 1 ELSE 0 END)
        AS [0-150],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 150 AND TotalPrice1 / Qty1 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 150 AND TotalPrice2 / Qty2 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 150 AND TotalPrice3 / Qty3 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 150 AND TotalPrice4 / Qty4 <= 200 THEN 1 ELSE 0 END)
        AS [151-200],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 200 AND TotalPrice1 / Qty1 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 200 AND TotalPrice2 / Qty2 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 200 AND TotalPrice3 / Qty3 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 200 AND TotalPrice4 / Qty4 <= 250 THEN 1 ELSE 0 END)
        AS [200-250],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 250 THEN 1 ELSE 0 END)
        AS [250+]
    FROM dbo.table1
    GROUP BY
        Name
) AS t1
FULL OUTER JOIN (
    SELECT
        Name,
        SUM(CASE WHEN Qty1 = 0 THEN 1 WHEN TotalPrice1 / Qty1 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 1 WHEN TotalPrice2 / Qty2 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 1 WHEN TotalPrice3 / Qty3 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 1 WHEN TotalPrice4 / Qty4 <= 150 THEN 1 ELSE 0 END)
        AS [0-150],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 150 AND TotalPrice1 / Qty1 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 150 AND TotalPrice2 / Qty2 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 150 AND TotalPrice3 / Qty3 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 150 AND TotalPrice4 / Qty4 <= 200 THEN 1 ELSE 0 END)
        AS [151-200],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 200 AND TotalPrice1 / Qty1 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 200 AND TotalPrice2 / Qty2 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 200 AND TotalPrice3 / Qty3 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 200 AND TotalPrice4 / Qty4 <= 250 THEN 1 ELSE 0 END)
        AS [200-250],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 250 THEN 1 ELSE 0 END)
        AS [250+]
    FROM dbo.table2
    GROUP BY
        Name
) AS t2 ON
    t2.Name = t1.Name
FULL OUTER JOIN (
    SELECT
        Name,
        SUM(CASE WHEN Qty1 = 0 THEN 1 WHEN TotalPrice1 / Qty1 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 1 WHEN TotalPrice2 / Qty2 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 1 WHEN TotalPrice3 / Qty3 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 1 WHEN TotalPrice4 / Qty4 <= 150 THEN 1 ELSE 0 END)
        AS [0-150],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 150 AND TotalPrice1 / Qty1 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 150 AND TotalPrice2 / Qty2 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 150 AND TotalPrice3 / Qty3 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 150 AND TotalPrice4 / Qty4 <= 200 THEN 1 ELSE 0 END)
        AS [151-200],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 200 AND TotalPrice1 / Qty1 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 200 AND TotalPrice2 / Qty2 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 200 AND TotalPrice3 / Qty3 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 200 AND TotalPrice4 / Qty4 <= 250 THEN 1 ELSE 0 END)
        AS [200-250],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 250 THEN 1 ELSE 0 END)
        AS [250+]
    FROM dbo.table3
    GROUP BY
        Name
) AS t3 ON
    t3.Name IN (t1.Name, t2.Name)
FULL OUTER JOIN (
    SELECT
        Name,
        SUM(CASE WHEN Qty1 = 0 THEN 1 WHEN TotalPrice1 / Qty1 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 1 WHEN TotalPrice2 / Qty2 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 1 WHEN TotalPrice3 / Qty3 <= 150 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 1 WHEN TotalPrice4 / Qty4 <= 150 THEN 1 ELSE 0 END)
        AS [0-150],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 150 AND TotalPrice1 / Qty1 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 150 AND TotalPrice2 / Qty2 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 150 AND TotalPrice3 / Qty3 <= 200 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 150 AND TotalPrice4 / Qty4 <= 200 THEN 1 ELSE 0 END)
        AS [151-200],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 200 AND TotalPrice1 / Qty1 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 200 AND TotalPrice2 / Qty2 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 200 AND TotalPrice3 / Qty3 <= 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 200 AND TotalPrice4 / Qty4 <= 250 THEN 1 ELSE 0 END)
        AS [200-250],
        SUM(CASE WHEN Qty1 = 0 THEN 0 WHEN TotalPrice1 / Qty1 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty2 = 0 THEN 0 WHEN TotalPrice2 / Qty2 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty3 = 0 THEN 0 WHEN TotalPrice3 / Qty3 > 250 THEN 1 ELSE 0 END +
            CASE WHEN Qty4 = 0 THEN 0 WHEN TotalPrice4 / Qty4 > 250 THEN 1 ELSE 0 END)
        AS [250+]
    FROM dbo.table4
    GROUP BY
        Name
) AS t4 ON
    t4.Name IN (t1.Name, t2.Name, t3.Name)
ORDER BY
    Name


If you specifically want each value on a separate row, we can add a CROSS JOIN to do that rather easily.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Believe I mis-interpreted some stuff:
>> get the individual price (TotalPrice1 / Qty1)
>> for each TotalPrice (i.e. pairs of Price-number / Qty-number)
so:  (TotalPrice1 / Qty1),(TotalPrice2 / Qty2) etc.

The code below (in rudimentary tests) produces the required output, but to do so requires transposing your existing rows into 4x those rows, then summarising from there. Please note the embedded comments regarding filtering each of the 4 source tables could be important for performance. (nb: there is no information about sizes/indexes etc)

It's possible that using "UNPIVOT" would be a more elegant approach, but as it's not possible for me to test against the real tables - and the transform is relatively simple - I've opted for a "cross join approach".
with expand as (
                /* this "Common Table Expression (CTE)" is used in cross joins to provide conditions needed by case expressions */
                select 1 as expand_id union all
                select 2 as expand_id union all
                select 3 as expand_id union all
                select 4 as expand_id
                )
select
  normed.name
, case
    when  (normed.Iprice / normed.Qty) >= 0   AND (normed.Iprice / normed.Qty) < 151 then '0-150'
    when  (normed.Iprice / normed.Qty) >= 151 AND (normed.Iprice / normed.Qty) < 201 then '151-200'
    when  (normed.Iprice / normed.Qty) >= 201 AND (normed.Iprice / normed.Qty) < 251 then '201-250'
    when  (normed.Iprice / normed.Qty) >= 251                                              then '251+'
    else 'bad data'
  end as Band
, sum(normed.Qty) as Qty
, case
    when  (normed.Iprice / normed.Qty) >= 0   AND (normed.Iprice / normed.Qty) < 151 then 1
    when  (normed.Iprice / normed.Qty) >= 151 AND (normed.Iprice / normed.Qty) < 201 then 2
    when  (normed.Iprice / normed.Qty) >= 201 AND (normed.Iprice / normed.Qty) < 251 then 3
    when  (normed.Iprice / normed.Qty) >= 251                                              then 4
    else -1
  end as band_num
from (
       /* this is where the massaged raw data becomes a 'normalized' list with values like this:
          name qty Iprice
          xxxx 1   12.2
          xxxx 1   12.2
          xxxx 1   12.2
          xxxx 1   12.2

          from this we can summarize into the wanted bands (see above)
        */
        select
          raw_data.name
          /* using coalesce to put values into single columns */
        , isnull(COALESCE(raw_data.Q1,raw_data.Q2,raw_data.Q3,raw_data.Q4),-1) as Qty
        , isnull(COALESCE(raw_data.Iprice_1,raw_data.Iprice_2,raw_data.Iprice_3,raw_data.Iprice_4),-1) as Iprice
        from (
               /* this is where the 4 source tables are massaged, the real table names will be required
                  the outcome of this set of sub-queries is a long list valuse that look like this - for each original real record:
                  name qty1 qty2 qty3 qty4 etc
                  xxxx 1    null null null
                  xxxx null 1    null null
                  xxxx null null 1    null
                  xxxx null null null 1
                */
                select
                  name
                , case when expand_id = 1 then  Qty1 else null end as Q1
                , case when expand_id = 2 then  Qty2 else null end as Q2
                , case when expand_id = 3 then  Qty3 else null end as Q3
                , case when expand_id = 4 then  Qty4 else null end as Q4
                , case when expand_id = 1 then  TotalPrice1/Qty1 else null end as Iprice_1
                , case when expand_id = 2 then  TotalPrice2/Qty2 else null end as Iprice_2
                , case when expand_id = 3 then  TotalPrice3/Qty3 else null end as Iprice_3
                , case when expand_id = 4 then  TotalPrice4/Qty4 else null end as Iprice_4
                from tableA
                cross join expand /* multiplies number of rows, used to normalize the data */
                where 1 = 1 /* change filtering of this table to suit */

                union all

                select
                  name
                , case when expand_id = 1 then  Qty1 else null end as Q1
                , case when expand_id = 2 then  Qty2 else null end as Q2
                , case when expand_id = 3 then  Qty3 else null end as Q3
                , case when expand_id = 4 then  Qty4 else null end as Q4
                , case when expand_id = 1 then  TotalPrice1/Qty1 else null end as Iprice_1
                , case when expand_id = 2 then  TotalPrice2/Qty2 else null end as Iprice_2
                , case when expand_id = 3 then  TotalPrice3/Qty3 else null end as Iprice_3
                , case when expand_id = 4 then  TotalPrice4/Qty4 else null end as Iprice_4
                from tableB
                cross join expand /* multiplies number of rows, used to normalize the data */
                where 1 = 1 /* change filtering of this table to suit */

                union all

                select
                  name
                , case when expand_id = 1 then  Qty1 else null end as Q1
                , case when expand_id = 2 then  Qty2 else null end as Q2
                , case when expand_id = 3 then  Qty3 else null end as Q3
                , case when expand_id = 4 then  Qty4 else null end as Q4
                , case when expand_id = 1 then  TotalPrice1/Qty1 else null end as Iprice_1
                , case when expand_id = 2 then  TotalPrice2/Qty2 else null end as Iprice_2
                , case when expand_id = 3 then  TotalPrice3/Qty3 else null end as Iprice_3
                , case when expand_id = 4 then  TotalPrice4/Qty4 else null end as Iprice_4
                from tableC
                cross join expand /* multiplies number of rows, used to normalize the data */
                where 1 = 1 /* change filtering of this table to suit */

                union all

                select
                  name
                , case when expand_id = 1 then  Qty1 else null end as Q1
                , case when expand_id = 2 then  Qty2 else null end as Q2
                , case when expand_id = 3 then  Qty3 else null end as Q3
                , case when expand_id = 4 then  Qty4 else null end as Q4
                , case when expand_id = 1 then  TotalPrice1/Qty1 else null end as Iprice_1
                , case when expand_id = 2 then  TotalPrice2/Qty2 else null end as Iprice_2
                , case when expand_id = 3 then  TotalPrice3/Qty3 else null end as Iprice_3
                , case when expand_id = 4 then  TotalPrice4/Qty4 else null end as Iprice_4
                from tableD
                cross join expand /* multiplies number of rows, used to normalize the data */
                where 1 = 1 /* change filtering of this table to suit */

              ) raw_data
       ) normed
group by
  name
, case
    when  (normed.Iprice / normed.Qty) >= 0   AND (normed.Iprice / normed.Qty) < 151 then 1
    when  (normed.Iprice / normed.Qty) >= 151 AND (normed.Iprice / normed.Qty) < 201 then 2
    when  (normed.Iprice / normed.Qty) >= 201 AND (normed.Iprice / normed.Qty) < 251 then 3
    when  (normed.Iprice / normed.Qty) >= 251                          then 4
    else -1
  end
, case
    when  (normed.Iprice / normed.Qty) >= 0   AND (normed.Iprice / normed.Qty) < 151 then '0-150'
    when  (normed.Iprice / normed.Qty) >= 151 AND (normed.Iprice / normed.Qty) < 201 then '151-200'
    when  (normed.Iprice / normed.Qty) >= 201 AND (normed.Iprice / normed.Qty) < 251 then '201-250'
    when  (normed.Iprice / normed.Qty) >= 251                          then '251+'
    else 'bad data'
  end
order by normed.name, normed.band_num

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
by the way, I have followed you boundaries from the question in my code, and of course you are in control of those, but a more common set of boundaries would be something like this:
From    (up)To  Label
   0    150     0 to 150
 150    200     150 to 200
 200    250     200 to 250
 250            over 250

Open in new window

Just thought I'd mention it.

Author

Commented:
Paul,
Thanks again for the response. Looks like you've gone all-out!

I tried your original query yesterday but came into some formatting issues with some of the data and then spent a lot of time fiddling with the query itself.

I'll take a look at your new query and take into account the suggestions you've made.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
can you elaborate on "some formatting issues" - is this code for data difficulties i.e. do you need to also "clean" the data too?

The point being, is there anything needed in the query?
Try working in small amounts of data first (using "top 100" for example)

if you need "random data" there are ways in sql server to do this also.

Author

Commented:
Also I realise that I've been entirely sparse with providing context and details as to my data. I'll try to expand -

We store the details of 4 separate items in one record, and each item has a quantity and a price associated with it, so we might have:
4 of Item1 at £519.96,
2 of Item2 at £421.88
0 of Item3 at £0.00,
0 of Item4 at £0.00

Each record also has a user associated with it. The usernames are common across all of the databases.

What I need is to know how many items each user has associated with them, and how much commission they earn based on each of these items (which I didn't mention earlier because I thought I'd leave myself at least a bit of work but I think that this is entirely beyond me!)

To work out the commission we fit the price of the item into one of the bands mentioned above. Each band has an associated value.
These are the bands as they were provided to me:
£0-£150 = £1.50
£151-£200 = £2.00
£201-£250 = £2.50
£251 +  = £3.50

Unfortunately we only store the total price of the items so first we need to obtain the individual price (Price1/Quantity1, Price2/Quantity2...) in order to fit it into one of the commission bands.

Example:
With 4 of Item1 at £519.96,
a single Item1 costs £129.99 which is £1.50 commission according to the bands,
which gives us £6.00 total commission with 4 of Item1
---
In the end we'll have a recordset with records showing a username, which one of the four bands the record details for that username, the quantity of items that fall within that band for that username, and a total commission value based on the band values and the quantity of items in that band for that username.

There should be four of these records per user, one per band.

In regard to data types; they're pretty messed up.
Don't even ask why but quantity and price are stored as nvarchar, as well as the username. The first thing I do in the SELECT is CAST the quantity to type int and CONVERT the price to decimal(16,2), and check for blank or null fields, so treat the data like that. I also COLLATE the username using SQL_Latin1_General_CP1_CI_AS
to avoid collation conflicts between the databases.

Here's my SELECT from the original query:
select CASE WHEN Username IS NULL THEN '' ELSE Username END COLLATE SQL_Latin1_General_CP1_CI_AS AS Username, CAST((CASE WHEN qty1 IS NULL THEN '0' WHEN qty1 = '' THEN '0' ELSE qty1 END) AS int) AS qty1, CAST((CASE WHEN qty2 IS NULL THEN '0' WHEN qty2 = '' THEN '0' ELSE qty2 END) AS int) AS qty2, CAST((CASE WHEN qty3 IS NULL THEN '0' WHEN qty3 = '' THEN '0' ELSE qty3 END) AS int) AS qty3, CAST((CASE WHEN qty4 IS NULL THEN '0' WHEN qty4 = '' THEN '0' ELSE qty4 END) AS int) AS qty4, CONVERT(DECIMAL(16,2),(CASE WHEN commission1 IS NULL THEN '0.0' WHEN commission1 = '' THEN '0.0' ELSE commission1 END)) AS commission1, CONVERT(DECIMAL(16,2),(CASE WHEN commission2 IS NULL THEN '0.0' WHEN commission2 = '' THEN '0.0' ELSE commission2 END)) AS commission2, CONVERT(DECIMAL(16,2),(CASE WHEN commission3 IS NULL THEN '0.0' WHEN commission3 = '' THEN '0.0' ELSE commission3 END)) AS commission3, CONVERT(DECIMAL(16,2),(CASE WHEN commission4 IS NULL THEN '0.0' WHEN commission4 = '' THEN '0.0' ELSE commission4 END)) AS commission4
                from tbl_main

Open in new window



Hopefully that makes my requirements perfectly clear but I'm around to answer questions.

Thanks again!!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
entirely sparse
may we quote you? This is such a common issue I wrote a blog about being clairvoyant :) There's also an attractive British ring to "entirely sparse" too. I like it.

Ok, I'll digest this dense matter but I do have a few quick questions, you mention "There should be four of these records per user, one per band." is this:

a. mandatory. i.e. always 4 records per person
b. only the rows that actually exist (0 , 1, 2 or 4 rows)

Given that there has been a commission revelation, will you be storing these results, or are you just wanting a "quickie calculation"...

There has to be a date involved, are the records selected over a period? what period? Please be very specific. (apologies if this is already spelled out).

That's it for now, but I wanted to catch you whilst in loquacious mood.

Cheers.

Author

Commented:
I try to keep things colourful :P
Quote away haha.

First of all, no there don't always need to be four records. If there are no prices within a band for a particular user then that record doesn't need to be displayed just to show that it has nothing in it, although it doesn't matter at all whether it is there or not; whichever is easiest to write for.

Secondly, nothing here needs to be stored; it's just a dump of data for a report. I'll have the application print or mail it.

Finally, you've brought up something that I forgot about entirely!
There is a date field and the results will need to be displayed within a date range.
Typically I'll implement the query as a stored procedure and have the values passed in by an application.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
please see "Beware of Between"

I will ask you to be very precise about the date range: is it for a calendar month for example? or something else.

& Please do tell me the date information is stored as datetime (not as strings)?????

Author

Commented:
Aha....
The worst is come to bare - the date is nvarchar(15). I've had no end of problems converting to and from datetime and maintaining EU date format.

The date range will be between dd/mm/yyyy and dd/mm/yyyy (including the two dates).
I'm waiting to check on someone to get the precise ranges for the commission band since I don't know if 0-150 means to £150.00 or to £150.99.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Nightmare having datetime in char of any type!! darn.

I would also assume commissions change over time, do you have a table of these and if so how is this defined?

Our first step will be to ignore the detail - we have to get the skeleton working first.

Other useful information will be:
real table names, real field names
how many records are in each table
ideally the table/field definitions (also indexes)

It's quite possible any query of this sort will perform "badly" - what is your expectation on performance?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
hehe. so, integers as text, currency as text, and dates as text. Do they also use clay tablets?

If we can get the data efficiently, then the rest is relatively easy.

Can you get the complete definitions of the 4 base tables please? I need to know what I'm dealing with.

The code below is most probably "incomplete", there are no 'prices' nor any 'dates', I will need ALL the fields you use from those 4 tables. And the real names of these tables please.
SELECT
   CASE
      WHEN Username IS NULL THEN ''
      ELSE Username
   END COLLATE SQL_Latin1_General_CP1_CI_AS AS Username
   , CAST((
       CASE
          WHEN qty1 IS NULL THEN '0'
          WHEN qty1 = ''    THEN '0'
          ELSE qty1
       END) AS INT)
     AS qty1
   , CAST((
       CASE
          WHEN qty2 IS NULL THEN '0'
          WHEN qty2 = ''    THEN '0'
          ELSE qty2
       END) AS INT)
     AS qty2
   , CAST((
       CASE
          WHEN qty3 IS NULL THEN '0'
          WHEN qty3 = ''    THEN '0'
          ELSE qty3
       END) AS INT)
     AS qty3
   , CAST((
       CASE
          WHEN qty4 IS NULL THEN '0'
          WHEN qty4 = ''    THEN '0'
          ELSE qty4
       END) AS   INT)
     AS qty4
   , CONVERT(DECIMAL(16,2),(
       CASE
          WHEN commission1 IS NULL THEN '0.0'
          WHEN commission1 = ''    THEN '0.0'
          ELSE commission1
       END))
     AS commission1
   , CONVERT(DECIMAL(16,2),(
       CASE
          WHEN commission2 IS NULL THEN '0.0'
          WHEN commission2 = ''    THEN '0.0'
          ELSE commission2
       END))
     AS commission2
   , CONVERT(DECIMAL(16,2),(
       CASE
          WHEN commission3 IS NULL THEN '0.0'
          WHEN commission3 = ''    THEN '0.0'
          ELSE commission3
       END))
     AS commission3
   , CONVERT(DECIMAL(16,2),(
       CASE
          WHEN commission4 IS NULL THEN '0.0'
          WHEN commission4 = ''    THEN '0.0'
          ELSE commission4
       END))
     AS commission4
FROM tbl_main

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
apologies for multiple posts, but what is the "significance" of each of the 4 tables? e.g. do they represent regions, or product types or org structure? or: Presumably there is a reason for 4 tables - what is that reason? (this may reveal what I'm after)

Author

Commented:
I don't know if the commissions change but I like to assume so.
My concern is that if I create a table with the bands and commission values in one of the databases then that might be dropped at some point and then the query would break.
I could create a table in all of the different databases but that would be a poor solution.
Would it be a good idea to create a new database specifically for storing utility data which is shared by multiple other databases?

Ok, database details...

Well, first of all, there are now six databases to deal with (number of records in brackets):
BenNevis2(1700)
CardnetSTH(20)
Everest(4578)
FDMS(1408)
HMSSecondaries(1371)
HMSUpgrade(1334)

Records are stored on a table named tbl_main. Each of the databases have their own version of tbl_main.

Within tbl_main we will be using the following fields in order to run the query:
SellerCode nvarchar(10) Allow Null (Username - actually just initials)
Exported nvarchar(15) Allow Null (Date field)
qty1 nvarchar(50) Allow Null
qty2 nvarchar(50) Allow Null
qty3 nvarchar(50) Allow Null
qty4 nvarchar(50) Allow Null
commission1 nvarchar(50) Allow Null (TotalPrice)
commission2 nvarchar(50) Allow Null
commission3 nvarchar(50) Allow Null
commission4 nvarchar(50) Allow Null

In terms of performance, expectations are low. It won't be run frequently and access to the database isn't particularly busy.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Ok, so I mentally translate commission to mean price I assume...

Also I notice this:
SellerCode nvarchar(10) Allow Null (Username - actually just initials)

This means horrible rubbish can be in that column. (It should be a controlled value, no nulls.) Is there an ID or unique field in these tables? - I want that too please (you may not, but I insist).

Regarding databases, bit beyond my remit for a single question,  if you have the power to put data into a database then do so. That is your call.

I'm going to stick with the immediate need and try to make it reasonably robust, without employing more tables. To be honest if you were going to go to a database of your own with tables etc that would probably require on-site design/build assistance, not something that can easily be typed into a text box.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
As to performance, you should group each table separately first, then join the grouped data, as I did in my sample code earlier.  It is more efficient to group 100,000 rows 4 times than to union them and then group 400,000 rows all at once.



>> 
a more common set of boundaries would be something like this:
From    (up)To  Label
   0    150     0 to 150
 150    200     150 to 200
 200    250     200 to 250
 250            over 250
<<

More common, maybe, but definitely less accurate.  150 falls into two categories, which means you can never accurately classify it.

His original ranges were accurate.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Scott, no problem with any observations - thanks. The ranges are NOT what I would propose in the query... at all .. believe me. (it was a "functional question")

As is so often the case here at EE there is way more to the requirement than the original question indicated.

Regarding the grouping, I would agree - IF - the data was "mutually exclusive" - I'm afraid the results of doing it per db will be wrong - but I'm not certain of that - but something to be explored. Certainly dragging data from (now) 6 dbs, doing a lot of text manipulation then calcs on top - not a simple query :)

I could do with some advice on sql server specifics. e.g. can I use a table function across multiple db's? or is that just a silly idea?? appreciate your opinion.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The initial totals can always be performed first on the individual tables, even if the final computations must be deferred.  Performance will be much better summing each individually, then combining them, vs combining all the raw data and then doing the groupings/summaries.

A table function can be invoked across dbs, and for multiple dbs.  Unless it's an inline table-valued function, though, the performance will be reduced by using a function.

Author

Commented:
Thanks for advising.

The ID field is the primary key on each of the tables but the values are not unique between tables.

Apparently our system is a real mess haha. I'm struggling to find a unique value.
Probably the best we can do is to combine a few fields, and even then there is technically the chance for duplication but the circumstances will likely never come to be.
A combination of the ID, MID, NAME, and [TRADING AS NAME] fields would be the closest we could manage.

Also I got an update on the commission ranges:
£0.00 - £150.99 = £1.50
£151.00 - £200.99 = £2.00
£201.00 - £250.99 = £2.50
£251 +  = £3.50

Author

Commented:
Here are the details of the fields above in case you want to use them:
ID int (Is Identity)
MID nvarchar(50) Allow Nulls
NAME nvarchar(255) Allow Nulls
[TRADING AS NAME] nvarchar(255) Allow Nulls
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
You have provided some code (thanks) on how you convert the fields etc, but not on how you select, and given that the "date" is actually nvarchar I'd like to see the whole truth of your existing code. Please refer to earlier questions - I need the precise nature of the date range, the precise layout of the textual dates. (Your existing code will probably reveal how you select by the dates you see). So the full code please.

Also, does your existing approach pull the data from all the tables at once, or do you do it in parts, one table at a time? As Scott rightly points out we do have to draw data from 6 places (although data volumes are not huge) - so "have you done this before?".

Also following the tips from Scott I would like to know the answer to this:
if "John" exists in 2 or more databases, let's say 4, is that one John for the calculations or 4? i.e. do we have to combine the data to get the correct results or can the results be computed per database then combined?

If the questions are unclear do let me know when you post the full code. You can attach code as a text file if you prefer. If there are privacy or confidential stuff obviously remove what you have to.

tip:
sp_help 'dbo.tbl_main' -- all table details revealed

Open in new window

Author

Commented:
ogawd. I was just reading back up the page and it looks like one or two of your posts have passed me by - probably when I've been writing something and you've posted in the meantime.

Just going through and getting some more information for you now, keeping in mind what you said above and in your clairvoyancy post.

In regards to existing code - there isn't really any beyond what was posted above, with a few modifications where I hadn't provided you with specific information.

Let me gather some info and put it into one post, then I've not got stuff all over the place...
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I really need to see the date filtering as a priority, some sample data would also be damn handy (paste into text file, attach that)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Ok, here is some code to test

You need to supply a database.tablename in this code, and double check the field names too.

It's only structured for a single database right now, and you must answer the question below before we can proceed. Please see if this is a. working (no failures) and b.if there are any calculation errors (to the extent you can verify them)
                                                /* first the date boundaries, the lower date is INCLUDED, the upper date is EXCLUDED */
declare @date_start nvarchar(15) = '2013/01/01' /* >= -- equal and above this date */
declare @date_until nvarchar(15) = '2013/02/01' /* <  -- less than this date. Think "the beginning of" the next day */

                                                /* now the boundarys of 4 bands, only need 3 values (plus 0) */
declare @bound_1_low  decimal(16,2) =   0.00
declare @bound_1_high decimal(16,2) = 151.00
declare @bound_2_high decimal(16,2) = 201.00
declare @bound_3_high decimal(16,2) = 251.00

                                                /* and the commission rates for each band */
declare @com_rate_1 decimal(16,2) =  1.50
declare @com_rate_2 decimal(16,2) =  2.00
declare @com_rate_3 decimal(16,2) =  2.50
declare @com_rate_4 decimal(16,2) =  3.50

select
  username
, band_1
, band_2
, band_3
, band_4
, (band_1 * @com_rate_1) as ccommission_1
, (band_2 * @com_rate_2) as ccommission_2
, (band_3 * @com_rate_3) as ccommission_3
, (band_4 * @com_rate_4) as ccommission_4
, @bound_1_low  as bound_1_low
, @bound_1_high as bound_1_high
, @bound_2_high as bound_2_high
, @bound_3_high as bound_3_high
, @date_start as [Date From]
, @date_until as [Date Until]
from (
            select
            username
            ,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                 case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                 case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                 case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
            as [band_1]
            ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                 case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                 case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                 case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
            as [band_2]
            ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                 case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                 case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                 case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
            as [band_3]
            ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                 case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                 case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                 case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
            as [band_4]
        from (
                select
                     isnull(sellercode,'') collate sql_latin1_general_cp1_ci_as as username
                   , cast('0' + isnull(qty1,'0') as int) as qty1
                   , cast('0' + isnull(qty2,'0') as int) as qty2
                   , cast('0' + isnull(qty3,'0') as int) as qty3
                   , cast('0' + isnull(qty4,'0') as int) as qty4
                   , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                   , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                   , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                   , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                from tbl_main
                where exported >= @date_start and exported < @date_until
                ) raw1
                group by username
    ) bands1

Open in new window

now that I have had a look at Scott's (very nice code) versus my original approaches I can see we have made opposing assumptions. Scott has assumed the data is "database specific" whereas I was assuming all the data needed collecting to summarise by username.

so, if the same username is found in 2 or more databases, what are the rules?
Do we treat each database separately, or do we group all records by username?

Author

Commented:
Handily I was preparing just that! It's a little sample from the CardnetSTH table tbl_main.

Also attached is the table details for each tbl_main in each of the databases. Let me know if the format is ok.
--

Date filtering is precise to the day. When filtering in other queries I've used similar to the following:
select * from dbo.tbl_main
where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @DateFrom, 103)
AND CONVERT(datetime,[Exported], 103) <= CONVERT(datetime, @DateTo, 103)

Open in new window

where Exported is the nvarchar date held in tbl_main, and @DateFrom and @DateTo are string dates passed to a stored procedure by an application.
--

Now, as for the six databases; it's an organisational thing.
Records are entered into the different databases based on their context, or rather where the data was received from and where it's going. The databases all use the same data structure, but the data itself has different targets, and some business logic is applied to data in some tables whilst not in others.
--

Ah, one more thing.
I put together a table for the Commission Bands (Common.dbo.tbl_CommissionBand, details attached).
Hopefully it comes in handy. It could be entirely useless. Let me know.

Each record represents a Band and the Commission value related to that band (data attached).

*** Working through your last post now.
testdata.xls
BenNevis2.dbo.tbl-main.xls
CardnetSTH.dbo.tbl-main.xls
Everest.dbo.tbl-main.xls
FDMS.dbo.tbl-main.xls
HMSSecondaries.dbo.tbl-main.xls
HMSUpgrade.dbo.tbl-main.xls
Common.dbo.tbl-CommissionBand.xls
banddata.xls

Author

Commented:
so, if the same username is found in 2 or more databases, what are the rules?
Do we treat each database separately, or do we group all records by username?

Usernames (SellerCode) are common across all databases. That is; the same group of users make use of all six databases.
The data should be dealt with as if it were one resultset, and organised by SellerCode.
The aim is to calculate the commission values for each user. The source of the data is irrelevant.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
>> Scott has assumed the data is "database specific" whereas I was assuming all the data needed collecting to summarise by username. <<

As I noted earlier, no matter what the required combined calcs are, the individual tables can still be summarized separately, using intermediate totals if/as necessary, and then all tables joined to do the final computations.

Again, the performance of grouping rows is always faster in smaller groups vs larger groups for the same total number.

Author

Commented:
First of all, Paul,  with a little fiddling that query returns the results perfectly!

Secondly, taking into account Scott's note on performance, would something similar to this be more efficient (if not pretty cumbersome looking)?

                                                /* first the date boundaries, the lower date is INCLUDED, the upper date is EXCLUDED */
declare @date_start nvarchar(15) = '01/01/2011' /* >= -- equal and above this date */
declare @date_until nvarchar(15) = '01/01/2013' /* <  -- less than this date. Think "the beginning of" the next day */

                                                /* now the boundaries of 4 bands, only need 3 values (plus 0) */
declare @bound_1_low  decimal(16,2) =   0.00
declare @bound_1_high decimal(16,2) = 151.00
declare @bound_2_high decimal(16,2) = 201.00
declare @bound_3_high decimal(16,2) = 251.00

                                                /* and the commission rates for each band */
declare @com_rate_1 decimal(16,2) =  1.50
declare @com_rate_2 decimal(16,2) =  2.00
declare @com_rate_3 decimal(16,2) =  2.50
declare @com_rate_4 decimal(16,2) =  3.50




select
  username
, sum(band_1)
, sum(band_2)
, sum(band_3)
, sum(band_4)
, sum(ccommission_1)
, sum(ccommission_2)
, sum(ccommission_3)
, sum(ccommission_4)
, bound_1_low
, bound_1_high
, bound_2_high
, bound_3_high
, [Date From]
, [Date Until]
from (
		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from BenNevis2.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw1
				group by username
		) bands1
		    
		union all

		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from CardnetSTH.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw2
				group by username
		) bands2
		    
		union all
		    
		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from Everest.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw3
				group by username
		) bands3
		
		union all
		    
		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from FDMS.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw4
				group by username
		) bands4
		    
		union all
		    
		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from HMSSecondaries.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw5
				group by username
		) bands5
		    
		union all
		    
		select
		  username
		, band_1
		, band_2
		, band_3
		, band_4
		, (band_1 * @com_rate_1) as ccommission_1
		, (band_2 * @com_rate_2) as ccommission_2
		, (band_3 * @com_rate_3) as ccommission_3
		, (band_4 * @com_rate_4) as ccommission_4
		, @bound_1_low  as bound_1_low
		, @bound_1_high as bound_1_high
		, @bound_2_high as bound_2_high
		, @bound_3_high as bound_3_high
		, @date_start as [Date From]
		, @date_until as [Date Until]
		from (
				select
				username
				,sum(case when qty1 = 0 then 1 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
					case when qty2 = 0 then 1 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
					case when qty3 = 0 then 1 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
					case when qty4 = 0 then 1 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
					as [band_1]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
					as [band_2]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
					as [band_3]
				,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
					case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
					case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
					case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
					as [band_4]
				from (
						select
							isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
							, cast('0' + isnull(qty1,'0') as int) as qty1
							, cast('0' + isnull(qty2,'0') as int) as qty2
							, cast('0' + isnull(qty3,'0') as int) as qty3
							, cast('0' + isnull(qty4,'0') as int) as qty4
							, convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
							, convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
							, convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
							, convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
							, [Exported]
						from HMSUpgrade.dbo.tbl_main
						where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
						and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
				) raw6
				group by username
		) bands6
		
) finalresult
group by username, bound_1_low, bound_1_high, bound_2_high, bound_3_high, [Date From], [Date Until]
	

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
@Scott - yes message heard loud and clear - wilco.

@CaptainGiblets - no clairvoyancy required now - thanks.

I would still like you to try out the code I provided earlier please and report back. There is no substitute for the real thing in testing.

Now I know the databases aren't relevant to the calculations I propose to move ahead with a union all of each summary results set (see Scott I do hear you) with a final grouping of those summaries.

At this stage putting boundaries and commission rate etc into their own table isn't required - if you look at the latest posted code it simply uses a small set of declarations so you may freely alter either the boundaries or the commission rates to suit.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Ah, I see you have already done a run or two - and amended the code pretty much as I was going to suggest!

We are close.

Just a point on the date filtering, you may not have to convert both sides of this into datetime for it to be successful - it is just as likely to work with pure strings and no conversion at all. This needs to be verified on-site - but it will save some time/performance.

from:
where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)

To:
where Exported >= 'String'
and Exported < 'String'
/* this is dependent on how the date is stored in the string field of course. */
If you are more comfortable with the date conversion - and its working I'm fine with that too.

also, I included some stuff for reference you have replicated 6 times

            , @bound_1_low  as bound_1_low
            , @bound_1_high as bound_1_high
            , @bound_2_high as bound_2_high
            , @bound_3_high as bound_3_high
            , @date_start as [Date From]
            , @date_until as [Date Until]

these lines don't need replication, and could occur in the final query (or not at all)

The only other thing I'd like to see is aliases applied throughout - but beside that I think we really do have a solution.

Wonderful. Thanks for your inputs Scott - helped me from going into a very deep rabbit hole.

In conclusion tonight - thanks for all the additional data added I'll cast my eyes over it after some sleep.

Cheers.
Paul

Author

Commented:
ogeez it's late there, assuming you're still in Aus!

Looks like I was overenthusiastic with the copy-pasta. I'll try to refine it a bit, work in some aliases, and I need to work out some kinks (the query that I posted is actually returning a conversion error, although it worked fine when selecting from just one database).
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
try it database by database, in chunks to iron out the wrinkles
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Excellent, got some sleep. I've reviewed the query sent by you that has the 6 tables referenced. Seemed I had some 1's where there should have been 0's. Also adjusted for the repetition of parameters in selection lists, and have embedded possible alternative to where filters for dates.

You mentioned you had to do some tweaking and then you were having some possible conversion issues. So, at the end of this code block is an alternative block for performing the raw data conversions which is really just a minor tweak to your own original script for this. I was hoping my data conversion approach would be a tad faster and equally reliable - perhaps neither is true, so you may want to use your original method for this.

I trust we are ever so close to answering your question(s) now :) Cheers.
                                                /* first the date boundaries, the lower date is INCLUDED, the upper date is EXCLUDED */
declare @date_start nvarchar(15) = '01/01/2011' /* >= -- equal and above this date */
declare @date_until nvarchar(15) = '01/01/2013' /* <  -- less than this date. Think "the beginning of" the next day */

                                                /* now the boundaries of 4 bands, only need 3 values (plus 0) */
declare @bound_1_low  decimal(16,2) =   0.00
declare @bound_1_high decimal(16,2) = 151.00
declare @bound_2_high decimal(16,2) = 201.00
declare @bound_3_high decimal(16,2) = 251.00

                                                /* and the commission rates for each band */
declare @com_rate_1 decimal(16,2) =  1.50
declare @com_rate_2 decimal(16,2) =  2.00
declare @com_rate_3 decimal(16,2) =  2.50
declare @com_rate_4 decimal(16,2) =  3.50




select
  username
, sum(band_1)
, sum(band_2)
, sum(band_3)
, sum(band_4)
, sum(commission_1)
, sum(commission_2)
, sum(commission_3)
, sum(commission_4)
/* below don't know if these are really required, were included for reference */
, @bound_1_low  as bound_1_low
, @bound_1_high as bound_1_high
, @bound_2_high as bound_2_high
, @bound_3_high as bound_3_high
, @date_start as date_start
, @date_until as date_until
from (
        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                /* this first sum block: had an error where if qtyX = 0 it was being set to 1 */
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from BenNevis2.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw1
                group by username
        ) bands1
            
        union all

        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from CardnetSTH.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw2
                group by username
        ) bands2
            
        union all
            
        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from Everest.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw3
                group by username
        ) bands3
        
        union all
            
        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from FDMS.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw4
                group by username
        ) bands4
            
        union all
            
        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from HMSSecondaries.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw5
                group by username
        ) bands5
            
        union all
            
        select
          username
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                username
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
                              isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as username
                            , cast('0' + isnull(qty1,'0') as int) as qty1
                            , cast('0' + isnull(qty2,'0') as int) as qty2
                            , cast('0' + isnull(qty3,'0') as int) as qty3
                            , cast('0' + isnull(qty4,'0') as int) as qty4
                            , convert(decimal(16,2),('0' + isnull(commission1,'0'))) as totalprice1
                            , convert(decimal(16,2),('0' + isnull(commission2,'0'))) as totalprice2
                            , convert(decimal(16,2),('0' + isnull(commission3,'0'))) as totalprice3
                            , convert(decimal(16,2),('0' + isnull(commission4,'0'))) as totalprice4
                            , [Exported]
                        from HMSUpgrade.dbo.tbl_main
                        /*
                        where [Exported] >= @date_start
                        and [Exported] < @date_until
                        */
                        where CONVERT(datetime,[Exported], 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,[Exported], 103) < CONVERT(datetime, @date_until, 103)
                ) raw6
                group by username
        ) bands6
        
) finalresult
group by
  username
  /* below don't know if these are actually required here */
, @bound_1_low
, @bound_1_high
, @bound_2_high
, @bound_3_high
, @date_start
, @date_until



     /* alternative raw data conversions, closer to original */
 /*
   SELECT
       CASE WHEN Username IS NULL THEN '' ELSE Username END COLLATE SQL_Latin1_General_CP1_CI_AS AS Username 
     , CAST(( CASE WHEN qty1 IS NULL OR qty1 = '' THEN '0' ELSE qty1 END ) AS INT) AS qty1 
     , CAST(( CASE WHEN qty2 IS NULL OR qty2 = '' THEN '0' ELSE qty2 END ) AS INT) AS qty2 
     , CAST(( CASE WHEN qty3 IS NULL OR qty3 = '' THEN '0' ELSE qty3 END ) AS INT) AS qty3 
     , CAST(( CASE WHEN qty4 IS NULL OR qty4 = '' THEN '0' ELSE qty4 END ) AS INT) AS qty4 
     , CONVERT(DECIMAL(16,2),( CASE WHEN commission1 IS NULL OR commission1 = '' THEN '0.0' ELSE commission1 END )) AS commission1 
     , CONVERT(DECIMAL(16,2),( CASE WHEN commission2 IS NULL OR commission2 = '' THEN '0.0' ELSE commission2 END )) AS commission2 
     , CONVERT(DECIMAL(16,2),( CASE WHEN commission3 IS NULL OR commission3 = '' THEN '0.0' ELSE commission3 END )) AS commission3 
     , CONVERT(DECIMAL(16,2),( CASE WHEN commission4 IS NULL OR commission4 = '' THEN '0.0' ELSE commission4 END )) AS commission4
     FROM dbo.tbl_main
*/     

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Hi captain, (interesting nickname by the way)

How is the mega summarisation query going? Any progress or issues?

Author

Commented:
Don't even ask about the name; it wasn't my decision x_x

I'm back on task this morning and I'll get back to you when I've tried out the new query.

Author

Commented:
Hmm, I've been getting that same error, but I've determined that it's specific to database HMSUpgrade.

Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to numeric.

I tried using my old select method, which seems to work. I'll take a look at the differences between the two methods and try to find a happy middle-ground.

Can you suggest a way to troubleshoot this? From what I can see there is no difference between the data in HMSUpgrade and the other databases - they all contain blank or null fields and all return a resultset besides HMSUpgrade.

It's just occurred to me to try selecting from the fields individually, so I'll find out which column in particular has the issue and get back to you.

Author

Commented:
It's definitely a value in the commission1 field.
I'll do a bit more investigating.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I see. Then it's a "data issue" and probably one we need to cater for on ALL those "intended to be" number fields

e.g.
      case  
          when isnumeric([commission1]) = 1 then  cast([commission1] AS <<whatever>>)
          else 0
     end

which raises the spectre of what I had initially tried to do which was to have an error trap of "bad data" - the point being that when non-number data is encountered - what is the right thing to do?

grrrrr, fields should be the right data type!!!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
oh, you could try something like this:
select
commission1
from that_bad_table
where isnumeric([commission1]) <> 1
and ... your date filters

Author

Commented:
I agree haha, but I didn't build the thing :(

The data is, unfortunately, very unpredictable, which is why I had about 200 characters a line of just validation checking.

Let me refactor one more time.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
It may pay to go back to to a much more comprehensive data validation routine, but I did base all suggestions so far on your code, in fact the latest code suggested had such an alternative at the end for consideration - however so far no code has used the isnumeric function - so if you have stuff you've not shared - and it works - then by all means use that.

I cannot be blamed for those tables designs either by the way :)

Author

Commented:
Hey, I'm not blaming anyone :P

I have a working query and everything seems a-ok!
There are some slight differences between the numbers that I'm getting and the numbers generated by someone who calculated manually, but that's something for me to sort out.

I'll post the final query plus a couple extra fields which expose a little more information in the resultset -

                                                /* first the date boundaries, the lower date is INCLUDED, the upper date is EXCLUDED */
declare @date_start nvarchar(15) = '01/01/2011' /* >= -- equal and above this date */
declare @date_until nvarchar(15) = '01/01/2013' /* <  -- less than  or equal to this date */

                                                /* now the boundaries of 4 bands, only need 3 values (plus 0) */
declare @bound_1_low  decimal(16,2) =   0.00
declare @bound_1_high decimal(16,2) = 151.00
declare @bound_2_high decimal(16,2) = 201.00
declare @bound_3_high decimal(16,2) = 251.00

                                                /* and the commission rates for each band */
declare @com_rate_1 decimal(16,2) =  1.50
declare @com_rate_2 decimal(16,2) =  2.00
declare @com_rate_3 decimal(16,2) =  2.50
declare @com_rate_4 decimal(16,2) =  3.50


select
  [Seller Code]
, @date_start as [Date From]
, @date_until as [Date To]
, sum(band_1) as [Band 1 Qty]
, sum(band_2) as [Band 2 Qty]
, sum(band_3) as [Band 3 Qty]
, sum(band_4) as [Band 4 Qty]
, sum(commission_1) as [Band 1 Commission]
, sum(commission_2) as [Band 2 Commission]
, sum(commission_3) as [Band 3 Commission]
, sum(commission_4) as [Band 4 Commission]
, sum(commission_1+commission_2+commission_3+commission_4) as [Total Commission]
, @bound_1_high-1 as [Band 1 cap]
, @bound_2_high-1 as [Band 2 cap]
, @bound_3_high-1 as [Band 3 cap]
, @bound_3_high as [Band 4 as value or over]
from (
        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from BenNevis2.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw1
                group by [Seller Code]
        ) bands1
            
        union all

        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from CardnetSTH.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw2
                group by [Seller Code]
        ) bands2
            
        union all
            
        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from Everest.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw3
                group by [Seller Code]
        ) bands3
        
        union all
            
        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from FDMS.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw4
                group by [Seller Code]
        ) bands4
          
        union all
            
        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from HMSSecondaries.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw5
                group by [Seller Code]
        ) bands5

        union all
            
        select
          [Seller Code]
        , band_1
        , band_2
        , band_3
        , band_4
        , (band_1 * @com_rate_1) as commission_1
        , (band_2 * @com_rate_2) as commission_2
        , (band_3 * @com_rate_3) as commission_3
        , (band_4 * @com_rate_4) as commission_4
        from (
                select
                [Seller Code]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >  @bound_1_low  and totalprice2 / qty2 < @bound_1_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >  @bound_1_low  and totalprice3 / qty3 < @bound_1_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >  @bound_1_low  and totalprice4 / qty4 < @bound_1_high then 1 else 0 end) 
                    as [band_1]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_1_high and totalprice1 / qty1 < @bound_2_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_1_high and totalprice2 / qty2 < @bound_2_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_1_high and totalprice3 / qty3 < @bound_2_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_1_high and totalprice4 / qty4 < @bound_2_high then 1 else 0 end)
                    as [band_2]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 >= @bound_2_high and totalprice1 / qty1 < @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 >= @bound_2_high and totalprice2 / qty2 < @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 >= @bound_2_high and totalprice3 / qty3 < @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 >= @bound_2_high and totalprice4 / qty4 < @bound_3_high then 1 else 0 end)
                    as [band_3]
                ,sum(case when qty1 = 0 then 0 when totalprice1 / qty1 > @bound_3_high then 1 else 0 end +
                     case when qty2 = 0 then 0 when totalprice2 / qty2 > @bound_3_high then 1 else 0 end +
                     case when qty3 = 0 then 0 when totalprice3 / qty3 > @bound_3_high then 1 else 0 end +
                     case when qty4 = 0 then 0 when totalprice4 / qty4 > @bound_3_high then 1 else 0 end)
                    as [band_4]
                from (
                        select
							  isnull(SellerCode,'') collate sql_latin1_general_cp1_ci_as as [Seller Code]
					, cast('0' + isnull(qty1,'0') as int) as qty1
					, cast('0' + isnull(qty2,'0') as int) as qty2
					, cast('0' + isnull(qty3,'0') as int) as qty3
					, cast('0' + isnull(qty4,'0') as int) as qty4
					, convert(decimal(16,2),( case when commission1 IS NULL OR commission1 = '' then '0.0' else commission1 end )) as totalprice1 
					, convert(decimal(16,2),( case when commission2 IS NULL OR commission2 = '' then '0.0' else commission2 end )) as totalprice2 
					, convert(decimal(16,2),( case when commission3 IS NULL OR commission3 = '' then '0.0' else commission3 end )) as totalprice3 
					, convert(decimal(16,2),( case when commission4 IS NULL OR commission4 = '' then '0.0' else commission4 end )) as totalprice4
					, Exported
						from HMSUpgrade.dbo.tbl_main
                        where 
                        CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
                        and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)
                ) raw6
                group by [Seller Code]
        ) bands6

) finalresult
group by
  [Seller Code]

Open in new window



Please let me know if there is anything glaringly wrong!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Nothing glaringly obvious I can see.

Perhaps the only comment would be these:
, @bound_1_high-1 as [Band 1 cap]
, @bound_2_high-1 as [Band 2 cap]
, @bound_3_high-1 as [Band 3 cap]

maybe?
, @bound_1_high-0.01 as [Band 1 cap]
, @bound_2_high-0.01 as [Band 2 cap]
, @bound_3_high-0.01 as [Band 3 cap]

Just out of interest, how long does to take to run? (approx)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
actually, there is this...

where CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
    and CONVERT(datetime,Exported, 103) <= CONVERT(datetime, @date_until, 103)

(here the higher date is included)

it is , by and large, safer to use less than on the high boundary...

where CONVERT(datetime,Exported, 103) >= CONVERT(datetime, @date_start, 103)
    and CONVERT(datetime,Exported, 103) <  CONVERT(datetime, @date_until, 103)
 
This decision is yours to make, but if you do wish to continue date filtering this way you should make the notes at the top of the file consistent with your filtering method.

This is what the top of the file says now:
/* first the date boundaries, the lower date is INCLUDED, the upper date is EXCLUDED */
declare @date_start nvarchar(15) = '01/01/2011' /* >= -- equal and above this date */
declare @date_until nvarchar(15) = '01/01/2013' /* <  -- less than  or equal to this date */

(and I see "or equal to" has been added)

Let me finish with this thought:
(>= 2013/01/01 and <= 2014/01/01) = 1 year and 1 day

(>= 2013/01/01 and < 2014/0/01) = 1 year

Author

Commented:
Given a typical date range (a months worth of data) -

SQL Server Execution Times:
  CPU time = 31 ms,  elapsed time = 26 ms.

It's pretty fast, I'd say.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
well then, timing does not seem to be an issue - that's good.

Think we may have done this to death now, but I will also now observe that, because timing is acceptably fast - you could err on the side of caution in the "text to number" conversions. The problem is, as you well know, is that anything could get into those text fields.

Alternatively: "If it ain't broke don't fix it";

You are the  judge. Cheers.

Author

Commented:
I'll enforce it as best I can.

Otherwise I'm pretty certain I can sign this off with confidence. Thanks for all of the help, patience, and advice!

Now, do I just mark everything as a solution? haha

Author

Commented:
Ah, I entirely missed something!

When calculating the bands, the method only ever returns a value of 1 or 0

sum(case when qty1 = 0 then 0 when (totalprice1 / qty1) >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then 1 else 0 end + ...

Open in new window


It just needs a little change so that it returns the quantity instead of a constant

sum(case when qty1 = 0 then 0 when (totalprice1 / qty1) >  @bound_1_low  and totalprice1 / qty1 < @bound_1_high then qty1 else 0 end + ...

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
ooops - yes, qtyN not 1 - well spotted...

>>Now, do I just mark everything as a solution?
It would be an needlessly complex task to spread points over so many posts :)

If awarding points please recall it was Scott who insisted on summarisation, and the major calclation case statements are his, at 38910988

I think my main contributions come through code at 38917686 and (Beware of Between) 38913319

Author

Commented:
Support was thorough and informed, feedback rapid and well presented, and I came away with not only a solution, but also a strong understanding of how it was achieved and how the solution might be applied in a broader scope.

(Wow that came off super formal. I didn't think it'd just post it at the bottom of the page haha)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Thanks  Captain, it was a pleasure to assist. Many thanks to Scott too.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial