Solved

Max Date selection

Posted on 2011-03-11
10
383 Views
Last Modified: 2012-08-13
Hello,
I have the query shown in the code window. THe resulting list has every completed service call we had from 01/01/2009  showing the rep who handlet it and what zip code the customer is in.
The list will show duplicates rep zip and dates but what i want is to show the last rep to work on that piece of equipment.
so if the result were
Rep,ZIP,date
TA0245,33760,02/05/2009
MI4578,33760,05/09/2010

then i want the last record to show becuas the date is higer.  hope this makes sense.  This of course needs to be applied to all the records  so i cant use TOp 1 or something like that.

Thank you
SELECT distinct SERV_CALL.REPRESENTATIVE AS REP, CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 AS ZIP, SERV_CALL.COMPLETE_DATE_SCA
FROM         SERV_CALL INNER JOIN
                      SERIAL_EQUIP ON SERV_CALL.SERIAL = SERIAL_EQUIP.SERIAL INNER JOIN
                      CUSTOMR_SHIP_TO ON SERIAL_EQUIP.CUSTOMER_NUMBER = CUSTOMR_SHIP_TO.CUSTOMER_NUMBER
WHERE     (SERV_CALL.COMPLETE_DATE_SCA > '01/01/2009') AND (CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 <> '')
order by SERV_CALL.COMPLETE_DATE_SCA DESC

Open in new window

0
Comment
Question by:acardullo
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35109810
this article shall solve the problem:
http://www.experts-exchange.com/A_3203.html
0
 

Author Comment

by:acardullo
ID: 35109845
i understand about the whole "teach a man how to fish thing" but could you give me the fish right now becuase i need this rather quick.  Thank you
0
 
LVL 3

Expert Comment

by:brd24gor
ID: 35109858
Give this a try. I took out the code involving serial numbers to try and get to the root of your request. Once you get that working, then it will be little work to get that back in.
SELECT distinct sc.SERV_CALL.REPRESENTATIVE AS REP, sc.CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 AS ZIP, sc.SERV_CALL.COMPLETE_DATE_SCA as DATE
FROM SERV_CALL sc
INNER JOIN (
    SELECT sc1.SRV_CALL.REPRESENTATIVE as REP, max(sc1.SERV_CALL.COMPLETE_DATE_SCA) as MAXDATE
    FROM SERV_CALL sc1
    GROUP BY REP )
    sc1 ON sc.SRV_CALL.REPRESENTATIVE = sc1.SRV_CALL.REPRESENTATIVE AND sc.SERV_CALL.COMPLETE_DATE_SCA = sc1.SERV_CALL.COMPLETE_DATE_SCA
WHERE     (SERV_CALL.COMPLETE_DATE_SCA > '01/01/2009') AND (CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 <> '')
order by SERV_CALL.COMPLETE_DATE_SCA DESC

Open in new window

0
 

Author Comment

by:acardullo
ID: 35109871
brd24gor  your code result in this message

Msg 207, Level 16, State 1, Line 6
Invalid column name 'REP'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'SRV_CALL'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'SERV_CALL'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'SRV_CALL'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'SRV_CALL'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'SERV_CALL'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'SERV_CALL'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "SERV_CALL.COMPLETE_DATE_SCA" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SERV_CALL'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'CUSTOMR_SHIP_TO'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SERV_CALL'.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "SERV_CALL.COMPLETE_DATE_SCA" could not be bound.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35109884
one example would be:
SELECT sc.REPRESENTATIVE AS REP
     , c.CUS_POSTAL_CODE_1 AS ZIP
     , sc.COMPLETE_DATE_SCA
FROM SERV_CALL sc 
JOIN SERIAL_EQUIP e
  ON sc.SERIAL = e.SERIAL 
JOIN CUSTOMR_SHIP_TO c
  ON e.CUSTOMER_NUMBER = c.CUSTOMER_NUMBER
