Help with Start and End dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Start and End dates

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2011-08-08 : 12:28:02

Hello All,
I am needing some help on getting the start and end dates returned to me given the start date being current date. The Start date will always be the current month's day 1 (1st of the month). The issue for me is finding the end date.
My issue is honestly where to start this task...so i am trying out some querying.

My task is to return the start and end dates, but the time span expands as the date goes by. I hope that i can explain this. Here it goes:
The calendar for August 2011 have 31 days. My start of the week is Sunday - End of week is Sat.
Week 1 - Aug 1 - Aug 6
Week 2 - 7-13
Week 3 - 14-20
Week 4 - 21-27
Week 5 - 28-31

If i was to get the start and end dates for week 1, I would always want it to select start date = 1st and then use the Sat date for the end date ie.end date = 6th.

The following week (2) i can run the query on 8th (today) or any day this week, and i want to always select the 1st for the start date, but since it's only the second week, select the previous Sat (6th) for the end date.

The next week (3) - is the challenge, let's say today is the 15th of Aug or any date within the week, I want it to select not only the previous week but all the way to the 1st as well. so... my start would be Aug 1 but my end date expected results would be 13th of Aug.

The following week (4) - let's say it's the 22nd or any date within that 4th week... my expected start date = Aug 1 and the end date would need to be Aug 20th.

The last week (5) - let's say its 29th or any date BEFORE the end of the month (31st), it would need to have a start date of Aug 1 and end date of the 27th. On this week as well, i would want to run this script on the 31st (month end), so i would want my start and end dates be the total month ie. Aug 1 - Aug 31st.

As you can see, my weeks keep adding up each week for the selection of end date. Start date is always set to current month start date (1st of the month)

I am trying to use IF..ELSE statements for my function...so i started trying to build a query to see what the date(s) functions are doing...I am probably thinking this all wrong and as a beginner i really want to learn. don't be too hard on me...

DECLARE @dt_start datetime
,@v_selectedstart datetime
,@v_end datetime
,@p_start datetime
,@p_end datetime
,@v_firstprevweek datetime
,@v_firstcurrmon datetime
,@v_lastprevweek datetime
,@v_lastcurrmon datetime
,@v_firstcurrwk datetime
,@v_lastcurrwk datetime
,@v_firstnextmo datetime
, @v_wkMonth datetime

SET @dt_start = '2011-08-08'
SET @v_selectedstart = @dt_start
SET @v_firstcurrmon = DATEADD(mm,DATEDIFF(mm,0,@dt_start),0)
SET @v_lastcurrmon = DATEADD(month, DATEDIFF(month, 0, @dt_start) + 1, 0) - 1;
SET @v_firstcurrwk = DATEADD(wk,DATEDIFF(wk,0,@dt_start),-1)
SET @v_lastcurrwk = DATEADD(wk,DATEDIFF(wk,0,@dt_start),5)
SET @v_firstprevweek = DATEADD(wk,DATEDIFF(wk,7,@dt_start),-1)
SET @v_lastprevweek = DATEADD(wk,DATEDIFF(wk,7,@dt_start),5)
SET @v_firstnextmo = DATEADD(mm,DATEDIFF(mm,0,@dt_start)+1,0)

PRINT 'This is the Selected Date: ' + convert(varchar(10),@v_selectedstart,102)

BEGIN
/* START DATE - set the start to always be the 1st of the current month */
SET @p_start = niku.CMN_FIRST_OF_MONTH_FCT(@v_selectedstart)
/* WEEK 1 - set the start to the first of the current month, set the end of the last of current week */
IF (@v_selectedstart BETWEEN @v_firstcurrmon AND @v_lastcurrmon)
BEGIN
SET @p_end = DATEADD(wk,DATEDIFF(wk,0,@dt_start),5)
PRINT 'This is in the first week of the month: ' + 'Start: ' + convert(varchar(10),@p_start,102) + ' Finish: ' + convert(varchar(10),@p_end,102);
END
--/* SECOND WEEK - set the end date to the sat previous week*/
--ELSE IF (@v_selectedstart BETWEEN @v_firstcurrmon AND @v_lastcurrmon)
--BEGIN
-- SET @p_end = DATEADD(wk,DATEDIFF(wk,7,@dt_start),5)
-- PRINT 'This is in the second week of the month: ' + 'Start: ' + convert(varchar(10),@p_start,102) + ' Finish: ' + convert--(varchar(10),@p_end,102);
--END

END;

Thank you for your time and patience. I hopefully have explained this. If you need more explaination, please just ask.

Keri

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-08 : 14:01:38

Although you can do date math to get the dates you want, I'd suggest you create some sort of Date table. Something similar to a Number or Tally table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 14:05:31

isnt it a matter of just doing group by like this?


DECLARE @start datetime,@end datetime

SELECT @start=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0),@end=DATEADD(mm,1,@start)-1 

SELECT MIN([Date]) AS Start,MAX([Date]) AS End
FROM dbo.CalendarTable(@start,@end,0)
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0)

Calendar Table can be found at

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-08 : 14:55:49

quote:
Originally posted by visakh16

isnt it a matter of just doing group by like this?

DECLARE @start datetime,@end datetime

SELECT @start=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0),@end=DATEADD(mm,1,@start)-1

SELECT MIN([Date]) AS Start,MAX([Date]) AS End
FROM dbo.CalendarTable(@start,@end,0)
GROUP BY DATEADD(wk,DATEDIFF(wk,0,[Date]),0)

Calendar Table can be found at

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I need to understand how i would then use that in my SP.

My SP currently takes in the start and end date, but i don't want that. i want it to be intelligent enough to get the dates for me given the requirements i listed above. So currently i just take my start and end dates to whatever i choose them to be and run them in my SP.


ALTER PROCEDURE [niku].[KT_TIMESHEET_APPROVAL_SP] (
      @p_job_run_id numeric
      ,@p_job_user_id numeric
      ,@dfromdate datetime
	  ,@dtodate datetime

)
AS
BEGIN  /* main BEGIN */
    DELETE FROM KT_TIMESHEET_APPROVAL_TBL /* delete the rows in the table */
DECLARE /* declare the variables */
  @V_TP numeric(10,0)
  ,@V_START datetime 
  ,@V_FINISH datetime
  ,@V_STATUS nvarchar(15)
  ,@V_RID numeric(10, 0)
  ,@V_RESOURCE nvarchar(194)
  ,@V_APPROVER_ID numeric (10, 0)
  ,@V_APPROVER nvarchar(194)
  ,@V_APPROVER_EMAIL nvarchar(765)
  ,@V_DATEARCHIVED datetime
  ,@V_COMMENTS nvarchar(255)
  ,@V_CONFIG_COUNT integer
  ,@TIMESTAMP datetime
  ,@V_STARTDATE datetime /* dtfromdate */
  ,@V_ENDDATE datetime /* dttodate */
  ,@ARCHIVEDDATE datetime
  ,@e int

  BEGIN /* second BEGIN */ 
  SELECT @ARCHIVEDDATE = GETDATE()
  SELECT @TIMESTAMP = GETDATE()
  SELECT @V_STARTDATE =  ISNULL(@dfromdate, convert(datetime, '01/01/1990', 101))
  SELECT @V_ENDDATE =  ISNULL(@dtodate, convert(datetime, '01/01/1990', 101))
...
...
...

Thank you for any in sight

Keri

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 10:15:36

if you see my logic its taking dates automatically based on system date. its then using it to build the calendar table and i feel thats what you're also looking at.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-09 : 10:59:51

quote:
Originally posted by visakh16

if you see my logic its taking dates automatically based on system date. its then using it to build the calendar table and i feel thats what you're also looking at.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you so much for your calendar table. i am understanding how this returns the table, but not understanding where i would place this to return a specific start and end date in my SP.
Do i need to make a seperate function to return the start and end date based on this calendar table results? Can i use a scalar-valued function to return the dates needed? Also, would i be able to add the week number so that i can create IF..Else for telling my function to return specific start and end dates based on the calendar table results?
The calendar table returns:

quote:

