Query RSS

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

• • Reply
  mgebhard

  All-Star

  52151 Points

  23250 Posts

  Re: Query

  Dec 21, 2020 11:57 AM|mgebhard|LINK

  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.

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

    

• • Reply
  mgebhard

  All-Star

  52151 Points

  23250 Posts

  Re: Query

  Dec 21, 2020 04:14 PM|mgebhard|LINK

  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.

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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.