April 21, 2016 at 11:15 am
I am creating a cycle sql that give me the range of second dates to Sunday of each month, for that I am using the following cycle, but he is returning from Sunday to Saturday, what I need to do to make it give me back from Monday to Sunday?
drop table #tmp_datesforMonth
go
declare @begDate datetime = '2016-04-01'
declare @endDate datetime = '2016-04-30';
WITH N(n) AS
( SELECT 0
UNION ALL
SELECT n+1
FROM N
WHERE n <= datepart(dd,@enddate)
)
SELECT DATEADD(dd,n,@BegDate) as dDate
into #tmp_datesforMonth
FROM N
WHERE MONTH(DATEADD(dd,n,@BegDate)) = MONTH(@BegDate)
select
MIN(dDate) as sunday,
Max(dDate) as monday
, datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) as WeekNumForMonth
from #tmp_datesforMonth
--where datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate)=3
group by datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate)
order by 3, 1
April 21, 2016 at 12:35 pm
Based on what you posted, here's an easy way to accomplish it. Notice that I changed your recursive CTE into something more efficient. I also removed all the calculations and left an easier way to define week numbers.
declare @begDate datetime = '2016-04-01'
declare @endDate datetime = '2016-04-30';
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(DATEDIFF( dd, @begDate, @endDate) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
)
SELECT DATEADD(dd,n,@BegDate) as dDate
INTO #tmp_datesforMonth
FROM cteTally;
SET DATEFIRST 1;
WITH cteWeeks AS(
SELECT dDate,
DENSE_RANK() OVER( ORDER BY DATEPART(WK, dDate)) -1 WeekNumForMonth
FROM #tmp_datesforMonth
)
SELECT
MIN(dDate) as monday
,MAX(dDate) as sunday
,WeekNumForMonth
FROM cteWeeks
GROUP BY WeekNumForMonth;
go
drop table #tmp_datesforMonth;
There are better ways to do this, let me try something and I might come back.
April 22, 2016 at 3:21 am
Hi,
This was the expected result, the problem is that when I put this in a stored procedure gives the following error:
Can not drop the table '#tmp_datesforMonthAM', because it does not exist or you do not have permission.
Can you help me?
April 22, 2016 at 5:59 am
You can get rid of this statement:
drop table #tmp_datesforMonthAM
-- Itzik Ben-Gan 2001
April 22, 2016 at 6:37 am
How do I do?
April 22, 2016 at 6:59 am
Here's the code for what I mentioned yesterday. Instead of creating rows for every single day, I create rows per week. This is also independent from settings, so it would work on any SQL Server.
declare @begDate datetime = '2016-04-01'
declare @endDate datetime = '2016-04-30';
WITH
cteWeeks AS(
SELECT (DATEDIFF(DD, 0, @begDate)/7+n) - (DATEDIFF(DD, 0, @begDate)/7) + 1 WeekNumForMonth,
DATEADD(DD, (DATEDIFF(DD, 0, @begDate)/7+n)*7 , 0) WeekStart,
DATEADD(DD, (DATEDIFF(DD, 0, @begDate)/7+n)*7 , 6) WeekEnd
FROM (VALUES(0),(1),(2),(3),(4),(5))E(n)
)
SELECT WeekNumForMonth,
CASE WHEN WeekStart < @begDate THEN @begDate ELSE WeekStart END,
CASE WHEN WeekEnd > @endDate THEN @endDate ELSE WeekEnd END
FROM cteWeeks
WHERE WeekStart < @endDate
AND WeekEnd > @begDate
For the other question.
GO is a batch separator. When you include it, it's the same as running the code before the GO and then run the code after the GO.
This is useful when creating programming objects like stored procedures because SPs should be the only thing in the batch. So you create different batches when using GO to have different procedures on the same script or other statements that are not part of the procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply