12 month from prompted date

• Kerrie Sorrell

  SSC Eights!

  Points: 921

  More actions

  June 19, 2006 at 12:58 pm

  #168849

  hi all,

  i am having issues with the date prompt, what i want to happen is that the user is prompted for date and report comes back with data from prompted date and on. what is happening is it is being ignored. i am not sure what is going on i am still kind of new at this. any help would be appreciated.

  thanks in advanced, kerrie

  SELECT    

  CC.CostCentre

  , RTRIM(EMP.SurName) + ', ' + EMP.ForeName AS E_Name

  , DATEPART(MM,TL.TimeLineDate) AS MONTH

  , CONVERT(numeric(9, 2), TL.Hours / 8) AS Days

  , TL.TimeLineDate

  , CASE WHEN DATEPART(MM,TL.TimeLineDate) = 1

             THEN 'January'

             WHEN DATEPART(MM,TL.TimeLineDate) = 2

             THEN 'February'

             WHEN DATEPART(MM,TL.TimeLineDate) = 3

             THEN 'March'

             WHEN DATEPART(MM,TL.TimeLineDate) = 4

             THEN 'April'

             WHEN DATEPART(MM,TL.TimeLineDate) = 5

             THEN 'May'

             WHEN DATEPART(MM,TL.TimeLineDate) = 6

             THEN 'June'

             WHEN DATEPART(MM,TL.TimeLineDate) = 7

             THEN 'July'

             WHEN DATEPART(MM,TL.TimeLineDate) = 8

             THEN 'August'

             WHEN DATEPART(MM,TL.TimeLineDate) = 9

             THEN 'September'

             WHEN DATEPART(MM,TL.TimeLineDate) = 10

             THEN 'October'

             WHEN DATEPART(MM,TL.TimeLineDate) = 11

             THEN 'November'

             ELSE 'December'

   END AS MONTH_1

  FROM        

  dbo.CostCentres CC LEFT OUTER JOIN

  dbo.Employees EMP ON CC.CostCentreId = EMP.CostCentreId LEFT OUTER JOIN

  dbo.TimeLines TL ON EMP.EmployeeId = TL.EmployeeId

  WHERE    

  TL.TimeLineDate <= dateadd(month, 12, @Date) AND

  ((CC.CostCentreId IN (56, 22, 14, 57, 23, 61)) OR (CC.CostCentreId IS NULL)) AND

  (TL.StageCode = '02bill')

  ORDER BY

  CC.CostCentre

  , RTRIM(EMP.SurName) + ', ' + EMP.ForeName

  ,TL.TimeLineDate

• Mike Kendall

  SSC Eights!

  Points: 958

  More actions

  June 19, 2006 at 1:36 pm

  #644679

  Kerrie,

  Your code looks reasonable.  I'd comment out the date test and see what the query produces without it, to eliminate the possibility the problem is elsewhere.  Also, you can shorten the code by using the DATENAME function.

  DATENAME

  (MONTH,DATEPART(MONTH,TL.TimeLineDate))

  Good luck

  Mike