Fetch the data only last date of the month on range basis

[ScottPletcher]

I don't understand using an abbreviation. Is "m" MONTH or MINUTE? What is "mm"? Why should that be interpreted as "MONTH" [unless you've already memorized that]? Why not just be very clear and write "MONTH" when you mean month? I'll admit I do use "ms", but that's unambiguous and is rather long when spelled out.

[JeffModen]

@ScottPletcher

The -1 happens to go back to December, which has 31 days, which is why the hack works. It gets copied and causes issues all the time.

Lordy... look at the code I posted, Scott. It worked every time and only because it's one day less than "0".

[JeffModen]

@ScottPletcher

I don't understand using an abbreviation. Is "m" MONTH or MINUTE? What is "mm"? Why should that be interpreted as "MONTH" [unless you've already memorized that]? Why not just be very clear and write "MONTH" when you mean month? I'll admit I do use "ms", but that's unambiguous and is rather long when spelled out.

Ok.. but now you ARE talking standards... 2 character standards for the date parts. "mm" is certainly not the same as "mi" (nor is it ambiguous to me) and if someone doesn't know which is which, they should curl up with "the book" and learn the standard abbreviations. To me, they all make complete sense (except where MS deviated from the 2 character standard). Very thankfully, they didn't make month "MM" and minute "mm" like they have in some languages where you have no choice but to follow the case sensitive abbreviations. IIRC, they DID make that mistake with the "new" FORMAT function.

