Does your customer data work for you?

Businesses that have robust reporting on their customer databases can predict future trends.

Data queries can be used to manage marketing, promotions, export email addresses or prepare shipping labels for example. Get the query wrong, and the business plans will be wrong too.

Here is a specific example provided by our latest Guest Blogger Paul Milligan from CarLand.

 

Producing a SQL Report on Data Between 2 dates?

This seems quite a trivial task, but there a few common mistakes I have made when answering this question and wish to share my thoughts with you and help you benefit from my experience.
You will also learn how to strip the time component from a date time (there are a number of ways to do this, but I’ll stick to the one I normally use).

Please note the examples below use the date format YYYYMMDD.

There are three scenarios to consider, starting with scenario 1 with an example stored procedure.

Scenario 1:

The manager asks for a daily report, i.e. everything that has happened for one whole day.

The manager also wishes to specify a date to produce the report on, e.g. “I want to produce a report for March 1st 2017”.

Seems simple,let us try this approach…

CREATE PROCEDURE
Reports.uspProductionReportForOneDay

(

@DateToShow datetime

)

AS

BEGIN

DECLARE

@StartDate datetime = @DateToShow,

@EndDate datetime = DATEADD(dd, 1, @DateToShow)

SELECT

SUM(PL.Sales),

P.ProductName

FROM

Production.tblProductLog PL

WHERE

PL.LogDate >=
@StartDate AND PL.LogDate <= @EndDate

GROUP

BY

PL.ProductName;

END;

GO

So far so good.

What if we pass in @DateToShow with a time component, e.g. 20170301 15:32:15, look at the range now…

@StartDate = ‘20170301 15:32:15’

@EndDate = ‘20170302 15:32:15’

Not quite what we want as we are now reporting on tomorrow’s product sales too!

Scenario 2:

Therefore, we will try a different approach, one I have used in the past and this has a small issue too.

DECLARE

@StartDate datetime = DATEADD(DAY, DATEDIFF(DAY, 0, @DateToShow), 0),

@EndDate datetime = DATEADD(DAY, DATEDIFF(DAY, 0, @DateToShow), 0) + ’23:59:59′;

Using this approach the range changes to:

@StartDate = ‘20170301 00:00:00’

@EndDate = ‘20170301 23:59:59’

This looks good we now get full days’ worth of data for the March 1st, or do we?
@EndDate is ‘20170301 23:59:59’ but this is not equal to ‘20170301 23:59:59.999’!
Infact this does not account for that final second to midnight!
In most cases, this report would be fine, but there is a small margin of error nevertheless.

Scenario 3:

Therefore, what we need is…

DECLARE

@StartDate datetime = DATEADD(DAY, DATEDIFF(DAY, 0, ‘20170301 15:32’), 0),

@EndDate datetime;

SET @EndDate = DATEADD(DAY, 1, @StartDate)

Now we get:

@StartDate = ‘20170301 00:00:00’

@EndDate = ‘20170302 00:00:00’;

We need to adjust the WHERE clause too in the original stored procedure; note the less than equal <= has changed to a less than <

WHERE

PL.LogDate >=
@StartDate AND PL.LogDate < @EndDate

And now we will get a full day’s worth of data.

This is also the reason why I did not use the BETWEEN keyword because this is inclusive and would not work above.

This same logic can be applied to other situations too, for example, “Please give me a stock report for the whole of March”.
You would want 20170301 00:00:00 to 20170401:00:00:00 and the same where clause above.

Final note:

With the introduction of the “date” data type in SQL 2008 the following is also possible to set up the start and end dates:

DECLARE

@DateToShow datetime = ‘20170301 15:32’;

DECLARE

@StartDate datetime = CAST(@DateToShow AS date),

@EndDate datetime;

SET @EndDate = DATEADD(DAY, 1, @StartDate)

Thanks for reading.

Paul Milligan.

Technical Lead at Carland.com.

Paul Milligan
Paul Milligan

I am the Technical Lead at Carland.com. This is a multi-skilled role involving mobile app development, web sites, web api services, backend services, and backend databases; basically full stack development. The articles I write are to help people further their skills and knowledge.