Home Analyst Portal

SQL Query by date

Ken_CurtisKen_Curtis Customer IT Monkey ✭
The issue is that datetime means I cannot group results by date. For example, all tickets assigned to Tier 1 by date. Each ticket tends to have its own datetime. I've played with CONVERT and other sql modifications in the query without luck. Any examples that could help?

Best Answer

Answers

  • Ken_CurtisKen_Curtis Customer IT Monkey ✭
    This is for dashboards mainly (I managed to save inadvertently), using the Chart with SQL tools.
  • Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭

    I'd create a derived date from the datetime field for this.

    E.g. Derived value for YYYYMM for sorting. You can add the day in the same way.

    SELECT CAST(YEAR(FirstAssignedDate) as varchar(4) + RIGHT('0' + CAST(MONTH(FirstAssignedDate) as varchar(2)), 2) as DateSort
  • Ken_CurtisKen_Curtis Customer IT Monkey ✭
    This is definitely closer. So I run this in SQL with success:
    SELECT TOP 25 CAST(MONTH(Created) AS varchar(2)) + CAST(DAY(Created) AS varchar(2)) + CAST(YEAR(Created) AS varchar(4))
      FROM [CiresonDB].[dbo].[WorkItem]

    But from the SQL Chart Widget, this fails:
    SELECT TOP 25 CAST(MONTH(wi.Created) AS varchar(2)) + CAST(DAY(wi.Created) AS varchar(2)) + CAST(YEAR(wi.Created) AS varchar(4)) AS DateSort

    Note that per the example the wi.Field seems to be the required format. For example, this works:
    SELECT TOP 25 wi.WorkItemId, wi.Created, wi.Title, ds.DisplayString Tier, wi.AssignedUser FROM [WorkItem] wi LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId INNER JOIN [DisplayString] ds ON ds.ElementID=enum.EnumerationID
  • Ken_CurtisKen_Curtis Customer IT Monkey ✭
    Thanks!!
Sign In or Register to comment.