However, I'll never truly chastise anyone that want's to spell out the long form of the date parts. Again, that's a personal choice. I do find it annoying that not only is there a YEAR, MONTH, and DAY datepart, but there's also a YEAR(), MONTH(), and DAY() set of functions, as well. I use the two character abbreviations (never the single character ones because they're not actually "standard") most to differentiate between those two things and, for me anyway, it makes long temporal formulas easier for me to read because they abbreviations are (normally but MS screwed up on a few like "mcs" which should have been "us") the same width (makes for nice vertical alignment when appropriate) and they don't create such a horizontal span of code (important on small screens and I run into a lot of those and despise horizontal scrolling).

Notice that I did not call the use of the abbreviations a "Best Practice" even though the abbreviations are "standard". Just like you using then spelled out datepart names, it's a personal preference for me. I only brought it up as a way of saying that, despite my strong personal preferences, I don't call other people's stuff a "garbage "short-cut" (kludge!)" just because I disagree with it's use.

[JeffModen]

@Wim_Leys

I'll also tell you that I'll never use EOMONTH.

Can You explain why?

I already did. It's because of the "convention" (in this case, a very real, effective, and safe "Best Practice" on "Temporal Data" forums (yeah, they have forums for that, too)) of using what is known as "Closed/Open" date criteria that uses >= / <, which Scott also mentioned in one of his posts.

But, this is a very important subject and so let's get on with some more detailed information.

Let's say that you had a problem kind of similar to what's on this post except you just wanted to return all rows that occur in the month of December, 2019.

A lot of people say to themselves, "Well, I have a Date column that uses the DATE datatype, so I don't need worry about time". So, they form the WHERE clause in one of the two manners...

WHERE SomeDateColumn BETWEEN @MonthStartDate AND @MonthEndDate
... or, as of 2012 (IIRC)...
WHERE SomeDateColumn BETWEEN @MonthStartDate AND EOMONTH(@MonthStartDate)

In both cases, the BETWEEN resolves to...

WHERE SomeDateColumn >= @MonthStartDate AND SomeDateColumn <= @MonthEndDate
... or, as of 2012 (IIRC)...
WHERE SomeDateColumn >= @MonthStartDate AND SomeDateColumn <= EOMONTH(@MonthStartDate)

That's known as the "Closed/Closed" convention. At this point, I'll tell you that "Closed" means the date is "inclusive" in the range and "Open" means the date is "exclusive" of the range. In the cases above, both the start and end dates, however formed, are "included" in the date range.

Now, if the column datatype is truly a DATE datatype, then no problem other than upsetting some temporal purists.

But the code is NOT bullet-proof. If the person writing that code doesn't know about the "time element" problem when the column is actually a DATETIME column, they'll be missing most of the final day of the month if the column does, in fact, have non-zero (non-midnight) times included in the dates. Newbies also look to people for help and when they see such code, they have no idea of the requirement that it'll only work correctly if there is no actual "time element" included in the column.

We're not done yet, though. I can train newbies and others that don't understand the potential problem. That's a part of the reason why I do 100% code reviews for the front-end developers. I can't control the future though. For example, I cannot control a future requirement that changes the datatype of that column from DATE to DATETIME or DATETIME2 and people start using the "time element" of the datatype. If someone does that, they either have to check for all code that might be using such "Closed/Closed" criteria and still possibly miss some or they'll get "silent failures" where all but the very first instant of the last day of the month is missed due to having a "time element".

Some will say, "No problem! Just use 23:59:59.999 as part of the last day:. Ok... if the datatype is a DATETIME datatype, 23:59:59.999 will round up to the next day You could use 23:59:59.997 but if it's a DATETIME2, you could still be missing data if it's a DATETIME2(7). Of course, you could customize the end date for the datatype but that kind of insitu customizaiton is a real PITA. There's a much simpler method that handles EVERYTHING for EVERY DATATYPE no matter what it changes from and to. And that's the "Best Practice" of using the "Closed/Open" method, as follows:

WHERE SomeDateColumn >= @MonthStartDate AND SomeDateColumn < @NEXTMonthStartDate

Since EOMONTH is mostly used for "Closed/Closed" temporal criteria and I almost never use that convention (I almost always use "Closed/Open" because it's relatively bullet-proof and can't actually remember if I've even used "Closed/Closed" in the past, but don't believe I have), I will NEVER have the need to use EOMONTH() and so I can safely say that I'll never use EOMONTH.

Also, because I use only the "Closed/Open" method (again, Scott also correctly pointed this out), I don't have to ever be concerned with the datatype so long as it carries at least the date. I only have to worry about using 1 method and I never have to worry about some newbie getting it wrong by copying my code and using it for a different temporal datatype.

BTW, the terms "Closed" and "Open" actually come from how Temporal Charts are drawn. As a memory aid, here's a charted explanation.

Don't mistake that for how Gantt charts are drawn where a filled in object frequently means that something has started or completed and an open object means that it's been scheduled but has not yet been acted upon.

[Wim_Leys]

Jeff,

Thank you for your thorough explanation. It makes perfect sense.
I recognise the consequences of using DATETIME.

Except for creatated_at and updated_at I see no clear use for DATETIME columns (in my situation at work, education). I wonder if it's not better to use, as a rule of thumb, separate DATE and TIME columns instead of one DATETIME column. We have to constantly use DATETIME functions to get the start or end date of a course or the first or last hour lessons start or end.

I know "it depends", but what is your idea about using separate DATE and TIME columns instead of one DATETIME column?

[ScottPletcher]

I'm surprised you can't agree that the standard pattern for "rounding" a datetime is the best-practice method. Rather than the vague and thus unhelpful:

The "Best Practice" on this is "whatever someone is comfortable using with some exceptions".

So presumably if someone was "more comfortable" converting to char then go back to date/datetime to "round" to day -- CAST(CONVERT(varchar(8), StartTime, 112) AS datetime -- that would be ok as their "best practice". Except, of course, that using char will be much slower than the other method. Which is why I have no qualms whatsoever of pointing out the other method as a true "best practice" ... until and unless someone can provide a better method that is more efficient and consistent in pattern.

[ScottPletcher]

Only because -1, one day less than zero, happens to take you back to December 31. Yes, -1 by itself will always do that. But once you adopt and proselytize for this method, you're inviting the use of -2, and other variations that won't work, as others have also noted. You're opening a Pandora's box of future bugs. And for no real gain. It's still clearer to simply subtract a day afterward if that is what you need to do. I see no need to risk bugs just to be oh-so "clever".

[Sameer]

Sir JeffModen,
Thanks for asking .

@JeffModen

Or are you trying to return all of the rows that occurred on the last date of each month?

Yes trying to return all the rows of each month based on Item Names. If there is no entry on last date of the month then fetch the last record date is available in the database.

I am posting two tables first table required to query data last date of the month and second table belongs to the last available record of any date of item names.

Table 1.

USE [tempdb]
GO
--DROP TABLE ITEM_VOL
CREATE TABLE [dbo].[ITEM_VOL](
	[EVENT_GROUP_ID] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[EVENT_TYPE] [varchar](14) NULL,
	[PERIOD] [varchar](20) NULL,
	[ITEM_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[VAL1] [numeric](28, 12) NULL,
	[VAL2] [numeric](28, 12) NULL,

)
GO
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/30/2013',	'12/30/2013',1	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/31/2013',	'12/31/2013',2	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/1/2014',	'1/1/2014',	3	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/2/2014',	'1/2/2014',	4	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/3/2014',	'1/3/2014',	5	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/4/2014',	'1/4/2014',	6	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/31/2014',	'1/31/2014',	7	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/6/2015',	'5/6/2015',	8	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/7/2015',	'5/7/2015',	9	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/8/2015',	'5/8/2015',	10	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/31/2015',	'5/31/2015',	11	)

Select * from ITEM_VOL

Result Requirements in Green Highlighted**

Table 2.

USE [tempdb]
GO
--DROP TABLE ITEM_VOL2
CREATE TABLE [dbo].[ITEM_VOL2](
	[EVENT_GROUP_ID] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[EVENT_TYPE] [varchar](14) NULL,
	[PERIOD] [varchar](20) NULL,
	[ITEM_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[VAL1] [numeric](28, 12) NULL,
	[VAL2] [numeric](28, 12) NULL,

)

GO

INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/10/2014',	'1/10/2014',	12	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/11/2014',	'1/11/2014',	13	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/12/2014',	'1/12/2014',	14	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/13/2014',	'1/13/2014',	15	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'3/10/2014',	'3/10/2014',	71	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'3/11/2014',	'3/11/2014',	72	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'4/27/2014',	'3/27/2014',	88	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'4/28/2014',	'3/28/2014',	89	)
INSERT [dbo].[ITEM_VOL2] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY3',	'4/29/2014',	'3/29/2014',	90	)


Select * from ITEM_VOL2

Result Requirements in Green Highlighted

[JeffModen]

AWESOME!!!! Now we're cooking with gas! If you'd done that 4 days ago, we'd have been done 4 days ago!

Ok, first of all, I "Moden-ized" the format of the test data code for my own use (there's an article buried in this code).

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--      Example 1:  Return all rows for last day/entry of month paritioned by the ITEM_NAME.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--===== If the test table already exists, drop it to make reruns easier.
     IF OBJECT_ID('tempdb..#ITEM_VOL','U') IS NOT NULL
   DROP TABLE #ITEM_VOL
;
GO
--===== Create the test table.
 CREATE TABLE #ITEM_VOL
        (
         EVENT_GROUP_ID CHAR(32)         NULL
        ,ITEM_ID        CHAR(32)         NULL
        ,EVENT_TYPE     VARCHAR(14)      NULL
        ,PERIOD         VARCHAR(20)      NULL
        ,ITEM_NAME      VARCHAR(20)      NULL
        ,START_DATETIME DATETIME         NULL
        ,END_DATETIME   DATETIME         NULL
        ,VAL1           NUMERIC(28, 12)  NULL
        ,VAL2           NUMERIC(28, 12)  NULL
        )
;
GO
--===== Add test data to the test table.  The "*" in the comment rows identifies what we should return.
 INSERT INTO #ITEM_VOL 
         (EVENT_GROUP_ID, ITEM_ID, EVENT_TYPE, PERIOD, ITEM_NAME , START_DATETIME, END_DATETIME, VAL1)
 VALUES ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'12/30/2013','12/30/2013', 1)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'12/31/2013','12/31/2013', 2) --*
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/1/2014'  ,'1/1/2014'  , 3)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/2/2014'  ,'1/2/2014'  , 4)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/3/2014'  ,'1/3/2014'  , 5)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/4/2014'  ,'1/4/2014'  , 6)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/31/2014' ,'1/31/2014' , 7) --*
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','5/6/2015'  ,'5/6/2015'  , 8)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','5/7/2015'  ,'5/7/2015'  , 9)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','5/8/2015'  ,'5/8/2015'  ,10)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','5/31/2015' ,'5/31/2015' ,11) --*
;
GO   
--===== Display the contents of the test table   
 SELECT *
   FROM #ITEM_VOL
  ORDER BY START_DATETIME
;
GO
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--      Example 2:  Return all rows for last day/entry of month paritioned by the ITEM_NAME.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--===== If the test table already exists, drop it to make reruns easier.
     IF OBJECT_ID('tempdb..#ITEM_VOL2','U') IS NOT NULL
   DROP TABLE #ITEM_VOL2
;
--===== Create the test table.
 CREATE TABLE #ITEM_VOL2
        (
         EVENT_GROUP_ID CHAR(32)         NULL
        ,ITEM_ID        CHAR(32)         NULL
        ,EVENT_TYPE     VARCHAR(14)      NULL
        ,PERIOD         VARCHAR(20)      NULL
        ,ITEM_NAME      VARCHAR(20)      NULL
        ,START_DATETIME DATETIME         NULL
        ,END_DATETIME   DATETIME         NULL
        ,VAL1           NUMERIC(28, 12)  NULL
        ,VAL2           NUMERIC(28, 12)  NULL
        )
;
GO
--===== Add test data to the test table.  The "*" in the comment rows identifies what we should return.
 INSERT INTO #ITEM_VOL2 
         (EVENT_GROUP_ID, ITEM_ID, EVENT_TYPE, PERIOD, ITEM_NAME , START_DATETIME, END_DATETIME, VAL1)
 VALUES ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/10/2014' ,'1/10/2014' ,12) 
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/11/2014' ,'1/11/2014' ,13)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/12/2014' ,'1/12/2014' ,14)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY' ,'1/13/2014' ,'1/13/2014' ,15) --*
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','3/10/2014' ,'3/10/2014' ,71)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY1','3/11/2014' ,'3/11/2014' ,72) --*
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY3','4/27/2014' ,'3/27/2014' ,88)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY3','4/28/2014' ,'3/28/2014' ,89)
      , ('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST','EVENT','FACILITY3','4/29/2014' ,'3/29/2014' ,90) --*
GO 
--===== Display the contents of the test table        
 SELECT *
   FROM #ITEM_VOL2
  ORDER BY START_DATETIME
;
GO

Here's the solution to the first problem you posed...

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--      Solution 1:  Return all rows for last day/entry of month paritioned by the ITEM_NAME.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DECLARE @pLoMonthDate DATE = '20131215'
      , @pHiMonthDate DATE = '20150515'
;
   WITH cteEnumerate AS
(
 SELECT *
      , Keeper = ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,START_DATETIME),ITEM_NAME ORDER BY START_DATETIME DESC, ITEM_NAME)
   FROM #ITEM_VOL
  WHERE START_DATETIME >= DATEADD(mm,DATEDIFF(mm, 0,@pLoMonthDate),0) --Start of First Month IN desired date domain
    AND START_DATETIME <  DATEADD(mm,DATEDIFF(mm,-1,@pHiMonthDate),0) --Start of First Month AFTER desired date domain
)
 SELECT EVENT_GROUP_ID, ITEM_ID, EVENT_TYPE, PERIOD, ITEM_NAME , START_DATETIME, END_DATETIME, VAL1, VAL2
   FROM cteEnumerate
  WHERE Keeper = 1
  ORDER BY START_DATETIME, ITEM_NAME 
;
GO

...and, here's the solution to the second problem you posted...

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--      Solution 2:  Return all rows for last day/entry of month paritioned by the ITEM_NAME.
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DECLARE @pLoMonthDate DATE = '20131215'
      , @pHiMonthDate DATE = '20150515'
;
   WITH cteEnumerate AS
(
 SELECT *
      , Keeper = ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,START_DATETIME),ITEM_NAME ORDER BY START_DATETIME DESC, ITEM_NAME)
   FROM #ITEM_VOL2
  WHERE START_DATETIME >= DATEADD(mm,DATEDIFF(mm, 0,@pLoMonthDate),0) --Start of First Month IN desired date domain
    AND START_DATETIME <  DATEADD(mm,DATEDIFF(mm,-1,@pHiMonthDate),0) --Start of First Month AFTER desired date domain
)
 SELECT EVENT_GROUP_ID, ITEM_ID, EVENT_TYPE, PERIOD, ITEM_NAME , START_DATETIME, END_DATETIME, VAL1, VAL2
   FROM cteEnumerate
  WHERE Keeper = 1
  ORDER BY START_DATETIME, ITEM_NAME 
;
GO

Now, if you look closely, except for the table name we're reading from, the two solutions are absolutely identical in all ways..

You probably have more test data and so I'll let you play with indexes to optimize the performance.

