How to make the Month period of 31 days for all months

1

Hi

I have this chart:

Screenshot 1444-12-03 at 9.34.38 AM
Screenshot 1444-12-03 at 9.34.38 AM2980×1220 256 KB

and this its SQL Code:

SELECT DateFromParts(year("b8akJSMS2"."Ticket"."ScheduledVisitDateTime") , month("b8akJSMS2"."Ticket"."ScheduledVisitDateTime") , 1) AS "ScheduledVisitDateTime" , count(*) AS "count", sum("b8akJSMS2"."Ticket"."Price") AS "sum"
FROM "b8akJSMS2"."Ticket"
WHERE (("b8akJSMS2"."Ticket"."Status" <> 'TicketStatus_CanceledByCustomer'
    OR "b8akJSMS2"."Ticket"."Status" IS NULL)
   AND ("b8akJSMS2"."Ticket"."Status" <> 'TicketStatus_CanceledByB8akAgent' OR "b8akJSMS2"."Ticket"."Status" IS NULL) AND ("b8akJSMS2"."Ticket"."Status" <> 'TicketStatus_Canceled' OR "b8akJSMS2"."Ticket"."Status" IS NULL) AND ("b8akJSMS2"."Ticket"."MajorId" = 3772 OR "b8akJSMS2"."Ticket"."MajorId" = 9671) AND "b8akJSMS2"."Ticket"."ScheduledVisitDateTime" >= DateFromParts(year(dateadd(month, -6, getdate())), month(dateadd(month, -6, getdate())), 1) AND "b8akJSMS2"."Ticket"."ScheduledVisitDateTime" < DateFromParts(year(dateadd(month, 1, getdate())), month(dateadd(month, 1, getdate())), 1))
GROUP BY year("b8akJSMS2"."Ticket"."ScheduledVisitDateTime"), month("b8akJSMS2"."Ticket"."ScheduledVisitDateTime")
ORDER BY year("b8akJSMS2"."Ticket"."ScheduledVisitDateTime") ASC, month("b8akJSMS2"."Ticket"."ScheduledVisitDateTime") ASC

As you can see we have months, and some months as we know like February has only 28 days, and we would like to create each month period of 31 days on the graph, could we do that by modifying this SQL Question?

I will appreciate your help

2

How can you have 31 days per month when there aren't 31 days in all months? Do you want an average per month or something?

3

@AndrewMBaines
No, I want to make the x-axis counting 31 days and set them as January, then counting the next 31 days and set them as February ...etc

Could we do that?

4

So the 1st of March would be displayed as the 29th February (30th in a Leap Year)?
Switch to day number of year or week number.

5

@AndrewMBaines
Yes

6

is there any method to do that?

7

You could do a lookup from day of year to some table for your 'new year'.
Honestly, for the user confusion, just change to week number or something normal.