>>>Hi,
>>>
>>>I am working on a report (in VFP 9) but the report is based on data in the MS SQL Server. This is why I put this question in the MS SQL Server Forum.
>>>
>>>The SQL Select needs to Count a number of records for various condition (see an example below). But what I find challenging is how to specify that the Group By should be on the Monday of every week.
>>>
>>>Here is an example:
>>>

>>>select [Monday_Of_Each_Week], Count(TableName.Deleted = 'D') as DEL_RECS, Count(TableName.Status = 'I' as STAT1_RECS from TableName 
>>>group by [Monday_Of_Each_Week]
>>>

>>>
>>>The Monday of each week would be as follows:
>>>The first Monday of 2024 is 01/01/2024, 2nd Monday is 01/08/2024, and so on. Basically the program should select the first week (01/01/2024) where Monday falls on the year. For example, next year (2025), the first week will be the week of 01/06/2025 (because the first Monday in the year is 01/06/2025).
>>>
>>>UPDATE. I am updating the SQL Select above because I need to show the count of records for each week (52 weeks in the year). Therefore, the SQL Select should select 52 records.
>>>
>>>Any suggestions?
>>>
>>>TIA.
>>Check
>>https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16
>>
>>DATEPART() function
>>
>>
>>Something like
>>

>>select DATEPART(wk, DateField)            AS WeekNumber
>>        , Count(TableName.Deleted = 'D') AS DEL_RECS
>>        , Count(TableName.Status = 'I')     AS STAT1_RECS
>>from TableName 
>>WHERE YEAR(DateField) = 2024 -- 2023??
>>group by DATEPART(wk, DateField) 
>>

>>
>>NOT TESTED!!!!
>
>I am probably not doing something correctly. Here is my actual SQL Select:
>

>select DATEPART(wk, date_open) AS WeekNumber, Count(rec_id = 'D') AS DEL_RECS, Count(work_his.Status = 'I') AS STAT1_RECS 
>from work_his where year(date_open) = 2024 group by DATEPART(wk, date_open) 
>

>Date_open is the DateTime field.
>Strange thing is the in the first DATEPART() the field DATE_OPEN has red underline and the message "date_open" is invalid column. The wk parameter in the first DATEPART() does not have the red underline.
>And the second DATEPART() (in the Group By) has it in revers. Wk has red underline and the message (when hovering the mouse over it) says "Incorrect syntax near 'wk'; Expecting Select or '('"
>What am I missing?

My mistake. I didn't read the whole Query. You should use SUM() not COUNT().
And also you should define your first date of the week. Should it be MONDAY or something else

SET DATEFIRST 1 -- Monday is the first day of the week

DECLARE @Tbl TABLE (datefield datetime, Deleted char(1), Status char(1))
DECLARE @i int  = 1
WHILE @i < 366
   BEGIN
       INSERT INTO @Tbl VALUES (DATEADD(day,@i,CAST('20231231' as datetime))
                              , CASE WHEN @i % 7 = 0 THEN 'D' ELSE '' END
                              , CASE WHEN @i % 2 = 0 THEN 'I' ELSE '' END
                            )
        SET @i = @i + 1
    END

SELECT * FROM @Tbl

select DATEPART(wk, DateField) AS WeekNumber
     , MIN(datefield)          AS datefield
     , SUM(CASE WHEN TableName.Deleted = 'D' THEN 1 ElSE 0 END) AS DEL_RECS
     , SUM(CASE WHEN TableName.Status = 'I' THEN 1 ELSE 0 END)  AS STAT1_RECS
from @Tbl TableName 
WHERE YEAR(DateField) = 2024
group by DATEPART(wk, DateField)