Solved

Max Date selection

Posted on 2011-03-11
10
387 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 143

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 143

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
 
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 41

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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