Database

<connectionStrings><br />
		<add name="conString" connectionString="Data Source=kassem;Initial Catalog=CDC;persist security info=False;user id=sa; password=kassem255;" providerName="System.Data.SqlClient"/><br />
		<add name="AccessCon" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\current bdl Europe Schd\bdl_results.mdb" providerName="system.data.oledb"/><br />
	</connectionStrings>

<br />
Create Proc Check_Geocode<br />
(<br />
@num_key varchar(10),<br />
@Extension int, <br />
@OUTRES varchar(10) oUTpUT<br />
)<br />
as <br />
set @OutRes = (select Geocode from sde.Boundary_Codes<br />
where Extension = @Extension And Numkey = @Num_key)<br />
<br />

<br />
declare @OutRes varchar(10)<br />
exec Check_Geocode 3,'0400',@OutRes Out<br />
select @OutRes as Geocode<br />

<br />
<br />
select * from Boundary_Codes<br />
where Numkey = '0400' and Extension = 3<br />

Select 'My Business' AS BusName, sum(PassVer.PassedVerifier) AS 'Passed Verifiers', Sum(PassVer.CashFunded)AS 'Cash Funded', SUM(PassVer.OtherFunding) AS 'Other Funding', Sum(PassVer.CompletedApplications) AS 'Completed Applications', SUM(PassVer.CompleteFundedCash) AS 'Complete - Funded Cash', SUM(Passver.CompleteNotFundedCash) AS 'Complete-Not Cash', SUM(PassVer.Total) AS 'Total Applications',
	cast((Sum(PassVer.PassedVerifier) * 100.00) / sum(PassVer.CompletedApplications)  AS Float) AS '% Completed and verified',
	cast((SUM(PassVer.CompletedApplications)* 100.00) / Sum(PassVer.Total)  AS Float) AS '% of Completed from Total'
FROM (
--Selects Applications which Passed Verifier
Select 1 AS 'PassedVerifier', 0 As 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which passed the verifier and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 1 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which passed the verifier and are funded by methods other than Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 1 As 'OtherFunding', 0 AS 'CompletedApplications',0 AS 'CompleteFundedCash',  0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId != 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which are completed.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 1 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed'
UNION ALL

--Selects Applications which are completed and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 1 AS 'CompleteFundedCash',  0 AS 'CompleteNotFundedCash', 0 AS 'Total' 
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId = 'Cash'
UNION ALL

--Selects Applications which are completed and are not funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash',  1 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId != 'Cash'
UNION ALL

--Total amount of applications.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash',  0 AS 'CompleteNotFundedCash', 1 AS 'Total'
FROM dbo.BK_Account) PassVer


SELECT 'My Business' AS BusName,
CASE WHEN GROUPING (CAST(MONTH(A.CreationDate) AS VARCHAR))= 1 THEN 'Grand Total' 
else (CAST(MONTH(A.CreationDate) AS VARCHAR)) end as MonthCreated,
Count(*) AS TotalCompletedApplications,
SUM(A.FundingAmount) AS SumTotalCompletedApplications
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
WHERE (A.Visible = 1) AND (Q.QueueId= 'Pass Validation' ) 
GROUP BY (CAST(MONTH(A.CreationDate) AS VARCHAR))
With rollup