troubleshooting Question

T-SQL--MAX Is Not Working III:  The Search for More Knowledge

Avatar of TBSupport
TBSupport asked on
Microsoft SQL Server 2008
9 Comments1 Solution186 ViewsLast Modified:
Hello:

Sigh.....I thought that I had this cleared up in a posting of mine from yesterday.  But, I don't.  For goodness sake, I want one flippin' TRXBEGDT record.  That's not what I'm getting, when I use MAX.

Once again, please review the query and let me know what to do for goodness sake.

I don't want to hear anything about rewrites, CTEs, new inner joins, etc.

TBSupport

SELECT    'MED' AS company, CAST('202EMPLPIM' AS CHAR(15)) AS rectype, CAST('16860' AS CHAR(16)) AS cocode, CAST(MED.dbo.UPR00100.SOCSCNUM AS CHAR(11)) 
                      AS ssn, CAST(MED.dbo.UPR00100.EMPLOYID AS CHAR(64)) AS empl_id, CAST(MED.dbo.UPR00100.SOCSCNUM AS CHAR(64)) AS user_id, CAST('A868' AS CHAR(4)) 
                      AS inhousenum, CONVERT(VARCHAR(8), MAX(MED.dbo.UPR00900.LSTPCKDT), 112) AS pasofdate, CAST(MED.dbo.UPR00100.FRSTNAME AS CHAR(64)) AS fn, 
                      CAST(MED.dbo.UPR00100.MIDLNAME AS CHAR(64)) AS mn, CAST(MED.dbo.UPR00100.LASTNAME AS CHAR(64)) AS ln, CAST('' AS CHAR(12)) AS suffix, 
                      CAST('' AS CHAR(5)) AS title, CAST(RTRIM(CAST(RIGHT(MED.dbo.UPR00100.SOCSCNUM, 10) AS VARCHAR(10))) + CAST(YEAR(MED.dbo.UPR00100.BRTHDATE) 
                      AS VARCHAR(4)) AS CHAR(8)) AS dfpin, CAST('Y' AS CHAR(1)) AS direct_login, CAST(MED.dbo.UPR00100.LOCATNID AS CHAR(12)) AS verdiv, 
                      CAST(MED.dbo.UPR00100.JOBTITLE AS CHAR(64)) AS jobtitle, CAST((CASE UPR00100.LASTDAYWORKED_I WHEN '1/1/1900' THEN 'A' ELSE 'G' END) AS CHAR(1)) 
                      AS eestatcd, CONVERT(VARCHAR(8), MED.dbo.UPR00100.STRTDATE, 112) AS mrhdate, CAST('' AS CHAR(1)) AS eestattype, CONVERT(VARCHAR(8), 
                      MED.dbo.UPR00100.STRTDATE, 112) AS orighiredate, CAST('' AS CHAR(8)) AS termdate, CAST('' AS CHAR(2)) AS termreason, CAST('9304' AS CHAR(10)) 
                      AS uctermreason, CAST(CASE LASTDAYWORKED_I WHEN '19000101' THEN '' ELSE CONVERT(VARCHAR(8), MED.dbo.UPR00100.LASTDAYWORKED_I, 112) 
                      END AS CHAR(8)) AS lastdaywrkf, CAST(MED.dbo.UPR00102.STATE AS CHAR(2)) AS wrkstate, CAST('100' AS CHAR(20)) AS wrkloccd, CAST('04249741' AS CHAR(15)) 
                      AS fein, CAST('09363930' AS CHAR(15)) AS suiacctnum, CONVERT(VARCHAR(8), MED.dbo.UPR00100.STRTDATE, 112) AS adjhiredate, CAST('000' AS CHAR(3)) 
                      AS yrsofserv, CAST('00' AS CHAR(2)) AS mthofserv, 
                      CAST((CASE WHEN UPR00400.PAYUNPER = 1 THEN '07' WHEN UPR00400.PAYUNPER = 2 THEN '06' WHEN UPR00400.PAYUNPER = 3 THEN '05' WHEN UPR00400.PAYUNPER
                       = 4 THEN '04' WHEN UPR00400.PAYUNPER = 5 THEN '03' WHEN UPR00400.PAYUNPER = 6 THEN '02' WHEN UPR00400.PAYUNPER = 7 THEN '01' WHEN UPR00400.PAYUNPER
                       = 8 THEN '08' END) AS CHAR(2)) AS payfreq, CAST('' AS CHAR(1)) AS twn_add, CAST('' AS CHAR(1)) AS ucx_add, CAST('' AS CHAR(1)) AS ignore_bp, 
                      CAST('U' AS CHAR(1)) AS add_ind_1, CAST('home' AS CHAR(20)) AS type_1, CAST(MED.dbo.UPR00102.ADDRESS1 AS CHAR(60)) AS I1_1, 
                      COALESCE (CAST(MED.dbo.UPR00102.ADDRESS2 AS CHAR(60)), '') AS I2_1, COALESCE (CAST(MED.dbo.UPR00102.ADDRESS3 AS CHAR(60)), '') AS I3_1, 
                      CAST(MED.dbo.UPR00102.CITY AS CHAR(60)) AS city_1, CAST(MED.dbo.UPR00102.STATE AS CHAR(2)) AS state_1, CAST(MED.dbo.UPR00102.ZIPCODE AS CHAR(16)) 
                      AS zip_1, CAST('US' AS CHAR(2)) AS country_1, CAST('' AS CHAR(20)) AS county_1, CAST('' AS CHAR(64)) AS province_1, CAST('' AS CHAR(1)) AS add_ind_2, 
                      CAST('' AS CHAR(20)) AS type_2, CAST('' AS CHAR(60)) AS I1_2, CAST('' AS CHAR(60)) AS I2_2, CAST('' AS CHAR(60)) AS I3_2, CAST('' AS CHAR(60)) AS city_2, 
                      CAST('' AS CHAR(2)) AS state_2, CAST('' AS CHAR(16)) AS zip_2, CAST('' AS CHAR(2)) AS country_2, CAST('' AS CHAR(20)) AS county_2, CAST('' AS CHAR(64)) 
                      AS province_2, CAST('' AS CHAR(1)) AS add_ind_3, CAST('' AS CHAR(20)) AS type_3, CAST('' AS CHAR(60)) AS I1_3, CAST('' AS CHAR(60)) AS I2_3, CAST('' AS CHAR(60)) 
                      AS I3_3, CAST('' AS CHAR(60)) AS city_3, CAST('' AS CHAR(2)) AS state_3, CAST('' AS CHAR(16)) AS zip_3, CAST('' AS CHAR(2)) AS country_3, CAST('' AS CHAR(20)) 
                      AS county_3, CAST('' AS CHAR(64)) AS province_3, CAST('' AS CHAR(1)) AS add_ind_4, CAST('' AS CHAR(20)) AS type_4, CAST('' AS CHAR(60)) AS I1_4, 
                      CAST('' AS CHAR(60)) AS I2_4, CAST('' AS CHAR(60)) AS I3_4, CAST('' AS CHAR(60)) AS city_4, CAST('' AS CHAR(2)) AS state_4, CAST('' AS CHAR(16)) AS zip_4, 
                      CAST('' AS CHAR(2)) AS country_4, CAST('' AS CHAR(20)) AS county_4, CAST('' AS CHAR(64)) AS province_4, CAST('' AS CHAR(1)) AS home_phone_ind, 
                      CAST('' AS CHAR(3)) AS home_phone_country, CAST('' AS CHAR(16)) AS home_phone, CAST('' AS CHAR(1)) AS work_phone_ind, CAST('' AS CHAR(3)) 
                      AS work_phone_country, CAST('' AS CHAR(16)) AS work_phone, CAST('' AS CHAR(6)) AS work_phone_ext, CAST('' AS CHAR(1)) AS cell_phone_ind, CAST('' AS CHAR(3)) 
                      AS cell_phone_country, CAST('' AS CHAR(16)) AS cell_phone, CAST('' AS CHAR(1)) AS pager_ind, CAST('' AS CHAR(3)) AS pager_country, CAST('' AS CHAR(16)) AS pager, 
                      CAST('' AS CHAR(6)) AS pager_pin, CAST('' AS CHAR(1)) AS fax_ind, CAST('' AS CHAR(3)) AS fax_country, CAST('' AS CHAR(16)) AS fax, CAST('' AS CHAR(6)) AS fax_ext, 
                      CAST('' AS CHAR(1)) AS other_ind, CAST('' AS CHAR(3)) AS other_phone_country, CAST('' AS CHAR(16)) AS other_phone, CAST('' AS CHAR(6)) AS other_phone_ext, 
                      CAST('' AS CHAR(256)) AS email1, CAST('' AS CHAR(256)) AS email2, CAST('' AS CHAR(256)) AS email3, CAST('' AS CHAR(256)) AS email4, CAST('' AS CHAR(256)) 
                      AS email5, CAST('' AS CHAR(256)) AS email6, CAST((CASE UPR00100.MARITALSTATUS WHEN '1' THEN 'M' WHEN '2' THEN 'S' ELSE 'O' END) AS CHAR(1)) 
                      AS marital_status, CAST((CASE UPR00100.GENDER WHEN '1' THEN 'M' WHEN '2' THEN 'F' ELSE 'U' END) AS CHAR(1)) AS gender, 
                      CAST(CASE UPR00100.BRTHDATE WHEN '1/1/1900' THEN '' ELSE CONVERT(VARCHAR(8), UPR00100.BRTHDATE, 112) END AS CHAR(8)) AS birth_date, 
                      CAST('' AS CHAR(256)) AS employer_def_1, CAST('' AS CHAR(256)) AS employer_def_2, CAST('' AS CHAR(256)) AS employer_def_3, CAST('' AS CHAR(256)) 
                      AS employer_def_4, CAST('' AS CHAR(1)) AS employer_log_def_1, CAST('' AS CHAR(1)) AS employer_log_def_2, CAST('' AS CHAR(1)) AS employer_log_def_3, 
                      CAST('' AS CHAR(1)) AS employer_log_def_4, CAST('' AS CHAR(1)) AS filler2, CAST('I' AS CHAR(1)) AS consent_flag, CAST('00000000' AS CHAR(8)) AS consent_date, 
                      CAST('' AS CHAR(1)) AS primary_email, CAST('' AS CHAR(1)) AS secondary_email, CAST('' AS CHAR(7)) AS address, CAST('' AS CHAR(4)) AS daytime_phone, 
                      CAST('' AS CHAR(4)) AS evening_phone, CAST('' AS CHAR(73)) AS filler, CAST('71912' AS CHAR(5)) AS tci_id, CAST('' AS CHAR(1)) AS wotc_add, CAST('' AS CHAR(1)) 
                      AS tics_add, CAST('' AS CHAR(32)) AS tax_group_name, MAX(CONVERT(VARCHAR(8), MED.dbo.UPR30300.TRXBEGDT, 112)) AS pay_process_begin_date, 
                      CAST('' AS CHAR(1)) AS filler1, CAST('' AS CHAR(1)) AS filler8, CAST('' AS CHAR(1)) AS filler3, CAST('' AS CHAR(1)) AS filler4, CAST('' AS CHAR(1)) AS filler5, 
                      CAST('' AS CHAR(1)) AS filler6, CAST('' AS CHAR(1)) AS filler7, CAST('' AS CHAR(1)) AS Expr1, CAST('' AS CHAR(1)) AS filler9, CAST('' AS CHAR(143)) AS filler10
