Help With Query To Count Number of Pets & Vaccinations

Transact-SQL
1

I need help in writing a query that will answer this question: What is the total numbers of pets born each year and how many of them had at least one vaccination in their first year of life (365 days)? Output should return: for each year total count of pets born and count of pets with at least one vaccination in their first year of life.

The data within the tables in the picture are only a small subset of the real data being used.
This is my current code which I can determine the year the pet was born and if it had a vaccination, but I can't determine if the vaccination is within 365 day from DOB.

If possible, please use a CTE. I'm trying to learn more about CTEs so I can incorporate them into my other coding

SELECT
[Type]
,[Gender]
,[DateOfBirth]
,[Date]
,[ProcedureType]
,YEAR(DateOfBirth) as yob
,IIF([Date]>=DateOfBirth,1,0) dv
FROM [SAMPLEDB].[dbo].[Pets] p

left outer join [SAMPLEDB].[dbo].[HISTORY] h
on p.PetID = h.PetID
where ProcedureType = 'VACCINATIONS'

Pet Tables5
Pet Tables5528×735 19.4 KB

2

Try

SELECT  
  tblPet.[PetID],
  tblPet.[Name],
  tblPet.[Type],
  tblPet.[Gender],
  tblHist.[ProcedureType],
  tblPet.[DateOfBirth] as dob,
  YEAR(tblPet.[DateOfBirth]) as yob,
  tblHist.[Date] as dt
FROM [SAMPLEDB].[dbo].[Pets] tblPet
LEFT OUTER JOIN [SAMPLEDB].[dbo].[HISTORY] tblHist
ON tblPet.[PetID] = tblHist.[PetID]
WHERE tblHist.[ProcedureType] = 'VACCINATIONS' AND DATEDIFF(day,tblPet.[DateOfBirth],tblHist.[Date]) < 365
1 Like
3

And as CTE

WITH CTE_PetVaccinations AS (
    SELECT  
        tblPet.[PetID],
        tblPet.[Name],
        tblPet.[Type],
        tblPet.[Gender],
        tblHist.[ProcedureType],
        tblPet.[DateOfBirth] AS dob,
        YEAR(tblPet.[DateOfBirth]) AS yob,
        tblHist.[Date] AS dt
    FROM [SAMPLEDB].[dbo].[Pets] tblPet
    LEFT OUTER JOIN [SAMPLEDB].[dbo].[HISTORY] tblHist
    ON tblPet.[PetID] = tblHist.[PetID]
    WHERE tblHist.[ProcedureType] = 'VACCINATIONS'
      AND DATEDIFF(day, tblPet.[DateOfBirth], tblHist.[Date]) < 365
)
SELECT * FROM CTE_PetVaccinations;
1 Like
4 

SELECT 
    YEAR(P.DateOfBirth) AS yob,
    COUNT(*) AS number_pets_born,
    SUM(CASE WHEN EXISTS (SELECT * FROM HISTORY H WHERE H.PetID = P.PetID AND
        H.ProcedureType = 'VACCINATIONS' AND 
        H.Date BETWEEN P.DateOfBirth AND DATEADD(YEAR, 1, P.DateOfBirth)) 
            THEN 1 ELSE 0 END) AS number_pets_vaccinated
FROM PETS P
WHERE P.Type IN ('Cat', 'Dog' /*, ...*/)
GROUP BY YEAR(P.DateOfBirth)
ORDER BY YEAR(P.DateOfBirth)
1 Like
5 
Hi 

Hope this helps 

A different way of query 

# Total Pets Born and Vaccinations Count

WITH PetVaccination AS (
    SELECT 
        YEAR(p.DateOfBirth) AS YearOfBirth,
        p.PetID,
        COUNT(CASE WHEN h.ProcedureType = 'VACCINATIONS' AND h.Date BETWEEN p.DateOfBirth AND DATEADD(DAY, 365, p.DateOfBirth) THEN 1 END) AS VaccinationCount
    FROM 
        [SAMPLEDB].[dbo].[Pets] p
    LEFT JOIN 
        [SAMPLEDB].[dbo].[HISTORY] h ON p.PetID = h.PetID
    GROUP BY 
        YEAR(p.DateOfBirth), p.PetID
)

SELECT 
    YearOfBirth,
    COUNT(PetID) AS TotalPetsBorn,
    SUM(CASE WHEN VaccinationCount > 0 THEN 1 ELSE 0 END) AS PetsWithVaccination
FROM 
    PetVaccination
GROUP BY 
    YearOfBirth
ORDER BY 
    YearOfBirth;