Count how many first and last entries in given period of time are equal

Not sure if this adresses you problem correctly, since it is more of a workaround. But considering the following:

News need to be published before they can be "unpublished". So if you'd add 1 for each "published" and substract 1 for each "unpublished" your balance will be positive (or 1 to be exact) if first and last is "published". It will be 0 if you have as many unpublished as published and negative, if it has more unpublished than published (which logically cannot be the case but obviously might arise, since you set a date threshhold in the query where a 'published' might be occured before).

You might use this query to find out:

SELECT SUM(CASE status WHEN 'PUBLISHED' THEN 1 ELSE -1 END) AS 'publishbalance'FROM news_eventsWHERE date >= '2015-11-12 15:01:56.195'GROUP BY news_id

First of all, subqueries are a substantial part of SQL. A framework forbidding their use is a bad framework.

However, "first" and "last" can be expressed with NOT EXISTS: where not exists an earlier or later entry for the same news_id and date range.

select count(*)from mytable firstjoin mytable last on last.news_id = first.news_idwhere date between @from and @toand not exists(  select *  from mytable before_first  where before_first.news_id = first.news_id  and before_first.date < first.date  and before_first.date >= @from)and not exists(  select *  from mytable after_last  where after_last.news_id = last.news_id  and after_last.date > last.date  and after_last.date <= @to)and first.status = @statusand last.status = @status;

NOT EXISTS to the rescue:

SELECT ff.id ,ff.news_id ,ff.status , ff.zdate AS startdate        , ll.zdate AS enddateFROM newsflash ffJOIN newsflash ll        ON ff.news_id = ll.news_id        AND ff.status = ll.status        AND ff.zdate < ll.zdate        AND  NOT EXISTS (                SELECT * FROM newsflash nx                WHERE nx.news_id = ff.news_id                AND nx.zdate >= '2016-01-01' AND nx.zdate < '2016-02-01'                AND (nx.zdate < ff.zdate OR nx.zdate > ll.zdate)                )ORDER BY ff.id        ;