My first attempt would be using a cursor loop, loading in the date into a temp table, with this as a starting point:
SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())
However, I just want to know if there is even a better way of doing this, without a cursor loop?
Heavens no, don't use a while loop or cursor loop. Use a simple data calc and a "standard" tally table:
;WITH
cteTally10Base AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally10 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cteTally10Base
)
SELECT DATEADD(DAY, t.number, sunday_date) AS current_week_dates
FROM cteTally10 t
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7, CAST(GETDATE() AS date)) AS sunday_date
) AS get_prior_sunday_date
WHERE t.number BETWEEN 0 AND 6
ORDER BY t.number
select cast(dateadd(day,-(datediff(day,-1,current_timestamp)%7)+n,current_timestamp) as date) as thedate
,datename(weekday,dateadd(day,-(datediff(day,-1,current_timestamp)%7)+n,current_timestamp)) as thedatename
from (values(0),(1),(2),(3),(4),(5),(6)) as tally(n)
;
Are you wanting to return the list of day in this week or the begin and end date of this week? If you want the list of days Scott's or Bitsmeds's solutions are awesome. If you want the begin and end date:
SET DATEFIRST 7;
WITH Wk
AS (SELECT
DateAdd(WEEK, DateDiff(WEEK, 0, GetDate()), 0) - 1 StartWeek
, DateAdd(WEEK, 1, DateAdd(WEEK, DateDiff(WEEK, 0, GetDate()), 0)) - 2 EndWeek)
SELECT
Wk.StartWeek
, DateName(dw, Wk.StartWeek) StartDay
, Wk.EndWeek
, DateName(dw, Wk.EndWeek) EndDay
FROM
Wk;
I have never seen any of these methods of getting a range like this. Pretty heavy duty!
Scott, I do have a question for you. Since you avoid using cursor loops, and if this question was to get the range of dates in a month which varies between 28 - 31 days, how would you do this? I noticed if we use the tally method from yours and BitsMed, a fixed number of data have to be entered in the value section. For example: (0),(1),(2),(3),(4),(5),(6)
Therefore, for days (between 28 - 31 days) in a month, would you then use a loop?
And that's one of the reasons I used the tally structure I did: it easily adjusts to add more tally rows. For example, this will give you up to 100 rows. Inevitably you will at some point need more tally rows.
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cteTally10 t1
CROSS JOIN cteTally10 t2
)
SELECT *
FROM cteTally100
With E1(n) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X)),
E10(n) As (Select 1 From E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)
Select Top (@Count) Row_Number() Over (Order By n) As Value from E10
Will give you @Count numbers up to 10^9, which is more than enough for most purposes.
Scott, maybe I did a poor job of explaining about the month range or maybe I did not understand your reply.
In the first post, I asked about week which we know it will always be 7 days. In the followup question to you, I asked about month which could vary anywhere 28 to 31 days. April is 30 days and May is 31 days.
So i guess my question is would I have to hard code 28-31 days in the VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0) section? How would I handle dynamic days depending on the month? Thanks
The tally table merely generates numbers using cross joins and the Row_number function. By adding more tally rows, I believe Scott means another cross join.
Every "row" in the tally table in the example is a Cartesian join exponentially increasing the number number of rows numbers the tally produces. You could also use a select from values expression to return 31 numbers and use the DateAdd function to calculate the dates.
-- =============================================
-- Author: Joe T
-- Create date: 12/11/2017
-- Description:
-- select num from dbo.itfTally(255)
-- =============================================
ALTER FUNCTION [dbo].[itfTally]
(
@Limit int
)
RETURNS table WITH SCHEMABINDING
AS
RETURN
(
WITH n10 (num) AS ( SELECT num FROM (VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9)) n(num) )
, n100 (num) AS ( SELECT n10.num FROM n10 n10 CROSS JOIN n10 n)
, n10000 (num) AS ( SELECT n100.num FROM n100 n100 CROSS JOIN n100 n)
, n100000000 (num) AS ( SELECT n10000.num FROM n10000 n10000 CROSS JOIN n10000 n)
, rn (num) AS ( SELECT 0 UNION SELECT Row_Number() OVER (ORDER BY n.num) FROM n100000000 n)
SELECT
r.num
FROM rn r
WHERE r.num<=@Limit
)
You can easily create a tally function like the one above and generate as many numbers as you need.
This function is used in reporting in month over month comparisons.
/* RCIT Database Support
By: Joe Torre
On: Aug 8th 2018
For: This funtion returns monthly inerval values for the range passed in
DECLARE @d date = GetDate();
SELECT
BegDt
, EndDt
FROM dbo.MonthlyCalendar ('20150101', @d);
*/
ALTER FUNCTION [dbo].[MonthlyCalendar] (@BegDate datetime, @EndDate datetime) RETURNS TABLE
AS RETURN
(
WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
, n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
, n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
, nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
, Calendar (BegDt, EndDt) AS (
SELECT
DateAdd(m, nums.num, @BegDate)
, DateAdd(MILLISECOND, -3, DateAdd(m, 1, DateAdd(m, nums.num,@BegDate)))
FROM nums
)
SELECT
c.BegDt
, c.EndDt
FROM
Calendar c
WHERE
c.BegDt < @EndDate
)
Or, using my Tally table function (which starts at 1, as I find that more useful)
Create Or Alter Function [dbo].[Tally](@Count Int)
Returns Table
With SchemaBinding
As Return
(
With E1(n) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X)),
E10(n) As (Select 1 From E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)
Select Top (@Count) Row_Number() Over (Order By n) Value From E10
)
GO
It becomes a simple case of:
Declare @Today Date = GetDate()
Select
DateAdd(Day, T.value, EOMonth(@Today, -1))
From Tally(DatePart(Day, EOMONTH(@Today))) T
It's not really "introduced to SQL Server" as such. It's a programming technique that has been applicable in every SQL dialect ever. There have been many different ways of implementing it, the most basic being to just create a real table and populate it with a big range of numbers using a loop, but the general theory that there are a whole class of problems that can be quickly solved with a table full of continuous ascending numbers has been a staple of set-based programming for a long time.
If anything, the minor annoyance is that it hasn't been added to SQL Server as an intrinsic function that could probably be even more efficient and avoid cluttering up query plans with boilerplate number generation (you can do that by using a real table, but it has higher IO overheads than just generating numbers on the fly)
I finally got a chance to come back to this and looked into yourfunctions. They work!
I am able to understand most of it but there are some lines I don't understand. Can you help me understand the logic on these lines?
First, what is "With SchemaBinding"?
Second, what are these lines doing? I guess trying to understand the tallying part:
WITH
E1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X))
, E10(n) AS (SELECT 1 FROM E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)
--PURPOSE: Return the passed in value from the table E10
SELECT TOP (@Count) Row_Number() OVER (ORDER BY n) MyValue FROM E10
SchemaBinding effectively "locks" the structure of all referenced objects (tables, views etc) so they cannot be changed. In doing so, the optimizer can apply a more rigorous series of optimizations, such as deciding a function is deterministic (always returns the same result from the same input), which can produce better performing code. For functions like this which don't reference any objects at all, there is literally no down side to marking the function as schema bound so it's always a good idea.
As to the tallying, the first common table expression, E1, creates a table with 10 rows (each just containing the number 1). The second common table expression, E10, then CROSS JOINs that table to itself several times (using the "," syntax for CROSS JOINS purely for brevity). That has the effect of a table with 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 rows in it. Finally we select @Count rows from that table, returning a row number each time - thus effectively generating a sequence of @Count numbers.
Er, yeah, probably. Not that it matters much, it will already generate a crazy number of rows and you can keep adding on another cross join if you need more.
As to the "n", it's just a column name alias because all columns in a CTE need to be named and that syntax is just a bit shorter. The following two queries are equivalent:
With X As (Select 1 As A, 2 As B, 3 As C, 4 As D) Select * From X;
With X(A,B,C,D) As (Select 1,2,3,4) Select * From X;
You used something like this in your query: datediff(day,-1,current_timestamp)%7
Usually when I use DATEDIFF, I provide two dates (Start and End). In this case, you are providing numbers. Can you help me understand and the usage of mod (%) of 7? I am going through and breaking down your queries to understand. Yes, you knowledge transfer is not wasted. I go through every single query and play with them. Thank You
Why does something like this returns a value of 43285.
Date 0 = '1900-01-01'. Date -1 (I never use that "trick", because I don't like negative dates) = '1899-12-31' (i.e. one day before day 0).
The %7 strips out whole weeks. This is good for determining the day of week of a given date. Date 0 is a Monday. So, if the datediff % 7 = 0, then that date is also a Monday. If it's 3, then that date is a Thursday (three days past Monday can only be Thursday).
If you need to determine if a day is Sunday, for example, you could use day 6 and % 7 and check for 0.
One big advantage of this method is that the @@DATEFIRST and/or language settings don't affect the result, you always get the correct result regardless of those settings. I'd avoid code that adjusts using @@DATEFIRST in the calc. It's just prone to confusion/error.