Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update a table from a temporary talbe in SP

Author  Topic 

JohnDW
Starting Member

45 Posts

Posted - 2013-09-22 : 13:36:34
USE [Database]
GO
/****** Object: StoredProcedure [dbo].[InDELKK1] Script Date: 09/22/2013 18:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InDELKK1]

AS
BEGIN

SET NOCOUNT ON;


Declare @i int
Declare @MaxVanOrderdatum datetime
Declare @AankoopBdr decimal(6,2)
declare @Klantnummer int
declare @Tal smallint
declare @Cont smallint


Declare @Temp Table (MaxVanOrderdatum Datetime,AankoopBdr decimal(6,2), Klantnummer int, Tal smallint)

Insert @Temp
select
Max(Klantenkaart.Orderdatum) AS MaxVanOrderdatum,
Klantenkaart.Aankoopbedrag as AankoopBdr,
Klant.Klantnummer,
Count(Klantenkaart.Aankoopbedrag) AS Tal
FROM
Gemeente
INNER JOIN
Klant
ON Gemeente.GemeenteId = Klant.GemeenteId
INNER JOIN
Klantenkaart
ON Klant.Klantnummer = Klantenkaart.Klantnummer
GROUP BY
Klant.Klantnummer,
Klantenkaart.Aankoopbedrag,
Gemeente.Gemeente
HAVING
Max(Klantenkaart.Orderdatum) < DATEADD(YEAR, -2, SYSDATETIME())
-- the result I get until here is ok (in tabel @Temp
-- then I want to insert the result into DelKK (table) but this doesn't work

Declare @val int
select @Cont = Count(klantnummer) from @Temp
select @val = 1

I want to insert the results of a temporary table
into a table in sqlserver .
The results of the temporary table I get,
but I can't manage it to put it into Delkk (= a table in my sql server db)

While @val <= @Cont

Select top 1 @MaxVanOrderdatum = MaxVanOrderdatum ,
@AankoopBdr = AankoopBdr ,
@Klantnummer = klantnummer ,
@Tal = Tal from @Temp
Insert into Delkk (Orderdatum, AankoopBdr, Klantnummer, Tal)
values (@MaxVanOrderdatum, @AankoopBdr,@Klantnummer,@Tal)

SELECT @val=@val+1
END

I need help for this.
Txs,

John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 13:44:00
why do you need loop? wont this suffice?


USE [Database]
GO
/****** Object: StoredProcedure [dbo].[InDELKK1] Script Date: 09/22/2013 18:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InDELKK1]

AS
BEGIN

SET NOCOUNT ON;



Insert into Delkk (Orderdatum, AankoopBdr, Klantnummer, Tal)
select
Max(Klantenkaart.Orderdatum),
Klantenkaart.Aankoopbedrag,
Klant.Klantnummer,
Count(Klantenkaart.Aankoopbedrag)
FROM
Gemeente
INNER JOIN
Klant
ON Gemeente.GemeenteId = Klant.GemeenteId
INNER JOIN
Klantenkaart
ON Klant.Klantnummer = Klantenkaart.Klantnummer
GROUP BY
Klant.Klantnummer,
Klantenkaart.Aankoopbedrag,
Gemeente.Gemeente
HAVING
Max(Klantenkaart.Orderdatum) < DATEADD(YEAR, -2, SYSDATETIME())
-- the result I get until here is ok (in tabel @Temp
-- then I want to insert the result into DelKK (table) but this doesn't work


END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JohnDW
Starting Member

45 Posts

Posted - 2013-09-23 : 07:22:59
This wont suffice:

explanation:

KlantenkaartOrderId| Orderdatum (datetime) |OrderId | AankoopBedrag| Klantnummer
22197 2011-09-23 11:01:42.000 0 224.06 178
23314 2011-12-14 11:45:43.000 0 20.00 178
24647 2012-03-03 15:21:55.000 0 74.95 178
24868 2012-04-03 16:28:33.000 0 156.60 178
25700 2012-06-14 12:10:35.000 0 37.90 178
27234 2012-11-03 13:47:57.000 0 191.65 178
31027 2013-08-14 11:34:46.000 60593 83.80 178

KlantenkaartOrderId| Orderdatum (datetime) |OrderId | AankoopBedrag| Klantnummer
31356 2011-09-11 00:00:00.000 0 20.00 3756
31357 2010-06-21 00:00:00.000 0 20.00 3756
31358 2010-05-21 00:00:00.000 0 20.00 3756
31359 2010-08-21 00:00:00.000 0 20.00 3756
31360 2010-09-21 00:00:00.000 0 21.00 3756
31361 2011-09-11 00:00:00.000 0 22.00 3756
31362 2011-06-21 00:00:00.000 0 22.00 3756
31363 2011-05-21 00:00:00.000 0 20.00 3756
31364 2011-08-21 00:00:00.000 0 20.00 3756
31365 2011-09-21 00:00:00.000 0 20.00 3756

Above are the columns of table 'Klantenkaart' with some data.
The Purpose of the SP is that it is updating the records
to another table where the max value of 'Orderdatum' for each 'klantnummer' group = two years ago.
So the SP must group the data by klantnummer, and see if the maximum of 'Orderdatum' is
< DATEADD(YEAR, -2, SYSDATETIME()).
If it is so the SP must update those records to another table and, after the update, the SP must be abel to delete all the rows of table 'Klantenkaart' where the 'klantnummer' is existing in
'Klantenkaart' (where the max(Orderdatum) of the records group by klantnummer = < DATEADD(YEAR, -2, SYSDATETIME()) .
So for example:
from the records above, stored in table 'Klantenkaart' 3756 must be updated (max(Orderdatum) < DATEADD(YEAR, -2, SYSDATETIME()) and deleted,
klantnummer 178 must not be updated and deleted in klantenkaart, because
the last 'orderdatum' was less then two years ago (2013-08-14 11:34:46.000).

I hope this is clear now.
For that reason I think that I must work with a temporary table where I store the records for each klantnummer where the max(0rderdatum) < 2 years ago , with a loop through Klantenkaart. That data I want to store in another table, and after that I want to delete the 'klantnummer' from the temporary table that is in 'Klantenkaart'.
Plan for SP:
1.
select MAX(Orderdatum), klantnummer from Klantenkaart
group by klantnummer
2.
where orderdatum DATEADD(YEAR, -2, SYSDATETIME())
3. Update that selection
4. Delete klantnummer (where max(orderdatum) is 2 year ago) in table
klantenkaart.

Hope this is more clear.

Txs for the support,

John
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 09:09:44
Ok..I think i got you

even then you could simply do something like


USE [Database]
GO
/****** Object: StoredProcedure [dbo].[InDELKK1] Script Date: 09/22/2013 18:31:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InDELKK1]

AS
BEGIN

SET NOCOUNT ON;



Insert into Delkk (Orderdatum, AankoopBdr, Klantnummer, Tal)
SELECT columns....
FROM
(SELECT *,
Max(Klantenkaart.Orderdatum) OVER (PARTITION BY Klantnummer) AS MaxOrderDate
FROM
Gemeente
INNER JOIN
Klant
ON Gemeente.GemeenteId = Klant.GemeenteId
INNER JOIN
Klantenkaart
ON Klant.Klantnummer = Klantenkaart.Klantnummer
)t
WHERE MaxOrderDate < DATEADD(YEAR, -2, SYSDATETIME())

DELETE k
FROM Klantenkaart k
WHERE EXISTS(SELECT 1 FROM Delkk WHERE Klantnummer = K.Klantnummer)


END




replace columns with actual fields in above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JohnDW
Starting Member

45 Posts

Posted - 2013-09-24 : 03:29:29
I tried this SP but it don't work.

I' ll have to do it with a loop.
But I need help .

the SP I have till now:

select klantnummer,MAX(Orderdatum)As Orderdatum from klantenkaart
group by Klantnummer

-- the code above (the query) works just fine. it's the part of the SP
-- below that doesn't work.
Declare @Orderdatum datetime
Declare @Klantnummer int
While((Select Count(*) From @tab1)>0)
Begin
Set @Orderdatum=(Select Top 1 Orderdatum From @tab1)
Set @Klantnummer= (Select Top 1 Klantnummer from @tab1)
If @Orderdatum < DATEADD(YEAR, -2, SYSDATETIME())
Insert Into @FinalTable
Select klantnummer, Aankoopbedrag From Klantenkaart Where Orderdatum=@Orderdatum
Where klantnummer=@Klantnummer
Delete @tab1 Where klantnummer=@Klantnummer
End

Select * From @FinalTable


Where's the bug?

John
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-24 : 07:03:49
Two things you consider in the above script:
1) You are using DATEADD(YEAR, -2, SYSDATETIME()) ... means it will search for exact timestamp with milliseconds.... like this (2011-09-24 16:29:52.0788437 )...
First check whether you are getting records using below query
Select klantnummer, Aankoopbedrag From Klantenkaart Where Orderdatum= DATEADD(YEAR, -2, SYSDATETIME())


2) There should AND operator for more than one condition in WHERE clause
If @Orderdatum < DATEADD(YEAR, -2, SYSDATETIME())
Insert Into @FinalTable
Select klantnummer, Aankoopbedrag From Klantenkaart Where Orderdatum=@Orderdatum
Where AND klantnummer=@Klantnummer
Delete @tab1 Where klantnummer=@Klantnummer
End


--
Chandu
Go to Top of Page

JohnDW
Starting Member

45 Posts

Posted - 2013-09-24 : 07:20:12
Bandi,

1. the query gives a good result
2.
SSMS gives the message:

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'And'.

Isn't Where good?

John
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-24 : 07:26:46
Insert Into @FinalTable
Select klantnummer, Aankoopbedrag From Klantenkaart Where Orderdatum=@Orderdatum
AND klantnummer=@Klantnummer



--
Chandu
Go to Top of Page

JohnDW
Starting Member

45 Posts

Posted - 2013-09-24 : 07:30:35


still doesn't work
Go to Top of Page
   

- Advertisement -