• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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