Skip to content
Advertisement

SQL – How can I rewrite this request without many SELECT

I want to have the number of open requests for each month before the 1st of each month. I use ComboChart and my orange line is, at the moment, the average for the 6 past months, I want to change this data for the opened requests.

Exemple : for April, I have 2 new requests and 1 closed request and I want the total of the requests open before the April 1st.

The period is for 6 months before and the selected month. It will be a plus if you can use PIVOT 🙂

Here what I have so far

The results is what I expect, but I think it can be improve. What I want is this

Evolution of requests

Advertisement

Answer

try converting each Select to a CASE statement that evaluates to 1 if the conditions are true and zero otherwise. Then SUM each case statement e.g. (pseudo-code, not actual SQL):

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement