tsql query efficiency

Question

I have a table that is defined as follows:

CREATE TABLE [dbo].[ListingStats](
    [ListingStatID] [int] IDENTITY(1,1) NOT NULL,
    [StatTypeID] [int] NOT NULL,
    [CreatedDate] [date] NOT NULL,
    [ListingID] [int] NOT NULL,
    CONSTRAINT [PK_ListingStats] PRIMARY KEY CLUSTERED 
(
[ListingStatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am trying to get 4 totals for each type (listingStatTypeID) for today, last 7 days, last 30 days, and all time.

I am not really sure what the most efficient query will be. Right now I have the following that I can repeat for each TYPE but that will be a big query and it will be a lot of calls.

declare @listingID int
set @listingID = 209722
--today
select count(1) from ListingStats where listingid = @listingid and CreatedDate = getdate()
--last 7 days
select count(1) from ListingStats where listingid = @listingid and CreatedDate > getdate()-7
--last 30 days
select count(1) from ListingStats where listingid = @listingid and CreatedDate > getdate()-30 
--all time
select count(1) from ListingStats where listingid = @listingid

I am always calling this for one listing at a time by providing a listingID. If anyone could provide a direction I would appreciate it. Should I create a view of some sort?

Expected Results

-Type------Today-----7Days---30Days---Ever
  1         44        50       500    5000
  2         22        40       90     1000
  3         55        55       555    5555

Community · Accepted Answer · 2017-05-23 11:50:56Z

2

For a single listing, you can use CASE expressions to get the four values in a single query:

SELECT SUM(CASE WHEN CreatedDate = getdate()    THEN 1 ELSE 0 END) AS v_today,
       SUM(CASE WHEN CreatedDate > getdate()-7  THEN 1 ELSE 0 END) AS v_week,
       SUM(CASE WHEN CreatedDate > getdate()-30 THEN 1 ELSE 0 END) AS v_month,
       COUNT(*) AS v_alltime
  FROM ListingStats
 WHERE listingid = @listingid

For all listing IDs, then

SELECT ListingID,
       SUM(CASE WHEN CreatedDate = getdate()    THEN 1 ELSE 0 END) AS v_today,
       SUM(CASE WHEN CreatedDate > getdate()-7  THEN 1 ELSE 0 END) AS v_week,
       SUM(CASE WHEN CreatedDate > getdate()-30 THEN 1 ELSE 0 END) AS v_month,
       COUNT(*) AS v_alltime
  FROM ListingStats
 GROUP BY ListingID

If you want to get summaries for each StatTypeID (within a ListingID), then:

SELECT ListingID,
       StatTypeID,
       SUM(CASE WHEN CreatedDate = getdate()    THEN 1 ELSE 0 END) AS v_today,
       SUM(CASE WHEN CreatedDate > getdate()-7  THEN 1 ELSE 0 END) AS v_week,
       SUM(CASE WHEN CreatedDate > getdate()-30 THEN 1 ELSE 0 END) AS v_month,
       COUNT(*) AS v_alltime
  FROM ListingStats
 GROUP BY ListingID, StatTypeID

If you want the summaries by StatTypeID across all ListingIDs, then:

SELECT StatTypeID,
       SUM(CASE WHEN CreatedDate = getdate()    THEN 1 ELSE 0 END) AS v_today,
       SUM(CASE WHEN CreatedDate > getdate()-7  THEN 1 ELSE 0 END) AS v_week,
       SUM(CASE WHEN CreatedDate > getdate()-30 THEN 1 ELSE 0 END) AS v_month,
       COUNT(*) AS v_alltime
  FROM ListingStats
 GROUP BY StatTypeID

Judging from the 'expected results' section that has been added, this last query is closest to what you need. And judging from the comments, if you want the summaries by StatTypeID for a specific ListingID, then:

SELECT StatTypeID,
       SUM(CASE WHEN CreatedDate = getdate()    THEN 1 ELSE 0 END) AS v_today,
       SUM(CASE WHEN CreatedDate > getdate()-7  THEN 1 ELSE 0 END) AS v_week,
       SUM(CASE WHEN CreatedDate > getdate()-30 THEN 1 ELSE 0 END) AS v_month,
       COUNT(*) AS v_alltime
  FROM ListingStats
 WHERE ListingID = @listingid
 GROUP BY StatTypeID

If there are issues with your getdate() function calls (as suggested by Gordon Linoff in his answer), you also need to fix those.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Dec 15, 2014 at 16:06

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zoinky Over a year ago

I think your last query is what I am looking for, but changing the end to where listingid = @listingid GROUP BY StatTypeID and removing listingid from select, also fixing the date as Gordon pointed out

Zoinky Over a year ago

Is there any benefit in putting this query in a view? it will be called on demand (user has to click a button to bring the stats up)

Jonathan Leffler Over a year ago

If you're still looking for the information for a single ListingID value, then yes, modify the last query with WHERE ListingID = @listingid.

Jonathan Leffler Over a year ago

The benefit of the view is that you write SELECT * FROM ViewName WHERE ListingID = @listingid instead of the long query. If there are going to be multiple places where you need the information, then a view centralizes the query. So yes, there could be some benefit to creating a view.

Zoinky Over a year ago

perfect thanks, also is there an easy way to add a fifth column that will be the sum of all the other sums instead of looping and adding them together?

|

Gordon Linoff · Accepted Answer · 2014-12-15 16:17:26Z

1

You can do this with conditional aggregation. This puts the values into four columns:

select sum(case when CreatedDate = cast(getdate() as date) then 1 else 0 end) as today,
       sum(case when CreatedDate > cast(getdate() - 7 as date) then 1 else 0 end) as lastweek,
       sum(case when CreatedDate > cast(getdate() - 30 as date) then 1 else 0 end) as last30,
       count(*) as ever  
from ListingStats
where listingid = @listingid;

Note that getdate() returns a datetime, with a time component (despite the name), so this query casts the value to a date (removing the time component).

To get this for all listing ids:

select sum(case when CreatedDate = cast(getdate() as date) then 1 else 0 end) as today,
       sum(case when CreatedDate > cast(getdate() - 7 as date) then 1 else 0 end) as lastweek,
       sum(case when CreatedDate > cast(getdate() - 30 as date) then 1 else 0 end) as last30,
       count(*) as ever  
from ListingStats
group by listingid;

edited Dec 15, 2014 at 16:17

answered Dec 15, 2014 at 16:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Zoinky Over a year ago

Thank you, now I have to do this for reach type, should i be calling the same query for each type or is there more efficient way to separate them, I will update original post on how i need the result to look like

Deep Over a year ago

adding that column in group by <columns> will do your work.

Collectives™ on Stack Overflow

tsql query efficiency

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related