Start EndDate
2011-08-01 00:00:00.000 2011-08-06 00:00:00.000
2011-08-07 00:00:00.000 2011-08-13 00:00:00.000
2011-08-14 00:00:00.000 2011-08-20 00:00:00.000
2011-08-21 00:00:00.000 2011-08-27 00:00:00.000
2011-08-28 00:00:00.000 2011-08-31 00:00:00.000

How do i make it return the dates based on...
If the selected date is somewhere in the first week, select the start and endDate of week 1.
If the selected date is somewhere in the second week, select the start date from week 1 and end date of week 1.
If the selected date is somewhere in the third week, select the start date from week 1 and the end date of week 2.
so on and so fourth...

Thank you again for your time.

Keri

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-09 : 12:06:32

you can use this just like a table as its a table valued function.
for getting the start and end dates you can apply grouping on week like what i've done in my example.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-09 : 14:30:51

quote:
Originally posted by visakh16

you can use this just like a table as its a table valued function.
for getting the start and end dates you can apply grouping on week like what i've done in my example.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Isn't it already grouped by week? as in your example? Can i add to the calendar table the code for week number so i can use that to pull the week end date?
I found this code: ((DATEPART(DAY, @StartDate)-1) / 7) + 1

I placed this in the code, and i don't get the expected results when I run the function. I am missing this i think.

So when i run this code... i get the correct week number.


declare @startdate datetime
set @startdate = getdate()

SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) + 1

So when i run


SELECT Date,Day,WeekDay, WeekNum FROM niku.revisedCalendarTable('2011-08-01','2011-08-31',0)

I get all one's down the WeekNum column.


Date	                Day	        WeekDay	WeekNum
2011-08-01 00:00:00.000	Monday	        1	1
2011-08-02 00:00:00.000	Tuesday	        1	1
2011-08-03 00:00:00.000	Wednesday	1	1
2011-08-04 00:00:00.000	Thursday	1	1
2011-08-05 00:00:00.000	Friday	        1	1
2011-08-06 00:00:00.000	Saturday	0	1
2011-08-07 00:00:00.000	Sunday	        0	1
2011-08-08 00:00:00.000	Monday	        1	1
2011-08-09 00:00:00.000	Tuesday   	1	1
2011-08-10 00:00:00.000	Wednesday	1	1
2011-08-11 00:00:00.000	Thursday	1	1
2011-08-12 00:00:00.000	Friday	        1	1
2011-08-13 00:00:00.000	Saturday	0	1

I added to the code


;With Calendar_CTE (Date,Day,WeekDay,WeekNum)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) + 1

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END, ((DATEPART(DAY, Date)-1) / 7) + 1

FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = @EndDate
)

I believe the issue is


((DATEPART(DAY, Date)-1) / 7) + 1

Do i need to add to the count? So it adds a 1 to every week?
so the results would be


Date	                Day	        WeekDay	WeekNum
2011-08-01 00:00:00.000	Monday	        1	1
2011-08-02 00:00:00.000	Tuesday	        1	1
2011-08-03 00:00:00.000	Wednesday	1	1
2011-08-04 00:00:00.000	Thursday	1	1
2011-08-05 00:00:00.000	Friday	        1	1
2011-08-06 00:00:00.000	Saturday	0	1
2011-08-07 00:00:00.000	Sunday	        0	2
2011-08-08 00:00:00.000	Monday	        1	2
2011-08-09 00:00:00.000	Tuesday	        1	2
2011-08-10 00:00:00.000	Wednesday	1	2
2011-08-11 00:00:00.000	Thursday	1	2
2011-08-12 00:00:00.000	Friday	        1	2
2011-08-13 00:00:00.000	Saturday	0	2
...
...
...

Again, thank you for your patience and help

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-10 : 08:14:00

i think you need this?


;With Calendar_CTE (Date,Day,WeekDay,WeekNum)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) + 1

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END, 
datepart(wk,Date)-DATEPART(wk,dateadd(mm,datediff(mm,0,Date),0)) + 1

FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = @EndDate
)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-10 : 14:12:29

quote:

Originally posted by visakh16

i think you need this?


