CODEWITHSUNDEEP
sqlms-access
Scott Caldarera
Scott Caldarera

Reputation:

SQL Select Question

I using this in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help.

I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all the fields from the table but only the 6 oldest rows that fall in a range for each group of:

COUPONS.DocType, COUPONS.PayTo, COUPONS.ContactName, COUPONS.ContactNumber,
COUPONS.DocFooter, COUPONS.PQBName, COUPONS.LetterDate, COUPONS.RetireeFirstName,
COUPONS.RetireeLastName, COUPONS.Address1, COUPONS.Address2, COUPONS.City, 
COUPONS.State, COUPONS.ZIP, COUPONS.PQBSSN, COUPONS.EmployerCode
ordered by the COUPONS.DateDue.

Like: select only records with a date range 01/01/2009 - 12/01/2009, and of those only select the 6 oldest entries.

I have monkeyed with this for a bit and am having no luck. I know this is pretty basic, but I just cant seem to make this work. Here is the SQL select I use to get the date from the table now.

SELECT COUPONS.DocType, COUPONS.PayTo, COUPONS.ContactName, COUPONS.ContactNumber,
COUPONS.DocFooter, COUPONS.PQBName, COUPONS.LetterDate, COUPONS.RetireeFirstName, 
COUPONS.RetireeLastName, COUPONS.Address1, COUPONS.Address2, COUPONS.City, 
COUPONS.State, COUPONS.ZIP, COUPONS.PQBSSN, COUPONS.EmployerCode, COUPONS.AmountDue, 
COUPONS.DateDue, Right([DateDue],4)+Left([DateDue],2)+Mid([datedue],4,2) AS SORTDATE
FROM COUPONS
ORDER BY COUPONS.DocType, COUPONS.PayTo, COUPONS.ContactName, COUPONS.ContactNumber, 
COUPONS.DocFooter, COUPONS.PQBName, COUPONS.LetterDate, Right([DateDue],4)+Left
([DateDue],2)+Mid([datedue],4,2);

Upvotes: 2

Views: 451

Answers (8)

richardtallent
richardtallent

Reputation: 35363

If you have control over the database but MUST use a text-based date, store your dates using the ODBC canonical format:

yyyy-mm-dd               // if there's no time element
yyyy-mm-dd HH:MM:ss      // if time is needed as well

This has a few distinct advantages:

  • World-friendly, for users who aren't in the US and may think mm-dd-yyyy means dd-mm-yyyy
  • Sorts by date naturally, so normal < and > operators work just fine (and those operations are doing a textual comparison, they never actually convert the text to a date).
  • Your business layer will likely be able to read dates in this format correctly without adjusting your code at all
  • If you have fields that don't have actual dates, this won't generate a CONVERT() error like many of the suggestions already posted. (For instance, if you are also dealing with dirty values like "Next Tuesday" or "N/A" that you can't clean out of the database.)

Converting your existing date data is a simple exercise of UPDATE with RIGHT(), LEFT(), etc., assuming your current date data is in a consistent format.

Once your data is stored in a format that can be queried more readily, it's a simple problem:

SELECT TOP 6 * FROM mytable WHERE mydate BETWEEN startdate AND enddate ORDER BY mydate DESC

As for your grouping problem, I don't understand the question well enough to propose an answer. But getting your date data stored in the most efficient text format will help sort everything else out.

Ok, I'm going to take a stab at your grouping problem:

SELECT DISTINCT DueDate, DocType, PayTo, ContactName, ContactNumber, [...other fields...]
FROM coupons c1
WHERE CDate(c1.DueDate) BETWEEN '01/01/2000' AND '01/01/2009'
  /* Here's where the "grouping" happens--actually just filtering out the others */
  AND (SELECT COUNT(*) FROM coupons c2 WHERE
    CDATE(c1.DueDate) >= CDATE(c2.DueDate)
    AND c2.DocType=c1.DocType
    AND c2.ContactName=c1.ContactName
    AND c2.ContactNumber=c1.ContactNumber
    [...test the other fields...]
    ) <= 6

I can't remember the ins and outs of Jet SQL enough to know if this subquery will work, but I think it will.

Upvotes: 1

JeffP
JeffP

Reputation: 1086

I think I understand your problem - let me give you a solution that doesn't get into dealing with your date issue - there are a number of solutions to that above.

Given this data:

   PQBSSN   DATE    PQBNAME
1   1/1/2009    A
1   1/2/2009    A
1   1/3/2009    A
1   1/4/2009    Z
1   1/5/2009    Z
1   1/6/2009    Z
2   1/1/2009    B
2   1/2/2009    B
2   1/3/2009    B
2   1/4/2009    B
2   1/5/2009    B
2   1/6/2009    B
3   1/1/2009    C
3   1/2/2009    C
3   1/3/2009    C
3   1/4/2009    C
3   1/5/2009    C
3   1/6/2009    C

SELECT C1.PQBSSN, C1.PQBNAME, C3.Date
FROM [SELECT DISTINCT CA.PQBSSN, CA.PQBNAME FROM COUPONS AS CA]. AS C1, 
     [SELECT DISTINCT CB.DATE FROM COUPONS AS CB]. AS C3
WHERE C3.DATE IN 
     (SELECT TOP 2 C2.DATE FROM COUPONS AS C2 WHERE C2.PQBSSN = C1.PQBSSN ORDER BY C2.DATE);

The breakdown:

The CA select gives the unique rows of non-date information

The CB select gives all the dates in the table

The "WHERE C3.DATE" select gives you the dates that apply to each matching group. You need to put checks in the WHERE of this select for every independent field if there isn't a unique key for the grouping rows.

This Gives: 

   PQBSS PQBNAME Date
    1   A   1/1/2009
    1   Z   1/1/2009
    2   B   1/1/2009
    3   C   1/1/2009
    1   A   1/2/2009
    1   Z   1/2/2009
    2   B   1/2/2009
    3   C   1/2/2009

I know this is a simplified version of your table, but I think it achieves your ends.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

answer not complete, working on it...

To restate the problem: the query is returning too many rows, you want ONLY six rows for each 'group' of distinct values for the seven first six columns listed in the ORDER BY clause.

You've already got the date issue worked out. Contrary to popular opinion, it is NOT necessary to cast to a DATETIME to get this query to work. The problem is the same whether you're ordering on a DATETIME expression or VARCHAR expression. You just want the "lowest" n values for EACH GROUP.

To get this result set in a single query, I think this is going to require a stopkey predicate with an inline view, does Access support common table expresssions?

for example.

WITH cte AS 
( SELECT ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS ROWNUM
       , ...
    FROM COUPONS c
)
SELECT ...
  FROM cte
 WHERE cte.ROWNUM <= 6

-or-

SELECT TOP 6 ... 
  FROM ...
 GROUP
    BY ...

answer not complete

sample SQL statement from OP, reformatted to be "human readable": 

SELECT c.DocType
     , c.PayTo
     , c.ContactName
     , c.ContactNumber
     , c.DocFooter
     , c.PQBName
     , c.LetterDate
     , c.RetireeFirstName
     , c.RetireeLastName
     , c.Address1
     , c.Address2
     , c.City
     , c.State
     , c.ZIP
     , c.PQBSSN
     , c.EmployerCode
     , c.AmountDue
     , c.DateDue
     , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2) AS SORTDATE
  FROM COUPONS c
 ORDER
    BY c.DocType
     , c.PayTo
     , c.ContactName
     , c.ContactNumber
     , c.DocFooter
     , c.PQBName
     , c.LetterDate
     , Right(c.[DateDue],4)+Left(c.[DateDue],2)+Mid(c.[DateDue],4,2)

