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.
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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InDELKK1] ASBEGIN SET NOCOUNT ON; Declare @i intDeclare @MaxVanOrderdatum datetimeDeclare @AankoopBdr decimal(6,2)declare @Klantnummer intdeclare @Tal smallintdeclare @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 workDeclare @val intselect @Cont = Count(klantnummer) from @Tempselect @val = 1I want to insert the results of a temporary tableinto 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 @TempInsert into Delkk (Orderdatum, AankoopBdr, Klantnummer, Tal)values (@MaxVanOrderdatum, @AankoopBdr,@Klantnummer,@Tal)SELECT @val=@val+1END 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InDELKK1] ASBEGINSET 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.GemeenteIdINNER JOIN Klantenkaart ON Klant.Klantnummer = Klantenkaart.KlantnummerGROUP BY Klant.Klantnummer, Klantenkaart.Aankoopbedrag, Gemeente.GemeenteHAVING 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 workEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2013-09-23 : 07:22:59
|
This wont suffice:explanation:KlantenkaartOrderId| Orderdatum (datetime) |OrderId | AankoopBedrag| Klantnummer22197 2011-09-23 11:01:42.000 0 224.06 17823314 2011-12-14 11:45:43.000 0 20.00 17824647 2012-03-03 15:21:55.000 0 74.95 17824868 2012-04-03 16:28:33.000 0 156.60 17825700 2012-06-14 12:10:35.000 0 37.90 17827234 2012-11-03 13:47:57.000 0 191.65 17831027 2013-08-14 11:34:46.000 60593 83.80 178KlantenkaartOrderId| Orderdatum (datetime) |OrderId | AankoopBedrag| Klantnummer31356 2011-09-11 00:00:00.000 0 20.00 375631357 2010-06-21 00:00:00.000 0 20.00 375631358 2010-05-21 00:00:00.000 0 20.00 375631359 2010-08-21 00:00:00.000 0 20.00 375631360 2010-09-21 00:00:00.000 0 21.00 375631361 2011-09-11 00:00:00.000 0 22.00 375631362 2011-06-21 00:00:00.000 0 22.00 375631363 2011-05-21 00:00:00.000 0 20.00 375631364 2011-08-21 00:00:00.000 0 20.00 375631365 2011-09-21 00:00:00.000 0 20.00 3756Above are the columns of table 'Klantenkaart' with some data.The Purpose of the SP is that it is updating the recordsto 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, becausethe 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 selection4. Delete klantnummer (where max(orderdatum) is 2 year ago) in tableklantenkaart. Hope this is more clear.Txs for the support,John |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-23 : 09:09:44
|
Ok..I think i got youeven then you could simply do something likeUSE [Database]GO/****** Object: StoredProcedure [dbo].[InDELKK1] Script Date: 09/22/2013 18:31:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InDELKK1] ASBEGINSET NOCOUNT ON;Insert into Delkk (Orderdatum, AankoopBdr, Klantnummer, Tal)SELECT columns....FROM(SELECT *,Max(Klantenkaart.Orderdatum) OVER (PARTITION BY Klantnummer) AS MaxOrderDateFROM Gemeente INNER JOIN Klant ON Gemeente.GemeenteId = Klant.GemeenteIdINNER JOIN Klantenkaart ON Klant.Klantnummer = Klantenkaart.Klantnummer)tWHERE MaxOrderDate < DATEADD(YEAR, -2, SYSDATETIME())DELETE kFROM Klantenkaart kWHERE EXISTS(SELECT 1 FROM Delkk WHERE Klantnummer = K.Klantnummer)END replace columns with actual fields in above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 datetimeDeclare @Klantnummer intWhile((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=@OrderdatumWhere klantnummer=@Klantnummer Delete @tab1 Where klantnummer=@KlantnummerEndSelect * From @FinalTableWhere's the bug?John |
|
|
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 clauseIf @Orderdatum < DATEADD(YEAR, -2, SYSDATETIME())Insert Into @FinalTable Select klantnummer, Aankoopbedrag From Klantenkaart Where Orderdatum=@OrderdatumWhere AND klantnummer=@KlantnummerDelete @tab1 Where klantnummer=@KlantnummerEnd--Chandu |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2013-09-24 : 07:20:12
|
Bandi,1. the query gives a good result2.SSMS gives the message:Msg 156, Level 15, State 1, Line 19Incorrect syntax near the keyword 'And'.Isn't Where good?John |
|
|
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=@OrderdatumAND klantnummer=@Klantnummer--Chandu |
|
|
JohnDW
Starting Member
45 Posts |
Posted - 2013-09-24 : 07:30:35
|
still doesn't work |
|
|
|
|
|
|
|