Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Unanswered: Need help writing a query to display all records

    Hi! I'm having a terrible time trying to write this query and I could definitely use some help. I'm trying to do an auto report where the user selects a pay period and it returns all eligible users and their timesheets for that period, regardless of whether they did a timesheet then or not. So, no matter how many timesheets were submitted (even if zero), the result set should always be the same - just with different timesheets.

    I can get it to return all users, however the timesheets are showing as NULL even for payperiods that have them. I've been struggling with this all day and I can't figure out where I'm screwing up - so I could definitely use some other eyes on this.

    Thank you so much!
    -T

    My SQL:
    Code:
    SELECT DISTINCT lkpPayPeriods.PayPeriodEnd, T.TimesheetID, PR01EMF.EMF_EMPL_NAME AS UserName
    	
    FROM lkpPayPeriods CROSS APPLY DeltekData..PR01EMF
    
    LEFT JOIN tblTimesheet T ON 
    RTRIM(LTRIM(PR01EMF.EMF_EMPL_ID)) = T.EmployeeNum
    AND lkpPayPeriods.PayPeriodID = T.TimesheetID 
    
    LEFT JOIN DeltekData..PR01EMF S ON
    S.EMF_EMPL_ID = LEFT(PR01EMF.EMF_SUPERVISOR, 5)
    
    WHERE PR01EMF.EMF_STATUS='A' AND LEFT(PR01EMF.EMF_USERDEF_CODE14, 1) = 'Y'
    AND lkpPayPeriods.PayPeriodID = 4

    tblTimesheet:
    Code:
    TimesheetID	EmployeeNum	PayPeriodID	CreatedDate		CreatedBy	ModifiedDate		ModifiedBy
    ------------------------------------------------------------------------------------------------------------------------------
    10048		01600     	3		2010-12-06 12:25:58.740	mangelou	2010-12-09 15:30:26.133	hlangston
    10053		02123     	3		2010-12-09 14:02:19.610	splath		2010-12-09 14:04:14.940	splath
    10054		00001     	3		2010-12-09 14:33:45.203	hlangston	2010-12-09 15:21:54.090	hlangston
    10055		04573     	3		2010-12-09 14:37:50.087	owilde		NULL			NULL
    10056		02123     	2		2010-12-20 14:14:43.263	splath		NULL			NULL
    10057		01600     	2		2010-12-20 15:27:11.703	mangelou	NULL			NULL
    10058		00397     	2		2010-12-20 15:32:59.270	scrane		NULL			NULL
    10059		04573     	4		2010-12-22 17:04:42.913	owilde		NULL			NULL
    10060		00001     	4		2010-12-27 14:13:08.917	hlangston	NULL			NULL
    10061		04573     	4		2010-12-27 14:17:21.837	owilde		2010-12-31 10:08:33.470	owilde	
    10062		04573     	4		2011-01-02 00:36:52.583	owilde		2011-01-03 16:07:09.477	owilde
    10063		01600     	5		2011-01-05 16:02:24.277	mangelou	NULL			NULL
    10064		04573     	5		2011-01-06 13:19:19.560	owilde		NULL			NULL
    lkpPayPeriods:
    Code:
    PayPeriodID	PayPeriodStart		PayPeriodEnd
    -----------------------------------------------------------
    2		2010-11-22 00:00:00	2010-12-05 00:00:00
    3		2010-12-06 00:00:00	2010-12-19 00:00:00
    4		2010-12-20 00:00:00	2011-01-02 00:00:00
    5		2011-01-03 00:00:00	2011-01-16 00:00:00
    PR01EMF:
    Code:
    EMF_EMPL_ID	EMF_EMPL_NAME		EMF_SUPERVISOR	EMF_STATUS	EMF_USERDEF_CODE14
    ------------------------------------------------------------------------------------------
    00001    	Langston, Hughes	00002 J PRELUT	A		Y 
    00002		Prelutsky, Jack		00001 H LANGST	A		N
    00011    	Blake, William		00002 J PRELUT	A		Y 
    00150    	Silverstein, Shel       00001 H LANGST 	A		Y
    00397    	Crane, Stephen	       	01600 M ANGELO	A		Y 
    00403		Brooks, Gwendolyn	08464 A AKHMAT	A		N
    01600    	Angelou, Maya		00150 S SILVER	A		Y 
    01635    	Pablo, Neruda          	00001 H LANGST 	A		Y 
    01853    	Frost, Robert	        02937 S HEANEY	A		Y 
    01738    	Ginsberg, Alan		00403 G BROOKS	A		Y 
    02108    	Dickinson, Emily        00403 G BROOKS	A		Y 
    02123    	Plath, Sylvia		01600 M ANGELO	A		Y 
    02563    	Barnes, William		00001 H LANGST 	A		Y 
    02937    	Heaney, Seamus          00150 S SILVER	A		Y 
    04573    	Wilde, Oscar		00001 F LANGST 	A		Y

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I thought CROSS APPLY was only used for table valued functions. I don't see Supervisor used anywhere, so i took it out of my own example. Does this work better for you?
    Code:
    SELECT DISTINCT lkpPayPeriods.PayPeriodEnd, 
    	T.TimesheetID, 
    	PR01EMF.EMF_EMPL_NAME AS UserName
    FROM 
    	DeltekData..PR01EMF LEFT JOIN 
    	(lkpPayPeriods inner join
    	tblTimesheet T on lkpPayPeriods.PayPeriodID = T.TimesheetID) 
    		on RTRIM(LTRIM(PR01EMF.EMF_EMPL_ID)) = T.EmployeeNum	
    WHERE PR01EMF.EMF_STATUS='A' 
      AND LEFT(PR01EMF.EMF_USERDEF_CODE14, 1) = 'Y'
      AND lkpPayPeriods.PayPeriodID = 4

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Hi MCrowley,

    Thanks for responding! I originally was using DeltekData..PR01EMF, lkpPayPeriods but I got an error of "The multi-part identifier 'lkpPayPeriods.PayPeriodID' could not be bound.", so I switched to CROSS APPLY.

    I tried your suggestion, but it didn't seem to help - it didn't return any rows

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    SELECT DISTINCT lkpPayPeriods.PayPeriodEnd, 
    	T.TimesheetID, 
    	PR01EMF.EMF_EMPL_NAME AS UserName
    FROM 
    	DeltekData..PR01EMF LEFT JOIN 
    	(lkpPayPeriods inner join
    	tblTimesheet T on lkpPayPeriods.PayPeriodID = T.TimesheetID) 
    		on RTRIM(LTRIM(PR01EMF.EMF_EMPL_ID)) = T.EmployeeNum	
    WHERE PR01EMF.EMF_STATUS='A' 
      AND LEFT(PR01EMF.EMF_USERDEF_CODE14, 1) = 'Y'
      AND isnull(lkpPayPeriods.PayPeriodID, 4) = 4
    How about this?

  5. #5
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    That now pulls all the people, but every PayPeriodEnd and TimesheetID column is NULL - even for the ones that have timesheets for that period

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •