Max Date selection

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

acardulloAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this article shall solve the problem:
http://www.experts-exchange.com/A_3203.html
0
 
acardulloAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
brd24gorCommented:
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
 
acardulloAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
brd24gorCommented:
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
 
acardulloAuthor Commented:
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
 
acardulloAuthor Commented:
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
 
brd24gorCommented:
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
All Courses

From novice to tech pro — start learning today.