SQL Server - Comparing records with current month and year

Riddhi Valecha

5y961SQL

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 ?

5 Replies

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

Satya Karki5y ago
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.
+3
Rijwan Ansari5y ago
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
+2
Rijwan Ansari5y ago
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
+2
Riddhi Valecha5y ago
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.
+1
Sachin Singh5y ago
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()) ;
+1