I am trying to create a report that uses a time, based on a period of 7 days. The client wants the report to give data based on 4 periods a month. The periods should be the "1st to 7th", then the "8th to 14th", then "15th to 21st" and 22nd to the end of the month.

Anyone that knows if this is possible?

This is my statement to read data from SQL.

SELECT
[ClientName],[ProjectName],[ProductName],[DepName],[DepAd1],[TRUENET]
FROM [database].[dbo].[tabel] 
Where [Veie_DateTime] >= (@FromDato) AND [Veie_DateTime] <= (@ToDato) AND [ProjectName] IN (@Prosjekt) AND [ClientName] IN (@Kunde) AND [DepName] IN (@Inn_Ut) AND  [DepAd1] IN (@Avdeling)
ORDER BY [ClientName]

I would like to replace:

[Veie_DateTime] >= (@FromDato) AND [Veie_DateTime] <= (@ToDato)

With something like this:

[Veie_DateTime] = (@Month) AND [Veie_DateTime] = (@Period)

I am trying to create a report that uses a time, based on a period of 7 days. The client wants the report to give data based on 4 periods a month. The periods should be the "1st to 7th", then the "8th to 14th", then "15th to 21st" and 22nd to the end of the month.

Anyone that knows if this is possible?

This is my statement to read data from SQL.

SELECT
[ClientName],[ProjectName],[ProductName],[DepName],[DepAd1],[TRUENET]
FROM [database].[dbo].[tabel] 
Where [Veie_DateTime] >= (@FromDato) AND [Veie_DateTime] <= (@ToDato) AND [ProjectName] IN (@Prosjekt) AND [ClientName] IN (@Kunde) AND [DepName] IN (@Inn_Ut) AND  [DepAd1] IN (@Avdeling)
ORDER BY [ClientName]

I would like to replace:

[Veie_DateTime] >= (@FromDato) AND [Veie_DateTime] <= (@ToDato)

With something like this:

[Veie_DateTime] = (@Month) AND [Veie_DateTime] = (@Period)

• sql
• sql-server
• report

• 1 A minimal reproducible example is a great start when asing for SQL assistance. – jarlh Commented Mar 17 at 9:31
• Which dbms are you using? (Both DATEADD and DATEDIFF are product specific functions.) – jarlh Commented Mar 17 at 9:32
• 1 What is the expected output and how do the quarters show up in that output? – Tim Biegeleisen Commented Mar 17 at 9:33

2 Answers 2

We can use a case expression:

SELECT  DocDate,
    CASE 
        WHEN DAY(DocDate) BETWEEN 1 AND 7 THEN 1
        WHEN DAY(DocDate) BETWEEN 8 AND 14 THEN 2
        WHEN DAY(DocDate) BETWEEN 15 AND 21 THEN 3
        ELSE 4  -- Covers 22nd to end of the month
    END AS Monthpart
FROM mytable;

With SQL Server 2022

As your parts are of 7 days each (except the last one), you can simply filter each entry on the desired period parameter matching the euclidian divide of the day of month by 7 (with a special rule to have the last period unbounded):

-- We will report for the last quarter of March:
declare @Month date = '2025-03-01';
declare @Period int = 3; -- Period 1st-7th should be mapped to 0, 8th-14th to 1, 15th-21st to 2, 22nd-31st to 3.

SELECT *
FROM mytable
WHERE DATETRUNC(month, DocDate) = @Month
AND LEAST((DATEPART(day, DocDate) - 1) / 7, 3) = @Period;

See it in a fiddle.

Playing with mathematics on SQL Server < 2022

Note that the LEAST is SQL Server 2022, on older versions you will have to be a bit creative:

Division by 7 will return us 0 for the first quarter, 1 for the 2nd, 2 for the 3rd, 3 for the 4th until the 28, and 4 for the remaining days (29th to 31st). So to have the last quarter go from the 22th to the 31st, we would like 3 and 4 to be merged, right? That is, we would like the division to return the same for the last two 7-days periods.

But if we reverse our order so that they are the first two periods (as returned by our euclidian divide),
we can shift them around 0 and rely on the division of the first negative numbers to return 0, exactly like their positive counterparts.

Thus by mapping the 1st of month to 31, the 2nd to 30, and so on,
we get a reverse order month which will naturally adjust around 0 to have the last half-week merged with the 4th 7-day period:

Yay, we've got our last period last 10 days instead of 7!

SELECT *
FROM mytable
WHERE DATEADD(month, DATEDIFF(month, 0, DocDate), 0) = @Month
AND 3 - (28 - DATEPART(day, DocDate)) / 7 = @Period;

(included in a simplified fiddle)