SQL Server - Query for getting count for report

Question

Dear Team,

I am again stuck up in making SQL Query. I am sharing the data and system flow.

Please help me out -

 

tbl_EquipmentMaster
ID	EmpCode	EquipName	Brand	SerialNumber	Status	Active
1	A1	Laptop	Lenovo	S1	Assigned	1
2	A1	Laptop	HP	S2	Assigned	1
3	A1	Laptop	HP	S3	Assigned	1
4	A1	Laptop	Lenovo	S4	Assigned	1
5	A1	Laptop	Lenovo	S5	Assigned	1
6	A1	Desktop	Intel	S6	Assigned	1
7	A1	Desktop	Intel	S7	Assigned	1
8	A1	Desktop	Intel	S8	Assigned	1
9	A1	Desktop	Intel	S9	Assigned	1
10	A1	Desktop	Intel	S10	Assigned	1
11	B1	Desktop	Intel	S11	Assigned	1
12	B1	Laptop	HP	S12	Assigned	1
13	B1	Desktop	Intel	S13	Assigned	1
14	B1	Laptop	HP	S14	Assigned	1
15	B1	Desktop	Intel	S15	Assigned	1
16	B1	Laptop	Lenovo	S16	Assigned	1
17	B1	Desktop	Intel	S17	Assigned	1
18	B1	Desktop	Intel	S18	Assigned	1
19	B1	Laptop	HP	S19	Assigned	1
20	B1	Laptop	Lenovo	S20	Assigned	1

 

tbl_EquipmentCertification
ID	EmpCode	EquipName	SerialNumber	Status	CertifyStatus	CertifiedBy	CertifiedOn	Active	EquipMasterID
1	A1	Laptop	S1	Assigned	1	A1	01-01-2021	1	1
2	A1	Laptop	S2	Assigned	1	A1	01-01-2021	1	2
3	A1	Laptop	S3	Assigned	1	A1	01-01-2021	1	3
4	A1	Laptop	S4	Assigned	0	A1	01-01-2021	1	4
5	A1	Laptop	S5	Assigned	0	A1	01-01-2021	1	5
6	B1	Desktop	S11	Assigned	0	B1	01-01-2021	1	11
7	B1	Laptop	S12	Assigned	0	B1	01-01-2021	1	12
8	B1	Desktop	S13	Assigned	1	B1	01-01-2021	1	13
9	B1	Laptop	S14	Assigned	1	B1	01-01-2021	1	14
10	B1	Desktop	S15	Assigned	1	B1	01-01-2021	1	15
11	B1	iPAD	i1	Assigned	2	B1	01-01-2021	1	0
12	A1	Tablet	T1	Assigned	2	A1	01-01-2021	1	0
13	A1	Tablet	T2	Assigned	2	A1	01-01-2021	1	0
14	A1	iPAD	i2	Assigned	2	A1	01-01-2021	1	0
15	A1	HDD	H1	Assigned	2	A1	01-01-2021	1	0
16	A1	HDD	H2	Assigned	2	A1	01-01-2021	1	0
17	B1	Tablet	T3	Assigned	2	B1	01-01-2021	1	0
18	B1	Tablet	T4	Assigned	2	B1	01-01-2021	1	0
19	B1	iPAD	i3	Assigned	2	B1	01-01-2021	1	0
20	B1	iPAD	i4	Assigned	2	B1	01-01-2021	1	0

 

Report-
Sr. No	Employee Name	Total Assets	Total certified Assets	Pending Assets	Total Do Not Own Devices	Total Added New Assets
1	A1	10+5=15	3	10	2	5
2	B1	10+5=15	3	10	2	5

 

 Above are the 2 tables and the Report format that I am not able to make.

 

System flow -

 

Hello Team,

I have two tables -
1. tbl_EquipmentMaster
2. tbl_EquipmentCertification

1. tbl_EquipmentMaster
ID - Primary Key
EmpCode - EmployeeCode
EquipName - Equipment Name
Brand - Brand of Equipment
SerialNumber - SerialNumber of Equipments
Status - Assigned - that equipment is assigned to that employee
Active - 1- Active ; 0 - InActive

2. tbl_EquipmentCertification
ID - Primary Key
EmpCode - EmployeeCode
EquipName - Equipment Name
SerialNumber - SerialNumber of Equipments
Status - Assigned - that equipment is assigned to that employee
CertifyStatus - 1 - Employee Owns the equipment ; 0 - Employee DO NOT Own the equipment; 2- Newly Added
CertifiedBy - Same as EmpCode
CertifiedOn - Date
Active - 1- Active ; 0 - InActive
EquipmentMasterID - ForeignKey of ID Column of table tbl_EquipmentMaster

Flow -

When emp-A1 logs in, he gets a list of assets assigned to him from "tbl_EquipmentMaster" table in a gridview.
It's a check-box gridview, as A1 can select any number of records from the gridview.

There are 3 buttons on the page - "I OWN", "I DO NOT OWN" , "ADD NEW EQUIPMENT"

WHen A1 selects 3 records (with SerialNumber S1,S2.S3) and clicks on "I OWN" button, the records are inserted in table - "tbl_EquipmentCertification".
For these records, CertifyStatus = "1" -> measn that these equipments are with employee-A1.

WHen A1 selects 3 records (with SerialNumber S4,S5) and clicks on "I DO NOT OWN" button, the records are inserted in table - "tbl_EquipmentCertification".
For these records, CertifyStatus = "0" -> measn that these equipments ARE NOT with employee-A1.

WHen A1 clicks on "ADD NEW EQUIPMENT" button, records are inserted in
table - "tbl_EquipmentCertification", where CertifyStatus = "2" -> measn that these equipments newly added by employee-A1.

Report -
1. PendingAssets - total count from table "tbl_EquipmentMaster"
2. Total Certified Assets - total count from table "tbl_EquipmentCertification" where certifystatus = 1
3. Total Do Not Own Devices - total count from table "tbl_EquipmentCertification" where certifystatus = 0
4. Total Added New Assets - total count from table "tbl_EquipmentCertification" where certifystatus = 2
5. Total Assets - > Certified Assets Count + Newly Added Assets Count