;With Calendar_CTE (Date,Day,WeekDay,WeekNum)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) + 1

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END, 
datepart(wk,Date)-DATEPART(wk,dateadd(mm,datediff(mm,0,Date),0)) + 1

FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = @EndDate
)

---------------------------------------------------------------------SQL Server MVP
http://visakhm.blogspot.com/

Thank you so much :-)
So i was wrong where i thought i was...
i tried the example useage as


SELECT Date,Day,WeekDay,WeekNum FROM dbo.CalendarTable('2011-08-01','2011-08-31',0)

but that gives me all 1's for the week number...

Maybe i am needing to group differently than the example you provided earlier?

Thanks again

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

EDIT: I am getting some weird results on the end date


startdate	        enddate	                weeknum
2011-08-01 00:00:00.000	2011-08-07 00:00:00.000	1
2011-08-08 00:00:00.000	2011-08-14 00:00:00.000	2
2011-08-15 00:00:00.000	2011-08-21 00:00:00.000	3
2011-08-22 00:00:00.000	2011-08-28 00:00:00.000	4
2011-08-29 00:00:00.000	2011-08-31 00:00:00.000	5

I am trying just the CTE



DECLARE @dt_start datetime, @StartDate datetime--, @enddate datetime

SET @dt_start = getdate()
/* niku.CMN_TRUNC_DATE_FCT(getdate()) */
--SET @startdate = @dt_start;

SET @StartDate = niku.CMN_FIRST_OF_MONTH_FCT(@dt_start) 

;With Calendar_CTE (Date,Day,WeekDay,WeekNum)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) + 1

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END, 
datepart(wk,Date)-DATEPART(wk,dateadd(mm,datediff(mm,0,Date),0)) + 1

FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = niku.KT_LAST_OF_MONTH_FCT(Date)
)


SELECT min(date) startdate, max(date) enddate , weeknum
FROM Calendar_CTE
GROUP BY weeknum

Not sure what i am doing wrong please.

thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 11:28:29

why is it weird? i think its returning correctly? whats your datefirst setting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-11 : 12:41:20

quote:
Originally posted by visakh16

why is it weird? i think its returning correctly? whats your datefirst setting?

------------------------------------------------------------------------------------------------------
SQL Server MVP

http://visakhm.blogspot.com/

My start date is always set to the first of the month. Its returning the end date of Sunday instead of Saturday.

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 12:43:37

nope. I'm asking about datefirst setting of db. run below query and post result

SELECT @@DATEFIRST

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-11 : 12:49:14

I have my CTE working properly... now i am trying to figure out how to change that into a Function that i can use to return only the @EndDate


DECLARE @dtStart datetime, @StartDate datetime

SET @dtStart = getdate()
SET @StartDate = niku.CMN_FIRST_OF_MONTH_FCT(@dtStart) 


;With Calendar_CTE (Date,Day,WeekDay,WeekNum)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,((DATEPART(DAY, @StartDate)-1) / 7) +1

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END, 
datepart(wk,Date)-DATEPART(wk,dateadd(mm,datediff(mm,0,Date),0)) +1

FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = niku.KT_LAST_OF_MONTH_FCT(Date)
)

SELECT max(date) enddate
FROM Calendar_CTE
WHERE  weekNum = 1
AND  datepart(dw,date) >= 1 AND datepart(dw,date) <= 7

the result was


enddate
2011-08-07 00:00:00.000

I want to be able to tell it...


IF (WeekNum = 1)
SELECT max(date) enddate
FROM Calendar_CTE
WHERE  weekNum = 1
AND  datepart(dw,date) >= 1 AND datepart(dw,date) <= 7

IF (WeekNum = 2)
SELECT max(date) enddate
FROM Calendar_CTE
WHERE  weekNum = 2
AND  datepart(dw,date) >= 1 AND datepart(dw,date) <= 7
...
...

