arajoe
asked on
Returning a Dynamic Table
I am trying to create a return table that has dynamic colums. I have done some researching and found this code
/*
CREATE TABLE #fundinfo
(
onum int,
fund varchar(30),
amt int
)
INSERT INTO #fundinfo VALUES (1, 'ABCDfund', 45000)
INSERT INTO #fundinfo VALUES (1, 'XYXfund', 345600)
INSERT INTO #fundinfo VALUES (1, 'QWERFund', 890000)
INSERT INTO #fundinfo VALUES (2, 'ABCDfund', 89000)
INSERT INTO #fundinfo VALUES (2, 'XYXfund', 34440)
INSERT INTO #fundinfo VALUES (2, 'QWERFund', 100000)
SELECT * FROM #fundinfo
*/
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctfund + ' int'
FROM (SELECT DISTINCT(fund) as distinctfund FROM #fundinfo) a
SET @sqlstmt = 'CREATE TABLE tmp_output (onum int' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
DECLARE @order int
DECLARE @fund varchar(30)
DECLARE @amt int
DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)
DECLARE c_Order CURSOR FOR SELECT DISTINCT(onum) distinctonum FROM #fundinfo
OPEN c_Order
FETCH NEXT FROM c_Order INTO @order
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
@insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
EXEC sp_executesql @sqlstmt
FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
SELECT * FROM tmp_output
DROP TABLE tmp_output
It seems to do what I am looking for but i have not been able to fine tune it to do what i need it to. Basically, i have two tables in MSSQL.
First is TBL_EVNT_SUP_SET (
[EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[STND_NUM] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[SUP_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[SUP_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[MGR_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[MGR_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[Notes] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[Misc] [numeric](4, 0) NULL ,
[Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[Last_Changed] [datetime] NULL )
Second is TBL_SUP_PERF (
[EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[STND_NUM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[POINT_ID] [numeric](4, 0) NOT NULL ,
[POINT_DESC] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[POINT_VALUE] [numeric](4, 0) NOT NULL ,
[Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL ,
[Last_Changed] [datetime] NULL
What I want is a table to be created using the POINT_DESC values as columns and have the value for each stand for every event. There can be an indefinite number of these and I dont care much it they hold null values when they are not being used. No mathmatical operations have to be done on this information as of yet, this will just allow me to export to create a canned report with the info into an excel spreadsheet. Any help with this would be greatly appreciated.
I've got the code far enough that it creates the table, I just don't know how to populate it. This is what I have thus far
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + REPLACE(distinctpoint, ' ', '_') + ' int'
FROM (SELECT DISTINCT(POINT_DESC) as distinctpoint FROM ARCSTRACKER.tracker.TBL_SU P_PERF) a
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
/*
DECLARE @EVNT_Id CHAR(10)
DECLARE @STND_NUM CHAR(5)
DECLARE @fund varchar(35)
DECLARE @amt int
DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)
DECLARE c_Order CURSOR FOR SELECT DISTINCT(EVNT_ID) distinctevent, DISTINCT(STND_NUM) distinctstndnum FROM ARCSTRACKER.tracker.TBL_SU P_PERF
OPEN c_Order
FETCH NEXT FROM c_Order INTO @EVNT_Id, @STND_NUM
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
@insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
EXEC sp_executesql @sqlstmt
FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
*/
SELECT * FROM tmp_output
DROP TABLE tmp_output
Thanks in advance, Joe.
/*
CREATE TABLE #fundinfo
(
onum int,
fund varchar(30),
amt int
)
INSERT INTO #fundinfo VALUES (1, 'ABCDfund', 45000)
INSERT INTO #fundinfo VALUES (1, 'XYXfund', 345600)
INSERT INTO #fundinfo VALUES (1, 'QWERFund', 890000)
INSERT INTO #fundinfo VALUES (2, 'ABCDfund', 89000)
INSERT INTO #fundinfo VALUES (2, 'XYXfund', 34440)
INSERT INTO #fundinfo VALUES (2, 'QWERFund', 100000)
SELECT * FROM #fundinfo
*/
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctfund + ' int'
FROM (SELECT DISTINCT(fund) as distinctfund FROM #fundinfo) a
SET @sqlstmt = 'CREATE TABLE tmp_output (onum int' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
DECLARE @order int
DECLARE @fund varchar(30)
DECLARE @amt int
DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)
DECLARE c_Order CURSOR FOR SELECT DISTINCT(onum) distinctonum FROM #fundinfo
OPEN c_Order
FETCH NEXT FROM c_Order INTO @order
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
@insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
EXEC sp_executesql @sqlstmt
FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
SELECT * FROM tmp_output
DROP TABLE tmp_output
It seems to do what I am looking for but i have not been able to fine tune it to do what i need it to. Basically, i have two tables in MSSQL.
First is TBL_EVNT_SUP_SET (
[EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
[STND_NUM] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_
[SUP_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[SUP_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[MGR_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[MGR_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_
[Notes] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_
[Misc] [numeric](4, 0) NULL ,
[Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_
[Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_
[Last_Changed] [datetime] NULL )
Second is TBL_SUP_PERF (
[EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
[STND_NUM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_
[POINT_ID] [numeric](4, 0) NOT NULL ,
[POINT_DESC] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_
[POINT_VALUE] [numeric](4, 0) NOT NULL ,
[Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_
[Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_
[Last_Changed] [datetime] NULL
What I want is a table to be created using the POINT_DESC values as columns and have the value for each stand for every event. There can be an indefinite number of these and I dont care much it they hold null values when they are not being used. No mathmatical operations have to be done on this information as of yet, this will just allow me to export to create a canned report with the info into an excel spreadsheet. Any help with this would be greatly appreciated.
I've got the code far enough that it creates the table, I just don't know how to populate it. This is what I have thus far
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + REPLACE(distinctpoint, ' ', '_') + ' int'
FROM (SELECT DISTINCT(POINT_DESC) as distinctpoint FROM ARCSTRACKER.tracker.TBL_SU
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
/*
DECLARE @EVNT_Id CHAR(10)
DECLARE @STND_NUM CHAR(5)
DECLARE @fund varchar(35)
DECLARE @amt int
DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)
DECLARE c_Order CURSOR FOR SELECT DISTINCT(EVNT_ID) distinctevent, DISTINCT(STND_NUM) distinctstndnum FROM ARCSTRACKER.tracker.TBL_SU
OPEN c_Order
FETCH NEXT FROM c_Order INTO @EVNT_Id, @STND_NUM
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
@insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
EXEC sp_executesql @sqlstmt
FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
*/
SELECT * FROM tmp_output
DROP TABLE tmp_output
Thanks in advance, Joe.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cleaned the code a little
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis tinctPoint s') IS NOT NULL
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des c, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SU P_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
(SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' +
'FROM tbl_evnt_sup_set ess ' +
'INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON sp.evnt_id = ess.evnt_id ' +
' /* AND sp.stnd_num = ess.stnd_num */ '
SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des
FROM ARCSTRACKER.tracker.TBL_SU
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
(SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' +
'FROM tbl_evnt_sup_set ess ' +
'INNER JOIN ARCSTRACKER.tracker.TBL_SU
' /* AND sp.stnd_num = ess.stnd_num */ '
SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
Did you print out the SQL statement? Do the values in it match values actually in the table?
ASKER
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
MAX(CASE WHEN sp.point_desc = 'ALL_PAPERWORK_SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE_EVENT_TRANSFERS_AT TACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'DURING_EVENT_TRANSFERS_AT TACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_COMPL ETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_MATCH ES_STAND' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_MATHCES_ST AND_SHEET' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'STAND_MANAGER_CHECKLIST_C OMPLETE' THEN point_value ELSE NULL END) FROM tbl_evnt_sup_set ess INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON sp.evnt_id = ess.evnt_id /* AND sp.stnd_num = ess.stnd_num */ GROUP BY ess.evnt_id, ess.stnd_num
Warning: Null value is eliminated by an aggregate or other SET operation.
is what i get from the print.
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
MAX(CASE WHEN sp.point_desc = 'ALL_PAPERWORK_SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE_EVENT_TRANSFERS_AT
MAX(CASE WHEN sp.point_desc = 'DURING_EVENT_TRANSFERS_AT
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_COMPL
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_MATCH
MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_MATHCES_ST
MAX(CASE WHEN sp.point_desc = 'STAND_MANAGER_CHECKLIST_C
Warning: Null value is eliminated by an aggregate or other SET operation.
is what i get from the print.
ASKER
Working Code:
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis tinctPoint s') IS NOT NULL
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des c, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SU P_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' +
'FROM tbl_evnt_sup_set ess ' +
'INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON sp.evnt_id = ess.evnt_id ' +
' /* AND sp.stnd_num = ess.stnd_num */ '
SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'
SET @sqlStmt = @sqlStmt + CHAR(13)
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
Got it I think. I had to use the replace command again to put the spaces back into the description and take out the _ I made it put in to make them valid column names. I'll post back after I check the data and let you know how it goes. Thanks, Joe.
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des
FROM ARCSTRACKER.tracker.TBL_SU
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' +
'FROM tbl_evnt_sup_set ess ' +
'INNER JOIN ARCSTRACKER.tracker.TBL_SU
' /* AND sp.stnd_num = ess.stnd_num */ '
SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'
SET @sqlStmt = @sqlStmt + CHAR(13)
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
Got it I think. I had to use the replace command again to put the spaces back into the description and take out the _ I made it put in to make them valid column names. I'll post back after I check the data and let you know how it goes. Thanks, Joe.
Sorry, was busy earlier. Also sorry about, D'OH, not thinking about having to re-replace the _/spaces. Thks for the grade though!
ASKER
No prob. If it wasn't for your help, I would have been nowhere.
ASKER
CREATE PROCEDURE tracker.dbp_TheCarson (@T0 CHAR(10), @FROM CHAR(10))
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
/*
--
--TAKE OUT
--
DECLARE @T0 CHAR(10)
DECLARE @FROM CHAR(10)
SET @T0 = 0000000000
SET @FROM = 9999999999
--
--
--
*/
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des
FROM ARCSTRACKER.tracker.TBL_SU
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(4000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150), OVER_SHORT_POINTS NUMERIC(11,8)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes), ' + CHAR(13) + ' (SELECT OS_RATING FROM fn_Calc_Stnd_OS_Perf sp.evnt_id, sp.stnd_num )'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' + CHAR(13)
+ 'FROM tbl_evnt_sup_set ess ' + CHAR(13)
+ 'INNER JOIN ARCSTRACKER.tracker.TBL_SU
+ CHAR(13) + ' sp.evnt_id = ess.evnt_id'
+ CHAR(13) + ' AND sp.stnd_num = ess.stnd_num '
+ CHAR(13) + ' AND ess.evnt_id <= ''' + @T0 + ''''
+ CHAR(13) + ' AND ess.evnt_id >= ''' + @FROM + ''''
SET @sqlStmt = @sqlStmt + ' '
+ CHAR(13) + 'GROUP BY ess.evnt_id, ess.stnd_num'
+ CHAR(13) + 'ORDER BY ess.evnt_id, ess.stnd_num'
--SET @sqlStmt = @sqlStmt + CHAR(13)
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
GO
One more thing. I am trying to add another column now that gets its value from a function. When I run the code, I get an error saying incorrect syntax near '.'. I can only assume it is from the aruguments the function takes.
Perhaps; I can't really tell without seeing the code. Also, remember to include the user name, normally dbo, on all uses of user-defined functions. For example:
SELECT dbo.userFunc1(param1, param2)
instead of:
SELECT userFunc1(param1, param2) --<<-- SQL does not accept
SELECT dbo.userFunc1(param1, param2)
instead of:
SELECT userFunc1(param1, param2) --<<-- SQL does not accept
ASKER
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf sp.evnt_id, sp.stnd_num ),
MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON
sp.evnt_id = ess.evnt_id
AND sp.stnd_num = ess.stnd_num
AND ess.evnt_id <= '9999999999'
AND ess.evnt_id >= '0 '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num
Thats the print out. This is the code to the Stored Prcedure that we finished yesterday:
CREATE PROCEDURE tracker.dbp_TheCarson (@T0 CHAR(10), @FROM CHAR(10))
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis tinctPoint s') IS NOT NULL
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
/*
--
--TAKE OUT
--
DECLARE @T0 CHAR(10)
DECLARE @FROM CHAR(10)
SET @T0 = 0000000000
SET @FROM = 9999999999
--
--
--
*/
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des c, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SU P_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(4000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150), OVER_SHORT_POINTS NUMERIC(11,8)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes), ' + CHAR(13) + ' (SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf sp.evnt_id, sp.stnd_num )'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' + CHAR(13)
+ 'FROM tbl_evnt_sup_set ess ' + CHAR(13)
+ 'INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON'
+ CHAR(13) + ' sp.evnt_id = ess.evnt_id'
+ CHAR(13) + ' AND sp.stnd_num = ess.stnd_num '
+ CHAR(13) + ' AND ess.evnt_id <= ''' + @T0 + ''''
+ CHAR(13) + ' AND ess.evnt_id >= ''' + @FROM + ''''
SET @sqlStmt = @sqlStmt + ' '
+ CHAR(13) + 'GROUP BY ess.evnt_id, ess.stnd_num'
+ CHAR(13) + 'ORDER BY ess.evnt_id, ess.stnd_num'
--SET @sqlStmt = @sqlStmt + CHAR(13)
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
GO
and this is the code to the function that it calls:
CREATE function tracker.fn_Calc_Stnd_OS_Pe rf ( @EventId varchar(10), @Stnd_Num CHAR(5))
RETURNS
@RetTable TABLE(
OS_Rating NUMERIC(11,8)
) AS
BEGIN
--DECLARE @Item_Gross_Sales NUMERIC(11,2)
DECLARE @stnd_cash NUMERIC(11,2)
DECLARE @stnd_OS NUMERIC(11,2)
DECLARE @Perc NUMERIC(11,8)
DECLARE @Ratio NUMERIC(11,8)
---DECLARE @OS_Pts_Max NUMERIC(11,8)
SET @Perc = (
(SELECT
PERC_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS T_OS_PERC P
WHERE
P.Last_Changed = (
SELECT
MAX(Last_Changed)
FROM
ARCSTRACKER.tracker.TBL_MS T_OS_PERC)
) / 100)
/*
SET @OS_Pts_Max = (
SELECT
PERC_POINT_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS T_OS_PERC)
WHERE
LAST_CHANGED = (
SELECT
MAX(LAST_CHANGED)
FROM
ARCSTRACKER.tracker.tbl_ms t_pts)) */
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
--GET THE CASH DATA
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
/*IF(
(SELECT
CSUM_CROOM_TOTAL
FROM
NEWARCS.dbnewarcs1.TblCash Sum B
WHERE
Csum_stnd_num = @Stnd_Num
AND Csum_Evnt_Id = @EventId) <> NULL)
BEGIN*/
SET @stnd_cash =
(SELECT
CSUM_CROOM_TOTAL
FROM
NEWARCS.dbnewarcs1.TblCash Sum B
WHERE
Csum_stnd_num = @Stnd_Num
AND Csum_Evnt_Id = @EventId
)
/*
--Stand Invetory Sales
IF (
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt Sum
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId)<>NULL)
BEGIN
SET @Item_Gross_Sales =
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt Sum
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId)
END
ELSE
BEGIN
SET @Item_Gross_Sales = 0
END*/
SET @stnd_OS = (@stnd_cash -
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt Sum
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId) )
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
--MAKE THE CALCULATIONS
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SET @Ratio = (
SELECT
PERC_POINT_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS T_OS_PERC P
WHERE
P.Last_Changed = (
SELECT
MAX(Last_Changed)
FROM
ARCSTRACKER.tracker.TBL_MS T_OS_PERC) )
If ( ABS(@stnd_OS) > (@stnd_cash * @Perc) )
BEGIN
SET @Ratio = 0
END
/*ELSE
BEGIN
SET @Ratio = @OS_Pts_Max
END
IF ( (@Item_Gross_Sales = 0) OR (@stnd_cash = 0) )
BEGIN
SET @Ratio = NULL
END
ELSE
BEGIN
If (@Ratio > @OS_Pts_Max)
BEGIN
SET @Ratio = @OS_Pts_Max
END
If (@Ratio < 0)
BEGIN
SET @Ratio = 0
END
END
*/
INSERT INTO @RetTable
VALUES(@Ratio)
RETURN
END
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SU
sp.evnt_id = ess.evnt_id
AND sp.stnd_num = ess.stnd_num
AND ess.evnt_id <= '9999999999'
AND ess.evnt_id >= '0 '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num
Thats the print out. This is the code to the Stored Prcedure that we finished yesterday:
CREATE PROCEDURE tracker.dbp_TheCarson (@T0 CHAR(10), @FROM CHAR(10))
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#dis
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
/*
--
--TAKE OUT
--
DECLARE @T0 CHAR(10)
DECLARE @FROM CHAR(10)
SET @T0 = 0000000000
SET @FROM = 9999999999
--
--
--
*/
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des
FROM ARCSTRACKER.tracker.TBL_SU
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(4000)
SET @fieldlist = ''
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150), OVER_SHORT_POINTS NUMERIC(11,8)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes), ' + CHAR(13) + ' (SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' + CHAR(13)
+ 'FROM tbl_evnt_sup_set ess ' + CHAR(13)
+ 'INNER JOIN ARCSTRACKER.tracker.TBL_SU
+ CHAR(13) + ' sp.evnt_id = ess.evnt_id'
+ CHAR(13) + ' AND sp.stnd_num = ess.stnd_num '
+ CHAR(13) + ' AND ess.evnt_id <= ''' + @T0 + ''''
+ CHAR(13) + ' AND ess.evnt_id >= ''' + @FROM + ''''
SET @sqlStmt = @sqlStmt + ' '
+ CHAR(13) + 'GROUP BY ess.evnt_id, ess.stnd_num'
+ CHAR(13) + 'ORDER BY ess.evnt_id, ess.stnd_num'
--SET @sqlStmt = @sqlStmt + CHAR(13)
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
GO
and this is the code to the function that it calls:
CREATE function tracker.fn_Calc_Stnd_OS_Pe
RETURNS
@RetTable TABLE(
OS_Rating NUMERIC(11,8)
) AS
BEGIN
--DECLARE @Item_Gross_Sales NUMERIC(11,2)
DECLARE @stnd_cash NUMERIC(11,2)
DECLARE @stnd_OS NUMERIC(11,2)
DECLARE @Perc NUMERIC(11,8)
DECLARE @Ratio NUMERIC(11,8)
---DECLARE @OS_Pts_Max NUMERIC(11,8)
SET @Perc = (
(SELECT
PERC_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS
WHERE
P.Last_Changed = (
SELECT
MAX(Last_Changed)
FROM
ARCSTRACKER.tracker.TBL_MS
) / 100)
/*
SET @OS_Pts_Max = (
SELECT
PERC_POINT_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS
WHERE
LAST_CHANGED = (
SELECT
MAX(LAST_CHANGED)
FROM
ARCSTRACKER.tracker.tbl_ms
--------------------------
--GET THE CASH DATA
--------------------------
/*IF(
(SELECT
CSUM_CROOM_TOTAL
FROM
NEWARCS.dbnewarcs1.TblCash
WHERE
Csum_stnd_num = @Stnd_Num
AND Csum_Evnt_Id = @EventId) <> NULL)
BEGIN*/
SET @stnd_cash =
(SELECT
CSUM_CROOM_TOTAL
FROM
NEWARCS.dbnewarcs1.TblCash
WHERE
Csum_stnd_num = @Stnd_Num
AND Csum_Evnt_Id = @EventId
)
/*
--Stand Invetory Sales
IF (
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId)<>NULL)
BEGIN
SET @Item_Gross_Sales =
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId)
END
ELSE
BEGIN
SET @Item_Gross_Sales = 0
END*/
SET @stnd_OS = (@stnd_cash -
(SELECT
SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
FROM
NEWARCS.dbnewarcs1.TblEvnt
WHERE
Esum_Stnd_Num = @Stnd_Num
AND Esum_Evnt_Id = @EventId) )
--------------------------
--MAKE THE CALCULATIONS
--------------------------
SET @Ratio = (
SELECT
PERC_POINT_VALUE
FROM
ARCSTRACKER.tracker.TBL_MS
WHERE
P.Last_Changed = (
SELECT
MAX(Last_Changed)
FROM
ARCSTRACKER.tracker.TBL_MS
If ( ABS(@stnd_OS) > (@stnd_cash * @Perc) )
BEGIN
SET @Ratio = 0
END
/*ELSE
BEGIN
SET @Ratio = @OS_Pts_Max
END
IF ( (@Item_Gross_Sales = 0) OR (@stnd_cash = 0) )
BEGIN
SET @Ratio = NULL
END
ELSE
BEGIN
If (@Ratio > @OS_Pts_Max)
BEGIN
SET @Ratio = @OS_Pts_Max
END
If (@Ratio < 0)
BEGIN
SET @Ratio = 0
END
END
*/
INSERT INTO @RetTable
VALUES(@Ratio)
RETURN
END
I don't think this function:
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf sp.evnt_id, sp.stnd_num ),
can be used in that context, even with the required parentheses around the parameters:
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf (sp.evnt_id, sp.stnd_num)),
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
can be used in that context, even with the required parentheses around the parameters:
(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
ASKER
I know almost nothing of SQL. Any ideas on how to make it?
Actually I mis-read it the first time. If the function returns a single value rather than a table of values, and it seems as if it probably does, try this:
max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf (sp.evnt_id, sp.stnd_num) )),
and let me mknow what u get.
max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
and let me mknow what u get.
ASKER
same deal
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe rf (ess.evnt_id, ess.stnd_num) )),
MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SU P_PERF sp ON
sp.evnt_id = ess.evnt_id
AND sp.stnd_num = ess.stnd_num
AND ess.evnt_id <= '9999999999'
AND ess.evnt_id >= '0 '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes),
max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Pe
MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SU
sp.evnt_id = ess.evnt_id
AND sp.stnd_num = ess.stnd_num
AND ess.evnt_id <= '9999999999'
AND ess.evnt_id >= '0 '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num
DOUBLE D'OH:
I read it right the first time. This is trying to return a table of values based on inputs from a table -- I don't think you can do that. Even this shouldn't work:
max((SELECT MAX(OS_RATING) FROM tracker.fn_Calc_Stnd_OS_Pe rf (ess.evnt_id, ess.stnd_num) )),
I read it right the first time. This is trying to return a table of values based on inputs from a table -- I don't think you can do that. Even this shouldn't work:
max((SELECT MAX(OS_RATING) FROM tracker.fn_Calc_Stnd_OS_Pe
ASKER
I was toying with trying to make it return just a single value, but I couldn't get it to. If I make it do that, will it work?
No, not really. If you can re-work the function to return a single value rather than a table, that is, a scalar rather than table function, then it should work fine.
ASKER
Well the way it works is there is actually ony one row in that table so it is just one value. I don't know how to make it return just, lets say, a numeric value.
ASKER
Nevermind, Figured it out. Thanks again.
ASKER
-- add all these lines
SET NOCOUNT ON --optional
IF OBJECT_ID('tempdb.dbo.#dis
DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
ident INT IDENTITY(1, 1),
distinctPoint VARCHAR(35),
PRIMARY KEY (ident)
)
DECLARE @numDistinctPoints SMALLINT
INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_des
FROM ARCSTRACKER.tracker.TBL_SU
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''
-- change to current code
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident
/*
SELECT @fieldlist = @fieldlist + ', ' + REPLACE(distinctpoint, ' ', '_') + ' int'
FROM (SELECT DISTINCT(POINT_DESC) as distinctpoint FROM ARCSTRACKER.tracker.TBL_SU
*/
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
/*
DECLARE @EVNT_Id CHAR(10)
DECLARE @STND_NUM CHAR(5)
DECLARE @fund varchar(35)
DECLARE @amt int
DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)
DECLARE c_Order CURSOR FOR SELECT DISTINCT(EVNT_ID) distinctevent, DISTINCT(STND_NUM) distinctstndnum FROM ARCSTRACKER.tracker.TBL_SU
OPEN c_Order
FETCH NEXT FROM c_Order INTO @EVNT_Id, @STND_NUM
WHILE @@FETCH_STATUS = 0
BEGIN
SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
@insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
EXEC sp_executesql @sqlstmt
FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
*/
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
' MAX(ess.mgr_fname), MAX(ess.mgr_lname), MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + ' MAX(CASE WHEN sp.point_desc = ''' +
(SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
' THEN point_value ELSE NULL END)'
SET @distinctPointNum = @distinctPointNum + 1
END --WHILE
SET @sqlStmt = @sqlStmt + ' ' +
'FROM tbl_evnt_sup_set ess ' +
'INNER JOIN ARCSTRACKER.tracker.TBL_SU
' /* AND sp.stnd_num = ess.stnd_num */ '
SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'
PRINT @sqlStmt --optional
EXEC(@sqlStmt)
SELECT * FROM tmp_output
DROP TABLE tmp_output
That all worked to add everything to the table except for the actual point values. It is giving me null in all of the value fields.