Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Incorrect information counting

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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'
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading