set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[HourlyData] (@id int, @dtBeg DateTime, @dtEnd DateTime,@datSum DateTime)
As
Declare @dat DateTime
Set @dat=@datSum
SELECT H.hBeg, HOURLY.H_DEND,H.A_Count,H.B_Count, HOURLY.H_FVOLUME,HOURLY.H_FAVGDP,HOURLY.H_BDPCONST,HOURLY.H_BPCONST,HOURLY.H_BTCONST,
HOURLY.H_BDENSCONST,HOURLY.H_NPERIOD, HOURLY.H_FAVGP,HOURLY.H_FAVGT, HOURLY.H_FAVGDENS, 
Case WHEN H.hBeg >=@dat or DateADD(hour,1,H.hBeg)='20-03-2013 9:00' THEN 9   ELSE 8 END As CH
FROM HOURLY INNER JOIN (
	 SELECT DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG)) as hBeg,
	 HOURLY.H_DEND, HOURLY.H_NRUNID, HOURLY.H_NID, A_Count, B_Count
	 FROM Hourly LEFT JOIN (
		SELECT AL_NRUNID, COUNT(*) as A_COUNT, 
		DateADD(second,DATEPART(second,AL_DATE)*-1,DATEADD(minute,DATEPART(minute,AL_DATE)*-1,AL_DATE)) as dat 
		FROM ALARM  
		WHERE AL_NRUNID=@id AND AL_DATE>=@dtBeg  AND AL_DATE<@dtEnd 
		GROUP BY  DateADD(second,DATEPART(second,AL_DATE)*-1,DATEADD(minute,DATEPART(minute,AL_DATE)*-1,AL_DATE)), AL_NRUNID
							)as A
		ON HOURLY.H_NRUNID = A.AL_NRUNID AND HOURLY.H_DBEG=A.dat
		LEFT JOIN (
		    SELECT AU_NRUNID, COUNT(*) as B_Count, 
			DateADD(second,DATEPART(second,AU_DATE)*-1,DATEADD(minute,DATEPART(minute,AU_DATE)*-1,AU_DATE)) as dat 
			FROM AUDIT
			WHERE AU_NRUNID=@id AND AU_Date>= @dtBeg AND AU_DATE<@dtEnd
			GROUP BY  DateADD(second,DATEPART(second,AU_DATE)*-1,DATEADD(minute,DATEPART(minute,AU_DATE)*-1,AU_DATE)), AU_NRUNID
					)as AB 
		ON  HOURLY.H_NRUNID = AB.AU_NRUNID AND HOURLY.H_DBEG=AB.dat
	WHERE Hourly.H_NRUNID=@id AND DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG))>=@dtBeg 
	AND HOURLY.H_DBEG <@dtEnd
	GROUP BY DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG)),
	HOURLY.H_DEND ,HOURLY.H_NRUNID, HOURLY.H_NID, A_Count, B_Count
) As H
ON Hourly.H_NID=H.H_NID

Case WHEN H.hBeg >=@dat

SELECT H.hBeg, HOURLY.H_DEND,H.A_Count,H.B_Count, 
HOURLY.H_FVOLUME,HOURLY.H_FAVGDP,HOURLY.H_BDPCONST,HOURLY.H_BPCONST,HOURLY.H_BTCONST,
HOURLY.H_BDENSCONST,HOURLY.H_NPERIOD, HOURLY.H_FAVGP,HOURLY.H_FAVGT, HOURLY.H_FAVGDENS, 
Case WHEN H.hBeg >=@dat or  DateADD(hour,1,H.hBeg)=@datSum THEN 9   ELSE 8 END As CH
FROM HOURLY INNER JOIN (
	 SELECT DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG)) as hBeg,
	 HOURLY.H_DEND, HOURLY.H_NRUNID, HOURLY.H_NID, A_Count, B_Count
	 FROM Hourly LEFT JOIN (
		SELECT AL_NRUNID, COUNT(*) as A_COUNT, 
		DateADD(second,DATEPART(second,AL_DATE)*-1,DATEADD(minute,DATEPART(minute,AL_DATE)*-1,AL_DATE)) as dat 
		FROM ALARM  
		WHERE AL_NRUNID=@id AND AL_DATE>=@dtBeg  AND AL_DATE<@dtEnd 
		GROUP BY  DateADD(second,DATEPART(second,AL_DATE)*-1,DATEADD(minute,DATEPART(minute,AL_DATE)*-1,AL_DATE)), AL_NRUNID
							)as A
		ON HOURLY.H_NRUNID = A.AL_NRUNID AND HOURLY.H_DBEG=A.dat
		LEFT JOIN (
		    SELECT AU_NRUNID, COUNT(*) as B_Count, 
			DateADD(second,DATEPART(second,AU_DATE)*-1,DATEADD(minute,DATEPART(minute,AU_DATE)*-1,AU_DATE)) as dat 
			FROM AUDIT
			WHERE AU_NRUNID=@id AND AU_Date>= @dtBeg AND AU_DATE<@dtEnd
			GROUP BY  DateADD(second,DATEPART(second,AU_DATE)*-1,DATEADD(minute,DATEPART(minute,AU_DATE)*-1,AU_DATE)), AU_NRUNID
					)as AB 
		ON  HOURLY.H_NRUNID = AB.AU_NRUNID AND HOURLY.H_DBEG=AB.dat
	WHERE Hourly.H_NRUNID=@id AND DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG))>=@dtBeg 
	AND HOURLY.H_DBEG <@dtEnd
	GROUP BY DateADD(second,DATEPART(second,HOURLY.H_DBEG)*-1,DATEADD(minute,DATEPART(minute,HOURLY.H_DBEG)*-1,HOURLY.H_DBEG)),
	HOURLY.H_DEND ,HOURLY.H_NRUNID, HOURLY.H_NID, A_Count, B_Count
) As H
ON Hourly.H_NID=H.H_NID

CAST(CONVERT(VARCHAR(14), GETDATE(), 120) +'00' AS DATETIME)
DATEADD(ss, DATEPART(ss, GETDATE())*-1, DATEADD(mi, DATEPART(mi, GETDATE())*-1, GETDATE()))

CAST(CONVERT(VARCHAR(14), GETDATE(), 120) +'00' AS DATETIME)
DATEADD(ss, DATEPART(ss, GETDATE())*-1, DATEADD(mi, DATEPART(mi, GETDATE())*-1, GETDATE()))

using System;
using System.Data;
using System.Data.SqlClient;

namespace CaseTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=tempdb;Integrated Security=True");
            SqlCommand sqlt = new SqlCommand("create procedure CaseTest	@P int AS select case when @p> 0 then 1 else 0 end", con);
            SqlCommand sqlp = new SqlCommand("CaseTest", con);
            sqlp.CommandType = CommandType.StoredProcedure;
            sqlp.Parameters.AddWithValue("@p", 100);
            con.Open();
            sqlt.ExecuteNonQuery();
            Console.WriteLine(sqlp.ExecuteScalar());
            sqlt.CommandText = "drop procedure CaseTest";
            sqlt.ExecuteNonQuery();
            con.Close();

        }
    }
}