[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL for first record with 2 columns that might indicate which is first

Posted on 2009-12-20
15
Medium Priority
?
272 Views
Last Modified: 2013-11-05
I use a logging application for ham radio which runs on top of MS Access. The record for each contact is all in 1 table. The are 2 ways to confirm a contact - Paper or Electronic. I would like to generate a report of the first confirmation (called a QSL - not be confused with SQL) for each country and by which QSL method (Paper or Electronic) it was confirmed by. The table name is
TABLE_HRD_CONTACTS_V01

The relevant columns are:
COL_CALL - the call sign of the operator I made a contact with
COL_TIME_ON - the date/time of the contact, not the confirmation
COL_COUNTRY - the country the operator is located in
COL_QSL_RCVD - contains a Y if a paper confirmation was received
COL_QSLRDATE - contains the date a paper confirmation was received
COL_LOTW_QSL_RCVD - contains a Y if an electronic confirmation was received
COL_LOTW_QSLRDATE - contains the date an electronic confirmation was received

A record in the table can be confirmed by neither method, by either method, or by both methods.  

I would like to see the results sorted in desc order of the confirmation date. If a record was confirmed by both methods I would like to only see the first (oldest) record based on the confirmation date for each country, not the contact date. The contact date/time (COL_TIME_ON) should be used if there are multiple records found for the same country on the same date. COL_CALL is just doe display to help me identify the record and I don't think is used in the criteria.

Let me know if there's any further info that would help.




Sample data with COL_ removed from column names and time not shown in TIME_ON:

CALL  TIME_ON    COUNTRY QSL_RCVD QSLRDATE   LOTW_QSL_RCVD LOTW_QSLRDATE
----  -------    ------- -------- --------   ------------- -------------
G1XXX 12/8/2008  ENGLAND                     Y             11/5/2009
G1XXX 1/9/2009   ENGLAND                     Y             11/5/2009
G2XXX 10/9/2009  ENGLAND                     Y             11/10/2009

CO8XX 9/24/2009  CUBA    Y        11/2/2009 
CO9XX 10/25/2009 CUBA                        Y             11/10/2009

K1XX  9/1/2008   US
K2XX  8/1/2009   US      Y        11/1/2009  Y             10/24/2009
K3XX  8/15/2009  US      Y        10/25/2009 
K4XX  8/2/2009   US                          Y             10/24/2009

SP2XX 11/22/2008 POLAND                      Y             10/2/2009
SP2XX 11/23/2008 POLAND  Y        10/1/2009
SP3XX 11/24/2008 POLAND  Y        9/28/2009  
SP3XX 11/25/2008 POLAND                      Y             10/1/2009


Sample report based on the above data (I think if I did it right):

COL_CALL  COL_TIME_ON  COL_COUNTRY  COL_METHOD  COL_QSL_DATE
--------  -----------  -----------  ----------  ------------
G1XXX     12/8/2008    ENGLAND      Electronic  11/5/2009
CO8XX     9/24/2009    CUBA         Paper       11/2/2009
K2XX      8/1/2009     US           Electronic  10/24/2009
SP3XX     11/24/2008   POLAND       Paper       9/28/2009

Open in new window

0
Comment
Question by:david_levine
  • 7
  • 7
15 Comments
 
LVL 5

Expert Comment

by:gemailj
ID: 26091148
i hope that work with you
select COL_CALL, min(COL_TIME_ON) as COL_TIME_ON, COL_COUNTRY, 
min(case LOTW_QSL_RCVD 
when 'Y' then 'Electronic' 
ELSE case QSL_RCVD when 'Y' then 'Paper' end
end) as COL_METHOD, 
min(case LOTW_QSL_RCVD 
when 'Y' then LOTW_QSLRDATE 
ELSE case QSL_RCVD when 'Y' then QSLRDATE end
end) as COL_QSL_DATE
 from TABLE_HRD_CONTACTS_V01

Open in new window

0
 
LVL 9

Author Comment

by:david_levine
ID: 26091242
gemailj,

Thanks for the quick response. If I open Access and create a new query with your SQL pasted in to it, I get the following message when I try to run it:

Syntax error (missing operator) in query expression 'min(case LOTW_QSL_RCVD when 'Y' then 'Electronic' ELSE case QSL_RCVD when 'Y' then 'Paper' end end'

Not that I believe everything I find on the Internet, but a quick search for Access Case indicates it is a VBA only construct. http://www.techonthenet.com/access/functions/advanced/case.php

If I need to do something different within Access to run the above, let me know.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091459
Are you sure you would like to see the result based on the oldest confirmation? I would say the older contact date is more valuable even if it was confirmed later.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:david_levine
ID: 26091484
pcelba:

The oldest confirmation date is what is most relevant for what I want this query to represent. Lets say I had 3 contacts with England. The first confirmation (QSL) is the most relevant, even if it isn't the first contact.

For when I first made a contact with each country, then as you stated, the oldest contact would be most relevant. If I want to determine which countries I've confirmed and when, the relevance is on the earliest confirmation date regardless of when the contact was made.

Any help you can provide is greatly appreciated!
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091500
OK, I am working on it. The first part is done already.
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 26091607
Hope you have Access 2007. I am not sure if older Access engine can process this query:
SELECT t.COL_CALL, t.COL_TIME_ON, t.COL_COUNTRY, 
       IIF(t.COL_QSL_RCVD="Y" And COL_QSLRDATE = t3.FirstConfirmation, "Paper", "Electronic") As COL_METHOD, 
       t3.FirstConfirmation As COL_QSLDATE
  FROM TABLE_HRD_CONTACTS_V01 t
  INNER JOIN (
       SELECT t1.COL_COUNTRY, MIN(t1.COL_TIME_ON) As FirstContact, MIN(t2.MinDate) As FirstConfirmation
         FROM TABLE_HRD_CONTACTS_V01 t1
         INNER JOIN (
            SELECT COL_COUNTRY, Min(IIf(COL_QSLRDATE<COL_LOTW_QSLRDATE Or IsNull(COL_LOTW_QSLRDATE), COL_QSLRDATE, COL_LOTW_QSLRDATE)) AS MinDate
            FROM TABLE_HRD_CONTACTS_V01
            WHERE COL_QSL_RCVD="Y" OR COL_LOTW_QSL_RCVD="Y"
            GROUP BY COL_COUNTRY ) t2 
       ON t1.COL_COUNTRY = t2.COL_COUNTRY AND (t1.COL_QSLRDATE = t2.Mindate OR t1.COL_LOTW_QSLRDATE = t2.Mindate)
       GROUP BY t1.COL_COUNTRY) t3 
    ON t.COL_COUNTRY = t3.COL_COUNTRY AND t.COL_TIME_ON = t3.FirstContact
ORDER BY t3.FirstConfirmation DESC
;

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091619
You could also split above query to several simpler queries for better readability...
0
 
LVL 9

Author Comment

by:david_levine
ID: 26091700
Getting close I think. The format of the report is right but the displayed results don't seem correct. It might be hard to explain why without breaking apart the query to see what isn't matching right.

It looks the data often has a date in col_qslrdate even if col_qsl_rcvd = 'N' and the same goes for col_lotw_qslrdate even if col_lotw_qsl_rcvd = 'N'

I think the queries that are using the dates without explicitly make sure that column is Y is causing errant results.

 

0
 
LVL 9

Author Comment

by:david_levine
ID: 26091738
In case you haven't started looking at it again, the subselect of:

            SELECT COL_COUNTRY, Min(IIf(COL_QSLRDATE<COL_LOTW_QSLRDATE Or IsNull(COL_LOTW_QSLRDATE), COL_QSLRDATE, COL_LOTW_QSLRDATE)) AS MinDate
            FROM TABLE_HRD_CONTACTS_V01
            WHERE COL_QSL_RCVD="Y" OR COL_LOTW_QSL_RCVD="Y"
            GROUP BY COL_COUNTRY

is the one that is returning unexpected results based on the dates being there even if the value is N.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091766
First of all you didn't mention "N" in "rcvd" columns in your question. Looking at your data I supposed "Y" and existing date only. If you have "N" and existing date then it is not correct.

You should clarify what everything can be in your data.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091799
If you require to look at the "Y" value then you may use following query:
SELECT COL_COUNTRY, Min(IIf((COL_QSLRDATE<COL_LOTW_QSLRDATE AND COL_QSL_RCVD="Y" And COL_LOTW_QSL_RCVD="Y") Or COL_LOTW_QSL_RCVD<>"Y", COL_QSLRDATE, COL_LOTW_QSLRDATE)) AS MinDate 
            FROM TABLE_HRD_CONTACTS_V01 
            WHERE COL_QSL_RCVD="Y" OR COL_LOTW_QSL_RCVD="Y" 
            GROUP BY COL_COUNTRY

Open in new window

0
 
LVL 9

Author Comment

by:david_levine
ID: 26091831
My example data does show NULL for the other fields, so I'm sorry about that. I was trying to represent the data in a way that made if clear to what it should be. I should have shown N and dates and N and blanks for some of the ones instead of blanks for all.

The latest change is very close. What I noticed is the record shown for the US has N for both records. A record for the next oldest seems correct. The record for the 3 oldest confirmed country shows the right call but the date is off by 2 days. I'll look at it in pieces and see what I can tell.
0
 
LVL 9

Author Comment

by:david_levine
ID: 26091871
Ok, you did it!!! Many thanks!!

The 2 issues I saw were data issues where the date field contained an invalid value when the flag was set to Y. When I fixed it, the results seem to look perfect.

Many, many thanks and sorry about the confusion with the sample data.
0
 
LVL 9

Author Closing Comment

by:david_levine
ID: 31668237
Thanks for an elegant solution!
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26091925
Data will be confusing us always. Youe are welcome!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question