Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

Using Case Statement to choose select statement

I want to choose between 2 select statements whether today is the beginning of the month or not.

I am getting an error.

How can I correct the syntax for the following code?

if OBJECT_ID(N'tempdb..#RollTest') is not null
		Begin
			drop table #RollTest
		End
	Create table #RollTest(
	   [AccountNumber] varchar (15)
		,[CAmt] numeric(15,2)
		,[VisitID] varchar (15),
		Typ varchar(5)
		);
		
/*-----------------------------------------------*/
/*                                               */
/*                                               */
/*         Input PriorDay Beginning ATB          */
/*                                               */
/*                                               */
/*-----------------------------------------------*/		
	Declare @BOM as DateTime;
	Declare @CD as DateTime;
	set @BOM = 	dateadd(dd,datediff(dd,0,dateadd(dd,-(DAY(dateadd(mm,1,getdate()))-1),dateadd(mm,0,getdate()))),0)-1
	set @CD = DATEADD(dd,datediff(dd,0,getdate()),0)
	insert into #RollTest
	Case When @BOM = @CD
		
			SELECT  DAPMA.AccountNumber
			,DAPMA.CAmt
			,DAPMA.VisitID
			FROM [livedb].[dbo].[vw_DAILY_ATB_Prior_Month_ATB] DAPMA
		
	else
		
			SELECT  convert(varchar(15),DATB.AccountNumber) as AccountNumber
			  ,convert(numeric(15,2),sum(DATB.CAmt)) as CAmt
			  ,convert(varchar(15),DATB.VisitID) as VisitID
			  ,'A' as Typ
			FROM [livedb].[dbo].[tbl_DAILY_ATB_CMBAL] DATB
			where DATB.VisitID is not null and SUBSTRING(DATB.AccountNumber,1,1)<>'B' and DATB.AccountNumber not in ('A00000221516','A0000222086','A00000272018') 
			group by DATB.AccountNumber,DATB.VisitID
	end
    

Open in new window


thanks

glen
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

thank you

Glen