• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Sql Query nested inside another Sql Query

I have 2 sql queries, that I need to combine.

The first:
SELECT     PAY_PD_END_DT, COUNT(EMPL_ID) AS ACT, PAY_PD_CD
FROM         DELTEKCP.DELTEK.EMPL_EARNINGS
WHERE     (CHK_DT > CONVERT(DATETIME, '2010-10-01 00:00:00', 102)) AND (S_PAYCHK_TYPE = 'R')
GROUP BY PAY_PD_END_DT, PAY_PD_CD
ORDER BY PAY_PD_END_DT

will return a result like:
PAY_PD_END_DT      ACT              PAY_PD_CD
2010-10-03                353                     W


The second:

SELECT     COUNT (EMPL_ID)
FROM         DELTEKCP.DELTEK.EMPL
WHERE     (ORIG_HIRE_DT < CONVERT(DATETIME, '2011-01-08 00:00:00', 102)) AND (TERM_DT IS NULL) OR (TERM_DT > CONVERT(DATETIME, '2011-01-08 00:00:00', 102))

Returns something like:
AAE
486

Now I would like to combine these, and where in the 2nd query I want ORIG_HIRE_DATE and TERM_DATE to look at Pay_PD_END_DT as the date it uses from the first query and the both queries to produce one outcome that looks like:


PAY_PD_END_DT      ACT              PAY_PD_CD     AAE
2010-10-03                353                     W               446
0
mikeewalton
Asked:
mikeewalton
  • 2
  • 2
1 Solution
 
Ephraim WangoyaCommented:
try
SELECT EARNINGS.PAY_PD_END_DT, 
		COUNT(EARNINGS.EMPL_ID) AS ACT, 
		EARNINGS.PAY_PD_CD,
		(SELECT COUNT (EMPL_ID)
		 FROM DELTEKCP.DELTEK.EMPL
		 WHERE EMPL_ID = EARNINGS.EMPL_ID
		 AND ORIG_HIRE_DT < EARNINGS.PAY_PD_END_DT
		 AND (TERM_DT IS NULL OR TERM_DT > EARNINGS.PAY_PD_END_DT)) AAE
FROM DELTEKCP.DELTEK.EMPL_EARNINGS EARNINGS
WHERE (EARNINGS.CHK_DT > CONVERT(DATETIME, '2010-10-01 00:00:00', 102)) 
AND (EARNINGS.S_PAYCHK_TYPE = 'R')
GROUP BY EARNINGS.PAY_PD_END_DT, EARNINGS.PAY_PD_CD
ORDER BY PAY_PD_END_DT

Open in new window

0
 
mikeewaltonAuthor Commented:
Column 'DELTEKCP.DELTEK.EMPL_EARNINGS.EMPL_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You may of seen it, but I should specify each query is on a separate table.

The first query is on  DELTEKCP.DELTEK.EMPL_EARNINGS
The second is on   DELTEKCP.DELTEK.EMPL

0
 
Ephraim WangoyaCommented:
Here
SELECT EARNINGS.PAY_PD_END_DT, 
		COUNT(EARNINGS.EMPL_ID) AS ACT, 
		EARNINGS.PAY_PD_CD,
		(SELECT COUNT (EMPL_ID)
		 FROM DELTEKCP.DELTEK.EMPL
		 WHERE ORIG_HIRE_DT < EARNINGS.PAY_PD_END_DT
		 AND (TERM_DT IS NULL OR TERM_DT > EARNINGS.PAY_PD_END_DT)) AAE
FROM DELTEKCP.DELTEK.EMPL_EARNINGS EARNINGS
WHERE (EARNINGS.CHK_DT > CONVERT(DATETIME, '2010-10-01 00:00:00', 102)) 
AND (EARNINGS.S_PAYCHK_TYPE = 'R')
GROUP BY EARNINGS.PAY_PD_END_DT, EARNINGS.PAY_PD_CD
ORDER BY PAY_PD_END_DT

Open in new window

0
 
mikeewaltonAuthor Commented:
Nicely done, works perfect!!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now