Incorrect information counting

Advertisements

I need to pull information from the database between 01.01.2021 and 31.12.2021

The first day of the week is Monday (not Sunday)

This is a fairly easy task, but I have problems with it, I get information only from 07.01.2021 to 16.09.2021
I do not fully understand how these functions work, so please help me

SELECT
a.ModifiedOn AS Week,
COUNT(a.ActivityId) AS Count
FROM ActivityPointer AS a 
WHERE 
a.ModifiedOn >= DATEADD(week, DATEDIFF(week, -1,DATEADD(week, 0, '2021')), 0) 
AND
a.ModifiedOn <= DATEADD(week, DATEDIFF(week, -5,DATEADD(week,43, '2021')), -1) 
AND
a.ActivityTypeCode IN ('4201','4210','4212') 
AND a.OwnerId IN('3C696B18-BFF4-E911-A68A-005056A18C45',  
'DDD1597F-4FCD-E411-80D3-0050568973A1',   
'0AD3654A-7517-E011-B418-00505689002A',   
'98FA2C51-A296-EB11-A6AD-005056A18C45',   
'56C940A2-B396-EB11-A6AD-005056A18C45',   
'379C0CE5-D3D2-E911-8105-02BF0A0AC819') 
GROUP BY a.OwnerId,a.OwneridName,a.ModifiedOn
ORDER BY ModifiedOn desc

But I am specifically interested in this part of the code

a.ModifiedOn >= DATEADD(week, DATEDIFF(week, -1,DATEADD(week, 0, '2021')), 0) 
AND
a.ModifiedOn <= DATEADD(week, DATEDIFF(week, -5,DATEADD(week,43, '2021')), -1) 

>Solution :

Simplify your query by placing your date information into the query:

a.ModifiedOn >= '2021.01.01'
AND
a.ModifiedOn <= '2021.12.31'

Leave a Reply Cancel reply