And, yeah... the date parameters work the way you want them to. I just didn't change them in what I posted. Both work on the month and year of whatever date you've included (technically, they work only by month serial number in the ROW_NUMBER() part of the code, which is the most important part).

[JeffModen]

@Wim_Leys

Jeff,

Thank you for your thorough explanation. It makes perfect sense.
I recognise the consequences of using DATETIME.

Except for creatated_at and updated_at I see no clear use for DATETIME columns (in my situation at work, education). I wonder if it's not better to use, as a rule of thumb, separate DATE and TIME columns instead of one DATETIME column. We have to constantly use DATETIME functions to get the start or end date of a course or the first or last hour lessons start or end.

I know "it depends", but what is your idea about using separate DATE and TIME columns instead of one DATETIME column?

Separating Date and Time into two separate columns serves only one purpose (look things up by exact date without regard to time). Keeping them together (especially as a DATETIME) serves many purposes. Accomplishing those same purposes with separate columns leads to a world of hurt because, in most cases, the only way to do what you want to do is to ... recombine the columns. If they're already combined, you don't have to go through those throws.

To wit, my recommendation is to never separate the two but, "It Depends" and there will be the occasional exception to that recommendation.

[JeffModen]

So you use your method and I'll use mine. Just stop calling my method garbage. I'm not going to relegate myself to using a proverbial 4 function calculator just because someone else might press the wrong button on a scientific calculator. That's as bad as Joe Celko saying that you shouldn't use CONVERT because someday, you might have to migrate the code to another RDBMS (and pure migrations are an absolute myth anyway).

Besides (as you can see in the code in my previous post), that's what comments are for. If someone uses -2, they'll quickly learn that's not the way to do it. If that causes a Pandora's box of future bugs, then the people writing the code really need another lesson in temporal functions in T-SQL. You're suggesting that the manual transmission on a Mustang shouldn't have a reverse on the shifter because someone might try to put it into reverse when moving forward.

@ScottPletcher

So presumably if someone was "more comfortable" converting to char then go back to date/datetime to "round" to day -- CAST(CONVERT(varchar(8), StartTime, 112) AS datetime -- that would be ok as their "best practice".

Heh.. Nope.... that would fall into the category of "with some exceptions". There IS a silver lining when people write such performance challenged code as what you state. That silver lining presents itself during the 100% peer reviews I do as a "training opportunity" to help them get "comfortable" with doing it a better way.

p.s I sometimes hate this forum. It will only let you post 3 times consecutively. If you have more people than that to reply to or you just want to keep the replies to individual questions separate, you can certainly end up needing to post more than 3 replies. I don't know why anyone would impart such a restriction on a forum that can be quite dynamic.

So, moving back to JeffW's post...

@jeffw8713

This can easily be confused and you can get incorrect results when you try something like:

dateadd(month, datediff(month, 0, getdate()), -2)

Which does not give you the end of the month 2 months ago - rather it will return 11/30/2019 which is the same result as using -1. In fact, this does not give us a previous end of the month - it will give us -2 days if the previous month happens to have 31 days.

I have to admit, I never had that problem even as a newbie because I actually did study the functions and very quickly understood date serial numbers from way back in the early days of Lotus-123.. I understand your concern but, again, I'm not going to relegate myself to a 4 function calculator just because someone might not know how to use a scientific calculator. If some does that, they'll learn what it actually is very quickly.

@jeffw8713

Understood and appreciated. I also don't care for spelling out "month" or using lower case for SQL Server reserved words nor ever using Camel Casing in SQL Server. Because of the >= / < (closed/open) convention for criteria to identify date ranges, I'll also tell you that I'll never use EOMONTH.

All of this is just style issues - and is a completely separate argument...none of which is relevant to this conversation. I prefer spelling out the date intervals as it makes the code (for me) easier to read...especially when looking at the different abbreviations (mm or m = month, mi or n = minute, ss or s = second, ns = nanosecond, etc...).

But it's not a completely separate issue... the use of -1 is, in fact, nothing more than a matter of "style".

[Sameer]

Sir JeffModen ! wonderful top of the line Thank you very much to resolve the issue.

Sir jeffw8713 and Scott thank you for initiative lesson learnt I need to send full explanation to it required too...

[JeffModen]

Apologies for the almost 3 week old response, Sameer. Thank you kindly for your feedback.