Group by Datetime with a condition

Question

I have data like this

25  NS  77021   2020-06-19 06:38:09.910 MWT0000018101   C   Harapan Face
25  NS  77022   2020-06-19 06:38:09.910 MWF0000045756   C   Harapan Face
25  NS  77023   2020-06-19 06:38:09.910 MWF0000045757   C   Harapan Face
26  NS  77303   2020-06-19 23:12:12.703 MWS0000023635   C   Harapan Face
26  NS  77304   2020-06-19 23:12:12.703 MWS0000023636   C   Harapan Face
26  NS  77305   2020-06-19 23:12:12.703 MWS0000023637   C   Harapan Face
26  NS  77306   2020-06-19 23:12:12.703 MWS0000023638   C   Harapan Face

The number 25 and 26 is week from date, I have created a function that if the hour from a date is more than 23:00 it will assigned to next week. This behavior is because there are work hour from 23:00 to 07:00. So 19th June 23:12 is assigned to next week.

When I want to group by week on this data, I can not give input with datetime data types. It must be in Date, because datetime doesn't have aggregate function.

So I use CAST(DATETIMEVALUE as DATE) as an input, but the function I've create before doesn't work. I think because the function not get time data. Cast to date is same with removing time.

Is there any solution for this case? Converting date to next date if hour is more than 23 is important. I shouldn't change this behavior due to company requirement. But I need to use grouping to get count of rows.

This is the query that I have created

SELECT  
    dbo.WEEKOFDATE(CAST(SAMPLEDATE AS DATE)) as WEEK,
    dbo.DAYOFDATE(CAST(SAMPLEDATE as date)) as SAMPLEDATE,
    COUNT(OID) as SampleCount  
FROM    SAMPLE
WHERE   (MONTH(SAMPLEDATE) = 6) AND (YEAR(SAMPLEDATE) = 2020)
GROUP BY CAST(SAMPLEDATE AS DATE),dbo.WEEKOFDATE(CAST(SAMPLEDATE AS DATE))

Added : Here is query to reproduce the table

CREATE TABLE [dbo].[SAMPLE](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [SAMPLEDATE] [datetime] NULL,
    [SAMPLENO] [int] NULL,
 CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED 
(
    [OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[SAMPLE] ON 
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77021, CAST(N'2020-06-19T06:38:09.910' AS DateTime), 1)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77022, CAST(N'2020-06-19T06:38:09.910' AS DateTime), 2)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77023, CAST(N'2020-06-19T06:38:09.910' AS DateTime), 3)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77303, CAST(N'2020-06-19T23:12:12.703' AS DateTime), 4)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77304, CAST(N'2020-06-19T23:12:12.703' AS DateTime), 5)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77305, CAST(N'2020-06-19T23:12:12.703' AS DateTime), 6)
GO
INSERT [dbo].[SAMPLE] ([OID], [SAMPLEDATE], [SAMPLENO]) VALUES (77306, CAST(N'2020-06-19T23:12:12.703' AS DateTime), 7)
GO
SET IDENTITY_INSERT [dbo].[SAMPLE] OFF
GO

Note that 19th June is Friday, week in this definition start from Saturday. so 19th June 06:38 is week 25 and 23:12 is week 26. And every the hour is more than 23:00, it assign as next day.

I need to count the rows every week.

These are my functions:

