Help with SQL query date ranges

I'm trying to write a query to pull some results by date and have a running summary until the month end. I can't figure out how to write the where clause. For example, I'd like to have the report count how many records were added in the last 24 hours (I believe this is working how I have it below), but also how many were added month-to-day. Next month the count would have to reset. Can someone help with this?

SELECT @RowCount = COUNT(*)
  FROM myTable
  WHERE (DATEDIFF(DAY,[dateLastUpdated],GETDATE()) <= 1) --this is for the last 24 hours
earwig75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
First of all,  you not use an expression with columns in where if avoidable. It is better to calculate the date boundary once, and compare against that:
WHERE dateLastUpdated >= dateadd(days, -1, getdate())

Open in new window

One way to do what you want is by using a conditional count, implemented by a SUM(CASE).
SELECT @LastDayCount = SUM(case when dateLastUpdated >= dateadd(days, -1, getdate()) then 1 else 0 end,
  @LastMonthCount = SUM(case when dateLastUpdated >= dateadd(month, -1, getdate()) then 1 else 9 end
FROM myTable
WHERE dateLastUpdated >= dateadd(month, -1, getdate())

Open in new window

0
Jeff DarlingDeveloper AnalystCommented:
One way would be to convert the date and then truncate to a length of 7 to only show year-month

This will return the counts for the current month.

SELECT @RowCount = COUNT(*)
FROM myTable
WHERE left(CONVERT(CHAR(10), [dateLastUpdated], 126), 7) = left(CONVERT(CHAR(10), getdate(), 126), 7)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Sorry, that was wrong. The current month is something different ...
SELECT @LastDayCount = SUM(case when dateLastUpdated >= dateadd(days, -1, getdate()) then 1 else 0 end,
  @ThisMonthCount = count(*)
FROM myTable
WHERE dateLastUpdated >= dateadd( day, -datepart(day, getdate())+1 )

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
+ You need to take care of getdate() carrying the time as of "right now"

i.e. to ensure the "month to date" starts at YYYY-MM-01 00:00:000 you need set the time to 00:00:00

SELECT 
    SUM(case when dateLastUpdated >= dateadd(day, -1, getdate()) then 1 else 0 end) as LastDayCount
  , count(*) as ThisMonthCount
FROM myTable
WHERE dateLastUpdated >= dateadd( day, -datepart(day,getdate()), dateadd(day,1,cast(getdate() as date)) )

Open in new window


details:
MS SQL Server 2008 Schema Setup**:

    CREATE TABLE myTable
        ([dateLastUpdated] datetime)
    ;
        
    INSERT INTO myTable
        ([dateLastUpdated])
    VALUES
        ('2015-07-21 12:24:25'),
        ('2015-07-19 09:26:43'),
        ('2015-07-17 06:29:01'),
        ('2015-07-15 03:31:20'),
        ('2015-07-13 00:33:38'),
        ('2015-07-10 21:35:56'),
        ('2015-07-08 18:38:14'),
        ('2015-07-06 15:40:33'),
        ('2015-07-04 12:42:51'),
        ('2015-07-02 09:45:09'),
        ('2015-07-01 00:00:00'), /* <<<< start of month */
        ('2015-06-28 03:49:46'),
        ('2015-06-26 00:52:04'),
        ('2015-06-23 21:54:22'),
        ('2015-06-21 18:56:40'),
        ('2015-06-19 15:58:59'),
        ('2015-06-17 13:01:17'),
        ('2015-06-15 10:03:35'),
        ('2015-06-13 07:05:53'),
        ('2015-06-11 04:08:12'),
        ('2015-06-09 01:10:30'),
        ('2015-06-06 22:12:48'),
        ('2015-06-04 19:15:06')
    ;
    
**Query 1**:

    
    SELECT 
        SUM(case when dateLastUpdated >= dateadd(day, -1, getdate()) then 1 else 0 end) as LastDayCount
      , count(*) as ThisMonthCount
    FROM myTable
    WHERE dateLastUpdated >= dateadd( day, -datepart(day,getdate()), dateadd(day,1,cast(getdate() as date)) )
                               
                               

**[Results][2]**:
    | LastDayCount | ThisMonthCount |
    |--------------|----------------|
    |            1 |             11 |

  [1]: http://sqlfiddle.com/#!3/d3427/1
  [2]: http://sqlfiddle.com/#!3/d3427/1/0

Open in new window

1
earwig75Author Commented:
Thanks Jeff, this was the easiest and most straight forward solution.
0
PortletPaulfreelancerCommented:
For improved performance you should avoid using functions on data in the where clause. In particular it should be avoided when you are attempting to manipulate the data to suit "constants" (in this case  "24 hours" and "this month" are the "constants" involved)

Why?

1. Lower performance

Using a function on data to filter or join removes the ability of the optimizer to access an index on that field making the query slower than it could be.

2. Avoidable calculations

For every row of data there is another calculation to be performed; hence hundreds, thousands or millions of unnecessary calculations are being performed for comparison to the constant.
It is far more efficient to do the reverse: alter the criteria to suit the data. This debate has existed almost since the beginning of SQL and there is a word that describes it: SARGABLE

Rules of thumb (for sargable predicates)
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.

One further thing.
Date/Time information is stored as numeric. Filtering by numeric information is faster than string comparisons. So conversion of dates to varchar for filtering is also slower than just leaving that data unchanged.

Bottom line:
The apparent simplicity of the SQL code isn't a good guide to its efficiency. Sargable predicates almost always look more complex than the less efficient approach.

ps: I'm not seeking any change to status of this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.