Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Create a view table to count the number of user accounts

Create a view table to count the number of user accounts,

Count the total number of Developer accounts
Count the total number of Individual accounts where "AccountType.AccountTypeCode" = "1" AND "AccountStatus.isActive" = true (1).
Count the total number of Business in accounts where "AccountType.AccountTypeCode" = "2" OR "AccountType.AccountTypeCode" = "3" AND "AccountStatus.isActive" = true (1).

The view table is to contain the following data

>vAccountTotal
-TotalDeveloper
-TotalIndividual
-TotalBusiness


create table Account (AccountID uniqueidentifier default newsequentialid() not null, AccountNumber varchar(128) null unique, UserID varchar(128) not null, Password nvarchar(128) not null, RegistrationDate datetime default getdate() not null, constraint PK_Account primary key (AccountID));
create table AccountAccountType (AccountAccountTypeID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null unique, AccountTypeID uniqueidentifier not null, constraint PK_AccountAccountType primary key (AccountAccountTypeID));
create table AccountType (AccountTypeID uniqueidentifier default newsequentialid() not null, AccountTypeCode int not null unique, Name nvarchar(50) not null, Description nvarchar(128) not null, constraint PK_AccountType primary key (AccountTypeID));
create table Developer (DeveloperID uniqueidentifier default newsequentialid() not null, [Column] int not null, constraint PK_Developer primary key (DeveloperID));
create table AccountStatus (AccountStatusID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null, isActive bit default 0 not null, isLocked bit default 0 not null, isSuspended bit default 0 not null, isHighRisk bit default 0 not null, constraint PK_AccountStatus primary key (AccountStatusID));
alter table AccountAccountType add constraint FK_AccountAccountType_AccountID_Account foreign key () references Account ();
alter table AccountStatus add constraint FK_AccountStatus_AccountID_Account foreign key (AccountID) references Account (AccountID);

Open in new window

accountcounter.jpg
0
robertkc
Asked:
robertkc
  • 6
  • 4
  • 2
2 Solutions
 
SharathData EngineerCommented:
provide some sample data from your tables.
0
 
robertkcAuthor Commented:
See code, simplified-sample.

Count the number of "AccountID" and "DeveloperID" rows where the conditions met the specified conditions above.



create table Account (AccountID uniqueidentifier default newsequentialid() not null, [Column] int null, constraint PK_Account primary key (AccountID));
create table AccountAccountType (AccountAccountTypeID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null unique, AccountTypeID uniqueidentifier not null, constraint PK_AccountAccountType primary key (AccountAccountTypeID));
create table AccountType (AccountTypeID uniqueidentifier default newsequentialid() not null, AccountTypeCode int not null unique, constraint PK_AccountType primary key (AccountTypeID));
create table Developer (DeveloperID uniqueidentifier default newsequentialid() not null, [Column] int null, constraint PK_Developer primary key (DeveloperID));
create table AccountStatus (AccountStatusID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null, isActive bit default 0 not null, constraint PK_AccountStatus primary key (AccountStatusID));
alter table AccountAccountType add constraint FK_AccountAccountType_AccountID_Account foreign key () references Account ();
alter table AccountStatus add constraint FK_AccountStatus_AccountID_Account foreign key (AccountID) references Account (AccountID);

Open in new window

0
 
SharathData EngineerCommented:
Can you proivde relationship bewteen Deveoper table to any other table?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
robertkcAuthor Commented:
Code above does not work, try code below.