/****** Object:  UserDefinedFunction [dbo].[BEGINWEEK]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BEGINWEEK]    
(@TheYear int,@WeekNumber int)                 
RETURNS datetime                         
AS
BEGIN 
       DECLARE @DateOut datetime
       SET @DateOut = DATEADD(day,7 * (@WeekNumber - 1), dbo.FIRSTDAYOFYEAR(@TheYear))
       
RETURN @DateOut 
END;
GO

/****** Object:  UserDefinedFunction [dbo].[BEGINYEAR]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[BEGINYEAR]        
(@TheYear INT)                     
RETURNS DATETIME                          
AS
BEGIN 
       DECLARE @DateOut DATETIME
       DECLARE @hr INT
       SET @hr = DATEPART(WEEKDAY,CAST('12/25/' + CAST((@TheYear-1) AS VARCHAR) AS DATETIME))
       SET @DateOut = DATEADD(DAY,7-@hr,CAST('12/25/' + CAST((@TheYear-1) AS VARCHAR) AS DATETIME))
       SET @DateOut = DATEADD(HOUR,-2,@DateOut)
RETURN @DateOut   
END;
GO

/****** Object:  UserDefinedFunction [dbo].[DAYOFDATE]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DAYOFDATE]
(
    @TheDate DATETIME
)
RETURNS DATE
AS
BEGIN
    DECLARE @DayNumber DATE
    IF DATEPART(HOUR, @TheDate) < 23
    BEGIN
        SET @DayNumber = @TheDate
    END
    ELSE
    BEGIN
        SET @DayNumber = DATEADD(DAY, 1, @TheDate)
    END
    RETURN @DayNumber
END
GO

/****** Object:  UserDefinedFunction [dbo].[ENDWEEK]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[ENDWEEK]           
(@TheYear int,@WeekNumber int)                     
RETURNS datetime                          
AS
BEGIN 
       DECLARE @DateOut datetime
       SET @DateOut = DATEADD(day,7, dbo.BEGINWEEK(@TheYear,@WeekNumber))
       
RETURN @DateOut  
END;
GO

/****** Object:  UserDefinedFunction [dbo].[FIRSTDAYOFYEAR]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FIRSTDAYOFYEAR]           
(@TheYear int)                     
RETURNS datetime                          
AS
BEGIN 
       DECLARE @DateOut datetime
       DECLARE @hr int
       SET @hr= 7 - DATEPART(Weekday,CAST('12/26/' + CAST((@TheYear - 1) AS CHAR) AS DATETIME))
       SET @DateOut = DATEADD(day,@hr,CAST('12/26/' + CAST((@TheYear - 1) AS CHAR) AS DATETIME))
       SET @DateOut = DATEADD(hour,-1,@DateOut)
       RETURN @DateOut    
END;
GO

/****** Object:  UserDefinedFunction [dbo].[WEEKOFDATE]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[WEEKOFDATE]       
(@TheDate DATETIME)                     
RETURNS INT                          
AS
BEGIN 
       DECLARE @WeekNumber INT
       DECLARE @TheYear INT
       SET @TheYear = dbo.YEAROFDATE(@TheDate)
       SET @WeekNumber = CAST((convert(float,datediff(hh,dbo.BEGINYEAR(@TheYear),@TheDate))/24)/7 AS INT) + 1
RETURN @WeekNumber   
END;
GO

/****** Object:  UserDefinedFunction [dbo].[YEAROFDATE]    Script Date: 28/07/2020 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[YEAROFDATE]        
(@TheDate datetime)                     
RETURNS int                          
AS
BEGIN 
       DECLARE @TheYear int
       SET @TheYear = YEAR(@TheDate)
       IF (@TheDate > dbo.BEGINYEAR(@TheYear + 1))
              SET @TheYear = @TheYear + 1
                               
RETURN @TheYear   
END;
GO

Answer 1

So for queries like this one I suggest using a sub-query to evaluate your functions which allows you to aggregate in your outer select without having to call the function multiple times, and without needing to all the casting e.g.

SELECT TheWeek, TheDay, COUNT(OID) AS SampleCount
FROM (
    SELECT  
        dbo.WEEKOFDATE(SAMPLEDATE) AS TheWeek
        , dbo.DAYOFDATE(SAMPLEDATE) AS TheDay
        , OID
    FROM SAMPLE
    WHERE MONTH(SAMPLEDATE) = 6 AND YEAR(SAMPLEDATE) = 2020
) X
GROUP BY TheWeek, TheDay; 

Some points to note:

1. I recommend not using reserved words as it often leads to trouble - hence why I have renamed your columns.
2. The fact that you renamed the output of the function as the same name as the column being provided to the function (SAMPLEDATE) was most likely causing issue. Having renamed them the query makes a lot more sense to me.
3. For better performance I would convert all of your functions to Inline Table Valued Functions as these typically perform much better than Scalar Functions.

Answer 2

The simplest solution might be aggregating your data from a derived table that applies your ruleset first.

For instance, I could imagine a stored procedure that accepts two DATE parameters, @WeekStart and @WeekEnd that can be manipulated to your requirements without the need for any custom functions.

Here's an example:

-- Week starting/ending DATE variables/parameters. 
DECLARE @WeekStart DATE = '06/19/2020', @WeekEnd DATE = '06/19/2020';

-- SELECT date from a derived table that includes the 23:00:00 restriction.
SELECT  
    dbo.WEEKOFDATE ( CAST ( SAMPLEDATE AS DATE ) ) AS [WEEK],
    dbo.DAYOFDATE ( CAST ( SAMPLEDATE AS DATE ) ) AS [SAMPLEDATE], 
    COUNT ( OID ) AS SampleCount  
FROM (

    -- Grab the underlying resultset first.
    SELECT * FROM dbo.[SAMPLE] WHERE
        [SAMPLEDATE] BETWEEN CAST ( @WeekStart AS DATETIME ) AND DATEADD ( hour, 23, CAST ( @WeekEnd AS DATETIME ) )
        
) AS DerivedTable
WHERE
    MONTH ( SAMPLEDATE ) = 6 
    AND YEAR ( SAMPLEDATE ) = 2020
GROUP BY 
    CAST ( SAMPLEDATE AS DATE ), dbo.WEEKOFDATE ( CAST ( SAMPLEDATE AS DATE ) );

The derived table filters based on your starting time of 00:00:00 and max ending time of 23:00:00.

Here's the resultset from the above query:

+------+------------+-------------+
| WEEK | SAMPLEDATE | SampleCount |
+------+------------+-------------+
|   25 | 2020-06-19 |           3 |
+------+------------+-------------+

A look at the derived table query resultset shows this:

+-------+-------------------------+----------+
|  OID  |       SAMPLEDATE        | SAMPLENO |
+-------+-------------------------+----------+
| 77021 | 2020-06-19 06:38:09.910 |        1 |
| 77022 | 2020-06-19 06:38:09.910 |        2 |
| 77023 | 2020-06-19 06:38:09.910 |        3 |
+-------+-------------------------+----------+

And here's what is happening with the @WeekStart/@WeekEnd variables:

SELECT
    CAST ( @WeekStart AS DATETIME ) AS WeekStart_DT,
    DATEADD ( hour, 23,  CAST ( @WeekEnd AS DATETIME ) ) AS WeekEnd_DT;

Reveals:

+-------------------------+-------------------------+
|      WeekStart_DT       |       WeekEnd_DT        |
+-------------------------+-------------------------+
| 2020-06-19 00:00:00.000 | 2020-06-19 23:00:00.000 |
+-------------------------+-------------------------+

Because--as you state--the type DATE has no time is present, you can safely add 23 hours to @WeekEnd to set your cutoff period. This is a simple example, but it should get you started in a decent direction without resorting to a lot of custom functions--which should be used sparingly given how they can affect performance.

Optionally, you could pass DATETIME parameters and include your time restrictions that way.

DECLARE
    @WeekStart DATETIME = '06/19/2020 00:00:00',
    @WeekEnd DATETIME = '06/19/2020 23:00:00';