Home Analyst Portal

SQL chart query

Tomi_KaartamaTomi_Kaartama Customer IT Monkey ✭
Hi,

I would want to create a chart in Analytics that would show the amount of service requests created by week for the past 90 days or so. Since we don't have SQL2012 running, I am using the following SQL command elsewhere to stack other requests per day. Unfortunately, replacing "day" in the command by "week" gave an error.

Is there a way in SQL to stack based on the week number?

SELECT RIGHT('0' + CAST(Month(CreatedDate) as varchar(2)), 2) + RIGHT('0' + CAST(Day(CreatedDate) as varchar(2)), 2) as CompletedDate, 

T: Tomi

Best Answers

  • Tomi_KaartamaTomi_Kaartama Customer IT Monkey ✭
    Answer ✓
    Thanks for the tip. The problem with the above was that the week numbers were sorted wrongly when you had weeks from 2017 and 2018. I solved this by using the following line:

    CAST(Year(CreatedDate) as varchar(4)) + ' - ' + Cast(DATEPART( wk, CreatedDate) as varchar(2)) as CreatedDate,


  • Tomi_KaartamaTomi_Kaartama Customer IT Monkey ✭
    Answer ✓
    But the above did not work entirely either since the week numbers were still sorting wrongly so here is the final code:

    SELECT CAST(Year(CreatedDate) as varchar(4)) + ' - ' + RIGHT('0' + Cast(DATEPART( wk, CreatedDate) as varchar(2)), 2) as CreatedDate,

Answers

  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @Tomi_Kaartama

    I would be interested in seeing how this turns out

  • Tomi_KaartamaTomi_Kaartama Customer IT Monkey ✭
    Answer ✓
    Thanks for the tip. The problem with the above was that the week numbers were sorted wrongly when you had weeks from 2017 and 2018. I solved this by using the following line:

    CAST(Year(CreatedDate) as varchar(4)) + ' - ' + Cast(DATEPART( wk, CreatedDate) as varchar(2)) as CreatedDate,


  • Tomi_KaartamaTomi_Kaartama Customer IT Monkey ✭
    Answer ✓
    But the above did not work entirely either since the week numbers were still sorting wrongly so here is the final code:

    SELECT CAST(Year(CreatedDate) as varchar(4)) + ' - ' + RIGHT('0' + Cast(DATEPART( wk, CreatedDate) as varchar(2)), 2) as CreatedDate,
Sign In or Register to comment.