Bericht 1 van 9NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 15-12-2004 |
Aan | : | Hugo Kornelis | MsgID | : | 1772.1 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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 9NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 15-12-2004 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 1772.2 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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 9NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 15-12-2004 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 1772.3 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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 9NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 16-12-2004 |
Aan | : | Hugo Kornelis | MsgID | : | 1772.4 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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(
en later bij teruglevering weer terug converteer ?
Groetjes --John
Bericht 5 van 9NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 16-12-2004 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 1772.5 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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(
en later bij teruglevering weer terug converteer ?
Ik zou zeker direct bij levering converteren. Maar dan naar datetime, niet naar CHAR(
. 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 9NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 16-12-2004 |
Aan | : | Hugo Kornelis | MsgID | : | 1772.6 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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(
.
Mijn gedachte was dat ik met CHAR(
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(
))
- 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 9NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 17-12-2004 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 1772.7 |
Onderwerp | : | SQLserver prijs-index | Forum | : | ws-nlcomputer |
Hoi John,
> Mijn gedachte was dat ik met CHAR(
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(
))
> - 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 9NL Computer Forum ~ SQL & Programmeren Van | : | John Kopmels (Sysop) | Datum | : | 17-12-2004 |
Aan | : | Hugo Kornelis | MsgID | : | 1772.8 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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(
> go
> ALTER TABLE MijnTabel
> ALTER COLUMN EINDDAT datetime
> godat 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 9NL Computer Forum ~ SQL & Programmeren Van | : | Hugo Kornelis | Datum | : | 19-12-2004 |
Aan | : | John Kopmels (Sysop) | MsgID | : | 1772.9 |
Onderwerp | : | SQLserver prijs-index | Forum | : | 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(
>> 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