SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update a table from a temporary talbe in SP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnDW
Starting Member

Belgium
45 Posts

Posted - 09/22/2013 :  13:36:34  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/22/2013 :  13:44:00  Show Profile  Reply with Quote
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

Edited by - visakh16 on 09/22/2013 13:47:04
Go to Top of Page

JohnDW
Starting Member

Belgium
45 Posts

Posted - 09/23/2013 :  07:22:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/23/2013 :  09:09:44  Show Profile  Reply with Quote
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

Belgium
45 Posts

Posted - 09/24/2013 :  03:29:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 09/24/2013 :  07:03:49  Show Profile  Reply with Quote
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

Belgium
45 Posts

Posted - 09/24/2013 :  07:20:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

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



--
Chandu
Go to Top of Page

JohnDW
Starting Member

Belgium
45 Posts

Posted - 09/24/2013 :  07:30:35  Show Profile  Reply with Quote


still doesn't work
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000