Displaying rooms occupancy in chart from booking assignment


I am working on booking assignment and have to show the rooms occupancy in line chart, i have written the advance query but the browser throws syntax error on execution. My query is stated below.Kindly have a look and can any body tell where i am doing wrong ?


SyntaxEditor Code Snippet

with mycte as(
     select @DateStart AS DateValue
     union all
     select DateValue + 1
     from    mycte   
     where   DateValue + 1 <= @DateEnd
select DateValue,
        ((select count({Booking}.[RoomId])
          from {Booking}
          where {Booking}.[CheckInDate]  <= DateValue
            and {Booking}.[CheckOutDate] >= DateValue
            and {Booking}.[StatusId] <> @StatusCanceledId) * 100 /(select count(*) from {Room}) AS Ocupation,  '', '', ''@DateStart 
from    mycte)

attached image states the error which browser throws

This is quite the exotic query, I don't recall having to do anything like this.

Here's one of the errors

AS Ocupation,  '', '', ''@DateStart 

I think you are making this much more difficult than it has to be....

Claring whats the solution then?

can you write the query for me which you are sayin'?

i'll be thankful to you

Well I'm not one to just give you the answer, you won't learn anything that way :)

But the general idea is to create a list, a counter variable and a date variable.
You create an IF-LOOP using the counter variable which you increment at the end of your IF-LOOP.
You set the date variable to the current date, and get all rooms which are occupied by counting the results of your query. Then at the end of the IF-LOOP you also increment this date variable by using the AddDays() function.

Afterwards you can use the INITdatapoint function to create datapoints for your chart.

Hi Claring

Here is my screen shot for home page preparation,have a  look, i have done the things you are saying..i'm just stucked at the query.


Usama Hassan

I see, you need to append the datapoints to a datapoint list, and you give that list to your chart.

hi Claring

You mean to say i don't need to use the sql query ?? but my graph was getting data points that were calculating and returning from the sql query..

I'm stucked at this point from last three days..kindly can you elaborate so that i can solve this issue

kind regards 

Usama Hassan

Hey Usama,

No no you still need the sql query to gather the data from the database.
However the charts only accept a DataPoint list, so you end up with 2 choices,
1) map the data to DataPoints by using the SQL result list directly
2) create a datapoint list yourself by using the InitDataPoint server action and the listappend server action to fill a datapoint list from the SQL result data.

Hi Usama,

I took the bookings course a while ago, and you're thinking way too complicated. I used an Aggregate, not a SQL query, to get the data points. Iirc, the guidance in the course's text also directs in that way.