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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

How to write a SQL Statement to combine the value in fields

I am developing an Access application using Access as the front end and SQL Server 2000 as the back end database.

Let's assume I have an Access table comprised to the following fields:
Is the a way to write a SQL Statement to SELECT UNIQUE records based on the Account field
so that the output looks like the values in the CODE section that follows based on the sample records that follow.

The goal is to display the values in fields Addr1, Addr2, and Addr3 for each UNIQUE Account based on the most recent value in the Date fields.
So for Account 123456, the Address fields extracted are based on the latest corresponding date of 2007-02-28.
For Account 683476, the Address fields extracted are based on the latest corresponding date of 2007-03-31.
For Account 856788, the Address fields extracted are based on the latest corresponding date of 2007-03-31.



Account   DEC06         JAN07           FEB07          MAR07         Addr1     Addr2        Addr3
123456    2006-12-31                                                                123 ST    JUNIPER     TULIP
123456                                              2007-02-28                      345 ST    ASTOR      ROSE
123456                        2007-01-31                                            632 ST    WESLEY    LILLY
683476                                                                  2007-03-31  999 ST    JOHN          MATT
683476                                              2007-02-28                      777 ST    HENRY       AMY
683476    2006-12-31                                                                345 ST    OLGA         LUIS
683476                        2007-01-31                                            888 ST    TOM            DEB
856788                                                                  2007-03-31  623 ST    MIKE           ALAN
856788    2006-12-31                                                                536 ST    BILLY         FRED
123456    2006-12-31 2007-01-31   2007-02-28                    345 ST     ASTOR       ROSE
683476    2006-12-31 2007-01-31   2007-02-28  2007-03-31        999 ST    JOHN          MATT
856788    2006-12-31                          2007-03-31        623 ST    MIKE           ALAN

Open in new window

0
zimmer9
Asked:
zimmer9
  • 3
  • 2
1 Solution
 
lwadwellCommented:
try ...
SELECT t1.account, 
       max(t1.dec06) as dec06, 
       max(t1.jan07) as jan07, 
       max(t1.feb07) as feb07, 
       max(t1.mar07) as mar07, 
       v1.addr1, 
       v1.addr2, 
       v1.addr3
  FROM addrdata t1
 INNER JOIN (SELECT a1.account, a1.addr1, a1.addr2, a1.addr3
               FROM addrdata a1
              WHERE datevalue(nz(mar07,nz(feb07,nz(jan07,dec06)))) = (SELECT max(datevalue(nz(mar07,nz(feb07,nz(jan07,dec06))))) 
                                                                        FROM addrdata a2 
                                                                       WHERE a1.account = a2.account 
                                                                       GROUP BY a2.account)) v1
         ON t1.account = v1.account
 GROUP BY t1.account, v1.addr1, v1.addr2, v1.addr3

Open in new window

0
 
zimmer9Author Commented:
When I execute the SQL Statement, I get the following message:

Server: Msg 195, Level 15, State 10, Line 13
'nz' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 14
'nz' is not a recognized function name.
0
 
zimmer9Author Commented:
Do you know how would I rewrite the SQL statement using ISNULL since 'nz' is not a recognized function name ?
0
 
zimmer9Author Commented:
DateValue is not a recognized function name in SQL Server 2000.

Do you know how I could used CONVERT or some other function instead ?
0
 
lwadwellCommented:
zimmer9,

Sorry ... that was all written using MS Access functions.  In SQL server we can use the COALESCE function to achieve what we need.

I have changed it below to use COALESCE, I had tested this in Access - but haven't done so in SQL Server.

lwadwell
SELECT t1.account, 
       max(t1.dec06) as dec06, 
       max(t1.jan07) as jan07, 
       max(t1.feb07) as feb07, 
       max(t1.mar07) as mar07, 
       v1.addr1, 
       v1.addr2, 
       v1.addr3
  FROM addrdata t1
 INNER JOIN (SELECT a1.account, a1.addr1, a1.addr2, a1.addr3
               FROM addrdata a1
              WHERE COALESCE(mar07,feb07,jan07,dec06) = (SELECT max(COALESCE(mar07,(feb07,(jan07,dec06)) 
                                                           FROM addrdata a2 
                                                          WHERE a1.account = a2.account 
                                                          GROUP BY a2.account)) v1
         ON t1.account = v1.account
 GROUP BY t1.account, v1.addr1, v1.addr2, v1.addr3

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now