Solved

MS SQL WHILE LOOP to extract data into different tables

Posted on 2008-06-17
21
3,440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
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
 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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