Upvotes: 0

Bill
Bill

Reputation: 4585

If you are sure that you have date strings in ALL of your rows, the easiest VB for converting to a datevalue is: CDate([DateDue]). It will fail on NULL, though.

So then you can get the oldest rows with:

Select Top 6 *
From myTable
ORDER BY CDate([DateDue]) ASC

Upvotes: 1

Cloudy
Cloudy

Reputation: 2411

If I'm reading your problem right, it appears that you have a text field that contains date-like data, and you want to be able to sort on it chronologically (within a range).

After some quick Googling, it appears that Access has a CDate() function that you can use to convert the string data into dates. Secondly, once you've got that problem addressed, the actual query to select a date range will probably look something like this (at least in Sql Server -- the syntax for Access will probably be similar but may differ slightly):

SELECT TOP 6 [...]
FROM [...]
WHERE DateDue BETWEEN @BeginDate AND @EndDate
ORDER BY DateDue ASC

That is the general idea of how you can get the six oldest entries within a date range.

Upvotes: 0

Jack Marchetti
Jack Marchetti

Reputation: 15754

Since your question is hard to read (format dude!!) I'm going to go off your initial question.

how do you select the six oldest dates, if the dates are represented as chars.

You need to cast the datedue as a datetime, then do an order by:

SELECT top 6 CAST(datedue as datetime) as DateDue from test order by DateDue asc

Upvotes: 0

Diego
Diego

Reputation: 7562

I'm not an expert of Access, but I think you could use a "SELEC TOP", if your version of Access supports it:

SELECT TOP 6 COUPONS.DocType, COUPONS.PayTo, COUPONS.ContactName, COUPONS.ContactNumber, COUPONS.DocFooter, COUPONS.PQBName, COUPONS.LetterDate, COUPONS.RetireeFirstName, COUPONS.RetireeLastName, COUPONS.Address1, COUPONS.Address2, COUPONS.City, COUPONS.State, COUPONS.ZIP, COUPONS.PQBSSN, COUPONS.EmployerCode, COUPONS.AmountDue, COUPONS.DateDue, Right([DateDue],4)+Left([DateDue],2)+Mid([datedue],4,2) AS SORTDATE FROM COUPONS

ORDER BY Right([DateDue],4)+Left([DateDue],2)+Mid([datedue],4,2), COUPONS.DocType, COUPONS.PayTo, COUPONS.ContactName, COUPONS.ContactNumber, COUPONS.DocFooter, COUPONS.PQBName, COUPONS.LetterDate

Upvotes: 0

Peter
Peter

Reputation: 48958

Probably you are adding the date values like this 

Right([DateDue],4)+Left([DateDue],2)+Mid([datedue],4,2);

(eg : 1996 + 04 + 21 = 2021)

So try this in your order clause 

Right([DateDue],4),Left([DateDue],2),Mid([datedue],4,2);

Or try concatening the values rather than adding them : 

Right([DateDue],4) & Left([DateDue],2) & Mid([datedue],4,2);

(eg : 1996 + 04 + 21 = 19960421)

After that indeed just use the top clause. I didn't realized that could be a problem, sorry.

Upvotes: 0

Related Questions