## 4 replies

Last post Dec 22, 2020 02:03 AM by yij sun

• jagjit saini

Member

30 Points

96 Posts

### Query

Dec 21, 2020 07:14 AM|jagjit saini|LINK

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

• mgebhard

All-Star

52151 Points

23250 Posts

### Re: Query

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.

• jagjit saini

Member

30 Points

96 Posts

### Re: Query

Dec 21, 2020 04:02 PM|jagjit saini|LINK

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

• mgebhard

All-Star

52151 Points

23250 Posts

### Re: Query

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

• yij sun

Contributor

3410 Points

1294 Posts

### Re: Query

Dec 22, 2020 02:03 AM|yij sun|LINK

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'