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

>
>How do you add the expression
>

>SET DATEFIRST 1
>

>to the SQL Select.
>Here is how I use my function exec_sql_command():
>

>       cSqlSelect = "select * from mytable where ..."
>	IF  !exec_sql_command( cSqlSelect, .T., "CUR_PM_WOS", .T. )
>

>Basically the function EXEC_SQL_COMMAND send the cSqlSelect to the SQL Server. I don't seem to find a way to add the "SET DATEFIRST 1" to the SQL Select.
>Thank you in advance.

TEXT TO cSQLSelect NOSHOW TEXTMERGE
   SET DATEFIRST 1
   SELECT ...
ENDTEXT
** or  cSqlSelect = "SET DATEFIRST 1  select * from mytable where ..."
IF  !exec_sql_command( cSqlSelect, .T., "CUR_PM_WOS", .T. )