agg_tran view

agg_tran view

USE [FPSA]
GO

/****** Object:  View [dbo].[AGG_TRANSACTION]    Script Date: 27-09-2017 13:28:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





/****** Script for SelectTopNRows command from SSMS  ******/
create view [dbo].[AGG_TRANSACTION] with schemabinding as SELECT [LEVEL]
      ,[GEOGRAPHY]
      ,convert(date,[DATE_TIME]) [DATE_TIME]
      ,sum(isnull([AADHAAR_AUTH],0)) [AADHAAR_AUTH]
      ,sum(isnull([AADHAAR_AUTH_OFFLINE],0)) [AADHAAR_AUTH_OFFLINE]
      ,sum(isnull([OTP_AUTH],0)) [OTP_AUTH]
      ,sum(isnull([UNAUTH_OFFLINE],0)) [UNAUTH_OFFLINE]
      ,sum(isnull([AADHAARAUTH_FAILED_OFFLINE],0)) [AADHAARAUTH_FAILED_OFFLINE]
      ,sum(isnull([ONLINE_OTP_FAILED],0)) [ONLINE_OTP_FAILED]
      ,sum(isnull([PURE_ONLINE],0)) [PURE_ONLINE]
      ,sum(isnull([PURE_OFFLINE],0)) [PURE_OFFLINE]
      ,sum(isnull([ONLINE_CANCEL],0)) [ONLINE_CANCEL]
      ,sum(isnull([OFFLINE_CANCEL],0)) [OFFLINE_CANCEL]
      ,sum(isnull([TOTAL],0)) [TOTAL]
      ,[LEVEL1]
      ,[LEVEL2]
	  ,[LEVEL21]
      ,[LEVEL3]
	  ,ALLOTMENT_MONTH,TYPE
	  , COUNT_BIG(*) AS countBig
  FROM dbo.AGG_TRANSACTION_P group by convert(date,date_time),LEVEL,GEOGRAPHY,ALLOTMENT_MONTH,TYPE,level1,level2,LEVEL21,level3














GO