USE DWDashboardSource ;
GO
ALTER VIEW dbo.vKioskLoginTracking AS
WITH cteAllValuesForCurrentHour AS
(
	SELECT DISTINCT
	CenterNumber = C.CNO_A
	,X.KioskNumber
	,TransDate = CONVERT(CHAR(10),GETDATE(),120)
	,TT.HourOfDay
	,CountKiosk1 = '0'
	,CountKiosk2 = '0'
	FROM
	DWDashboardSource.dbo.Centers C
	INNER JOIN
	(
		SELECT KioskNumber = '1'
		UNION
		SELECT KioskNumber = '2'
	) X
		ON 'Z' = 'Z'
	CROSS JOIN
	(
		SELECT DISTINCT HourOfDay =
			CASE LEN(DATEPART(hh,GETDATE()))
				WHEN 1 THEN '0' + CAST(DATEPART(hh,GETDATE()) AS CHAR(1))
				ELSE CAST(DATEPART(hh,GETDATE()) AS CHAR(2))
			END
	) TT
)
,cteKioskActivity AS
(
	SELECT
	CenterNumber = ISNULL(A.CenterNumber,B.CenterNumber)
	,TransDate = ISNULL(A.TransDate,B.TransDate)
	,HourOfDay = ISNULL(A.HourOfDay,B.HourOfDay)
	,CountKiosk1 = ISNULL(A.LoginCount,'0')
	,CountKiosk2 = ISNULL(B.LoginCount,'0')
	FROM
	(
		SELECT DISTINCT
		[CenterNumber]
		,TransDate = CAST([TransDate] AS DATE)
		,HourOfDay = CAST(SUBSTRING([TransTime],1 ,2) AS VARCHAR(8))
		,LoginCount = COUNT(*) OVER (PARTITION BY [CenterNumber],[KioskNumber],CAST([TransDate] AS DATE),CAST(SUBSTRING([TransTime],1 ,2) AS VARCHAR(8)))
		FROM [DWDashboardSource].[dbo].[CenterKioskActivity]
		WHERE KioskNumber = 1
	) A
	FULL JOIN
	(
		SELECT DISTINCT
		[CenterNumber]
		,TransDate = CAST([TransDate] AS DATE)
		,HourOfDay = CAST(SUBSTRING([TransTime],1 ,2) AS VARCHAR(8))
		,LoginCount = COUNT(*) OVER (PARTITION BY [CenterNumber],[KioskNumber],CAST([TransDate] AS DATE),CAST(SUBSTRING([TransTime],1 ,2) AS VARCHAR(8)))
		FROM [DWDashboardSource].[dbo].[CenterKioskActivity]
		WHERE KioskNumber = 2
	) B
		ON (A.CenterNumber = B.CenterNumber OR B.CenterNumber = A.CenterNumber)
		AND (A.TransDate = B.TransDate OR B.TransDate = A.TransDate)
		AND (A.HourOfDay = B.HourOfDay OR B.HourOfDay = A.HourOfDay)
)
SELECT DISTINCT
AVFCH.CenterNumber
,AVFCH.TransDate
,AVFCH.HourOfDay
,CountKiosk1 = ISNULL(KA.CountKiosk1,AVFCH.CountKiosk1)
,CountKiosk2 = ISNULL(KA.CountKiosk2,AVFCH.CountKiosk2)
FROM
cteAllValuesForCurrentHour AVFCH
LEFT JOIN
cteKioskActivity KA
	ON AVFCH.CenterNumber = KA.CenterNumber
	AND AVFCH.TransDate = KA.TransDate
	AND AVFCH.HourOfDay = KA.HourOfDay
;