[AccessD] first five dates per year

Hello Drew,

I will send you the structure in one week.
Today i am leaving for a week on a trip for my work.

TIA

Pedro Janssen
----- Original Message -----
From: "Drew Wutka" <DWUTKA at marlow.com>
To: <accessd at databaseadvisors.com>
Sent: Monday, March 10, 2003 12:37 AM
Subject: RE: [AccessD] first five dates per year


> Completely understand.  No problem.
>
> If Gustav's solution works best for you, that's the way to go.  If you do
> want to further the subquery solution, let me know, you could send me a
> 'blank' database, with no data, I just need the table structure (I can
fill
> in my own dummy data).
>
> Drew
>
> -----Original Message-----
> From: Pedro Janssen
> To: accessd at databaseadvisors.com
> Sent: 3/9/03 4:54 PM
> Subject: Re: [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
> >
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>