create table Account (AccountID uniqueidentifier default newsequentialid() not null, AccountNumber varchar(128) null unique, UserID varchar(128) not null, Password nvarchar(128) not null, RegistrationDate datetime default getdate() not null, constraint PK_Account primary key (AccountID));
create table AccountAccountType (AccountAccountTypeID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null, AccountTypeID uniqueidentifier not null, constraint PK_AccountAccountType primary key (AccountAccountTypeID));
create table AccountType (AccountTypeID uniqueidentifier default newsequentialid() not null, AccountTypeCode int not null unique, Name nvarchar(50) not null, Description nvarchar(128) not null, constraint PK_AccountType primary key (AccountTypeID));
create table Developer (DeveloperID uniqueidentifier default newsequentialid() not null, [Column] int not null, constraint PK_Developer primary key (DeveloperID));
create table AccountStatus (AccountStatusID uniqueidentifier default newsequentialid() not null, isActive bit default 0 not null, isLocked bit default 0 not null, isSuspended bit default 0 not null, AccountID uniqueidentifier not null, constraint PK_AccountStatus primary key (AccountStatusID));
alter table AccountAccountType add constraint FK_AccountAccountType_AccountID_Account foreign key (AccountID) references Account (AccountID);
alter table AccountAccountType add constraint FK_AccountAccountType_AccountTypeID_AccountType foreign key (AccountTypeID) references AccountType (AccountTypeID);
alter table AccountStatus add constraint FK_AccountStatus_AccountID_Account foreign key (AccountID) references Account (AccountID);

Open in new window

0
 
robertkcAuthor Commented:
Sample developer table has no relationship.
To get you started.
 
SELECT     ...........................
FROM         dbo.Account INNER JOIN
                      dbo.AccountAccountType ON dbo.Account.AccountID = dbo.AccountAccountType.AccountID INNER JOIN
                      dbo.AccountType ON dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID INNER JOIN
                      dbo.AccountStatus ON dbo.Account.AccountID = dbo.AccountStatus.AccountID CROSS JOIN
                      dbo.Developer

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
The cross join developer worries me a little bit.... it really serves no purpose other than to repeat the selection for however many developers there are.

Think there is something missing from the code you have provided above, or, do you really really want every developer to have every account ?
0
 
robertkcAuthor Commented:
Hi mark

"The cross join developer worries me a little bit"
could remove cross join and replace with ", dbo.Developer"?

"Think there is something missing from the code you have provided above, or, do you really really want every developer to have every account ?"
Yes that is correct, Developer has different account credientials/login/permission/contact details/features/security measures etcetc, developers serves as different purpose from a regular user.
(for ease of development and security reasons that it has been design this way).

see code snippet, how would this be put together into a single view table query?
(im not the strongest at this stuff as you can tell :) )
This should give the total number of active Individual accounts
---------------------------------------------------------------
SELECT 
  COUNT(dbo.Account.AccountID) AS TotalIndividual
FROM
  dbo.Account
  INNER JOIN dbo.AccountAccountType ON (dbo.Account.AccountID = dbo.AccountAccountType.AccountID)
  INNER JOIN dbo.AccountType ON (dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID)
  INNER JOIN dbo.AccountStatus ON (dbo.Account.AccountID = dbo.AccountStatus.AccountID)
WHERE
  dbo.AccountType.AccountTypeCode = 1 AND 
  dbo.AccountStatus.isActive = 1
 
 
This should give the total number of active business accounts
---------------------------------------------------------------
SELECT 
  COUNT(dbo.Account.AccountID) AS TotalBusiness
FROM
  dbo.Account
  INNER JOIN dbo.AccountAccountType ON (dbo.Account.AccountID = dbo.AccountAccountType.AccountID)
  INNER JOIN dbo.AccountType ON (dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID)
  INNER JOIN dbo.AccountStatus ON (dbo.Account.AccountID = dbo.AccountStatus.AccountID)
WHERE
  (dbo.AccountType.AccountTypeCode = 2 OR dbo.AccountType.AccountTypeCode = 3) AND 
  dbo.AccountStatus.isActive = 1
 
This should give the total number of active developer accounts
---------------------------------------------------------------
SELECT 
  COUNT(dbo.Developer.DeveloperID) AS TotalDeveloper
FROM
  dbo.Developer <--- ,dbo.developer
WHERE
  dbo.Developer.isActive = 1

Open in new window

0
 
robertkcAuthor Commented:
Solution found:

@ mark, Please advise if bad.


SELECT     COALESCE (T1.TotalPersonalAccount, 0) AS TotalPersonalAccount, COALESCE (T1.TotalBusinessAccount1, 0) AS TotalBusinessAccount1, 
                      COALESCE (T1.TotalBusinessAccount2, 0) AS TotalBusinessAccount2, T2.TotalDeveloperAccount
FROM         (SELECT     SUM(CASE WHEN dbo.AccountType.AccountTypeCode = 1000 THEN 1 ELSE 0 END) AS TotalPersonalAccount, 
                                              SUM(CASE WHEN dbo.AccountType.AccountTypeCode = 1001 THEN 1 ELSE 0 END) AS TotalBusinessAccount1, 
                                              SUM(CASE WHEN dbo.AccountType.AccountTypeCode = 1002 THEN 1 ELSE 0 END) AS TotalBusinessAccount2
                       FROM          dbo.Account INNER JOIN
                                              dbo.AccountAccountType ON dbo.Account.AccountID = dbo.AccountAccountType.AccountID INNER JOIN
                                              dbo.AccountType ON dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID INNER JOIN
                                              dbo.AccountStatus ON dbo.Account.AccountID = dbo.AccountStatus.AccountID
                       WHERE      (dbo.AccountType.AccountTypeCode IN (1000, 1001, 1002)) AND (dbo.AccountStatus.isActive = 1)) AS T1 CROSS JOIN
                          (SELECT     COUNT(DeveloperID) AS TotalDeveloperAccount
                            FROM          dbo.Developer
                            WHERE      (isActive = 1)) AS T2

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Not bad, there are three approaches when gathering data from disparate data sources. Probably the first is some kind of inline query, second is probaly a series of sub queries and third is the often overlooked union.

What I was working on before you posted was similar, though based on your earlier posting have a look at :



SELECT  sum(case when AccountType.AccountTypeCode = 1 then 1 else 0 end) as TotalIndividual
,       sum(case when AccountType.AccountTypeCode <> 1 then 1 else 0 end) as TotalBusiness
,       (SELECT COUNT(DeveloperID) FROM dbo.Developer WHERE isActive = 1) AS TotalDeveloper
 
FROM
  dbo.Account
  INNER JOIN dbo.AccountAccountType ON (dbo.Account.AccountID = dbo.AccountAccountType.AccountID)
  INNER JOIN dbo.AccountType ON (dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID)
  INNER JOIN dbo.AccountStatus ON (dbo.Account.AccountID = dbo.AccountStatus.AccountID)
WHERE
  dbo.AccountType.AccountTypeCode in (1,2,3) AND 
  dbo.AccountStatus.isActive = 1

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
The other way to present that query above retaining the idea of yours with the subquery :






SELECT  TotalIndividual
,       TotalBusiness
,       (SELECT COUNT(DeveloperID) FROM dbo.Developer WHERE isActive = 1) AS TotalDeveloper
 
FROM (   SELECT  sum(case when AccountType.AccountTypeCode = 1 then 1 else 0 end) as TotalIndividual
         ,       sum(case when AccountType.AccountTypeCode <> 1 then 1 else 0 end) as TotalBusiness
         FROM
           dbo.Account
           INNER JOIN dbo.AccountAccountType ON (dbo.Account.AccountID = dbo.AccountAccountType.AccountID)
           INNER JOIN dbo.AccountType ON (dbo.AccountAccountType.AccountTypeID = dbo.AccountType.AccountTypeID)
           INNER JOIN dbo.AccountStatus ON (dbo.Account.AccountID = dbo.AccountStatus.AccountID)
         WHERE
           dbo.AccountType.AccountTypeCode in (1,2,3) AND 
           dbo.AccountStatus.isActive = 1 ) A

Open in new window

0
 
robertkcAuthor Commented:
solution not tested
0
 
Mark WillsTopic AdvisorCommented:
Thanks, and if you have problems when you go to test, please let me know...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now