[AccessD] first five dates per year

Hello Gustav and Drew,

thanks Gustav for your explanation. The dates where not unique.
I used the Union All sql and now its working fine.

Drew, thanks for your offer, but i can't send You an example because
the data is classified. From the management in our hospital
and my boss i may not present Patientdata to other. Sorry, not even to
a database developer who wants to help me.

Pedro Janssen



----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Pedro Janssen" <accessd at databaseadvisors.com>
Sent: Sunday, March 09, 2003 3:08 PM
Subject: Re: [AccessD] first five dates per year


> Hi Pedro
>
> Well, didn't imagine you had one field only.
>
> Anyway, here's the SQL to extract those dates for three years.
> If dates are not unique, you'll have to replace every occurrence of
> UNION with UNION ALL as shown:
>
> <SQL>
>
> PARAMETERS
>   Year Short;
> SELECT TOP 5
>   Year AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year],1,1)
> UNION ALL
> SELECT TOP 5
>   Year+1 AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year]+1,1,1)
> UNION ALL
> SELECT TOP 5
>   Year+2 AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year]+2,1,1)
> ORDER BY
>   Year5,
>   [datum];
>
> </SQL>
>
> If dates are not unique but you wish to extract the first five unique
> dates for each year, you'll have to group by the date like this:
>
> <SQL>
>
> PARAMETERS
>   Year Short;
> SELECT TOP 5
>   Year AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year],1,1)
> GROUP BY
>   [datum]
> UNION
> SELECT TOP 5
>   Year+1 AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year]+1,1,1)
> GROUP BY
>   [datum]
> UNION
> SELECT TOP 5
>   Year+2 AS Year5,
>   [datum]
> FROM
>   tblTrans
> WHERE
>   [datum] >= DateSerial([Year]+2,1,1)
> GROUP BY
>   [datum]
> ORDER BY
>   Year5,
>   [datum];
>
> </SQL>
>
> All this, of course, assumes that your date field is of type
> date/time-value.
>
> /gustav
>
> > When i use only the part of the query with Year the result is good, but
the
> > Parameters is asking for the year and afterthat for Year5. I don't
> > understand this.
> > When i use Year and Union Year+1 i get result that don't fit.
> > Maybe it has something to do with the fieldnames. In your query which
field
> > is ID and which field is DateTrans.
>
> > For example in TblTrans i only have one field [datum].
> > From this field [datum] i need each year the first 5 dates.
> > Couls you give me the sql for Year and Year+1.
>
> >> Sure. In addition to the correct comments of Drew, here is the union
> >> query which collects data from maximum three years. If you need more
> >> years, simply insert more "Union Select ..." sections where you for
> >> each section increase by 1 the number to add to Year.
> >>
> >> <SQL>
> >>
> >> PARAMETERS
> >>   Year Short;
> >> SELECT TOP 5
> >>   Year AS Year5,
> >>   ID
> >> FROM
> >>   tblTrans
> >> WHERE
> >>   DateTrans >= DateSerial([Year],1,1)
> >> UNION
> >> SELECT TOP 5
> >>   Year+1 AS Year5,
> >>   ID
> >> FROM
> >>   tblTrans
> >> WHERE
> >>   DateTrans >= DateSerial([Year]+1,1,1)
> >> UNION
> >> SELECT TOP 5
> >>   Year+2 AS Year5,
> >>   ID
> >> FROM
> >>   tblTrans
> >> WHERE
> >>   DateTrans >= DateSerial([Year]+2,1,1)
> >> ORDER BY
> >>   Year5,
> >>   ID;
> >>
> >> </SQL>
> >>
> >> You will, of course, have to change the names of table and fields to
> >> those of your table.
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>