>>>>>>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
>>>>>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
>>>
>>>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. )
>
I was using a similar approach to yours. But I was separating the SET DATEFIRST 1 and the SELECT * expression with a semi-colon (;), which kept giving me an error. Now I know the correct syntax.
Thank you very much.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham