TM1 Forum

TRUNCATE TABLE [SAMP-TABLE].[dbo].[Company$Sales Accountability]

DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
DECLARE @FilterDate DateTime

SET @FromDate = '12/1/10'
SET @ToDate = '12/1/10'

SET @FilterDate = @FromDate - 1

WHILE @FilterDate < @ToDate
BEGIN

SET @FilterDate = @FilterDate + 1

INSERT INTO [SAMP-TABLE].[dbo].[Company$Sales Accountability]
([Store No_]
,[Store Name]
,[Date]
,[Staff ID]
,[Staff Name]
,[Tender Type]
,[Trans_ Amount]
,[Counted Amount]
,[Difference Amount])

SELECT TPE.[Store No_]
,''
,TPE.[Date]
,TPE.[Staff ID]
,''
,TPE.[Tender Type]
,SUM(TPE.[Amount Tendered]) [Trans Amount]
,0
,0

FROM [SAMP-TABLE].[dbo].[Company$Trans_ Payment Entry] TPE

WHERE TPE.[Date] = @FilterDate AND
TPE.[Store No_] <> '000100'

GROUP BY TPE.[Store No_], TPE.[Date], TPE.[Staff ID], TPE.[Tender Type]
ORDER BY TPE.[Date] ASC, TPE.[Store No_] ASC

END

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]

SET [Counted Amount] = STL.[Counted Amount],
[Difference Amount] = SAC.[Trans_ Amount] - STL.[Counted Amount]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC

JOIN [SAMP-TABLE].[dbo].[Company$Statement Line] STL
ON STL.[Store No_] = SAC.[Store No_] AND
STL.[Staff ID] = SAC.[Staff ID] AND
STL.[Tender Type] = SAC.[Tender Type]

JOIN [SAMP-TABLE].[dbo].[Company$Statement] STM
ON STM.[No_] = STL.[Statement No_] AND
STM.[Store No_] = SAC.[Store No_] AND
STM.[Posting Date] = SAC.[Date]

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Difference Amount] = SAC.[Trans_ Amount] - SAC.[Counted Amount]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Store Name] = SRE.[Name]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC
JOIN [SAMP-TABLE].[dbo].[Company$Store] SRE
ON SRE.[No_] = SAC.[Store No_]

UPDATE [SAMP-TABLE].[dbo].[Company$Sales Accountability]
SET [Staff Name] = STF.[First Name] + ' ' + STF.[Last Name]

FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability] SAC
JOIN [SAMP-TABLE].[dbo].[Company$Staff] STF
ON STF.[ID] = SAC.[Staff ID]

SELECT [Date]
,[Store No_]
,[Store Name]
,[Staff ID]
,[Staff Name]
,[Tender Type]
,[Trans_ Amount]
,[Counted Amount]
,[Difference Amount]
FROM [SAMP-TABLE].[dbo].[Company$Sales Accountability]

ORDER BY [Date], [Store No_], [Staff ID], [Tender Type]
GO

• INSERT and UPDATE are not allowed in the data tab.
• although ANYTHING is allowed it an ODBCOutput function, you have to split your text in 255 char segments and put them one after the other (eg:

  Code: Select all

  ODBCOutput(Source, SQLQuerySeg1, SQLQuerySeg2, SQLQuerySeg3, ...);

  .)
• finaly if you have done all that, I would remove the empty lines, sometimes the ODBC doesn't like empty lines in statement.

Gregor Koch wrote: I think you are better off having this query as a stored procedure in your source system and calling that from TI.