Solved

MS SQL WHILE LOOP to extract data into different tables

Posted on 2008-06-17
21
3,433 Views
Last Modified: 2012-05-05
I have a table that contains the following:

BookingID int (unique)
Employee vachar(200)
Facility varchar(200)
.
.
Total decimal(10,2)

I want to go through this table and put all of the rows for Facility 1 into a temporary table (i.e. #tmpFacility1), for Facility 2 into #tmpFacility2, ....., etc, so that each facility has their data in a new table that I can then pull from later in the sp.
0
Comment
Question by:NursingCorp
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21805961
how many facilities are there?
0
 

Author Comment

by:NursingCorp
ID: 21805972
It varies from week to week anywhere from 5 to 20
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21805995
So, you're going to code for up to 20 temp tables?
0
 
LVL 10

Expert Comment

by:adriankohws
ID: 21806053
If the rows are just this quantity, why you need to do that???
Just have a SQL Statement to select distinguish "Facility" you have in this Table.

Assumming your table name is "TableName"

SELECT DISTINCT Facility from Tablename

That solves your problem
0
 

Author Comment

by:NursingCorp
ID: 21806077
Yes, in the stored procedure, I will create 20 temp facility tables like #tmpFacility1, #tmpFacility2, etc, and put in the following at the beginning:

IF OBJECT_ID('tempdb..#tmpFacility1') IS NOT NULL
      DROP TABLE #tmpFacility1



0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21806112
Then to do what you want, you'll need 20 of these:

select * INTO #Table1
from yourtablename
where facility = 1

select * INTO #Table2
from yourtablename
where facility = 2

...etc
0
 

Author Comment

by:NursingCorp
ID: 21806134
I am trying to create an IIF file to export to QuickBooks, and I need it formatted so that it puts a row like this before each facility's data


SELECT 'TRNS' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,GetDate(),101)
 AS 'D', 'Accounts Receivable' AS 'E', A.Facility AS 'F', ' ' AS 'G', convert(varchar,B.ShiftSum) AS 'H',
UPPER(SUBSTRING(A.Facility, 1, 3)) + convert(varchar, getdate(), 112) AS 'I', ' ' AS 'J', 'N' AS 'K', 'Y' AS 'L', A.Facility AS 'M',
A.Address AS 'N', A.Address2 AS 'O', A.City + ', ' + A.State + ' ' + A.Zip AS 'P', ' ' AS 'Q',
convert(varchar, DATEADD(dd, 30, getdate()), 101) AS 'R', 'Net 30' AS 'S', ' ' AS 'T', 'N' AS 'U', '004' AS 'LINE'
FROM #tmpBigAll A, #tmpShiftSum B

==================================================================
UNION
Facility 1 data here
UNION
==================================================================
SELECT 'ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',
' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',
' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '006' AS 'LINE'
===================================================================

etc for each facility

0
 
LVL 19

Expert Comment

by:folderol
ID: 21806961
I am not familiar with Quickbooks.  Since this snippet here runs multiple select statements, there would be some blank lines, etc in the output.  If you were running this in query analyzer, you can specify output to file, and in options, you can suppress headings, which helps.

My suggestion would be to insert this output into another database table, then run a single select statement, after all the unions and loops, to create the IIF file.

My snippet uses a temp table instead, and since I don't know what you are writing to Quickbooks, I dummied up the columns.

The drop table line is virtually unnecessary.  I left it there because if you use query analyzer, then you can't do repetitive testing without opening a new connection for each test.

create procedure facility_export as

set nocount on

drop table #loopctrl

declare @nextfac int

declare @lastfac int

declare @facility varchar(200)

create table #loopctrl ( 

recno int identity (1,1) not null, 

Facility varchar(200) not null

)
 

create table #IIF(

A varchar(100) null,

B varchar(100) null,

C varchar(100) null,

D varchar(100) null,

E varchar(100) null,

F varchar(100) null,

G varchar(100) null,

H varchar(100) null,

I varchar(100) null,

J varchar(100) null,

K varchar(100) null,

L varchar(100) null,

M varchar(100) null,

N varchar(100) null,

O varchar(100) null,

P varchar(100) null,

Q varchar(100) null,

R varchar(100) null,

S varchar(100) null,

T varchar(100) null,

U varchar(100) null,

LINE varchar(100) not null

)
 

insert into #loopctrl(Facility)

select distinct Facility from yourtable
 

set @nextfac = 0

select @lastfac = max(recno) from #loopctrl
 

while @nextfac < @lastfac

