• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

sql to show total numbers accessed

Hi,

I have the table below.

Table 1 called Asse, has the following coloums

UN, DA, DB, DC, DD, DE
S      Y           Y    P
M             Y    Y          Y
C                           Y
M             Y     Y          Y

All blanks are either " " and not Nulls

I need sql statement to show how many D's, UN have access to

Please help

Thanks,

R8VI
0
R8VI
Asked:
R8VI
  • 3
1 Solution
 
malikirfan28Commented:
Kindly use the following query.

select UN, DA + DB + DC as 'TotalAccess'
from (
select UN,
      case when DA = 'Y' then 1 else 0 end as DA,
      case when DB = 'Y' then 1 else 0 end as DB,
      case when DC = 'Y' then 1 else 0 end as DC

from Asse) as a
0
 
malikirfan28Commented:
Above query will count how many D's have "Y" value for each UN.
0
 
R8VIAuthor Commented:
Ok is this the same as this

I need to know how many Drives (which is DA, DB so on) each user (UN) has access to

Please help

Thanks for you help so far

R8VI
0
 
malikirfan28Commented:
Yes this query will give your total number of Drives each user (UN) has access to.

select UN, DA + DB + DC + DD + DE as 'TotalAccess'
from (
select UN,
      case when DA = 'Y' then 1 else 0 end as DA,
      case when DB = 'Y' then 1 else 0 end as DB,
      case when DC = 'Y' then 1 else 0 end as DC,
      case when DD = 'Y' then 1 else 0 end as DD,
      case when DE = 'Y' then 1 else 0 end as DE
from Asse) as a.

Kindly give it a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now