SQL chart query

Tomi_Kaartama

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

Jeff_Lang

instead of using "week" to get the week number, using "wk" will get the week number of the year (1-53) for the date given
eg

SELECT DATEPART( wk, CreatedDate) AS WeekNumber

Tomi_Kaartama

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_Kaartama

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,

Karen_Bruster1

@Tomi_Kaartama

I would be interested in seeing how this turns out

Jeff_Lang

instead of using "week" to get the week number, using "wk" will get the week number of the year (1-53) for the date given
eg

SELECT DATEPART( wk, CreatedDate) AS WeekNumber

Tomi_Kaartama

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_Kaartama

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,