Search yesterdays date:

Question

Post reply

Search yesterdays date:

jman76

Hall of Fame

Points: 3388
More actions
September 7, 2012 at 4:44 am

#261584

I have created two parameters to search between two dates:

I would like to add an expressio so that when the user clicks view report, they will be able to view yesterdays data, without them having to use the date selector.

I have seen a expression as Today()

How can i create an expression to run yesterdays date?

My SQL is as follows:

SET DATEFORMAT DMY

SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date

FROM View_telesales

WHERE (Invoice_Date BETWEEN @StartDate AND @EndDate)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi

Add another parameter something like @DisplayYesterday

then in your dataset:



IF @DisplayYesterday = 1

SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date

FROM View_telesales

WHERE (Invoice_Date > = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Invoice_Date <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

ELSE IF @DisplayYesterday = 0

SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date

FROM View_telesales

WHERE (Invoice_Date > = @StartDate AND Invoice_Date < = @EndDate)

Then hide unused parameters as required

Also be careful of BETWEEN it can drop data off

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Andy Hyslop (9/7/2012)

Hi

Add another parameter something like @DisplayYesterday

then in your dataset:

IF @DisplayYesterday = 1 SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date FROM View_telesales WHERE (Invoice_Date > = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Invoice_Date <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) ELSE IF @DisplayYesterday = 0 SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date FROM View_telesales WHERE (Invoice_Date > = @StartDate AND Invoice_Date < = @EndDate)

Then hide unused parameters as required

Also be careful of BETWEEN it can drop data off

Andy

I'm not sure because I haven't tried your code but I believe your code will run into problems if the Invoice_Date has other than a midnight time element on it, Andy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Hi Jeff

Good catch I think that you may be correct on that thinking about it (although I haven't tested either)

I suppose this would be a work around for that:



IF @DisplayYesterday = 1

SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date

FROM View_telesales

WHERE

CAST(Invoice_Date AS DATE) > = CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS DATE)

AND CAST(Invoice_Date AS DATE) < CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) AS DATE)

ELSE IF @DisplayYesterday = 0

SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date

FROM View_telesales

WHERE (Invoice_Date > = @StartDate AND Invoice_Date < = @EndDate)

Bit ugly tho

could also default the start and end times in the first WHERE to take into account times..

I may be incorrect on this but doesn't SSRS default its date parameters to midnight?

Cheers

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Andy Hyslop (9/7/2012)

Hi Jeff

Good catch I think that you may be correct on that thinking about it (although I haven't tested either)

I suppose this would be a work around for that:

IF @DisplayYesterday = 1 SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date FROM View_telesales WHERE CAST(Invoice_Date AS DATE) > = CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS DATE) AND CAST(Invoice_Date AS DATE) < CAST(DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) AS DATE) ELSE IF @DisplayYesterday = 0 SELECT UserId, Class, SumInvoiceValue, SumCostPrice, Margin, Invoice_Date FROM View_telesales WHERE (Invoice_Date > = @StartDate AND Invoice_Date < = @EndDate)

Bit ugly tho

could also default the start and end times in the first WHERE to take into account times..

I may be incorrect on this but doesn't SSRS default its date parameters to midnight?

Cheers

Andy

Actually, you don't need all those casts. The following will return all invoices for yesterday:



SELECT

    UserId,

    Class,

    SumInvoiceValue,

    SumCostPrice,

    Margin,

    Invoice_Date

FROM

    View_telesales

WHERE

    Invoice_Date >=  DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0) AND -- for 2012-09-07 08:20:00, returns 2012-09-06 00:00:00.000

    Invoice_Date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0); -- for 2012-09-07 08:20:00, returns 2012-09-07 00:00:00.000