20
Views
7
Comments
Solved
Preview Data per Day for the current week
Question

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.

mvp_badge
MVP
Rank: #69
Solution

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.

SELECT
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).

mvp_badge
MVP
Rank: #2

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.

Rank: #2425

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.

mvp_badge
MVP
Rank: #2

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?

Rank: #2425

Yes, it is the only entity in the agreegate

Any advise?

mvp_badge
MVP
Rank: #2

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.

mvp_badge
MVP
Rank: #69
Solution

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.

SELECT
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).

mvp_badge
MVP
Rank: #2

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