>Sir while using SQLSERVER 2012, I have these codes
>
>
>

>SELECT hrs,Max(reading)as reading
>FROM   bin1
>WHERE (CONVERT(VARCHAR(10), date, 103) = CONVERT(VARCHAR(10), GETDATE(), 103))
> AND (hrs IS NOT NULL) and updown=0 and tik is null
>group by hrs
>union
>SELECT hrs,max(reading)as reading
>FROM   bin1_empty
>WHERE (CONVERT(VARCHAR(10), date, 103) = CONVERT(VARCHAR(10), GETDATE(), 103))
> AND (hrs IS NOT NULL)
>group by hrs
>order by hrs
>

>
>It display following result
>
>
>But I want to get this result
>
>
>Hours from 0 to 23
>Missed hours must have value 0
>
>Please

;with cte_hours(Hours)
AS
(
   SELECT 0 AS Hours
   UNION ALL
   SELECT Hours+1 as Hours
   FROM cte_hours
   WHERE Hours < 23
)

select ISNULL(Data.hrs, cte.Hours)  AS Hrs
      ,MAX(ISNULL(data.reading, 0)) AS reading
from (SELECT hrs
            ,Max(reading)as reading
      FROM   bin1
      WHERE CONVERT(date, date) = CONVERT(date, GETDATE())
        AND hrs IS NOT NULL
        AND updown=0
        AND tik is null
	  group by hrs
      UNION
	  SELECT hrs,max(reading)as reading
      FROM   bin1_empty
      WHERE CONVERT(date, date) = CONVERT(date, GETDATE())
		AND hrs IS NOT NULL
	  group by hrs) as data
CROSS JOIN cte_hours cte
group by 1
order by 1

NOT TESTED!
:-)