SQL Server - Query for getting count for report

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aman Gupta5y ago
Either make new table with all coloumns name you want in the table else use UPDATE with sql query.

Riddhi Valecha5y ago

My report format is as below-

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

@Aman sir - How do I get the query result in this format ?

I am not able to get this output in query.

Aman Gupta5y ago
SQL QUERIES are written below, SELECT COUNT(EmpCode) AS PendingAssets FROM tbl_EquipmentMaster; SELECT COUNT(EmpCode) AS TotalCertifiedAssets FROM tbl_EquipmentCertification where certifystatus = 1; SELECT COUNT(EmpCode) AS TotalDoNotOwnDevices FROM tbl_EquipmentCertification where certifystatus = 0; SELECT COUNT(EmpCode) AS TotalAddedNewAssets FROM tbl_EquipmentCertification where certifystatus = 2; SELECT TotalCertifiedAssets+TotalAddedNewAssets AS TotalAssets FROM tbl_EquipmentCertification