SQL Server - Comparing records with current month and year

Question

Hello Team, I need help in making a SQL Query again. I have two tables - 1. Asset Master ID UserName SerialNumber AssetType Active 1 Rohit S1224 CPU 1 2 Janvi S45677 CPU 1 3 Rohit S76544 Laptop 1 4 Rohit S34567 iPAD 1 5 Janvi S98778 Laptop 1 6 Janvi S567890 Mobile 1 2. Table - Asset Certification - ID SerialNumber CertifiedBy CertifiedOn Active 1 S1224 Rohit 2021-02-03 1 2 S34567 Rohit 2021-02-03 1 3 S98778 Janvi 2021-04-16 1 4 S76544 Rohit 2021-04-16 1 5 S1224 Rohit 2021-01-01 1 6 S98778 Janvi 2021-01-01 1 7 S76544 Rohit 2021-03-02 1 8 S76544 Rohit 2021-03-01 1 Now, I need to load all the assets that are not certfied in the current month and year from table Asset Certication. E.g. Current Month and YEar - April 2021. So, I need all detail from Master table - UserName, AssetType, SerialNumber and all records from Asset Certification table - where CertifiedOn is not - April 2021 (highlighted in yellow colour). Please guide - How do I make the query ?

Satya Karki · Answer

Hi, You can try this way SELECT am.username as 'User Name' ,am.assetType as 'Asset Type' , ac.* FROM AssetMaster am INNER JOIN AssetCertification ac on am.varSerialNo = ac.SerialNumber WHERE am.Active = 1 and Year (ac.CertifiedOn) YEAR (getdate()) and Month (ac.CertifiedOn) Month (getdate()) order by am.varDeviceType, am.varDeviceSubType, am.varSerialNo Please check column name as per your table structure.

Rijwan Ansari · Answer

Hi @Riddhi I have updated your query please try this and let me know your result select m.varDeviceType as 'Device Type' , m.varSerialNo as 'Serial Number' , m.varAssignTo as 'Assign To' , m.ID as 'Asset master ID' , t.* from AssetMaster m inner join AssetCertification t on m.varSerialNo = t.SerialNumber where m.Active=1 and Year (t.CertifiedOn) YEAR (getdate()) and Month (t.CertifiedOn) Month (getdate()) order by m.varDeviceType, m.varDeviceSubType, m.varSerialNo Regards Rijwan

Rijwan Ansari · Answer

I updated the query in previous answer as select m.varDeviceType as 'Device Type' , m.varSerialNo as 'Serial Number' , m.varAssignTo as 'Assign To' , m.ID as 'Asset master ID' , t.* from AssetMaster m inner join AssetCertification t on m.varSerialNo = t.SerialNumber where m.Active=1 and Year (t.CertifiedOn) YEAR (getdate()) and Month (t.CertifiedOn) Month (getdate()) order by m.varDeviceType, m.varDeviceSubType, m.varSerialNo

Sachin Singh · Answer

Test this select * from AssetMaster am inner join AssetCertification ac on am.SerialNumber=ac.SerialNumber where Year(ac.CertifiedOn)=YEAR (getdate()) and Month(ac.CertifiedOn)=Month(getdate()) ;

Riddhi Valecha · Answer

Hello Sir, I need the records that are NOT there of the current month and year. So, I need all records that are in white. I will share my query - select m.varDeviceType as 'Device Type', m.varSerialNo as 'Serial Number', m.varAssignTo as 'Assign To', m.ID , from (select max(ID) ID,varSerialNo, varAvarssignTossignTo from AssetMaster where Active = 1 group by varSerialNo,varDeviceType,varAssignTo,) m inner join AssetCertification t on m.varSerialNo = t.SerialNumber and Year(t.CertifiedOn) != YEAR (getdate()) and Month(t.CertifiedOn) != Month(getdate()) order by m.varDeviceType, m.varDeviceSubType, m.varSerialNo --------------- The requirement is - users need to certify their assets on monthly basis.

ID	UserName	SerialNumber	AssetType	Active
1	Rohit	S1224	CPU	1
2	Janvi	S45677	CPU	1
3	Rohit	S76544	Laptop	1
4	Rohit	S34567	iPAD	1
5	Janvi	S98778	Laptop	1
6	Janvi	S567890	Mobile	1

ID	SerialNumber	CertifiedBy	CertifiedOn	Active
1	S1224	Rohit	2021-02-03	1
2	S34567	Rohit	2021-02-03	1
3	S98778	Janvi	2021-04-16	1
4	S76544	Rohit	2021-04-16	1
5	S1224	Rohit	2021-01-01	1
6	S98778	Janvi	2021-01-01	1
7	S76544	Rohit	2021-03-02	1
8	S76544	Rohit	2021-03-01	1