SQL Complex Query
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!
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!
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 */
not sure how close the query above is, but with a bit more polish it might be like this:
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.
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 */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.
ASKER
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!
Thanks for the quick responses!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
>> 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
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 250ASKER
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.
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.
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.
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.
ASKER
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:
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:
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:
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_
to avoid collation conflicts between the databases.
Here's my SELECT from the original query:
Hopefully that makes my requirements perfectly clear but I'm around to answer questions.
Thanks again!!
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_
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_mainHopefully that makes my requirements perfectly clear but I'm around to answer questions.
Thanks again!!
entirely sparsemay 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.
ASKER
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.
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.
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)?????
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)?????
ASKER
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.
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.
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?
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?
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.
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
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)
ASKER
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):
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:
In terms of performance, expectations are low. It won't be run frequently and access to the database isn't particularly busy.
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.
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.
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.
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.
>>
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.
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.
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.
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.
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.
ASKER
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:
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
ASKER
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
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:
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 revealedASKER
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...
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...
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)
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)
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?
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
) bands1so, 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?
ASKER
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:
--
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_Commission
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
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)--
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_Commission
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
ASKER
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 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.
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.
ASKER
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)?
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]
@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.
@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.
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
and CONVERT(datetime,[Exported
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
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
and CONVERT(datetime,[Exported
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
ASKER
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).
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).
try it database by database, in chunks to iron out the wrinkles
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi captain, (interesting nickname by the way)
How is the mega summarisation query going? Any progress or issues?
How is the mega summarisation query going? Any progress or issues?
ASKER
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.
I'm back on task this morning and I'll get back to you when I've tried out the new query.
ASKER
Hmm, I've been getting that same error, but I've determined that it's specific to database HMSUpgrade.
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.
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.
ASKER
It's definitely a value in the commission1 field.
I'll do a bit more investigating.
I'll do a bit more investigating.
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!!!
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!!!
oh, you could try something like this:
select
commission1
from that_bad_table
where isnumeric([commission1]) <> 1
and ... your date filters
commission1
from that_bad_table
where isnumeric([commission1]) <> 1
and ... your date filters
ASKER
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.
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.
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 :)
I cannot be blamed for those tables designs either by the way :)
ASKER
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 -
Please let me know if there is anything glaringly wrong!
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]Please let me know if there is anything glaringly wrong!
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)
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)
actually, there is this...
where CONVERT(datetime,Exported,
and CONVERT(datetime,Exported,
(here the higher date is included)
it is , by and large, safer to use less than on the high boundary...
where CONVERT(datetime,Exported,
and CONVERT(datetime,Exported,
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
where CONVERT(datetime,Exported,
and CONVERT(datetime,Exported,
(here the higher date is included)
it is , by and large, safer to use less than on the high boundary...
where CONVERT(datetime,Exported,
and CONVERT(datetime,Exported,
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
ASKER
Given a typical date range (a months worth of data) -
It's pretty fast, I'd say.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 26 ms.
It's pretty fast, I'd say.
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.
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.
ASKER
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
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
ASKER
Ah, I entirely missed something!
When calculating the bands, the method only ever returns a value of 1 or 0
It just needs a little change so that it returns the quantity instead of a constant
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 + ...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 + ...
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
>>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
ASKER
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)
(Wow that came off super formal. I didn't think it'd just post it at the bottom of the page haha)
Thanks Captain, it was a pleasure to assist. Many thanks to Scott too.
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