need help in aging query (Date difference problem)

• uzwa ul haque

  SSChasing Mays

  Points: 607

  More actions

  May 12, 2006 at 7:53 am

  #168191

  I have a query which is supposed to return Following result set:

  -----------------------------------------------------------------

  Agent|Sale in Last Year|Last 6 Months|Last Quater|This Quater|...

  -----------------------------------------------------------------

  problem i am facing that user can enter any date .. i just cant figure out correct date differences between da date i send in as parameter and calculating these differences from database according to the fields.

  please help me out.

• Scorpion_66

  SSCertifiable

  Points: 7891

  More actions

  May 12, 2006 at 8:48 am

  #637593

  If we were to be given the table structure, we would be able to help much more efficiently.....

• SwePeso

  SSC-Dedicated

  Points: 39752

  More actions

  May 15, 2006 at 6:23 am

  #637804

  How are dates related to table structure?

  Just help the guy!

  Sale in Last Year?

  Does that mean previous full year or current running last year?

  DECLARE @WorkDate DATETIME, @StartDate DATETIME, @EndDate DATETIME

  Assume @WorkDate is the date the end users selects.

  Previous full year is then selected with

  SELECT @StartDate = '1/1/' + CAST(YEAR(@WorkDate) - 1 AS VARCHAR(4), @EndDate = '12/31/' + CAST(YEAR(@WorkDate) - 1 AS VARCHAR(4)

  If current running last year is then selected with

  SELECT @StartDate = DATEADD(yyyy, -1, @WorkDate), @EndDate = @WorkDate

  Last 6 Months?

  Does that mean previous full six months or current running last six months?

  DECLARE @WorkDate DATETIME, @StartDate DATETIME, @EndDate DATETIME

  Assume @WorkDate is the date the end users selects.

  Previous full six months is then selected with

  SELECT @StartDate = CONVERT(VARCHAR(6), DATEADD(mm, -6, @WorkDate), 112) + '01',

   @EndDate = DATEADD(dd, -1, DATEADD(mm, 6, @StartDate))

  If current running last six months is then selected with

  SELECT @StartDate = DATEADD(mm, -6, @WorkDate), @EndDate = @WorkDate

  Last Quarter?

  Does that mean previous full quarter or current running last quarter?

  DECLARE @WorkDate DATETIME, @StartDate DATETIME, @EndDate DATETIME

  Assume @WorkDate is the date the end users selects.

  Previous full quarter is then selected with

  SELECT @StartDate = CONVERT(VARCHAR(6), DATEADD(mm, -3 - (MONTH(@WorkDate) -1) % 3 , @WorkDate), 112) + '01',

   @EndDate = DATEADD(dd, -1, DATEADD(mm, 3, @StartDate))

  If current running last quarter is then selected with

  SELECT @StartDate = DATEADD(mm, -3, @WorkDate), @EndDate = @WorkDate

  This Quarter?

  Does that mean current full quarter or current running last quarter?

  DECLARE @WorkDate DATETIME, @StartDate DATETIME, @EndDate DATETIME

  Assume @WorkDate is the date the end users selects.

  Current full quarter is then selected with

  SELECT @StartDate = CONVERT(VARCHAR(6), DATEADD(mm, - (MONTH(@WorkDate) -1) % 3 , @WorkDate), 112) + '01',

   @EndDate = DATEADD(dd, -1, DATEADD(mm, 3, @StartDate))

  If current running last quarter is then selected with

  SELECT @StartDate = DATEADD(mm, -3, @WorkDate), @EndDate = @WorkDate

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• uzwa ul haque

  SSChasing Mays

  Points: 607

  More actions

  May 16, 2006 at 3:49 am

  #638084

  Grasshopper that was exactly what i was looking for, although i have solved my problem on my own but i used more number of lines than your solution.

  thumbs up buddy.