USE [master]
GO

/****** Object:  StoredProcedure [dbo].[test]    Script Date: 09/03/2012 11:48:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[test] @LastTransactionId Decimal(18,0),@LastSubmitted numeric(18,0) 
AS
	SET NOCOUNT ON  
	
  create table #temp1
  (
  PracticeName varchar(250) not null,
  Uniqueincluster decimal(18,0) not null,
  AppCount bigint not null,
  ProvCount bigint not null,
  BillableUnits bigint not null)
  
  create table #temp2
  (
  BillingMode nvarchar(50)
, QueuedAtDate datetime
, JobUnique nvarchar(50)
, [Status] nvarchar(50)
, ProductId nvarchar(75)
, TransactionDate datetime
, TransactionID nvarchar(50) not null
, AccountId int
, SubAccountId int
, EntityName nvarchar(100)
, ApplicationDescription nvarchar(100)
, OwnerName nvarchar(100)
, TransactionType nvarchar(1)
, TransactionStatus nvarchar(10)
, BillableUnits int
)
  
  insert into #temp1
  select 
  act.PracticeName,
  act.uniqueincluster,
  act.CallCount as "App Count",
  SUM(pct.CallCount) as "Prov Count",
  act.CallCount - SUM(pct.CallCount) as BillableUnits
  from CallClusterVARASP.dbo.ApplicationCallTally act  right join CallClusterVARASP.dbo.ProviderCallTally pct on pct.UniqueInCluster = act.UniqueInCluster
  where act.uniqueincluster between  @LastSubmitted and @LastTransactionId
  group by act.uniqueincluster, act.CallCount,  act.PracticeName
  having act.CallCount <> SUM(pct.CallCount)
  and SUM(pct.callcount) > 0
  order by 1,2
    
DECLARE @billableunits1 varchar(10)
DECLARE @uniqueincluster1 varchar(10)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT billableunits,uniqueincluster FROM #temp1 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert into #temp2
select distinct
''Submitted'' as BillingMode
, getdate() as QueuedAtDate
, act.jobUnique   as JobUnique
, ''Queued'' as Status
, ''PT'' as ProductId
, IsNull(dj.JobSubmittedAt, aj.JobSubmittedAt) as TransactionDate
, act.UniqueInCluster as TransactionID
, IsNull(dj.AccountNumber, aj.AccountNumber) as AccountId
, au.ID as SubAccountId
, act.PracticeName as EntityName
, act.Application as ApplicationDescription
, act.PracticeName as OwnerName
, ''P'' as TransactionType
, null as TransactionStatus
,'+@billableunits1+' as BillableUnits
 from ApplicationCallTally act 
left outer join ActiveJobs aj on aj.UniqueInCluster = act.UniqueInCluster and aj.JobUnique = aj.JobUnique
left outer join DoneJobs dj on dj.UniqueInCluster = act.UniqueInCluster and dj.JobUnique = dj.JobUnique
left outer join EzMedCore.dbo.AccountUser au on au.AccountNumber = IsNull(dj.AccountNumber, aj.AccountNumber) 
and act.AspUserID = au.ID
where act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'
--PRINT(@sql)
EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @billableunits1,@uniqueincluster1
END
CLOSE TableCursor
DEALLOCATE TableCursor

insert into HAM_SubmittedQueue
select * from #temp2

drop table #temp1
drop table #temp2


GO

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

'where act.uniqueincluster between select min('+@uniqueincluster+') from #temp1 and select max('+@uniqueincluster+') from #temp1'

'where act.uniqueincluster between (select min(uniqueincluster) from #temp1) and (select max(uniqueincluster) from #temp1)'