Can you kindly provide more  details along with sample data and also what you mean by I'm getting the result of only first case?

Saravanan

Can you kindly elaborate how you got count=3 and count=1?

Saravanan

Hi,

You are right. I made a small mistake.Below query gives accurate results. But you can Jeff Moden query as his query is always efficient.

Hi ,
Your SQL formatting looks very good. What tools your using for paste the query with color and proper formatting.

Solution:

WITH cte
AS (SELECT
...

Hi,
Welcome to SQL Central

create table calendar
(
[date] date,
value int);

insert into calendar values ('11/1/2017',123);
insert into calendar values ('11/2/2017',130);
insert into calendar values ('11/3/2017',140);

Thanks John. I posted the test data similar to what we got in Hive results(test data is coming like this may be due to hive nature as there is no...

Thanks Thorn and John . Will test with more test data and get back to you guys.

Regards,
Saravanan

That's good question I haven't got records like that . But I would like to exclude that records as well.

Regards,
Saravanan