I decided to set DATEFIRST 1 and it returned a enddate of Monday (i thought Monday was 2??? I am confused. I set DATEFIRST 6; and it's now showing the correct enddate...running the above CTE


enddate
2011-08-06 00:00:00.000

Do i always need to set the DATEFIRST?
Thank you for your help, I think I am getting myself more confused. :-(

Keri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-12 : 02:27:59

nope. its not a good way to manipulate DATEFIRST.changing seeting affects other queries in server too if they're working based on day number

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

furrelkt
Starting Member

49 Posts

Posted - 2011-08-15 : 11:59:50

Hi,
Please someone i am needing some help... this is driving me crazy..not sure why this not working? I dont know what else to check...anyone please??


USE [niku]
GO
/****** Object:  UserDefinedFunction [niku].[KTCalendarFct]    Script Date: 08/15/2011 15:58:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [niku].[KTCalendarFct] (@dtstart datetime)

RETURNS DATETIME AS 
BEGIN
	DECLARE @v_ret_date datetime,
					     @period_end_date datetime,
					    @p_startdate datetime,
					    @p_enddate datetime,
					    @p_weeknum bit
					   SET @p_startdate = niku.CMN_FIRST_OF_MONTH_FCT(@dtStart) 
					SET @p_enddate = niku.KT_LAST_OF_MONTH_FCT(@dtStart)
					
DECLARE cal_cur CURSOR FOR
		select MAX(period_end_date) period_end_date, ROW_NUMBER ( ) OVER (ORDER BY DATEADD(wk,DATEDIFF(wk,0,@p_startdate),0)) p_weeknum
		from  nbi_dim_calendar_time
		where day between @p_startdate and @p_enddate
		group by DATEADD(wk,DATEDIFF(wk,0,Day),0)

OPEN cal_cur
FETCH NEXT FROM cal_cur INTO  @period_end_date, @p_weeknum
   WHILE @@FETCH_STATUS = 0
   BEGIN
   IF @p_weeknum = 1
		BEGIN
			SET  @v_ret_date = @period_end_date
		END
   IF @p_weeknum = 2
		BEGIN
			SET  @v_ret_date = @period_end_date
		END
	IF @p_weeknum = 3
		BEGIN
			SET  @v_ret_date = @period_end_date
		END
	IF @p_weeknum = 4
		BEGIN
			SET  @v_ret_date = @period_end_date
		END
	IF @p_weeknum = 5
		BEGIN
			SET  @v_ret_date = @period_end_date
		END   
   END;

CLOSE cal_cur
DEALLOCATE cal_cur
	
		RETURN @v_ret_date

END

testing this...


/*testing the sql  nbi_dim_calendar_time table */
DECLARE @dt_start datetime, @p_startdate datetime, @p_enddate datetime
SET @dt_start = niku.CMN_TRUNC_DATE_FCT(getdate())
SET @p_startdate = niku.CMN_FIRST_OF_MONTH_FCT(@dt_start) 
SET @p_enddate = niku.KT_LAST_OF_MONTH_FCT(@dt_Start)

		select @p_startdate startdate, MAX(period_end_date) enddate, ROW_NUMBER ( ) OVER (ORDER BY DATEADD(wk,DATEDIFF(wk,0,@p_startdate),0)) weekNum
		from  nbi_dim_calendar_time
		where day between @p_startdate and @p_enddate
		group by DATEADD(wk,DATEDIFF(wk,0,Day),0)

Results: exactly what i am looking for, start date always the first, and the end dates are what i need.


startdate	        enddate	                weekNum
2011-08-01 00:00:00.000	2011-08-06 00:00:00.000	1
2011-08-01 00:00:00.000	2011-08-13 00:00:00.000	2
2011-08-01 00:00:00.000	2011-08-20 00:00:00.000	3
2011-08-01 00:00:00.000	2011-08-27 00:00:00.000	4
2011-08-01 00:00:00.000	2011-08-31 00:00:00.000	5

Function not working at all... :-(


/* using the function */
DECLARE @dt_start datetime, @StartDate datetime

SET @dt_start = niku.CMN_TRUNC_DATE_FCT(getdate())
/* niku.CMN_TRUNC_DATE_FCT(getdate()) */

SET @StartDate = niku.CMN_FIRST_OF_MONTH_FCT(@dt_start) 
select @startdate, niku.KTCalendarFct(@startdate) enddate 
*/

Thank you anyone, please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources