Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

218 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 22:20:59

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: SQLserver prijs-index  (gelezen 20034 keer)

0 leden en 1 gast bekijken dit topic.

Offline NLCOMP

  • Forumheld
  • *****
  • Berichten: 14.666
    • NL Computer Forum
SQLserver prijs-index
« Gepost op: 9 november 2009, 19:21:39 »
Bericht 1 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:15-12-2004
 Aan:Hugo KornelisMsgID:1772.1
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi Hugo,

Ik wil de prijs index van woningen bepalen  m.b.v.  stored procedure.

Er wordt  m.b.v. een sp een hulptabel (zstblIndexPerJaar) gevuld met data uit andere tabellen
(de ddl en inserts, stuur ik je eventueel per privepost vanwege privacy gevoeligheid v.d. data)

Met de volgende sp haal ik de data voor  het bepalen van de index op, dat werkt allemaal al.

ALTER PROCEDURE dbo.IndexPerKwartaal
AS
SELECT    CAST(LEFT(DATUM, 4) AS char(4)) + ' ' +
                    CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1)) AS Kwartaal,
                    COUNT(IID) AS Aantal,
                    CAST(AVG(PRIJS) AS int) AS GemPrijs
FROM         dbo.zstblIndexPerJaar
GROUP BY CAST(LEFT(DATUM, 4) AS char(4)) + ' ' + CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1))
ORDER BY CAST(LEFT(DATUM, 4) AS char(4)) + ' ' + CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1))

Als ik nu het resultset in Excel plak dan kan ik daar makkelijk  b.v. 1-1-1999 op 100 zetten
en daarna de faktor bepalen om voor de overige rijen de prijsindex te bepalen, in VB lukt dat
ook makelijk met een recordset en een loop daardoor, maar hoe in een stored procedure ?

Voorbeeld gewenste output :

Kwartaal  Aantal   GemPrijs   Index
1998 04|    176   |  143324   |  97
1999 01|    134   |  147566   |  100
1999 02|    128   |  167018   |  113
1999 03|    169   |  184755   |  125
1999 04|    114   |  204187   |  138

eerste 3 kolommen staan al in de sp de laatste moet er 'on the fly' bijkomen

bvb mijn dank

Groeten --John



Bericht 2 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:15-12-2004
 Aan:John Kopmels (Sysop)MsgID:1772.2
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi John,
Da's volgens mij prima te doen. Voor een geteste oplossing is een scriptje met DDL en INSERTS inderdaad wel handig, dus stuur maar op. Dat is dan meteen een goede geheugensteun, want ik heb in de nieuwe Forum interface nog geen manier gevonden om te zorgen dat ik een bericht bij mijn volgende bezoek automatisch weer te zien krijg.
Groetjes, Hugo


Bericht 3 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:15-12-2004
 Aan:John Kopmels (Sysop)MsgID:1772.3
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi John,
De eenvoudigste manier om dit te bereiken is simpelweg een subquery op te nemen in de SELECT voor de gemiddelde prijs van het eerste kwartaal van 1999. De formule wordt dan dus: "100 * gemiddelde dit kwartaal / gemiddelde eerste kwartaal 1999". Om dan exact de resultaten te krijgen die jij me gemaild hebt moet dit niet direct naar integer worden omgezet, maar eerst netjes worden afgerond. De totale query wordt dan:
SELECT    CAST(LEFT(DATUM, 4) AS char(4)) + ' ' +
                    CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1)) AS Kwartaal,
                    COUNT(IID) AS Aantal,
                    CAST(AVG(PRIJS) AS int) AS GemPrijs,
                    CAST(ROUND(100 * AVG(PRIJS)
                            / (SELECT AVG(PRIJS)
                               FROM   dbo.zstblIndexPerJaar
                               WHERE  DATUM >= '19990101'
                               AND    DATUM < '19990401'), 0) AS int) AS "Index"
FROM         dbo.zstblIndexPerJaar
GROUP BY CAST(LEFT(DATUM, 4) AS char(4)) + ' ' + CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1))
ORDER BY CAST(LEFT(DATUM, 4) AS char(4)) + ' ' + CAST(DATEPART(quarter, CAST(DATUM AS char(8))) AS char(1))
Groetjes, Hugo


Bericht 4 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:16-12-2004
 Aan:Hugo KornelisMsgID:1772.4
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi Hugo,

                    CAST(ROUND(100 * AVG(PRIJS)
                            / (SELECT AVG(PRIJS)
                               FROM   dbo.zstblIndexPerJaar
                               WHERE  DATUM >= '19990101'
                               AND    DATUM < '19990401'), 0) AS int) AS "Index"


Wederom geweldig, bedankt weer !

Nog een aanvullende vraag over 'Datum', :
dat wordt aangeleverd als INT (wel in het formaat YYYYMMDD)

Ik converteer eerst naar  String, maar jij gebruikt het direkt (dus implicite conversie?)

Is er winst bij het een of het ander, en idem is er winst te behalen als ik alle datumvelden
direkt na levering omzet naar CHAR(8) en later bij teruglevering weer terug converteer ?

Groetjes --John






Bericht 5 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:16-12-2004
 Aan:John Kopmels (Sysop)MsgID:1772.5
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi John,
 > Nog een aanvullende vraag over 'Datum', :
 > dat wordt aangeleverd als INT (wel in het formaat YYYYMMDD)
 >
 > Ik converteer eerst naar  String, maar jij gebruikt het direkt (dus implicite conversie?)
Wil je wel geloven dat me dit tot nu toe niet eens was opgevallen? <g>
Je kunt geen impliciete conversie gebruiken voor integer naar datetime. SQL Server denkt dan dat je het aantal dagen na 1-1-1900 in de integer hebt staan, en met waarden als 20040101 kom je dan voorbij het jaar 9999 en krijg je dus foutmeldingen:
declare @a datetime
set @a = 17
select @a
set @a = 20040101
select @a
De reden dat mijn query toch werkt, is dat SQL Server noch de als integer gedefinieerde kolom DATUM, noch de string constante '19990101' als datum beschouwt. In dit geval zal SQL Server de string constanten naar integer converteren en dan de expressie evalueren. In dit specifieke geval zou je dus de impliciete conversie kunnen vermijden door de single-quotes rond 19990101 weg te laten - maar ik zou zelf kiezen voor een meer structurele oplossing.
 > Is er winst bij het een of het ander, en idem is er winst te behalen als ik alle datumvelden
 > direkt na levering omzet naar CHAR(8) en later bij teruglevering weer terug converteer ?
Ik zou zeker direct bij levering converteren. Maar dan naar datetime, niet naar CHAR(8). Op die manier kun je veel makkelijker met de gegevens manipuleren (ik heb bv. overwogen om de test op eerste kwartaal 1999 niet uit te voeren aan de hand van begin- en einddatum, maar m.b.v. de YEAR en MONTH functies - dan zou de query dus faliekant fout zijn gelopen!). Bovendien krijg je direct een foutmelding als een niet bestaande datum wordt geleverd (19980230); sla je deze op als integer of string, dan zal SQL Server dit accepteren maar krijg je mogelijk wel onjuiste resultaten - en probeer daar dan maar eens de oorzaak van te vinden!
Groetjes, Hugo


Bericht 6 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:16-12-2004
 Aan:Hugo KornelisMsgID:1772.6
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi Hugo,
> Wil je wel geloven dat me dit tot nu toe niet eens was opgevallen? <g>

ik was al enigzinds verbaast dat je er niets over zei :-)

> Je kunt geen impliciete conversie gebruiken voor integer naar datetime.

ok ik zie het, geeft een overloop

> maar ik zou zelf kiezen voor een meer structurele oplossing.
> Ik zou zeker direct bij levering converteren. Maar dan naar datetime, niet naar CHAR(8).
Mijn gedachte was dat ik met CHAR(8) het dichts bij het orgineel formaat blijf en SQL Server
zelf zonodig een impliciete conversie doet bij strings die als datetime waarde herkent worden.

De data zit al in tabellen met een type INT, daarin laten zitten en converteren lukt toch niet ?

Dus iets als :
- een kolom bijmaken DATETIME
- de eerste converteren naar de tweede met CONVERT(DATETIME,CAST(EINDDAT AS CHAR(8)))
- de eerste deleten
- de tweede renamen

of is er een andere manier ?

Trouwens kan SMALLDATETIME ook ? ik ben niet in het tijdsdeel geinteresseerd.

Groetjes --John



Bericht 7 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:17-12-2004
 Aan:John Kopmels (Sysop)MsgID:1772.7
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi John,
 > Mijn gedachte was dat ik met CHAR(8) het dichts bij het orgineel formaat blijf en SQL Server
 > zelf zonodig een impliciete conversie doet bij strings die als datetime waarde herkent worden.
Ik begrijp de redenatie, maar om te beginnen loop je daarmee het risico dat een slecht geformatteerde datum op een totaal onverwacht moment tot runtime fouten leidt. Bovendien kan een eventuele index op de betreffende kolom niet gebruikt worden als de inhoud van de kolom geconverteerd moet worden en zul je in dergelijk gevallen dus al gauw een table scan krijgen. Afhankelijk van de omvang van de tabel kan dat de performance flink laten kelderen!
 > De data zit al in tabellen met een type INT, daarin laten zitten en converteren lukt toch niet ?

 > Dus iets als :
 > - een kolom bijmaken DATETIME
 > - de eerste converteren naar de tweede met CONVERT(DATETIME,CAST(EINDDAT AS CHAR(8)))
 > - de eerste deleten
 > - de tweede renamen

 > of is er een andere manier ?
Dit zal werken; wat je ook kan proberen (ik heb het zelf niet geprobeerd, maar volgens mij moet het lukken) is het datatype van de kolom via een tussenstap met ALTER TABLE te wijzigen:
ALTER TABLE MijnTabel
ALTER COLUMN EINDDAT char(8)
go
ALTER TABLE MijnTabel
ALTER COLUMN EINDDAT datetime
go
Als je meerdere kolommen te converteren hebt is jouw versie vermoedelijk sneller, omdat je dan alle kolommen tegelijk kunt converteren. Bij een ALTER TABLE kun je maar van één kolom tegelijk het datatype wijzigen.
 > Trouwens kan SMALLDATETIME ook ? ik ben niet in het tijdsdeel geinteresseerd.
Als je geen moeite hebt met het beperktere bereik (1 januari 1900 tot 6 juni 2079) kan dit ook. (Scheelt je weer 4 bytes per kolom). Gebruik van SMALLDATETIME betekent overigens niet dat je dan automatisch geen tijdsdeel meer hebt - het enige dat je wat dat betreft kwijtraakt zijn de seconden en milliseconden.
Groetjes, Hugo


Bericht 8 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:John Kopmels (Sysop)Datum:17-12-2004
 Aan:Hugo KornelisMsgID:1772.8
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi Hugo,
> om te beginnen loop je daarmee het risico dat een slecht geformatteerde
> datum op een totaal onverwacht moment tot runtime fouten leidt.

ok, maar dat is ook op frontend nivo nog wel te valideren

> Bovendien kan een eventuele index op de betreffende kolom niet gebruikt worden  als de inhoud van
> de kolom geconverteerd moet worden en zul je in dergelijk gevallen dus al gauw een table scan krijgen.
> Afhankelijk van de omvang van de tabel kan dat de performance flink laten kelderen!

dit is veel meer een goede reden, eens kijken hoe dit  bij collega's valt

>  wat je ook kan proberen is het datatype van de kolom via een tussenstap met ALTER TABLE te wijzigen:
> ALTER TABLE MijnTabel
> ALTER COLUMN EINDDAT char(8)
> go
> ALTER TABLE MijnTabel
> ALTER COLUMN EINDDAT datetime
> go

dat werkt ook ja, de conversie gebeurt natuurlijk automatisch in de 2e stap naar datetime
> Als je meerdere kolommen te converteren hebt is jouw versie vermoedelijk sneller,
> omdat je dan alle kolommen tegelijk kunt converteren.
> Bij een ALTER TABLE kun je maar van één kolom tegelijk het datatype wijzigen.

ik ga eerst eens overleggen en daarna wat testen

 > Trouwens kan SMALLDATETIME ook ? ik ben niet in het tijdsdeel geinteresseerd.
> Als je geen moeite hebt met het beperktere bereik (1 januari 1900 tot 6 juni 2079) kan dit ook.

dat is ruim voldoende, het gaat maar om enkele jaren voor en na huidige datum
alleeen bouwjaren niet, maar daarvan wordt alleen het jaartal als char(4) gebruikt

> Gebruik van SMALLDATETIME betekent overigens niet dat je dan automatisch geen tijdsdeel
> meer hebt - het enige dat je wat dat betreft kwijtraakt zijn de seconden en milliseconden.

het hele tijdsdeel is niet interessant dus de preciese ervan zeker niet :-)

bedankt weer voor de wijze woorden en het meedenken !

Groetjes --John




Bericht 9 van 9

NL Computer Forum ~ SQL & Programmeren
 Van:Hugo KornelisDatum:19-12-2004
 Aan:John Kopmels (Sysop)MsgID:1772.9
 Onderwerp:SQLserver prijs-indexForum:ws-nlcomputer
Hoi John,
>>  wat je ook kan proberen is het datatype van de kolom via een tussenstap met ALTER TABLE te wijzigen:
>> ALTER TABLE MijnTabel
>> ALTER COLUMN EINDDAT char(8)
>> go
>> ALTER TABLE MijnTabel
>> ALTER COLUMN EINDDAT datetime
>> go
>
> dat werkt ook ja, de conversie gebeurt natuurlijk automatisch in de 2e stap naar datetime

Om het heel precies te formuleren: er zal in beide stappen een conversie plaatsvinden. Dat geldt overigens ook voor alle andere voor de hand liggende methodes om JJJJMMDD van integer naar datetime te converteren.
Wil je het persé in één stap, dan denk ik dat je al gauw een gecompliceerde formule krijg die integer deling en modulo operator gebruikt om jaar, maand en dag individueel te extraheren en die dan met DATEADD functies aan elkaar te plakken. Het zal bepaald geen fraaie code worden....
Groetjes, Hugo