Preview Data per Day for the current week

Hello All,

i have the below entity

As you see, to this entity saved data for different days.

I want to display a table with the below format

Today |  Today + 1 |         Today + 2        | Today + 3 | Today + 4 | Today + 5 | Today + 6 |

Group of Area for today | Group of Area for Today + 1 | Group of Area for Today + 2| Group of Area for Today+3 | Group for of Area for Today+4 | | Group for of Area for Today+5 | | Group for of Area for Today+6 |

Yous advise or your support is highly appreciated.

If you want to display different dates in seperate columns, you'll need to join the same Entity multiple times, each time filtering on a different date.

Thanks for your reply Kilian. I have already did it but there is a confusion on this. If the Today column has not data(no group of Area data for today), then no data displayed to the next 6 days.

Well yes, that happens when you join the others on that one record. But that's probably not what you want. Is the calendar data Entity the only Entity in the Aggregate, or are you joining more? If so, is there an Entity that's always present?

Yes, it is the only entity in the agreegate

Any advise?

If they don't have anything in common, you can't query them in the same query. What I would do, especially if it's only 7 records, is for each date perform a seperate query, in a manual loop increasing the date by one (using AddDays), and append the result to a List.


Hi George.
This will require Advanced SQL.

How are you presenting the Area? If it is a list, the SQL needed is too complex to explain and maintain. You are better served by the suggestions above.

If you only wanted to show the total area, it could be done like this.

sum(case when datediff(day,@Today,{Entity}.Date)=0 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=1 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=2 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=3 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=4 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=5 then {Entity}.[Area] else 0 end),
sum(case when datediff(day,@Today,{Entity}.Date)=6 then {Entity}.[Area] else 0 end)
from {Entity}

where {Entity}.Date>=@Today

(yes, this is the easy version).


Ah, yeah, of course, that would work, thanks Nuno :).