WHERE sc.COMPLETE_DATE_SCA > '01/01/2009'
  AND c.CUS_POSTAL_CODE_1 <> ''
  AND sc.COMPLETE_DATE_SCA = ( 
       SELECT MAX(i.COMPLETE_DATE_SCA)
         FROM SERV_CALL i
        WHERE i.SERIAL = e.SERIAL 
     )
ORDER BY sc.COMPLETE_DATE_SCA DESC

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:brd24gor
ID: 35109914
Oops, try this
SELECT distinct sc.REPRESENTATIVE AS REP, sc.CUS_POSTAL_CODE_1 AS ZIP, sc.COMPLETE_DATE_SCA as DATE
FROM SERV_CALL sc
INNER JOIN (
    SELECT sc1.REPRESENTATIVE as REP, max(sc1.COMPLETE_DATE_SCA) as MAXDATE
    FROM SERV_CALL sc1
    GROUP BY REP )
    sc1 ON sc.REPRESENTATIVE = sc1.REPRESENTATIVE AND sc.COMPLETE_DATE_SCA = sc1.COMPLETE_DATE_SCA
WHERE     (SERV_CALL.COMPLETE_DATE_SCA > '01/01/2009') AND (CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 <> '')
order by SERV_CALL.COMPLETE_DATE_SCA DESC

Open in new window

0
 

Author Comment

by:acardullo
ID: 35109959
angel.  that can't be rightn.  when i run your code it get in excess of 13k records.  When i
your code wiht an added select distinct REp,ZIP wrapper i only get 3009.  So that means that there are Rep,Zip  combo duplicates which is what im trying to avoid.  Remeber,  i am trying to find the last rep's that have worder in that zip the last time.

Thanks
0
 

Author Comment

by:acardullo
ID: 35110260
brd24  still getting errors on your code.

Msg 207, Level 16, State 1, Line 6
Invalid column name 'REP'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'REPRESENTATIVE'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'COMPLETE_DATE_SCA'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "SERV_CALL.COMPLETE_DATE_SCA" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'CUS_POSTAL_CODE_1'.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "SERV_CALL.COMPLETE_DATE_SCA" could not be bound.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
 
LVL 3

Expert Comment

by:brd24gor
ID: 35110312
Did some more testing and cleaned my code into working condition. Sorry for putting that up...I was trying to quickly get you something.
SELECT sc.REPRESENTATIVE, sc.CUS_POSTAL_CODE_1, sc.COMPLETE_DATE_SCA
FROM SERV_CALL sc
INNER JOIN (
    SELECT REPRESENTATIVE as REP, max(COMPLETE_DATE_SCA) as DATE
    FROM SERV_CALL
    group by representative
	) sc1
ON sc.representative = sc1.REP AND sc.complete_date_sca = sc1.date
group by sc.representative, sc.complete_date_sca, sc.cus_postal_code_1

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35112408
Try this.
SELECT * 
    FROM (SELECT SERV_CALL.REPRESENTATIVE                                                                         AS REP,
                 CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1                                                                AS ZIP,
                 SERV_CALL.COMPLETE_DATE_SCA, 
                 ROW_NUMBER() 
                   OVER(PARTITION BY CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 ORDER BY SERV_CALL.COMPLETE_DATE_SCA DESC) rn
            FROM SERV_CALL 
                 INNER JOIN SERIAL_EQUIP 
                   ON SERV_CALL.SERIAL = SERIAL_EQUIP.SERIAL 
                 INNER JOIN CUSTOMR_SHIP_TO 
                   ON SERIAL_EQUIP.CUSTOMER_NUMBER = CUSTOMR_SHIP_TO.CUSTOMER_NUMBER 
           WHERE (SERV_CALL.COMPLETE_DATE_SCA > '01/01/2009') 
                 AND (CUSTOMR_SHIP_TO.CUS_POSTAL_CODE_1 <> '')) t1 
   WHERE rn = 1 
ORDER BY COMPLETE_DATE_SCA DESC

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now