Solved

SQL Query

Posted on 2015-01-23
9
175 Views
Last Modified: 2015-01-27
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
0
Comment
Question by:enjama
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 40567171
Hi,

Given your data and requirements, I've got the following which appears to work - fairly ugly though
select 
	'Today' as period
	, count( * ) as records
	, sum( convert( money, t.price_sum )) as total
	, sum( convert( money, t.price_sum )) / count( * ) as [$/record]
from #tmp_GridResults_1 t
where 
	t.today = 1

union all

select 
	'Yesterday' as period
	, count( * ) as records
	, sum( convert( money, t.price_sum )) as total
	, sum( convert( money, t.price_sum )) / count( * ) as [$/record]
from #tmp_GridResults_1 t
where 
	t.yesterday = 1

union all

select 
	'This month' as period
	, count( * ) as records
	, sum( convert( money, t.price_sum )) as total
	, sum( convert( money, t.price_sum )) / count( * ) as [$/record]
from #tmp_GridResults_1 t
where 
	t.thismonth = 1

union all

select 
	'Last month' as period
	, count( * ) as records
	, sum( convert( money, t.price_sum )) as total
	, sum( convert( money, t.price_sum )) / count( * ) as [$/record]
from #tmp_GridResults_1 t
where 
	t.lastmonth = 1
;

Open in new window


Now if the periods were a little more even and had a datetime field, then you could do something like

select
	dateadd( month, datediff( month, 0, t.TransactionDate ), 0 ) as StartOfPeriod
	, count( * ) as Records
	, sum( t.TransactionValue ) as Total
	, sum( t.TransactionValue ) / count( * ) as AverageTransaction
from dbo.TransactionTable t
where
	somewhereclause
group by
	dateadd( month, datediff( month, 0, t.TransactionDate ), 0 )
order by
	dateadd( month, datediff( month, 0, t.TransactionDate ), 0 )
;

Open in new window


HTH
  David
0
 

Author Comment

by:enjama
ID: 40570487
I did the union all before I posted this question.  The execution plan is slow - any way to speed it up?  I'm thinking grouping sets?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40571586
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 )

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
;

Open in new window


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/9

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:enjama
ID: 40572719
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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40574263
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40574335
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]
;

Open in new window

0
 

Author Closing Comment

by:enjama
ID: 40574381
Thanks David!  I'm accepting your solution, although I ended up grouping by grouping sets.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40574429
did you not try my last suggestion? oh well, just seems odd as you specifically asked not to union.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40574512
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question