0

How can we modify the below script to get the first-month data from the last 6 months?

  select datetimeInsert, Part_no,qty
  FROM  RECEIPT 
  where 
  datediff(month, datetimeInsert, getdate()) <= 6

=> Skip last 5 months data and display only 1st month data from the six months

Sam Bin Ham
  • 429
  • 7
  • 23
  • The question is not clear but maybe `datediff(month, datetimeInsert, getdate()) = 6` – Hamlet Hakobyan Nov 25 '21 at 07:47
  • Do you want a) the data of the last 6 months or b) the data of the month 6 months ago? Is this a) date specific (as in from 10th to 9th) or b) month fuzzy specific (as in all from May as of today)? – Knut Boehnert Nov 25 '21 at 07:57
  • @KnutBoehnert : i need to skip last 5 month data and display only 6th month. (Display only may ) – Sam Bin Ham Nov 25 '21 at 08:23
  • Hi @SamBinHam If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Udit Jindal Dec 08 '21 at 09:41

3 Answers3

2

Seems like you should be using date boundaries. So something like this:

SELECT datetimeInsert,
       Part_no,
       qty
FROM dbo.receipt
WHERE datetimeInsert >= DATEADD(DAY, 1, EOMONTH(GETDATE(),-7))
  AND datetimeInsert < DATEADD(DAY, 1, EOMONTH(GETDATE(),-6));

EOMONTH gets the date for the end of the month. So, for example, EOMONTH(GETDATE(),-7) returns the last date in the month 7 months ago (GETDATE() being today, and then the second parameter being 7 months ago). So for today, that would be 2021-04-30. I then add one day to value, to get the first day for the month 6 months ago: 2021-05-01. I then use the same logic for May/June.

This therefore ends up requiring that the value of datetimeInsert is >= 2021-05-01 and < 2021-06-01; meaning every date and time value for the month of May 2021.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • You could also use the more common calculation: DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 6, 0) and DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 5, 0) – Jeff Nov 25 '21 at 15:52
  • I could have, but I find the `EOMONTH` method a little "cleaner" @Jeff . – Thom A Nov 25 '21 at 16:13
  • if datetimeInsert is a datetime column, your calculation will include an implicit conversion since EOMONTH returns a date data type. If that implicit conversion is then performed on the column and not the calculated value it could cause issues with the cardinality estimator. – Jeff Nov 26 '21 at 17:18
  • *"if that implicit conversion is then performed on the column"* It won't be, @Jeff , due to [Data Type Precedence](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15). – Thom A Nov 26 '21 at 17:32
1

So if you want only the data from the month that was 6 months ago, use equal instead of less than:

select datetimeInsert, Part_no,qty
  from receipt 
 where datediff(month, datetimeInsert, getdate()) = 6

EDIT 1: A good point from @larnu is that the use of the datetimeInsert column inside the DATEDIFF() function makes this query less sargeable, i.e. it won't be able to take advantage of using indices.

You can achieve the same like this without sacrificing sargeability:

select datetimeInsert, Part_no,qty
  from receipt 
 where datetimeInsert >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, -7, GETDATE())))
   and datetimeInsert < DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, -6, GETDATE())));

Note: I'm just realizing that this second query does not just look at the month, but a the period that was between 7 and 6 months ago. So you will be getting the results from exactly 7 to 6 months ago, not the ones that are in the month that was 6 before the current month. If you want the latter (which is actually the same as your original query in the question) you'll find the solution in @larnu's answer.

EDIT 2: As commented by @Jeff, the time boundaries when using an expression like DATEADD(MONTH, -7, GETDATE()) will be those of a datetime exactly 7 months ago as of today at this exact time. I've updated both the statement above and the dbfiddle below to remedy that and highlight the difference.

See db<>fiddle.

buddemat
  • 4,552
  • 14
  • 29
  • 49
  • Hmm, this isn't particularly SARGable. Date boundaries are normally the way to go. – Thom A Nov 25 '21 at 09:09
  • Thanks for the comment, I edited it into my question – buddemat Nov 25 '21 at 09:25
  • Oh, just noticing you added an answer of your own. Guess I'll upvote that then... – buddemat Nov 25 '21 at 09:26
  • @buddemat - be careful you understand the time boundaries included in your statement. Subtracting 7 months from today gets you exactly 7 months ago as of today *at this time*. – Jeff Nov 25 '21 at 15:49
  • @Jeff Thank you for bringing that to my attention! I totally overlooked that. I updated my post for completeness' sake. – buddemat Nov 26 '21 at 09:02
0

I think you can achieve this by making a minor change in your query. Instead of <=6, you can do =0. This will give you latest 1 month data.

So the query would looks like,

  select datetimeInsert, Part_no,qty
  FROM  RECEIPT 
  where 
  datediff(month, datetimeInsert, getdate()) = 0

If you need last month data, use = 1

Like,

select datetimeInsert, Part_no,qty
  FROM  RECEIPT 
  where 
  datediff(month, datetimeInsert, getdate()) = 1
Udit Jindal
  • 151
  • 8