SQL Server SQL: If date in one table is within 12 months of a date in another table then Yes else No

rdrgkggo 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(169)

I have two tables. One for tests recorded and one for pre test questionnaire. I am wanting to select from my test recorded table and have a column for 'Pre test Questionnaire completed?' which would contain yes or no

A questionnaire must have been recorded within 12 months prior to doing a test. So, if I do a questionnaire on 01/01/2023 and do a test on 10/01/2023, the result would be yes for the column 'Pre test Questionnaire completed?'. If the dates where the other way round and I did a test on 01/01/2023 and a questionnaire on 10/01/2023, I would want the result to be no as the questionnaire was not recorded within 12 months of a test.

The tables join via a PersonID.

Table: tests recorded
| Person | Test Date |
| ------------ | ------------ |
| 1111 | 01/01/2023 |
| 2222 | 05/01/2023 |
| 3333 | 10/01/2023 |

Table: Questionnaire recorded

Person	Q Date
1111	10/12/2022
2222	20/05/2023

Desired output would be:

Person	Q Date	Q'nnaire completed in last 12months?
1111	10/12/2022	Yes
2222	20/05/2023	No
3333	10/01/2023	No

Desired output because person 1111 has questionnaire completed before the test, the other 2 don't.

Worth noting a person can have several questionnaires and tests.

If anyone is able to help with the correct join/ case statement for my additional column in the select that would be great. I've tried so many different dateadd/datediff and can't manage to work the logic.

Thanks

**EDIT:

I am using SQL Server. The following is the query I have tried:

SELECT wt.[WalkTestPK]
      ,wt.[PersonID]
      ,wt.[CompletionDate]
      , case 
      when (Q.[Completion time]) >= dateadd(day,365,wt.[CompletionDate]) then 'yes'
      else 'no'
      end as [Was a questionnaire completed within 12 months prioir to this test?]
      FROM [WalkTests] wt
  left join Questionnaires Q on Q.[Service Number] = wt.PersonID

Some clarification on multiple tests and questionnaires - An individual is required to do a test twice a year. A questionnaire should be filled in before a test and is valid for 12 months and therefore one questionnaire should cover 2 tests. What I want to do is select the data from my test table and query the questionnaire table to see if a questionnaire was filled out within the 12 months before a test.

In summary, if a record exists in the Questionnaire table for the PersonID who has done a test in the test table, I want to know if the record in the questionnaire table is within 12 months prior to the test date.

Hope this helps.

sql-server

来源：https://stackoverflow.com/questions/77245514/sql-if-date-in-one-table-is-within-12-months-of-a-date-in-another-table-then-ye

2条答案

按热度按时间

wydwbb8l1#

If a record exists in the Questionnaire table for the PersonID who has done a test in the test table, I want to know if the record in the questionnaire table is within 12 months prior to the test date.

This is the easiest case: you just want to know whether a relevant questionnaire exists for each test, so your output is just yes/no. You only join the questionnaires that you actually want to count, then you say Yes if a test has one and No if it doesn’t ( null ). Since there may be multiple matching questionnaires, you can avoid spread rows using distinct (or group by but I’m lazy)

select distinct T.WalkTestPK
    , T.PersonID
    , T.CompletionDate
    , [Questionnaire within 12 months] = iif(Q.ID is null, 'No', 'Yes')
from WalkTests T
    left join Questionnaires Q on Q.[Service Number] = T.PersonID
                              and Q.[Completion Time] >= dateadd(year, -1, T.CompletionDate)

If you need to output the completion date from the Questionnaires table, your result rows will spread. To avoid this, use group by and output max(Q.[Completion Time]) to show the latest matching questionnaire.

If you need to output even more stuff from the questionnaire, not just the latest date, you can use an ordered outer apply . This will give you all columns from the single newest questionnaire per person (or null if there wasn’t any within 1 year):

select T.WalkTestPK
    , T.PersonID
    , T.CompletionDate
    , [Questionnaire within 12 months] = iif(Q.Id is null, 'No', 'Yes')
    , Q.[Completion Time]
    , Q.ShoeSize
    , Q.Whatever
    , Q.SomeOtherColumn
from WalkTests T
    outer apply (select top 1 *
                 from Questionnaires Q
                 where Q.[Service Number] = T.PersonID
                     and Q.[Completion Time] >= dateadd(year, -1, T.CompletionDate)
                 order by Q.[Completion Time] desc) Q

(note I’m just writing this dry without standing up test data, there may be syntax errors or other mistakes)

赞(0）回复(0）举报 2023-10-15

eiee3dmh2#

Something like this should do it:

SELECT WalkTests.WalkTestID,
       WalkTests.PersonID,
       WalkTests.TestDate,
       CASE WHEN MAX(Questionnaires.QDate) > DATEADD(YEAR, -1, WalkTests.TestDate) THEN 'YES' ELSE 'NO' END AS [Completed within 12 months] 
FROM WalkTests
    LEFT JOIN Questionnaires ON WalkTests.PersonID = Questionnaires.PersonID
GROUP BY WalkTests.PersonID, WalkTests.TestDate
ORDER BY WalkTests.PersonID

You want to group by the WalkTests columns you want to display and select the MAX(Questionnaires.QDate) and then check if that is greater than 1 year before the test date.

Also, you will need to make sure you include the WalkTests PK in the query just so you don't just assume people who've taken multiple tests didn't test on the same day--i.e., you only want questionnaire records being grouped.

http://sqlfiddle.com/#!18/0a4a0/1

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL: If date in one table is within 12 months of a date in another table then Yes else No

2条答案

相关问题

热门标签

最新问答