none
How to count two date of service that happens in one day as 1 not 2?

    질문

  • Hello team,

    My table looks like this:

    PatientID    accountID      Name  DOB            DateOfService   Total
    1               2                    Rita   2/2/1972     01/01/2017         200
    1               2                    Rita   2/2/1972     01/01/2017         400
    2               3                    Rita  2/2/1972       01/12/2017        350

    I need to count the date of service, but once they happen on same day, they should be counted as one dateOfSerice and I want total based on the dateOf service which sometimes we have two of them on same day.

    How can I get this goal?

    Regards,

    CloudsInSky


    CloudsInSky

    2018년 7월 13일 금요일 오전 5:32

모든 응답

  • Hello team,

    My table looks like this:

    PatientID    accountID      Name  DOB            DateOfService   Total
    1               2                    Rita   2/2/1972     01/01/2017         200
    1               2                    Rita   2/2/1972     01/01/2017         400
    2               3                    Rita  2/2/1972       01/12/2017        350

    I need to count the date of service, but once they happen on same day, they should be counted as one dateOfSerice and I want total based on the dateOf service which sometimes we have two of them on same day.

    How can I get this goal?

    Regards,

    CloudsInSky


    CloudsInSky

    Hi cloudsInSky,

    Per the sample data above, what's your desired output ?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 13일 금요일 오전 6:28
  • Is this what you are looking for?

    create table test(PatientID int, accountID int, [Name] varchar(10), DOB date, DateOfService date, Total int)
    
    insert into test values(1, 2, 'Rita', '2/2/1972', '01/01/2017', 200)
    insert into test values(1, 2, 'Rita', '2/2/1972', '01/01/2017', 400)
    insert into test values(2, 3, 'Rita', '2/2/1972', '01/12/2017', 350)
    
    ;with cte as(
    select Name, count(*) as cnt from test group by Name, DateOfService
    )
    select Name, sum(1) from cte group by Name
    2018년 7월 13일 금요일 오전 6:38
  • do you mean unique day visits for a patient?

    i.e like

    SELECT patientID,Name,COUNT(DISTINCT DateOfService) AS UniqueDays,SUM(Total) AS PatientTotal
    FROM tableName
    GROUP BY PatientID,Name


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    2018년 7월 13일 금요일 오전 6:59
  • I would think accountID should be part of the group as well. But, not sure.
    2018년 7월 13일 금요일 오후 12:03
    중재자
  • Hello team,

    Does the first count of same date of service appear as 1 and the second one appears as 2?

    Please advise me!

    Regards,

    CloudsInSky


    CloudsInSky

    2018년 7월 18일 수요일 오전 4:28
  • Hello Visakh16,

    That could be distinct across the record.

    What I want to bring only one date and sum the values in those two days in value column.

    Regards,

    CloudsInSky


    CloudsInSky

    2018년 7월 18일 수요일 오전 4:31
  • Hello team,

    Does the first count of same date of service appear as 1 and the second one appears as 2?

    Please advise me!

    Regards,

    CloudsInSky


    CloudsInSky

    Hi CloudsInSky,

    You mean to want this?

    create table test1(PatientID int, accountID int, [Name] varchar(10), DOB date, DateOfService date, Total int)
    
    insert into test1 values(1, 2, 'Rita', '2/2/1972', '01/01/2017', 200)
    insert into test1 values(1, 2, 'Rita', '2/2/1972', '01/01/2017', 400)
    insert into test1 values(2, 3, 'Rita', '2/2/1972', '01/12/2017', 350)
    
    SELECT 
    PatientID,accountID,[Name],DOB,DateOfService,Total, 
    DENSE_RANK() over (order by DateOfService) as v_count --count of same date of service 
    FROM test1
    --Output
    /*
    PatientID   accountID   Name       DOB        DateOfService Total       v_count
    ----------- ----------- ---------- ---------- ------------- ----------- --------------------
    1           2           Rita       1972-02-02 2017-01-01    200         1
    1           2           Rita       1972-02-02 2017-01-01    400         1
    2           3           Rita       1972-02-02 2017-01-12    350         2
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 18일 수요일 오전 4:37
  • Hello team,

    Can you please explain about Dense_Rank()

    Regards,

    CloudsInSky


    CloudsInSky

    2018년 7월 18일 수요일 오전 4:45

  • Hello team,

    Can you please explain about Dense_Rank()

    Regards,

    CloudsInSky


    CloudsInSky

    As mentioned at https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017 , dense_rank function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

    In other words, when the next value of the column changes in ORDER BY clause of the function, correspondingly, the rankings would also increase by 1.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 7월 18일 수요일 오전 4:57
  • Hello Visakh16,

    That could be distinct across the record.

    What I want to bring only one date and sum the values in those two days in value column.

    Regards,

    CloudsInSky


    CloudsInSky

    Sounds like this

    SELECT patientID,accountID,Name,DateOfService,SUM(Total) AS PatientDayTotal
    FROM tableName
    GROUP BY PatientID,accountID,Name,DateOfService
    

    Or this

    SELECT patientID,accountID,Name,MIN(DateOfService) AS FirstVisitDate,SUM(Total) AS PatientDayTotal
    FROM tableName
    GROUP BY PatientID,accountID,Name
    

    to me


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 18일 수요일 오전 5:34
  • Hello team,

    Can you please explain about Dense_Rank()

    Regards,

    CloudsInSky


    CloudsInSky

    This will give you an idea..

    http://www.sqlservercurry.com/2009/04/rank-vs-denserank-with-example-using.html

    2018년 7월 18일 수요일 오전 6:07
  • I think Rank function gives my answer. 

    Regards,

    CloudsInSky


    CloudsInSky

    2018년 7월 19일 목요일 오전 4:48
  • I think Rank function gives my answer. 

    Regards,

    CloudsInSky


    CloudsInSky

    First explain what exactly you're expecting as output for a sample data. Then we may be able to suggest the exact function which can be applied in your case

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 19일 목요일 오전 5:39