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 https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements. Also `tblLabTestResult` is a very long alias, try to make it more readable with something a bit shorter — Charlieface, Aug 31 '21 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, Aug 31 '21 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, Aug 31 '21 at 13:44
`'__DATE__'` Is not a macro nor does tsql support that idea. So perhaps your application generates an actual date and replaces it in the query - either way it is a constant as far as the query is concerned. If you change that to a column, then you need to add the column to your GROUP BY clause. And beware - your logic assumes that there is at least one row with your desired dates. If your resultset requires a zero value for every date of concern, you need a different approach. — SMor, Aug 31 '21 at 14:24
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, Aug 31 '21 at 14:26
Again "`tblLabTestResult.CollectionDate` is invalid in the select list because it is not contained in either an aggregate function or the `GROUP BY` clause" is a pretty clear error message: you need to add it to the `GROUP BY`, which if you think about it is prefectly logical. We can't write every line for you and do all your work, you need to problem-solve and look at tutorials yourself. If there is something specific which you are stuck on and cannot find any help online by Googling/etc, come back and ask. — Charlieface, Aug 31 '21 at 14:55

score 1 · Answer 1 · answered Aug 31 '21 at 13:55

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;

score 1 · Answer 2 · answered Aug 31 '21 at 15:04

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;

Expanding SQL query for multiple dates

2 Answers2