FROM         MED.dbo.UPR30300 INNER JOIN
                      MED.dbo.UPR00900 ON MED.dbo.UPR30300.CHEKNMBR = MED.dbo.UPR00900.LPCHKNUM AND 
                      MED.dbo.UPR30300.EMPLOYID = MED.dbo.UPR00900.EMPLOYID INNER JOIN
                      MED.dbo.UPR00100 ON MED.dbo.UPR30300.EMPLOYID = MED.dbo.UPR00100.EMPLOYID INNER JOIN
                      MED.dbo.UPR00102 ON MED.dbo.UPR00100.EMPLOYID = MED.dbo.UPR00102.EMPLOYID INNER JOIN
                      MED.dbo.UPR00400 ON MED.dbo.UPR00100.EMPLOYID = MED.dbo.UPR00400.EMPLOYID
WHERE     (MED.dbo.UPR30300.UPRTRXAM > 0) and MED.dbo.UPR00100.SOCSCNUM = '018786594'
GROUP BY MED.dbo.UPR00900.EMPLOYID, MED.dbo.UPR00100.EMPLOYID, MED.dbo.UPR00100.SOCSCNUM, 
MED.dbo.UPR00100.FRSTNAME, 
                      MED.dbo.UPR00100.MIDLNAME, MED.dbo.UPR00100.LASTNAME, MED.dbo.UPR00100.JOBTITLE, MED.dbo.UPR00100.STRTDATE, 
                      MED.dbo.UPR00100.BENADJDATE, MED.dbo.UPR00400.PAYUNPER, MED.dbo.UPR00102.ADDRESS1, MED.dbo.UPR00102.ADDRESS2, 
                      MED.dbo.UPR00102.ADDRESS3, MED.dbo.UPR00102.CITY, MED.dbo.UPR00102.STATE, MED.dbo.UPR00102.ZIPCODE, MED.dbo.UPR00102.PHONE1, 
                      MED.dbo.UPR00100.MARITALSTATUS, MED.dbo.UPR00100.GENDER, MED.dbo.UPR00100.BRTHDATE, MED.dbo.UPR00100.LASTDAYWORKED_I, 
                      MED.dbo.UPR30300.PAYROLCD, MED.dbo.UPR30300.PAYRATE, MED.dbo.UPR00100.LOCATNID
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros