locked
Query RRS feed

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

    Tuesday, December 22, 2020 2:03 AM