Simple question

[Bokazoit]

I have a table with 100k+ rows. In one coloumn I have an indication of the period of the year (i.e. 200309 = sept. 2003) each period are represented several times.

I need to retrieve the quarters of each year so for that I use the coloumn with periods in a query. I then sum by totals and through a calcualtion I get the quarters. Here is the SQL view:

INSERT INTO Kvartal ( Periode, Kvartal )
SELECT Data.Periode, IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4))) AS Kvartal
FROM Data
GROUP BY Data.Periode, IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4)))
HAVING (((IIf(Right([Periode],2)<4,1,IIf(Right([Periode],2)>3 And Right([Periode],2)<7,2,IIf(Right([Periode],2)>6 And Right([Periode],2)<10,3,4))))<=[Choose quarter (ie 3):]));

Problem is that it's so damn slow since it has to retrieve all that data, create totals and then do the calculations. So it's not a wonder it's slow.

But is there an easier way to do this? I hope there is

In advance tx

 

[SkipVought]

Bokazoit

1) Why are you not using REAL DATES?

2) Calculate the quarter rather than nested iifs
[tt]
INT(RIGHT(([Periode]-1),2)/3)+1
[/tt]
[tt]
SELECT Data.Periode, INT(RIGHT(([Periode]-1),2)/3)+1 AS Kvartal

FROM Data

GROUP BY Data.Periode, INT(RIGHT(([Periode]-1),2)/3)+1

HAVING (Right([Periode],2)<=[Choose quarter (ie 3):]);
[/tt]

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[PHV]

You may try something like this:
SELECT DISTINCT Periode, 1 + (Right(Periode,2)-1) \ 3 AS Kvartal
FROM Data
WHERE 1 + (Right(Periode,2)-1) \ 3 <= [Choose quarter (ie 3):];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bokazoit]

Since the data isn't current. So I have added the periods myself.

I will try both suggestions tx and I return asap if it doesn't work

 

[Bokazoit]

Tx very much. It's quiet faster now than before and both suggestions was equal in speed so I choose the first