June 27, 2022 at 3:04 pm
Hello, thank you in advance for your indulgence
I have a difficult issue (for me anyway) I'm trying to resolve. I may not be going about it correctly, but let me explain. I have a large dataset that gets added to each month. Let's say I'm a vet and the dataset simply contains Month, Animal (which could be cat, dog, ferret, rabbit, goldfish etc.) and the totalVisits each month. I had a request to see the month (column), the Animal (row), and the number of visits (value). Easy enough. However there is also a request for the most recent month minus the previous month that I need to include. Basically I used CTEs to get the last month's values and another CTE for the month before it. I then created a third CTE to store the calculation between the other two CTEs. In my Master query with all the visit data, I join the third CTE, match with the Animal and for the most part, I have exactly what I need. Issue is, let's say in April we saw 3 dogs, 4 cats and 2 ferrets. In May we saw 4 dogs, 3 cats and 1 goldfish. When I do my third CTE, nothing will show up for either ferrets or goldfish.
I hope that made sense
Obviously, I'm not a vet and the data is more complex, but this is the gist of my issue....thank you for your time!
June 27, 2022 at 3:12 pm
Instead of an INNER JOIN, do a FULL OUTER JOIN.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 27, 2022 at 6:23 pm
Thank you! (Although I coulda sworn I tried that before....)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply