Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Copy results to History table

Hi,

I have a function that runs that extracts data based on the day the function is run. This data is used each time we run the function by Crystal Reports. The data currently gets put in to a temporary table but I would also like the same data to be copied in to a HISTORY table so we can retain excatly what was extracted each day. The part of the function though that run the extract to the history table though should only run once and not repeatedly every time the function is run. I was thinking that I could simply use the date column to stop it doing this: sort of 'if date = @date then end else select in to HISTORY from ctetemp.

I'm no DBA and this function that may need to be amended is probably the most critical function we have so I'd like advice from you guys on how best to do this. I have attached the current function, maybe it's a case of changing part of it to allow the copy out in to the HISTORY table (which I also don't have), I'm really not sure.

Any assistance would be much appreciated.

Regards,

Ken


ALTER FUNCTION [dbo].[ServiceViewBatch] (@date datetime)
RETURNS @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)


AS
-- body of the function
BEGIN

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

RETURN
END

Open in new window

0
kenuk110
Asked:
kenuk110
  • 13
  • 7
  • 5
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, but a FUNCTION cannot store data into real tables.
it can only use @table variables to store intermediate results etc ...

you need to change the logic/flow to either use a stored procedure instead of a function .
0
 
kenuk110Author Commented:
Is it possible that this function could infact be a stored procedure, it's fine running outside of this function and only needs to be run once a day, I'm just not sure which part I could use, if any?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can use a function in a stored procedure, but not in the other way, unfortunately.

does it have to be a function?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
sachinpatil10dCommented:
You cannot use insert or update or delete statements inside function you can use sub stored procedure

one thing you could do is insert the data in history table from function
means
Instead of passing date parameter in function check the date condition before.

if @date = getdate()
insert into History
select * from dbo.ServiceViewBatch()
else
select * from dbo.ServiceViewBatch()

0
 
kenuk110Author Commented:
No it doesn't have to be a function at all, I just thought as it was running is may as well do two jobs. It can by all means be a stored procedure but I'm still at a loss as to how to do it.
0
 
sachinpatil10dCommented:
if changing your function to store procedure is ok then check the following script
create proc [dbo].[ServiceViewBatch] 
@date datetime
as
BEGIN
-- body of the proc
declare @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

if @date = GETDATE()
insert into History
select * from @cte 
END

Open in new window

0
 
sachinpatil10dCommented:
sorry

This could be done
declare @count int
select @count = COUNT(*) from History where DATE = @date 
if @count > 0
insert into History
select * from @cte 

Open in new window

or
each time the records form history will be deleted and reinserted
delete History where date = @date
insert into History
select * from @cte 

Open in new window

0
 
kenuk110Author Commented:
Hi,

Sorry, do I need to do all three suggestions or the first long one or the second short one or the third short one.

If it's either of the second two, are these done as a stored procedure?



0
 
sachinpatil10dCommented:
use this
create proc [dbo].[ServiceViewBatch] 
@date datetime
as
BEGIN
-- body of the proc
declare @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History where DATE = @date 
if @count > 0
insert into History
select * from @cte 

END

Open in new window

0
 
kenuk110Author Commented:
Hi,

I copied this in to a stored procedure, I also created the HISTORY table, the procedure runs and the following message appreas:


Procedure executed successfully
Query time: 30.156ms
Affected rows: 9874

Affected rows: 9874

when I look in the HISTORY table though I don't see anything. Also, I'm not sure how to get the date in there?
0
 
sachinpatil10dCommented:
alter the storeprocedure condition and change the 2 lines
form
select @count = COUNT(*) from History where DATE = @date 
if @count > 0

Open in new window


to
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0

Open in new window

0
 
sachinpatil10dCommented:
HISTORY table should contain the column name date which will be checked in the query each time
0
 
kenuk110Author Commented:
Okay, I have changed that and when I try to save the procedure I get

[SQL Server]Column name or number of supplied values does not match table definition

I created a field in HISTORY called Date with type of date

Do I need to create this somewhere else too?
0
 
lisa_mcCommented:
hi

Thought i would show you how I would do it see attached

the sp below creates table if it doesnt exist plus it enters the date into the history file hope that helps
(have added sachinpatil10d count at bottom - thanks)

hope this helps

USE 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[ServiceViewBatch]  

@date datetime

AS

