SQL Code for SQL Server to get count of Treatments and Count of Medicine

Graham Rock 81 Reputation points
2023-02-02T12:01:19.0833333+00:00

Hello

I need to write a SQL Code that does a sites, count of two treatments and if they did that treatment did they have a medicine on the same day. I need to link the treatment's to the medicine by AnimalID and the same day. I need to do this for a whole month worth of treatments.

This is how I want the data.

User's image

I'm finding it very hard to link the Treatment count to the separate medicine's count.

This is how I get the Treatments for the animalid and date.

Select S.Name as Hospital, T.AnimalID, T.Description, Cast(T.ItemDate as Date) as ItemDate, T.Code

from Treatment T

Inner Join Site S on S.SiteID = T.SiteID

where code in ('CCC','CSC')

It almost the same query for Medicine but the codes are different.

What is the SQL code to do this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,666 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bjoern Peters 8,781 Reputation points
    2023-02-02T12:26:42.8166667+00:00

    Hi Graham,

    To assist you with your issue/problem adequately, it would be constructive for us to reproduce your scenario.

    Please provide us with at least the following things:

    (1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements.

    (2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL.

    (3) Desired output based on the sample data in #1 above.

    (4) Your SQL Server version (SELECT @@version;)

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments
  2. Bjoern Peters 8,781 Reputation points
    2023-02-02T12:27:06.12+00:00

    Hi Graham,

    To assist you with your issue/problem adequately, it would be constructive for us to reproduce your scenario.

    Please provide us with at least the following things:

    (1) DDL and sample data population, i.e., CREATE table(s) plus INSERT, T-SQL statements.

    (2) What you need to do, i.e., logic and your attempt implementation of it in T-SQL.

    (3) Desired output based on the sample data in #1 above.

    (4) Your SQL Server version (SELECT @@version;)

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    0 comments
  3. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-02-03T01:56:03.16+00:00

    Hi @Graham Rock

    You did show the desired result and some of the code. But you didn't provide source data. This leaves us at a head of the question.

    I am not engaged in your job and know nothing about the rules of treatment and medicine counting.

    You'd better provide the source data table creation statement and some test data, so that we can better help you.

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments