Query

Question

User1979860870 posted

Hi

  I have below query. In another column i want if Salary is generated in Nov'2020 i want to subtract 3 months i.e 01/08/2020

  Select department,Sum(salary),COUNT(*) FROM tbl1
  group by department

Thanks

Answer 1

User475983607 posted

It's fairly easy to write a SELECT CASE that checks the MONTH and YEAR of a DATETIME type.  It is also trivial to subtract 3 months for a DATETIME using the DATEADD function.

DECLARE @date DATETIME = '11/1/2020'
SELECT CASE WHEN YEAR(@date) = 2020 AND MONTH(@date) = 11 THEN DATEADD(m, -3, @date) END

Unfortunately, you have not shared a DATETIME column in your sample code and explained how to know when a salary is generated.

Answer 2

User1979860870 posted

Hi mgebhard

  Suppose system date is 21/12/2020 .I want that it should return 01/09/2020.

If system Date is 25/03/2021 then it should return 01/12/2020

Thanks

  

Answer 3

User475983607 posted

jagjit saini

  Suppose system date is 21/12/2020 .I want that it should return 01/09/2020.

If system Date is 25/03/2021 then it should return 01/12/2020

I think you should try to solve this well known math problem.  I'll give you a hint... Google.

If you run onto trouble solving this programming problem, then share your code.  If you make an valid attempt and are still unable to solve the math, then the community will provide a solution.

Answer 4

User1535942433 posted

Hi jagjit saini,

Accroding to your description,as far as I think,the logic is

1.Subtract the day of the month minus 1 days from the date.

2.Subtract three months.

Just like this:

DECLARE @date DATETIME = '12/21/2020'
SELECT  DATEADD(month,-3, dateadd(day, 1 - day(@date), @date)) 

DECLARE @date DATETIME = '03/25/2021'
SELECT  DATEADD(month,-3, dateadd(day, 1 - day(@date), @date)) 

Result:

Best regards,

Yijing Sun