Expanding SQL query for multiple dates

Question

I have a SQL query that includes a __DATE__ macro. A Python script replaces this macro with the current date and then the statement is executed thus giving one day's worth of data.

For the first item selected, I would like to use tblLabTestResult.CollectionDate instead of __DATE__.
I would like to include the prior 7 days instead of just the current day.
The desired output would be something similar to:

    Date,Result,Total
    2021-08-28,Detected,5
    2021-08-28,Not Detected,9
    2021-08-29,Detected,23
    2021-08-29,Not Detected,6
    2021-08-30,Detected,88
    2021-08-30,Not Detected,26

Current query:

    SELECT
        '__DATE__' as Date,
        tblLabTestResult.Result as Result,
        Count(tblLabTestResult.Result) as Total
    FROM
        PncRegDb.dbo.tblLabTestResult as tblLabTestResult
    WHERE
        tblLabTestResult.TestName like '%cov%'
        AND tblLabTestResult.TestName not like '%aoe%'
        AND tblLabTestResult.TestName not like '%antibody%'
        AND tblLabTestResult.CollectionDate >= '__DATE__'
        AND tblLabTestResult.CollectionDate <= '__DATE__ 11:59:59 PM'
    GROUP BY
        tblLabTestResult.Result;

How can I change my SQL query to accommodate these requirements? I am using MS SQL Server.

What have you tried so far? Obvious things would be for 1. a CASE expression (not sure what you mean by "first item") 2. DATEADD(day, -7.... Note that you really shouldn't have scripts doing find/replace, use proper parameterization instead, see stackoverflow.com/questions/7505808/…. Also tblLabTestResult is a very long alias, try to make it more readable with something a bit shorter — Charlieface
– Charlieface, Commented Aug 31, 2021 at 13:27
So what's your problem, just select it SELECT tblLabTestResult.CollectionDate as Date I'm striggling to understand what problems you are having here, please be clear? — Charlieface
– Charlieface, Commented Aug 31, 2021 at 13:36
When I try this, I get this error: tblLabTestResult.CollectionDate is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Also my knowledge of SQL is limited on how to fix this. — jftuga
– jftuga, Commented Aug 31, 2021 at 13:44
And another warning. Don't use inclusive value for your upper boundary. It may be unlikely but 23:59:59 is NOT the last possible time value within a given date. Far better to use an exclusive upper boundary of the next date at 00:00:00. — SMor
– SMor, Commented Aug 31, 2021 at 14:26

Syed · Accepted Answer · 2021-08-31 13:55:16Z

1

You can use DATEADD() function to get the date from 7 days ago and use all dates between date-7days and date. I have updated where condition in your query below:

SELECT
    '__DATE__' as Date,
    tblLabTestResult.Result as Result,
    Count(tblLabTestResult.Result) as Total
FROM
    PncRegDb.dbo.tblLabTestResult as tblLabTestResult
WHERE
    tblLabTestResult.TestName like '%cov%'
    AND tblLabTestResult.TestName not like '%aoe%'
    AND tblLabTestResult.TestName not like '%antibody%'
    AND tblLabTestResult.CollectionDate between DATEADD(day, -7, '__DATE__') and '__DATE__ 11:59:59 PM'
GROUP BY
    tblLabTestResult.Result;

answered Aug 31, 2021 at 13:55

Syed

14610 bronze badges

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

Comments

Charlieface · Accepted Answer · 2021-08-31 15:04:16Z

A few points:

Columns that are not aggregated must be in the GROUP BY
You should be passing your date as a parameter
Best to use a half-open interval to compare dates (exclusive end-point), so @endDate is the day after the one you want
Use short, meaningful aliases to make your code more readable
It doesn't make sense to group and aggregate by the same column. If Result is a non-nullable column then Count(Result) is the same as Count(*)
If you want to group by whole days (and CollectionDate has a time component) then replace ltr.CollectionDate with CAST(ltr.CollectionDate AS date) in both the SELECT and GROUP BY

SELECT
    ltr.CollectionDate as Date,
    ltr.Result as Result,
    COUNT(*) as Total
FROM
    PncRegDb.dbo.tblLabTestResult as tblLabTestResult
WHERE
    ltr.TestName like '%cov%'
    AND ltr.TestName not like '%aoe%'
    AND ltr.TestName not like '%antibody%'
    AND ltr.CollectionDate >= @startdate
    AND ltr.CollectionDate < @endDate
GROUP BY
    ltr.CollectionDate, ltr.Result;

Collectives™ on Stack Overflow

Expanding SQL query for multiple dates

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related