Datepart/Cast Issue

• skyline_android

  SSC Enthusiast

  Points: 190

  More actions

  August 7, 2012 at 6:25 am

  #265555

  Below is the codes that should pull all the list of Agents that has HPP data for the previous week.

  However, I used the CASE to add a column to specify (SUNDAY to SATURDAY) and use that in order to check which day has data and display it on the report.

  My question now, it only labels all columns to TUESDAY even the date does not fall on a Tuesday.

  DECLARE @TodayDayOfWeek INT

  DECLARE @EndOfPrevWeek DateTime

  DECLARE @StartOfPrevWeek DateTime

  --get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)

  SET @TodayDayOfWeek = datepart(weekday, GetDate())

  --get the last day of the previous week (last Sunday)

  SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())

  --get the first day of the previous week (the Monday before last)

  SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+5), GetDate())

  SELECT DISTINCT

  s.SiteID,

  ...

  CASE

  WHEN datepart(weekday, GetDate()) = 1 THEN 'SUNDAY'

  WHEN datepart(weekday, GetDate()) = 2 THEN 'MONDAY'

  WHEN datepart(weekday, GetDate()) = 3 THEN 'TUESDAY'

  WHEN datepart(weekday, GetDate()) = 4 THEN 'WEDNESDAY'

  WHEN datepart(weekday, GetDate()) = 5 THEN 'THURSDAY'

  WHEN datepart(weekday, GetDate()) = 6 THEN 'FRIDAY'

  WHEN datepart(weekday, GetDate()) = 7 THEN 'SATURDAY'

  END 'New_Date'

  FROM Statement[/u]

  WHERE

  --Employees

  e.RowInactiveDate >= GETDATE()

  --Employee Role

  AND er.RoleId = '...Data...'

  AND er.[Primary] = '...Data...'

  AND er.RowInactiveDate >= GETDATE()

  --Sites

  AND es.SiteID IN ('...Data...')

  --Absenteeism

  AND ab.DateEffective BETWEEN (CONVERT(VARCHAR, @StartOfPrevWeek,101)) AND (CONVERT(VARCHAR, @EndOfPrevWeek+1,101))

  AND ab.[Absent] = '...Data...'

  AND ab.Scheduled = '..Data...'

  --Custom Metric

  AND m.MetricID = '...Data...'

  AND hpp.EmployeeID IS NULL

  --ServiceType

  AND aea.Application_ID = '...Data...'

  AND aea.Active = '...Data...'

  AND aea.Application_Role_ID = '...Data...'

  AND aea.Active = '...Data...' AND aea.Misc1 <> '...Data...'

  ORDER BY

  --s.Region,

  s.SiteName,

  ab.DateEffective,

  a.AccountName,

  sp.SubProgramName,

  e.LastName

  This is the results I had when I ran this script. I'm a newbie on SQL and I'm pretty sure that this script I had has issue or I am missing something. Hope to hear anyone from the community soon.

  

• Ant-Green

  SSC Guru

  Points: 113563

  More actions

  August 7, 2012 at 6:39 am

  #1522536

  That because GETDATE is getting the current date and time, therefore as today is Tuesday, they all show Tuesday.

  You need to use the dateeffective column instead of GetDate(), also take a look at the DATENAME function to eliminate the CASE statements.

• skyline_android

  SSC Enthusiast

  Points: 190

  More actions

  August 7, 2012 at 6:58 am

  #1522552

  Hi Anthony,

  Thanks for the quick reply. Can you tell me how can I use or insert the ab.DateEffective into the Case?

  CASE

  WHEN ab.DateEffective = 1 THEN 'SUNDAY'

  WHEN ab.DateEffective = 2 THEN 'MONDAY'

  ...

  END 'New_Date'

• Ant-Green

  SSC Guru

  Points: 113563

  More actions

  August 7, 2012 at 7:12 am

  #1522562

  In the CASE where you have GetDate(), replace it with DateEffective

  DATEPART(WEEKDAY, dateeffective) = 1 THEN 'Sunday'

  But I would do it like

  SELECT

  ...

  ...

  ...

  DATENAME(dw,dateeffective)

  FROM

  ...

• skyline_android

  SSC Enthusiast

  Points: 190

  More actions

  August 7, 2012 at 9:17 am

  #1522669

  Hi Anthony,

  It worked. Can I use the column name which is New_Date in the Where clause?

  What I'm aiming now is If there are profiles that is tagged DateEffective either to (Monday, Tuesday or any day up to Sunday) which ever comes first will be the only one to be displayed in the report.

  I believe I need to use the If then Else clause on this..

• Ant-Green

  SSC Guru

  Points: 113563

  More actions

  August 7, 2012 at 9:20 am

  #1522671

  No, you cannot as it violates logical query processing order.

  You would need to build in the DATENAME function or build in the CASE call within your WHERE clause.

  Best thing to do is follow the second link in my signature.

  Post us DDL, sample data and expected outcomes and we will be able to come up with a solution.