CODEWITHSUNDEEP
sqlsql-server
aminjahan45678
aminjahan45678

Reputation: 29

How to get some items and then remove duplicate items?

I have a table something like bellow. I want to get number of visits users and roles per day.

tables:

CREATE TABLE #TempUser
(
  [Id] int NULL,
  [Username] nvarchar(50) NULL
)

CREATE TABLE #TempRole
(
  [Id] int NULL,
  [Title] nvarchar(50) NULL
)

CREATE TABLE #TempUserRole
(
  [UserId] int NULL,
  [RoleId] int NULL,
  [LastDate] date NULL
)

data:

INSERT INTO #TempUser([Id],[UserName])
VALUES
(1,'Marvin'),
(2,'Tom'),
(3,'Charli')

INSERT INTO #TempRole([Id],[Title])
VALUES
(1,'Admin'),
(2,'Manager'),
(3,'Guest')

INSERT INTO #TempUserRole([UserId],[RoleId],[LastDate])
VALUES
(1,1,'2019-02-03'),
(1,2,'2019-02-06'),
(1,2,'2019-02-03'),
(3,3,'2019-02-03'),
(3,1,'2019-02-02'),
(3,1,'2019-02-03'),
(2,1,'2019-02-05'),
(2,1,'2019-02-05'),
(3,1,'2019-02-03'),
(1,1,'2019-02-03'),
(2,1,'2019-02-06'),
(2,1,'2019-02-03'),
(1,1,'2019-02-03'),
(2,1,'2019-02-02'),
(1,1,'2019-02-03'),
(2,1,'2019-02-05'),
(1,1,'2019-02-05'),
(2,1,'2019-02-03')

How do I get number of visits users and roles per day a list of users who have the same "user", "Role", "Date" , "CounterOfDay"? I need output should be look like this:

UserName  Role    Date          CounterOfDay

Charli    Admin   2019-02-02    2

Tom       Admin   2019-02-02    2
...

Upvotes: 2

Views: 71

Answers (2)

zealous
zealous

Reputation: 7503

You can try with the following. Here is the demo

with cte as
(
  select
    userName,
    title,
    lastDate,
    count(*) as total
  from tempUserRole tur
  join tempUser tu
  on tur.userId = tu.id

  join tempRole tr
  on tur.roleId = tr.id

  group by
    userName,
    title,
    lastDate
),
rnk as  
(
  select 
      *,
      dense_rank() over (order by lastDate, title, total) as rnk
  from cte
),
cnt as
( 
  select
    *,
    count(*) over (partition by rnk) as ttl
  from rnk
)


select
    userName,
    title,
    lastDate,
    total
from cnt
where ttl > 1

Upvotes: 1

Francesco Galletta
Francesco Galletta

Reputation: 718

This is your answer, but please check for any syntax errors it might have as I don't write queries for SQL-Server too often. 

WITH counters AS (
   SELECT UserId, RoleId, LastDate, COUNT(*) AS CounterOfDay 
   FROM TempUserRole 
   GROUP BY UserId, RoleId, LastDate
)

SELECT 
   user.UserName
   , role.Title
   , counters.LastDate
   , counters.CounterOfDay
FROM counters
JOIN TempRole AS role ON counters.RoleId = TempRole.Id
JOIN TempUser AS user ON counters.UserId = TempUser.Id

I think is pretty nice to read and it should be more performant as just doing the joins first.

Upvotes: 1

Related Questions