Select last 30 days with sql query

Question

I’m looking for the number Mon, Tue, Wed, Tür, Fri, Sat, Sun for the last 30 days. Is it possible to select the last 30 days and day of the week without an actual database table? Sort of

SELECT --everything between convert(date,GETDATE()), DATENAME(DW, GETDATE()) --and convert(date,GETDATE() - 30), DATENAME(DW, GETDATE()) 

Answer 1

You can use recursive CTE:

 ;WITH CTE AS ( SELECT convert(date,GETDATE()) sDate, DATENAME(DW, GETDATE()) sDayofWeek UNION ALL SELECT DATEADD(DAY,-1,sDate), DATENAME(DW, DATEADD(DAY,-1,sDate)) FROM CTE WHERE sDate > GETDATE()-29 ) SELECT * FROM CTE 

Answer 2

Well, there are several ways to do this.

• You can populate the temporary table using loop and INSERT statements, and then select the contents of the table. In fact, you could create a table-valued UDF.
• You can also create 30 SELECT statements, and UNION all together. But honestly, I think you're better off with option 1.

ETA: thinking about this, if all you need is the amount of each day of the week for the last 30 days, you can probably do this only using math, without returning 30 records.

There are 4 cases of each day of the week in any 30-day period, plus 2 additional days. So you really need to know which day of the week is the first day in your period and the second day. Those days of the week have 5 cases.

Answer 3

 WITH cteCount AS ( SELECT DATENAME(dw, GETDATE()) dw, 1 ix UNION ALL SELECT DATENAME(dw, DATEADD(d, -ix, GETDATE())), ix+1 FROM cteCount WHERE ix<30 ) SELECT dw, COUNT(1) cnt FROM cteCount GROUP BY dw 

Answer 4

A few solutions:

 SELECT ... From ... WHERE date > DATEADD(year, -1, GETDATE()) 

In addition, I think this statement will work with MySQL:

 select date_sub(now(),interval 30 day)as Datebefore30days; 

Answer 5

I'm pretty lazy and just load the temporary table and then make a group of choice in that temporary table

 DECLARE @tmpDates TABLE (calDate DATETIME) DECLARE @beginDate DATETIME SET @beginDate = DATEADD(day,-30,GETDATE()) WHILE @beginDate < GETDATE() BEGIN INSERT INTO @tmpDates ([calDate]) VALUES (@beginDate) SET @beginDate = DATEADD(DAY,1,@beginDate) END SELECT DATEPART(dw,[calDate]) AS [weekDay], COUNT(1) AS [dayCount] FROM @tmpDates GROUP BY DATEPART(dw,[calDate]) 

Answer 6

Number of hits for each day of the week for the last 30 days:

 SELECT DATENAME(dw,GETDATE())+' 5 times' as results UNION ALL SELECT DATENAME(dw,DATEADD(day,-1,GETDATE()))+' 5 times' UNION ALL SELECT DATENAME(dw,DATEADD(day,-2,GETDATE()))+' 4 times' UNION ALL SELECT DATENAME(dw,DATEADD(day,-3,GETDATE()))+' 4 times' UNION ALL SELECT DATENAME(dw,DATEADD(day,-4,GETDATE()))+' 4 times' UNION ALL SELECT DATENAME(dw,DATEADD(day,-5,GETDATE()))+' 4 times' UNION ALL SELECT DATENAME(dw,DATEADD(day,-6,GETDATE()))+' 4 times' 

It's really about dividing 30 by 7

---

It gives me

 results Thursday 5 times Wednesday 5 times Tuesday 4 times Monday 4 times Sunday 4 times Saturday 4 times Friday 4 times