SET NOCOUNT ON


	if  not exists (select * from db.dbo.sysobjects
where id = object_id(N'[db].[dbo].[history]'))
    CREATE TABLE [dbo].[history] (
	(
    -- columns returned by the function
	[date] datetime,
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL
	)

-- set date to todays date
set @date = getDate()

-- create temp tables
if object_id('tempdb..#cte','U') is not null drop table #cte
if object_id('tempdb..#ctetemp','U') is not null drop table #ctetemp


  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  into #ctetemp 
	FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup

INSERT INTO #cte

FROM #ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM #ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History 
where date = @date 
if @count > 0
insert into History
select date, * from #cte 

END

SET NOCOUNT Off

Open in new window

0
 
lisa_mcCommented:
apologies take the insert out of line 96

so shoudl read into #cte
0
 
kenuk110Author Commented:
Hi,

Sorry for the delay, I had to go home!

I ran what you put, I put it in a query and when it runs I get this message:


[Err] 42000 - [SQL Server]Incorrect syntax near '('.
42000 - [SQL Server]Incorrect syntax near the keyword 'SET'.

Any ideas? And also is it because I am running it in a query?
0
 
sachinpatil10dCommented:
Store procedure
create proc [dbo].[ServiceViewBatch] 
@date datetime
as
BEGIN
-- body of the proc
declare @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
    [Cost] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0
insert into History
select * from @cte 

END

Open in new window


Execute store procedure
declare @date datetime
set @date = getdate()
exec [ServiceViewBatch] @date 
select * from History

Open in new window

0
 
kenuk110Author Commented:
Hi,

I run this as a stored oricedure and I get this error, I'm assuming it's due to the DATE field I have added to the History table.
--------------------------
Procedure execution failed
21S01 - [SQL Server]Column name or number of supplied values does not match table definition.

Query time: 31.732ms
Affected rows: 10154
------------------------------

I then ran the EXECUTE part thinking that would do something but I got this back:
---------------------------------------------
[SQL] declare @date datetime
set @date = getdate()
exec [ServiceViewBatchHistory] @date
select * from History

[Err] 21S01 - [SQL Server]Column name or number of supplied values does not match table definition.


Affected rows: 9874
--------------------------

Any ideas?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this error means that for at least 1 of the INSERT ... SELECT, the number of columns in the SELECT does not match the number of column in the table you are inserting to.
so, please double-check the tables and columns, especially the History table ...

0
 
kenuk110Author Commented:
Hi,

I have checked the History table and also copied the code to create it again in this messge. I 'think' it's copying everything from the @cte1 temp table so I checked the entries against that one. I can't see anything wrong though.

CREATE TABLE [dbo].[History] (
[ServiceValue] decimal(18) NULL ,
[Cost] decimal(18) NULL ,
[ItemName] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[countryCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[storeCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[cityCode] nvarchar(2) COLLATE Latin1_General_CI_AS NOT NULL ,
[itemCodeParsed] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[storeType] nvarchar(1) COLLATE Latin1_General_CI_AS NOT NULL ,
[vendorCode] nvarchar(3) COLLATE Latin1_General_CI_AS NOT NULL ,
[upcCode] nvarchar(30) COLLATE Latin1_General_CI_AS NOT NULL ,
[rc] nvarchar(3) COLLATE Latin1_General_CI_AS NULL ,
[gridCodeX] nvarchar(4) COLLATE Latin1_General_CI_AS NOT NULL ,
[gridCodeY] nvarchar(4) COLLATE Latin1_General_CI_AS NOT NULL ,
[facings] nvarchar(3) COLLATE Latin1_General_CI_AS NULL ,
[brand] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[serviceDay] nvarchar(9) COLLATE Latin1_General_CI_AS NULL ,
[serviceType] nvarchar(5) COLLATE Latin1_General_CI_AS NULL ,
[ARABIC] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[TREF] nvarchar(255) COLLATE Latin1_General_CI_AS NULL ,
[serviceValueTotal] decimal(29,2) NULL ,
[rnum] bigint NULL ,
[RunTotal] decimal(38,2) NULL ,
[BatchGroup] int NULL ,
[date] date NULL 
)
ON [PRIMARY]
GO

Open in new window

0
 
kenuk110Author Commented:
I'm not sure if this helps but I don't have a DATE field in the table that copies INTO History, I simply added the date field to History. Could this be the problem?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
history table has 1 column more than @cte:
[date] date NULL




declare @count int
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0
insert into History
select c.* , @date 
 from @cte c

Open in new window

0
 
kenuk110Author Commented:
Hi,

I take it I amend the last part of this script with the script you sent? I did that anyway and ran it and now I get"

Must declare the table variable "@cte"

Sorry to be stupid, I'm really not a DBA but trying to learn!

0
 
kenuk110Author Commented:
Sorry, I called it @cte1

Just running it now....
0
 
kenuk110Author Commented:
It works great when I run the procedure on it's own but how do I pass the date variable from outside it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what do you mean exactly?
I mean, this code:
declare @date datetime
set nocount on
set @date = getdate()
exec [ServiceViewBatchHistory] @date

Open in new window


and let the procedure also return the data:
create proc [dbo].[ServiceViewBatch] 
@date datetime = null
as
BEGIN
set @date = isnull(@date, getdate())
set nocount on

-- body of the proc
declare @cte table
(
    -- columns returned by the function
        [ServiceValue] decimal NULL,
    [Cost] decimal NULL,
        [ItemName] nvarchar (255) NULL,
        [countryCode] [nvarchar](3) NOT NULL,
        [storeCode] [nvarchar](3) NOT NULL,
        [cityCode] [nvarchar](2) NOT NULL,
        --[itemCode] [nvarchar](1000) NOT NULL,
        [itemCodeParsed] [nvarchar] (255) NULL,
        [storeType] [nvarchar](1) NOT NULL,
        [vendorCode] [nvarchar](3) NOT NULL,
        [upcCode] [nvarchar](30) NOT NULL,
        [rc] [nvarchar](3)  NULL,
        [gridCodeX] [nvarchar](4) NOT NULL,
        [gridCodeY] [nvarchar](4) NOT NULL,
        [facings] [nvarchar](3) NULL,
        [brand] [nvarchar](255) NULL,
        [serviceDay] [nvarchar](9) NULL,
        [serviceType] [nvarchar](5) NULL,
        [ARABIC] [nvarchar](255) NULL,
        [TREF] [nvarchar](255) NULL,
        [serviceValueTotal] [decimal](29, 2) NULL,
        [rnum] [bigint] NULL,
        [RunTotal] [decimal](38, 2) NULL,
        [BatchGroup] [int] NULL)

declare @ctetemp table
(
    -- columns returned by the function
        [ServiceValue] decimal NULL,
    [Cost] decimal NULL,
        [ItemName] nvarchar (255) NULL,
        [countryCode] [nvarchar](3) NOT NULL,
        [storeCode] [nvarchar](3) NOT NULL,
        [cityCode] [nvarchar](2) NOT NULL,
        --[itemCode] [nvarchar](1000) NOT NULL,
        [itemCodeParsed] [nvarchar] (255) NULL,
        [storeType] [nvarchar](1) NOT NULL,
        [vendorCode] [nvarchar](3) NOT NULL,
        [upcCode] [nvarchar](30) NOT NULL,
        [rc] [nvarchar](3)  NULL,
        [gridCodeX] [nvarchar](4) NOT NULL,
        [gridCodeY] [nvarchar](4) NOT NULL,
        [facings] [nvarchar](3) NULL,
        [brand] [nvarchar](255) NULL,
        [serviceDay] [nvarchar](9) NULL,
        [serviceType] [nvarchar](5) NULL,
        [ARABIC] [nvarchar](255) NULL,
        [TREF] [nvarchar](255) NULL,
        [serviceValueTotal] [decimal](29, 2) NULL,
        [rnum] [bigint] NULL);
        
insert into @ctetemp
  SELECT [ServiceValue]
       , [Cost]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (PARTITION BY countryCode, cityCode, storeType, storeCode ORDER BY CountryCode, CityCode, StoreCode, storeType, RC, gridCodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a 
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
AND b.countryCode = a.countryCode AND  b.cityCode = a.cityCode AND b.storeType = a.storeType AND b.storeCode = a.storeCode
) rt 

declare @count int
select @count = COUNT(*) from History where convert(date,DATE) = convert(date,@date)
if @count = 0
begin
  insert into History
  select c.*, @date from @cte c
end

select * from @cte c
END

Open in new window


should run just fine?

you might consider, btw, to make that procedure have the @date variable optional, aka using getdate() as default, but that's just a matter of
0
 
lisa_mcCommented:
hi ken only just noticing your comment now v busy yesterday

when i wrote SP - it meant stored procedure

this is why you were gettin those errors

0
 
kenuk110Author Commented:
Hi all,

Thank you so much for your help in this, I really appreciate it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now