>>>>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) 
>

Perfect! Thank you!