CODEWITHSUNDEEP
sqlsql-serverselect
Mehwish
Mehwish

Reputation: 55

How to get 1 record on the basis of two column values in a single table?

The query is

select distinct b.UserID , cast(b.entrytime as date) ,count(*) as UserCount
from [dbo].[person] as a
join [dbo].[personcookie] as b
on a.UserID = b.UserID
where cast (b.entrytime as date) >= '08/21/2020'
and cast (b.leavetime as date) <= '08/27/2020' and a.distinction = 99
group by cast(b.entrytime as date), b.UserID

If same UserID has count more than 1 for same date, It should consider as 1. Now as it is shown in the image that USERID 10 has count 1 for 2020-08-26 and USERID 10 has count 2 for '2020-08-27'. It should show that user ID 10 has total count 2 for `2020-08-26 and 2020-08-27' (because for 2020-08-27 the count should be 1) as per the requirement. I have added the image of tables and what output i want enter image description here

Upvotes: 0

Views: 84

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

It seems you want one result row per user, so group by user, not by user and date. You want to count dates per user, but each day only once. This is a distinct count.

select 
  p.userid,
  count(distinct cast(pc.entrytime as date)) as date_count
from dbo.person as p
join dbo.personcookie as pc on pc.userid = p.userid
where p.distinction = 99
  and pc.entrytime >= '2020-08-08'
  and pc.leavetime < '2020-08-28'
group by p.userid
order by p.userid;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You seem to want dense_rank():

select p.UserID, cast(pc.entrytime as date), 
       dense_rank() over (partition by p.userID order by min(pc.entrytime)) as usercount
from [dbo].[person] p join
     [dbo].[personcookie] pc
     on pc.UserID = p.UserID
where cast(pc.entrytime as date) >= '2020-08-21' and
      cast(pc.leavetime as date) <= '2020-08-27'
group by cast(pc.entrytime as date), p.UserID;

Notes:

  • The only "real" change is using dense_rank(), which enumerates the days for a given user.
  • Use meaningful table aliases, rather than arbitrary letters.
  • Use standard date/time constants. In SQL Server, that is either YYYYMMDD or YYYY-MM-DD.

Upvotes: 0

Related Questions