begin

  select top 1 @facility = Facility from #loopctrl where recno > @nextfac

  set @nextfac = @nextfac + 1
 

  --=================================================================

  insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

  SELECT 'TRNS' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,GetDate(),101)

  AS 'D', 'Accounts Receivable' AS 'E', A.Facility AS 'F', ' ' AS 'G', convert(varchar,B.ShiftSum) AS 'H',

  UPPER(SUBSTRING(A.Facility, 1, 3)) + convert(varchar, getdate(), 112) AS 'I', ' ' AS 'J', 'N' AS 'K', 'Y' AS 'L', A.Facility AS 'M', 

  A.Address AS 'N', A.Address2 AS 'O', A.City + ', ' + A.State + ' ' + A.Zip AS 'P', ' ' AS 'Q',

  convert(varchar, DATEADD(dd, 30, getdate()), 101) AS 'R', 'Net 30' AS 'S', ' ' AS 'T', 'N' AS 'U', '004' AS 'LINE'

  FROM #tmpBigAll A, #tmpShiftSum B
 

  --==================================================================

  UNION

  insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

  select * from yourtable where Facility = @facility

  UNION

  --==================================================================

  insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

  SELECT 'ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',

  ' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',

  ' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '006' AS 'LINE'

  --===================================================================

end

select * from #IIF

go

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 21807102
After reading my post, some clarification seems necessary.

I meant that the way I'm looping through the unions would produce blank lines, etc in query analyzer results pane.  

My code does not put blanks, or other non-data into the #IIF table, so running that at the end shows what I believe is the rows you need for an IIF file (but I don't know QB so I'm not sure).  I was just trying to explain why I chose to use #IIF in the first place.

Another thing, just caught on you are using 2005.  Are you using SSIS?
0
 

Author Comment

by:NursingCorp
ID: 21808751
Hi folderol,

This looks like what I need. I have inputed it into my current sp, and I am getting the following error -

Msg 156, Level 15, State 1, Procedure spNC_ExportFacilityTest, Line 294
Incorrect syntax near the keyword 'insert'.
Msg 156, Level 15, State 1, Procedure spNC_ExportFacilityTest, Line 298
Incorrect syntax near the keyword 'insert'.

I am not sure what the problem is - I have attached a file of the entire sp as it now stands. As you can tell, I am a novice. I am an RN, that has learned this stuff from my reading of books and google.com. I am not sure what SSIS is.

Thanks,
Joe
spNC-ExportFacilityTest.txt
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 19

Expert Comment

by:folderol
ID: 21809353
Sorry, I'm apparantly half asleep today.

You no longer need the unions if you keep issuing the insert line like I originally planned. I should have taken them out.

Alternative #2, you can place all the unions into a single select, call that a virtual table named facility_loop, and use one insert to put the virtual table into the temp table #IIF.  

I suggest we go with the Alternate #2.  Below is a sample for discussion.  I can modify your code, but I need some pointers.  I see Line types 001 thru 006, but you only have Line 004, 005,  and 006 in the loop.  Is that correct, the other lines only appear once at the top if the IIF file format?

When you inserted my code, you left in my dummy "Select * from yourtable" which is line #60 in the code snippet I posted 3:06 EST above.  Should that not be the 005 portion? (Lines 254-258 in your code).  I put that where I think it belongs in the loop below, and I added the line
WHERE Facility = @facility

If it is working okay as is, I am inclined to leave lines 001,002, and 003 alone.  If it is not, then we should probably insert the these lines into #IIF table first, then process the loop, then select the whole thing in one go.

Last, you have an order by I don't understand.

ORDER BY LINE, F   which is line #265

Won't this mess up the 004, 005, 006 pattern?
 --=================================================================

insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE  

from

(

SELECT 'TRNS' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,GetDate(),101)

  AS 'D', 'Accounts Receivable' AS 'E', A.Facility AS 'F', ' ' AS 'G', convert(varchar,B.ShiftSum) AS 'H',

  UPPER(SUBSTRING(A.Facility, 1, 3)) + convert(varchar, getdate(), 112) AS 'I', ' ' AS 'J', 'N' AS 'K', 'Y' AS 'L', A.Facility AS 'M', 

  A.Address AS 'N', A.Address2 AS 'O', A.City + ', ' + A.State + ' ' + A.Zip AS 'P', ' ' AS 'Q',

  convert(varchar, DATEADD(dd, 30, getdate()), 101) AS 'R', 'Net 30' AS 'S', ' ' AS 'T', 'N' AS 'U', '004' AS 'LINE'

  FROM #tmpBigAll A, #tmpShiftSum B

 

  --==================================================================

  UNION

       SELECT 'SPL' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,A.StartDate,101) AS 'D', 'Sales' AS 'E', A.Facility AS 'F', 

     A.LicReq + ':' + A.EmpName AS 'G', '-' + convert(varchar,A.ShiftTotal) AS 'H', ' ' AS 'I', ' ' AS 'J', 'N' AS 'K', 

     '-' + convert(varchar,(A.ShiftHrs + A.OTHrs)) AS 'L', convert(varchar,A.EffRate) AS 'M', A.LicReq + ':' + A.EmpName AS 'N', 

     ' ' AS 'O', 'N' AS 'P', 'N' AS 'Q', 'NOTHING' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '005' AS 'LINE'

     FROM #tmpBigAll A 

     where A.Facility = @facility 

  UNION

  --==================================================================

  SELECT 'ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',

  ' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',

  ' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '006' AS 'LINE'

  --===================================================================

) 

