How to Get first one month data from last six month from current date in SQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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: 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(MONTH, -7, getdate())
   and datetimeInsert <= 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.

See db<>fiddle.

CodePudding user response：

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