enjama
asked on
SQL Query
I have a view I'm trying to query - it has a today column, yesterday column, thismonth column, and a lastmonth column as well as price info.
I want a summary of results - total price for today, total price for yesterday, total price this month (including today and yesterday), total price for last month (including yesterday if that is applicable.
I have attached the script to create a temp table which looks like the view.
My end result should look like
period records Total $/record
--------- ----------- --------------------------
lastmonth 1447 1949.49 1.3473
this month 1321 1674.32 1.2675
today 28 35.40 1.2643
yesterday 50 75.00 1.50
How can i query ?
table-contents.sql
I want a summary of results - total price for today, total price for yesterday, total price this month (including today and yesterday), total price for last month (including yesterday if that is applicable.
I have attached the script to create a temp table which looks like the view.
My end result should look like
period records Total $/record
--------- ----------- --------------------------
lastmonth 1447 1949.49 1.3473
this month 1321 1674.32 1.2675
today 28 35.40 1.2643
yesterday 50 75.00 1.50
How can i query ?
table-contents.sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would help performance if your data wasn't stored as nvarchar columns. Instead make them integers or money as fits the data. Not sure why that temp table was all nvarchar.
This approach avoids unions. Instead it does a simple group by (as a subquery) then "unpivots" that result using cross apply with values (for more on that technique see this blog and this EE article )
Details:
This approach avoids unions. Instead it does a simple group by (as a subquery) then "unpivots" that result using cross apply with values (for more on that technique see this blog and this EE article )
select
ca.period
, ca.records
, ca.total
, ca.total / ca.records as "$/record"
from (
select
[period]
, sum([price_sum]) as [price_sum]
, count(*) as [records]
from (
select
case when [today] = 1 then 'Today'
when [yesterday] = 1 then 'Yesterday'
when [thismonth] = 1 then 'This Month'
when [lastmonth] = 1 then 'Last Month'
end as [period]
, convert( money, t.price_sum ) as [price_sum]
from tmp_GridResults_1 t
) as u
group by
[period]
) as v
cross apply (
values
(case when v.period = 'Today' then [period] end, case when v.period = 'Today' then v.[records] end, case when v.period = 'Today' then v.[price_sum] end )
, (case when v.period = 'Yesterday' then [period] end, case when v.period = 'Yesterday' then v.[records] end, case when v.period = 'Yesterday' then v.[price_sum] end )
, (case when v.period = 'This Month' then [period] end, case when v.period = 'This Month' then v.[records] end, case when v.period = 'This Month' then v.[price_sum] end )
, (case when v.period = 'Last Month' then [period] end, case when v.period = 'Last Month' then v.[records] end, case when v.period = 'Last Month' then v.[price_sum] end )
) as ca ([period],[records],[total])
where ca.period is not null
;Details:
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE tmp_GridResults_1
([TODAY] int, [YESTERDAY] int, [THISMONTH] int, [LASTMONTH] int, [RECORD_ID] int, [PRICE] int, [RECORD_COUNT] int, [PRICE_SUM] int)
;
INSERT INTO tmp_GridResults_1
([TODAY], [YESTERDAY], [THISMONTH], [LASTMONTH], [RECORD_ID], [PRICE], [RECORD_COUNT], [PRICE_SUM])
VALUES
(1, 0, 0, 0, 8108364, 0.60, 1, 0.60),
(1, 0, 0, 0, 8139339, 1.00, 1, 1.00),
(1, 0, 0, 0, 8150460, 4.00, 1, 4.00),
(1, 0, 0, 0, 8155862, 4.00, 1, 4.00),
(1, 0, 0, 0, 8157908, 1.00, 1, 1.00),
(1, 0, 0, 0, 8158261, 1.00, 1, 1.00),
(1, 0, 0, 0, 8158277, 4.00, 1, 4.00),
(0, 1, 0, 0, 8108364, 0.60, 1, 0.60),
(0, 1, 0, 0, 8139339, 1.00, 1, 1.00),
(0, 1, 0, 0, 8150460, 4.00, 1, 4.00),
(0, 1, 0, 0, 8155862, 4.00, 1, 4.00),
(0, 1, 0, 0, 8157908, 1.00, 1, 1.00),
(0, 1, 0, 0, 8158261, 1.00, 1, 1.00),
(0, 1, 0, 0, 8158277, 4.00, 1, 4.00),
(0, 0, 1, 0, 8108364, 0.60, 1, 0.60),
(0, 0, 1, 0, 8139339, 1.00, 1, 1.00),
(0, 0, 1, 0, 8150460, 4.00, 1, 4.00),
(0, 0, 1, 0, 8155862, 4.00, 1, 4.00),
(0, 0, 1, 0, 8157908, 1.00, 1, 1.00),
(0, 0, 1, 0, 8158261, 1.00, 1, 1.00),
(0, 0, 1, 0, 8158277, 4.00, 1, 4.00),
(0, 0, 0, 1, 8108364, 0.60, 1, 0.60),
(0, 0, 0, 1, 8139339, 1.00, 1, 1.00),
(0, 0, 0, 1, 8150460, 4.00, 1, 4.00),
(0, 0, 0, 1, 8155862, 4.00, 1, 4.00),
(0, 0, 0, 1, 8157908, 1.00, 1, 1.00),
(0, 0, 0, 1, 8158261, 1.00, 1, 1.00),
(0, 0, 0, 1, 8158277, 4.00, 1, 4.00),
(0, 0, 0, 1, 8158370, 0.40, 1, 0.40),
(0, 0, 0, 1, 8158468, 0.10, 1, 0.10),
(0, 0, 0, 1, 8162133, 0.50, 1, 0.50),
(0, 0, 0, 1, 8185991, 4.00, 1, 4.00),
(0, 0, 0, 1, 8198302, 4.00, 1, 4.00),
(0, 0, 0, 1, 8198587, 1.00, 1, 1.00),
(0, 0, 0, 1, 8205676, 4.00, 1, 4.00),
(0, 0, 0, 1, 8219799, 1.00, 1, 1.00),
(0, 0, 0, 1, 8223108, 1.00, 1, 1.00),
(0, 0, 0, 1, 8223391, 1.00, 1, 1.00),
(0, 0, 0, 1, 8224794, 0.10, 1, 0.10),
(0, 0, 0, 1, 8225338, 4.00, 1, 4.00),
(0, 0, 0, 1, 8225440, 1.00, 1, 1.00),
(0, 0, 0, 1, 8226264, 0.20, 1, 0.20),
(0, 0, 0, 1, 8226765, 1.00, 1, 1.00),
(0, 0, 0, 1, 8227505, 0.50, 1, 0.50),
(0, 0, 0, 1, 8227628, 1.00, 1, 1.00),
(0, 0, 0, 1, 8228380, 4.00, 1, 4.00),
(0, 0, 0, 1, 8228787, 0.20, 1, 0.20),
(0, 0, 0, 1, 8228975, 0.50, 1, 0.50),
(0, 0, 0, 1, 8228989, 1.00, 1, 1.00),
(0, 0, 0, 1, 8229130, 1.00, 1, 1.00),
(0, 0, 0, 1, 8229370, 1.00, 1, 1.00),
(0, 0, 0, 1, 8230594, 1.00, 1, 1.00),
(0, 0, 0, 1, 8230677, 1.00, 1, 1.00),
(0, 0, 0, 1, 8230691, 1.00, 1, 1.00),
(0, 0, 0, 1, 8230900, 0.50, 1, 0.50),
(0, 0, 0, 1, 8230921, 0.80, 1, 0.80),
(0, 0, 0, 1, 8230927, 0.40, 1, 0.40),
(0, 0, 0, 1, 8231002, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231101, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231110, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231120, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231164, 0.20, 1, 0.20),
(0, 0, 0, 1, 8231267, 4.00, 1, 4.00),
(0, 0, 0, 1, 8231390, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231444, 0.20, 1, 0.20),
(0, 0, 0, 1, 8231559, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231681, 0.50, 1, 0.50),
(0, 0, 0, 1, 8231760, 0.90, 1, 0.90),
(0, 0, 0, 1, 8231793, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231807, 0.80, 1, 0.80),
(0, 0, 0, 1, 8231817, 4.00, 1, 4.00),
(0, 0, 0, 1, 8231856, 0.50, 1, 0.50),
(0, 0, 0, 1, 8231891, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231938, 0.50, 1, 0.50),
(0, 0, 0, 1, 8231973, 1.00, 1, 1.00),
(0, 0, 0, 1, 8231981, 0.50, 1, 0.50),
(0, 0, 0, 1, 8232014, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232094, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232128, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232160, 0.50, 1, 0.50),
(0, 0, 0, 1, 8232175, 0.10, 1, 0.10),
(0, 0, 0, 1, 8232210, 0.10, 1, 0.10),
(0, 0, 0, 1, 8232267, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232318, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232334, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232346, 1.00, 1, 1.00),
(0, 0, 0, 1, 8232387, 4.00, 1, 4.00),
(0, 0, 0, 1, 8232444, 1.00, 1, 1.00)
;
**Query 1**:
select
ca.period
, ca.records
, ca.total
, ca.total / ca.records as "$/record"
from (
select
[period]
, sum([price_sum]) as [price_sum]
, count(*) as [records]
from (
select
case when [today] = 1 then 'Today'
when [yesterday] = 1 then 'Yesterday'
when [thismonth] = 1 then 'This Month'
when [lastmonth] = 1 then 'Last Month'
end as [period]
, convert( money, t.price_sum ) as [price_sum]
from tmp_GridResults_1 t
) as u
group by
[period]
) as v
cross apply (
values
(case when v.period = 'Today' then [period] end, case when v.period = 'Today' then v.[records] end, case when v.period = 'Today' then v.[price_sum] end )
, (case when v.period = 'Yesterday' then [period] end, case when v.period = 'Yesterday' then v.[records] end, case when v.period = 'Yesterday' then v.[price_sum] end )
, (case when v.period = 'This Month' then [period] end, case when v.period = 'This Month' then v.[records] end, case when v.period = 'This Month' then v.[price_sum] end )
, (case when v.period = 'Last Month' then [period] end, case when v.period = 'Last Month' then v.[records] end, case when v.period = 'Last Month' then v.[price_sum] end )
) as ca ([period],[records],[total])
where ca.period is not null
**[Results][2]**:
| PERIOD | RECORDS | TOTAL | $/RECORD |
|------------|---------|-------|----------|
| Last Month | 67 | 77 | 1.1492 |
| This Month | 7 | 15 | 2.1428 |
| Today | 7 | 15 | 2.1428 |
| Yesterday | 7 | 15 | 2.1428 |
[1]: http://sqlfiddle.com/#!3/f25471/9ASKER
This was very much like my original query, and it does the same thing, yesterday and today need to be included in this month - with this they are not.
Hi,
I suggest then that you look at the group I did.
Write two queries: Group one by months, and one by days, and union the results together.
HTH
David
PS With the dates as datetimes you'll find that performance is fairly good.
I suggest then that you look at the group I did.
Write two queries: Group one by months, and one by days, and union the results together.
HTH
David
PS With the dates as datetimes you'll find that performance is fairly good.
select
[period]
, sum([price_sum]) as [price_sum]
, count(*) as [records]
, sum([price_sum]) / count(*) as "$/record"
from (
select
ca.*
from tmp_GridResults_1 t
cross apply (
values
(case when [today]=1 then 'today' end, case when [today]=1 then record_id end, case when [today]=1 then convert(money,price_sum) end)
, (case when [yesterday]=1 then 'yesterday' end, case when [yesterday]=1 then record_id end, case when [yesterday]=1 then convert(money,price_sum) end)
, (case when [lastmonth]=1 then 'lastmonth' end, case when [lastmonth]=1 then record_id end, case when [lastmonth]=1 then convert(money,price_sum) end)
, (
case when [today]=1 or [yesterday]=1 or [thismonth]=1 then 'thismonth' end
, case when [today]=1 or [yesterday]=1 or [thismonth]=1 then record_id end
, case when [today]=1 or [yesterday]=1 or [thismonth]=1 then convert(money,price_sum) end
)
) ca (period,record_id, price_sum)
where ca.period is not null
) as u
group by
[period]
;ASKER
Thanks David! I'm accepting your solution, although I ended up grouping by grouping sets.
did you not try my last suggestion? oh well, just seems odd as you specifically asked not to union.
Hi,
There's a couple of points implied in my answer that often have to spelt out to the business.
1. Sometimes the requirements should be changed a little to simplify the reporting.
2. Sometimes the data needs to be shaped for said reporting.
What do I mean? For instance, in the above scenario, mixing days and months in the grouping meant that there was no one measure to group on.
Shaping the data - I find it relatively easy to group on datetime fields by the datepart interval. I've seen a post where the datetime dimension for a datamart had almost every conceivable derivation of the datetime. The logic behind it was that the reporting was easier.
HTH
David
There's a couple of points implied in my answer that often have to spelt out to the business.
1. Sometimes the requirements should be changed a little to simplify the reporting.
2. Sometimes the data needs to be shaped for said reporting.
What do I mean? For instance, in the above scenario, mixing days and months in the grouping meant that there was no one measure to group on.
Shaping the data - I find it relatively easy to group on datetime fields by the datepart interval. I've seen a post where the datetime dimension for a datamart had almost every conceivable derivation of the datetime. The logic behind it was that the reporting was easier.
HTH
David
ASKER