as facility_loop

Open in new window

0
 

Author Comment

by:NursingCorp
ID: 21816150
Hi folderol

Okay, I went through and tried to clean up my code. I like the option 2. The only reason I had the Line column was to get the rows in the correct order. I have made some updates and have attached a code snippet of the updated sp.  I am now getting an error that reads

Msg 102, Level 15, State 1, Procedure spNC_ExportFacilityTest, Line 248
Incorrect syntax near 'facility_loop'.

I also attached a txt file that is a tab delimited file that imports into Quickbooks. This is ultimately what I am trying to acheive. If you open in Excel you can see starting with row 4 of how I am trying to make each facility data look. I will then just Union the data for rows 1 - 3.

Thanks,
Joe



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 
 

ALTER   PROC [dbo].[spNC_ExportFacilityTest]

   	@startdate varchar(10) = '01/01/1900',

	@enddate varchar(10) = '01/01/2099',

	@test bit = 0

AS
 

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#loopcontrol') IS NOT NULL

	DROP TABLE #loopcontrol
 

DECLARE @dtStart datetime

DECLARE @dtEnd datetime

DECLARE @OTFactor float

declare @nextfac int

declare @lastfac int

declare @facility varchar(200)

create table #loopctrl ( 

recno int identity (1,1) not null, 

Facility varchar(200) not null

)

 

create table #IIF(

A varchar(100) null,

B varchar(100) null,

C varchar(100) null,

D varchar(100) null,

E varchar(100) null,

F varchar(100) null,

G varchar(100) null,

H varchar(100) null,

I varchar(100) null,

J varchar(100) null,

K varchar(100) null,

L varchar(100) null,

M varchar(100) null,

N varchar(100) null,

O varchar(100) null,

P varchar(100) null,

Q varchar(100) null,

R varchar(100) null,

S varchar(100) null,

T varchar(100) null,

U varchar(100) null,

LINE varchar(100) not null

)
 

SET @dtStart = CONVERT(datetime, @startdate)

SET @dtEnd = DATEADD(dd, 1, DATEADD(dd, 0, CONVERT(datetime, @enddate)))

SET @OTFactor = 1.5
 

IF OBJECT_ID('tempdb..#tmpPayRates') IS NOT NULL

	DROP TABLE #tmpPayRates
 

IF OBJECT_ID('tempdb..#tmpFacRates') IS NOT NULL

	DROP TABLE #tmpFacRates
 

IF OBJECT_ID('tempdb..#tmpEmp') IS NOT NULL

	DROP TABLE #tmpEmp
 

IF OBJECT_ID('tempdb..#tmpBook') IS NOT NULL

	DROP TABLE #tmpBook
 

IF OBJECT_ID('tempdb..#tmpBigAll') IS NOT NULL

	DROP TABLE #tmpBigAll
 

IF OBJECT_ID('tempdb..#tmpBigAll2') IS NOT NULL

	DROP TABLE #tmpBigAll2
 

IF OBJECT_ID('tempdb..#tmpShiftSum') IS NOT NULL

	DROP TABLE #tmpShiftSum
 

SELECT XModID, 

	--SUBSTRING([key], 3, CHARINDEX('PayRate', [key])-3) as Service,

	SUBSTRING([key], CHARINDEX('PayRate', [key])+7, 10) as RateType,

	CONVERT(money, [value]) as Rate,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like '%FullName%' and mask = 1 and XModID = P.XModID) as EmpName

INTO #tmpPayRates

FROM KB_XMod_Index_VC50 P

WHERE [key] LIKE 'E_PayRate%'
 

If @test = 1

	SELECT * FROM #tmpPayRates
 

SELECT DISTINCT XModID, 

	SUBSTRING([key], 3, CHARINDEX('Bill', [key])-3) as Service,

	SUBSTRING([key], CHARINDEX('Bill', [key])+4, 10) as RateType,

	CONVERT(money, [value]) as Rate,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like '%customername%' and mask = 1 and XModID = P.XModID) as FacName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingAddress' and mask = 1 and XModID = P.XModID) as Address,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingAddress2' and mask = 1 and XModID = P.XModID) as Address2,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingCity' and mask = 1 and XModID = P.XModID) as City,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingState' and mask = 1 and XModID = P.XModID) as State,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingZip' and mask = 1 and XModID = P.XModID) as Zip
 

INTO #tmpFacRates

FROM KB_XMod_Index_VC50 P

WHERE [key] LIKE '%[A-Z]Bill%'
 

If @test = 1

	SELECT * FROM #tmpFacRates
 

SELECT M.XModID, F.[value] as FirstName, M.[value] as LastName, FL.[value] as FullName

INTO #tmpEmp

FROM KB_XMod_Index_VC50 M

JOIN (SELECT XModID, [value] FROM KB_XMod_Index_VC50 WHERE [key] LIKE '%Firstname%') F

	ON M.XModID = F.XModID

JOIN (SELECT XModID, [value] FROM KB_XMod_Index_VC50 WHERE [key] LIKE '%Fullname%') FL

	ON M.XModID = FL.XModID

WHERE M.[key] LIKE '%Lastname%'
 

If @test = 1

	SELECT * FROM #tmpPayRates
 
 

SELECT XModID, [value] as EmpName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_ShiftTime' and mask = 2 and XModID = B.XModID) as ShiftTime,

	CONVERT(varchar(20), '') as ShiftType,

	ISNULL(CONVERT(int, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_ShiftHours' and mask = 1 and XModID = B.XModID)), 0) as ShiftHrs,

    ISNULL(CONVERT(int, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_OTHours' and mask = 1 and XModID = B.XModID)), 0) as OTHrs,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_StartDate' and mask = 1 and XModID = B.XModID) as StartDate,

	CONVERT(datetime, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_StartDate' and mask = 1 and XModID = B.XModID)) as StartDate_date,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_LicenseRequested' and mask = 1 and XModID = B.XModID) as LicReq,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_SpecialtyRequested' and mask = 1 and XModID = B.XModID) as SpecReq,

	(SELECT [value] FROM KB_XMod_Index_VC100 WHERE [key] = 'B_FacilityName' and mask = 1 and XModID = B.XModID) as FacilityName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Status' and mask = 2 and XModID = B.XModID) as BookingStatus,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Incentive' and mask = 1 and XModID = B.XModID) as Incentive,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_IncentivePayRate' and mask = 1 and XModID = B.XModID) as IncentivePay,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_IncentiveBillRate' and mask = 1 and XModID = B.XModID) as IncentiveBill
 

INTO #tmpBook

FROM KB_XMod_Index_VC50 B

WHERE [key] = 'B_EmployeeName'

AND mask=1
 

UPDATE #tmpBook

SET ShiftType = 

	CASE

		WHEN DATEPART(dw, StartDate_date) IN (1, 7) THEN 'Wknd'

		ELSE dbo.fn_GetShiftType(ShiftTime)

	END

WHERE ShiftType = ''
 

IF @test = 1

	SELECT * FROM #tmpBook
 

SELECT 1 as recID, B.XModID as BookingID, B.EmpName, B.StartDate as StartDate, b.ShiftType, b.LicReq, b.SpecReq, B.ShiftHrs, 

B.OTHrs,B.FacilityName,B.Incentive,B.IncentivePay,B.IncentiveBill,

	CONVERT(decimal(6,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END) as EffRate, 

CONVERT(decimal(10,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END * 1.5) AS OTRate,

	CONVERT(decimal(10,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END * (B.ShiftHrs + (B.OTHrs * @OTFactor))) AS ShiftTotal, F.Address, F.Address2, F.City, F.State, F.Zip

   

INTO #tmpAll

FROM #tmpBook B

JOIN #tmpEmp E

	ON B.EmpName = E.FullName

LEFT JOIN #tmpPayRates R

	ON E.XmodID =  R.XmodID AND B.ShiftType = R.RateType

LEFT JOIN #tmpFacRates F

	ON B.FacilityName =  F.FacName AND B.ShiftType = F.RateType AND B.LicReq = F.Service

WHERE B.StartDate_date BETWEEN @dtStart and @dtEnd AND B.BookingStatus = 3

ORDER BY StartDate_Date
 
 

SELECT A.recID, A.BookingID, A.EmpName as Employee, A.FacilityName, A.StartDate, A.ShiftType, A.LicReq, A.SpecReq, A.ShiftHrs, A.OTHrs, 

A.EffRate, A.OTRate, A.Incentive, A.IncentivePay, A.ShiftTotal, R.XModID, R.RateType, R.Rate, R.EmpName, A.Address, A.Address2, A.City, A.State, A.Zip

INTO #tmpBigAll

FROM #tmpAll A, #tmpPayRates R

WHERE A.EmpName = R.EmpName AND A.ShiftType = R.RateType
 

UPDATE #tmpBigAll

SET #tmpBigAll.Rate = #tmpBigAll.IncentivePay

WHERE #tmpBigAll.Incentive = 'Yes' AND #tmpBigAll.IncentivePay <> '0.00'
 

SELECT DISTINCT SUM(ShiftTotal) AS ShiftSum 

INTO #tmpShiftSum

FROM #tmpBigAll
 

-- NEW SOLUTION ==================================================
 

insert into #loopctrl(Facility)

select distinct Facility from #tmpBigAll

 

set @nextfac = 0

select @lastfac = max(recno) from #loopctrl

 

while @nextfac < @lastfac

begin

  select top 1 @facility = Facility from #loopctrl where recno > @nextfac

  set @nextfac = @nextfac + 1

--=================================================================

insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE  

from

(

SELECT 'TRNS' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,GetDate(),101)

  AS 'D', 'Accounts Receivable' AS 'E', A.FacilityName AS 'F', ' ' AS 'G', convert(varchar,B.ShiftSum) AS 'H',

  UPPER(SUBSTRING(A.FacilityName, 1, 3)) + convert(varchar, getdate(), 112) AS 'I', ' ' AS 'J', 'N' AS 'K', 'Y' AS 'L', A.FacilityName AS 'M', 

  A.Address AS 'N', A.Address2 AS 'O', A.City + ', ' + A.State + ' ' + A.Zip AS 'P', ' ' AS 'Q',

  convert(varchar, DATEADD(dd, 30, getdate()), 101) AS 'R', 'Net 30' AS 'S', ' ' AS 'T', 'N' AS 'U', '004' AS 'LINE'

  FROM #tmpBigAll A, #tmpShiftSum B

 

  --==================================================================

  UNION

       SELECT 'SPL' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,A.StartDate,101) AS 'D', 'Sales' AS 'E', A.FacilityName AS 'F', 

     A.LicReq + ':' + A.EmpName AS 'G', '-' + convert(varchar,A.ShiftTotal) AS 'H', ' ' AS 'I', ' ' AS 'J', 'N' AS 'K', 

     '-' + convert(varchar,(A.ShiftHrs + A.OTHrs)) AS 'L', convert(varchar,A.EffRate) AS 'M', A.LicReq + ':' + A.EmpName AS 'N', 

     ' ' AS 'O', 'N' AS 'P', 'N' AS 'Q', 'NOTHING' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '005' AS 'LINE'

     FROM #tmpBigAll A 

     where A.FacilityName = @facility

  UNION

  --==================================================================

  SELECT 'ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',

  ' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',

  ' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '006' AS 'LINE'

) 

as facility_loop

Open in new window

testInvoice.txt
0
 
LVL 19

Accepted Solution

by:
folderol earned 500 total points
ID: 21817127
You clipped the bottom a little too close!  You are missing the last two lines:

as facility_loop  -- your snippet ends here
End
select * from #IIF

Its the missing END that causes the error.

Otherwise, the edits look very good, although I don't pretend to understand the whole thing.  If
you get this all to work, and you want to take it a step further, before the loop starts #IIF is empty.  You can add one row at a time like this below.  Paste it into your code before the line

insert into #loopctrl(Facility)

(but don't do it before you get it working so far!)  Good luck, Tom.
insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

SELECT '!TRNS' AS 'A', 'TRNSID' AS 'B', 'TRNSTYPE' AS 'C', 'DATE' AS 'D', 'ACCNT' AS 'E', 'NAME' AS 'F', 'CLASS' AS 'G', 'AMOUNT' AS 'H',

'DOCNUM' AS 'I', 'MEMO' AS 'J', 'CLEAR' AS 'K', 'TOPRINT' AS 'L', 'ADDR1' AS 'M', 'ADDR2' AS 'N', 'ADDR3' AS 'O', 'ADDR4' AS 'P', 'ADDR5' AS 'Q',

'DUEDATE' AS 'R', 'TERMS' AS 'S', 'PAID' AS 'T', 'SHIPDATE' AS 'U', '001' AS 'LINE'
 

insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

SELECT '!SPL' AS 'A', 'SPLID' AS 'B', 'TRNSTYPE' AS 'C', 'DATE' AS 'D', 'ACCNT' AS 'E', 'NAME' AS 'F', 'CLASS' AS 'G', 'AMOUNT' AS 'H',

'DOCNUM' AS 'I', 'MEMO' AS 'J', 'CLEAR' AS 'K', 'QNTY' AS 'L', 'PRICE' AS 'M', 'INVITEM' AS 'N', 'PAYMETH' AS 'O', 'TAXABLE' AS 'P', 'REIMBEXP' AS 'Q',

'EXTRA' AS 'R', 'TERMS' AS 'S', 'PAID' AS 'T', 'SHIPDATE' AS 'U', '002' AS 'LINE'
 

insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

SELECT '!ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',

' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',

' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '003' AS 'LINE'

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 21817217
Another thing, don't know how I spotted it, but #tmpAll the column changed from Facility to FacilityName so you need to change line #216 to

select distinct FacilityName from #tmpBigAll
0
 
LVL 19

Expert Comment

by:folderol
ID: 21817254
I'm guessing line #236 (currently blank) should be

where A.FacilityName = @facility

0
 

Author Comment

by:NursingCorp
ID: 21823405
Thanks Tom,

I was able to get it to alter the procedure without errors. When I executed the procedure it gave an error that it cannont receive NULL values, so I added WHERE FacilityName <> NULL on Line 217. I do have some NULL values in the table from the building process, but I ran the #tmpBigAll table and there are several facilities in there as well as a few NULLS.

The result that is created is a blank table with the headers of A, B, C, D, ....etc.
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 
 
 

ALTER   PROC [dbo].[spNC_ExportFacilityTest]

   	@startdate varchar(10) = '01/01/1900',

	@enddate varchar(10) = '01/01/2099',

	@test bit = 0

AS
 

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#loopcontrol') IS NOT NULL

	DROP TABLE #loopcontrol
 

DECLARE @dtStart datetime

DECLARE @dtEnd datetime

DECLARE @OTFactor float

declare @nextfac int

declare @lastfac int

declare @facility varchar(200)

create table #loopctrl ( 

recno int identity (1,1) not null, 

Facility varchar(200) not null

)

 

create table #IIF(

A varchar(100) null,

B varchar(100) null,

C varchar(100) null,

D varchar(100) null,

E varchar(100) null,

F varchar(100) null,

G varchar(100) null,

H varchar(100) null,

I varchar(100) null,

J varchar(100) null,

K varchar(100) null,

L varchar(100) null,

M varchar(100) null,

N varchar(100) null,

O varchar(100) null,

P varchar(100) null,

Q varchar(100) null,

R varchar(100) null,

S varchar(100) null,

T varchar(100) null,

U varchar(100) null,

LINE varchar(100) not null

)
 

SET @dtStart = CONVERT(datetime, @startdate)

SET @dtEnd = DATEADD(dd, 1, DATEADD(dd, 0, CONVERT(datetime, @enddate)))

SET @OTFactor = 1.5
 

IF OBJECT_ID('tempdb..#tmpPayRates') IS NOT NULL

	DROP TABLE #tmpPayRates
 

IF OBJECT_ID('tempdb..#tmpFacRates') IS NOT NULL

	DROP TABLE #tmpFacRates
 

IF OBJECT_ID('tempdb..#tmpEmp') IS NOT NULL

	DROP TABLE #tmpEmp
 

IF OBJECT_ID('tempdb..#tmpBook') IS NOT NULL

	DROP TABLE #tmpBook
 

IF OBJECT_ID('tempdb..#tmpBigAll') IS NOT NULL

	DROP TABLE #tmpBigAll
 

IF OBJECT_ID('tempdb..#tmpBigAll2') IS NOT NULL

	DROP TABLE #tmpBigAll2
 

IF OBJECT_ID('tempdb..#tmpShiftSum') IS NOT NULL

	DROP TABLE #tmpShiftSum
 

SELECT XModID, 

	--SUBSTRING([key], 3, CHARINDEX('PayRate', [key])-3) as Service,

	SUBSTRING([key], CHARINDEX('PayRate', [key])+7, 10) as RateType,

	CONVERT(money, [value]) as Rate,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like '%FullName%' and mask = 1 and XModID = P.XModID) as EmpName

INTO #tmpPayRates

FROM KB_XMod_Index_VC50 P

WHERE [key] LIKE 'E_PayRate%'
 

If @test = 1

	SELECT * FROM #tmpPayRates
 

SELECT DISTINCT XModID, 

	SUBSTRING([key], 3, CHARINDEX('Bill', [key])-3) as Service,

	SUBSTRING([key], CHARINDEX('Bill', [key])+4, 10) as RateType,

	CONVERT(money, [value]) as Rate,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like '%customername%' and mask = 1 and XModID = P.XModID) as FacName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingAddress' and mask = 1 and XModID = P.XModID) as Address,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingAddress2' and mask = 1 and XModID = P.XModID) as Address2,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingCity' and mask = 1 and XModID = P.XModID) as City,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingState' and mask = 1 and XModID = P.XModID) as State,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] Like 'C_BillingZip' and mask = 1 and XModID = P.XModID) as Zip
 

INTO #tmpFacRates

FROM KB_XMod_Index_VC50 P

WHERE [key] LIKE '%[A-Z]Bill%'
 

If @test = 1

	SELECT * FROM #tmpFacRates
 

SELECT M.XModID, F.[value] as FirstName, M.[value] as LastName, FL.[value] as FullName

INTO #tmpEmp

FROM KB_XMod_Index_VC50 M

