# 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

Monday, December 21, 2020 7:14 AM

### All replies

• 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.

Monday, December 21, 2020 11:57 AM
• 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

Monday, December 21, 2020 4:02 PM
• 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.

Monday, December 21, 2020 4:14 PM
• 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'

DECLARE @date DATETIME = '03/25/2021'

Result:

Best regards,

Yijing Sun

Tuesday, December 22, 2020 2:03 AM