JOIN (SELECT XModID, [value] FROM KB_XMod_Index_VC50 WHERE [key] LIKE '%Firstname%') F

	ON M.XModID = F.XModID

JOIN (SELECT XModID, [value] FROM KB_XMod_Index_VC50 WHERE [key] LIKE '%Fullname%') FL

	ON M.XModID = FL.XModID

WHERE M.[key] LIKE '%Lastname%'
 

If @test = 1

	SELECT * FROM #tmpPayRates
 
 

SELECT XModID, [value] as EmpName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_ShiftTime' and mask = 2 and XModID = B.XModID) as ShiftTime,

	CONVERT(varchar(20), '') as ShiftType,

	ISNULL(CONVERT(int, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_ShiftHours' and mask = 1 and XModID = B.XModID)), 0) as ShiftHrs,

    ISNULL(CONVERT(int, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_OTHours' and mask = 1 and XModID = B.XModID)), 0) as OTHrs,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_StartDate' and mask = 1 and XModID = B.XModID) as StartDate,

	CONVERT(datetime, (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_StartDate' and mask = 1 and XModID = B.XModID)) as StartDate_date,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_LicenseRequested' and mask = 1 and XModID = B.XModID) as LicReq,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_SpecialtyRequested' and mask = 1 and XModID = B.XModID) as SpecReq,

	(SELECT [value] FROM KB_XMod_Index_VC100 WHERE [key] = 'B_FacilityName' and mask = 1 and XModID = B.XModID) as FacilityName,

	(SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Status' and mask = 2 and XModID = B.XModID) as BookingStatus,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_Incentive' and mask = 1 and XModID = B.XModID) as Incentive,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_IncentivePayRate' and mask = 1 and XModID = B.XModID) as IncentivePay,

    (SELECT [value] FROM KB_XMod_Index_VC50 WHERE [key] = 'B_IncentiveBillRate' and mask = 1 and XModID = B.XModID) as IncentiveBill
 

INTO #tmpBook

FROM KB_XMod_Index_VC50 B

WHERE [key] = 'B_EmployeeName'

AND mask=1
 

UPDATE #tmpBook

SET ShiftType = 

	CASE

		WHEN DATEPART(dw, StartDate_date) IN (1, 7) THEN 'Wknd'

		ELSE dbo.fn_GetShiftType(ShiftTime)

	END

WHERE ShiftType = ''
 

IF @test = 1

	SELECT * FROM #tmpBook
 

SELECT 1 as recID, B.XModID as BookingID, B.EmpName, B.StartDate as StartDate, b.ShiftType, b.LicReq, b.SpecReq, B.ShiftHrs, 

B.OTHrs,B.FacilityName,B.Incentive,B.IncentivePay,B.IncentiveBill,

	CONVERT(decimal(6,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END) as EffRate, 

CONVERT(decimal(10,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END * 1.5) AS OTRate,

	CONVERT(decimal(10,2), CASE

        WHEN (B.IncentiveBill <> '0.00' AND B.Incentive = 'Yes') THEN (SELECT B.IncentiveBill FROM #tmpBook WHERE XModID = B.XModID)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Telemetry', 'Labor/Delivery', 'OR', 'ICU', 'PICU', 'PACU', 'ER', 'NICU', 'Cath Lab', 'CVICU', 'Dialysis')) 

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RNS' AND RateType = B.ShiftType)

		WHEN (B.LicReq = 'RN' AND B.SpecReq IN ('Med/Surg', 'Peds', 'Ortho', 'Neuro', 'Nursery', 'Geriatrics', 'Oncology', 'Private Duty', 'Psychiatric', 'LTC/SNF', 'Endoscopy', 'Radiology', 'Home Health', 'Other') )

			THEN (SELECT DISTINCT Rate FROM #tmpFacRates WHERE FacName = B.FacilityName and Service = 'RN' AND RateType = B.ShiftType)

		WHEN (B.LicReq IN ('lpn', 'CNA', 'aide', 'MA', 'crt', 'RRT', 'ARRT', 'PT', 'OT', 'SPT'))

			THEN F.Rate

		ELSE

			R.Rate

	END * (B.ShiftHrs + (B.OTHrs * @OTFactor))) AS ShiftTotal, F.Address, F.Address2, F.City, F.State, F.Zip

   

INTO #tmpAll

FROM #tmpBook B

JOIN #tmpEmp E

	ON B.EmpName = E.FullName

LEFT JOIN #tmpPayRates R

	ON E.XmodID =  R.XmodID AND B.ShiftType = R.RateType

LEFT JOIN #tmpFacRates F

	ON B.FacilityName =  F.FacName AND B.ShiftType = F.RateType AND B.LicReq = F.Service

WHERE B.StartDate_date BETWEEN @dtStart and @dtEnd AND B.BookingStatus = 3

ORDER BY StartDate_Date
 
 

SELECT A.recID, A.BookingID, A.EmpName as Employee, A.FacilityName, A.StartDate, A.ShiftType, A.LicReq, A.SpecReq, A.ShiftHrs, A.OTHrs, 

A.EffRate, A.OTRate, A.Incentive, A.IncentivePay, A.ShiftTotal, R.XModID, R.RateType, R.Rate, R.EmpName, A.Address, A.Address2, A.City, A.State, A.Zip

INTO #tmpBigAll

FROM #tmpAll A, #tmpPayRates R

WHERE A.EmpName = R.EmpName AND A.ShiftType = R.RateType
 

UPDATE #tmpBigAll

SET #tmpBigAll.Rate = #tmpBigAll.IncentivePay

WHERE #tmpBigAll.Incentive = 'Yes' AND #tmpBigAll.IncentivePay <> '0.00'
 

SELECT DISTINCT SUM(ShiftTotal) AS ShiftSum 

INTO #tmpShiftSum

FROM #tmpBigAll
 

SELECT * FROM #tmpBigAll
 

-- NEW SOLUTION ==================================================
 

insert into #loopctrl(Facility)

select distinct FacilityName from #tmpBigAll

WHERE FacilityName <> NULL

 

set @nextfac = 0

select @lastfac = max(recno) from #loopctrl

 

while @nextfac < @lastfac

begin

  select top 1 @facility = FacilityName from #loopctrl where recno > @nextfac

  set @nextfac = @nextfac + 1

--=================================================================

insert into #IIF(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE)

select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,LINE  

from

(

SELECT 'TRNS' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,GetDate(),101)

  AS 'D', 'Accounts Receivable' AS 'E', A.FacilityName AS 'F', ' ' AS 'G', convert(varchar,B.ShiftSum) AS 'H',

  UPPER(SUBSTRING(A.FacilityName, 1, 3)) + convert(varchar, getdate(), 112) AS 'I', ' ' AS 'J', 'N' AS 'K', 'Y' AS 'L', A.FacilityName AS 'M', 

  A.Address AS 'N', A.Address2 AS 'O', A.City + ', ' + A.State + ' ' + A.Zip AS 'P', ' ' AS 'Q',

  convert(varchar, DATEADD(dd, 30, getdate()), 101) AS 'R', 'Net 30' AS 'S', ' ' AS 'T', 'N' AS 'U', '004' AS 'LINE'

  FROM #tmpBigAll A, #tmpShiftSum B

  where A.FacilityName = @facility
 
 

  --==================================================================

  UNION

       SELECT 'SPL' AS 'A', ' ' AS 'B', 'INVOICE' AS 'C', convert(varchar,A.StartDate,101) AS 'D', 'Sales' AS 'E', A.FacilityName AS 'F', 

     A.LicReq + ':' + A.EmpName AS 'G', '-' + convert(varchar,A.ShiftTotal) AS 'H', ' ' AS 'I', ' ' AS 'J', 'N' AS 'K', 

     '-' + convert(varchar,(A.ShiftHrs + A.OTHrs)) AS 'L', convert(varchar,A.EffRate) AS 'M', A.LicReq + ':' + A.EmpName AS 'N', 

     ' ' AS 'O', 'N' AS 'P', 'N' AS 'Q', 'NOTHING' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '005' AS 'LINE'

     FROM #tmpBigAll A 

     where A.FacilityName = @facility
 

     UNION

  --==================================================================

  SELECT 'ENDTRNS' AS 'A', ' ' AS 'B', ' ' AS 'C', ' ' AS 'D', ' ' AS 'E', ' ' AS 'F', ' ' AS 'G', ' ' AS 'H',

  ' ' AS 'I', ' ' AS 'J', ' ' AS 'K', ' ' AS 'L', ' ' AS 'M', ' ' AS 'N', ' ' AS 'O', ' ' AS 'P', ' ' AS 'Q',

  ' ' AS 'R', ' ' AS 'S', ' ' AS 'T', ' ' AS 'U', '006' AS 'LINE'

) 

as facility_loop

End

select * from #IIF

Open in new window

0
 

Author Comment

by:NursingCorp
ID: 21823424
The WHERE clause is actually on Line 220 of the new inserted code snippet
0
 
LVL 19

Expert Comment

by:folderol
ID: 21824945
Use

Where Facilty is not null

Be careful with null, it is classified as undefined, which is not the same as an empty string.
In the case of equality evaluations, undefined always returns null, there is no true or false determination because the statement is not executed.  If you have two columns and you know or suspect they are both null then
column_A = column_B
will not work.  You need to rewrite it so it takes the form
TRUE = TRUE
for case statements, you could use
Case when column_A is null and column_B is null then ... else ... end

"is null" mimics a function and always returns a true or false, never undefined.
For assignments, oddly,
column_A = null
will clear any contents for the field and set it to undefined.  This is useful at times when using Update or Insert statements.

0
 
LVL 19

Expert Comment

by:folderol
ID: 21825048
where FacilityName is not null

(don't mean to confuse you :) I noticed you edited that to distinguish the loopctrl from your bigall)
0
 

Author Comment

by:NursingCorp
ID: 21825173
Tom,

Do you do any contract work? If so, can you email me at jcaracci@nursingcorp.com. I think it would be more efficient to outsource part of this project.

Thanks,
Joe
0
 
LVL 19

Expert Comment

by:folderol
ID: 21825940
Hey Joe,
No, I don't.  I saw your corp website.  Nice.  You should click the Request attention button in your question post atop this thread, and ask to have your email removed, keeps the spam down :)

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now