asked on

Join 2 SQL query results togther using sequel

Ok, i think this is an easy one but I cant remember how to do this. I have 2 queries with 2 seprate results sets from which I need to create one table

Query 1
SELECT
dateName(month,project_recd_dt) as recdMonth,
COUNT(CASE WHEN PRESALES_STATUS = 'Pending Review'
THEN 1
END) AS pending
FROM...

Result set 1:
JAN 1
FEB 2
MAR 3

Query 2
SELECT
dateName(month,project_recd_dt) as recdMonth,
SUM(stepMins) as Time
FROM...

Result set 2:
JAN 11 hrs
FEB 22 hrs
MAR 33 hrs

I would like to join these 2 sets together to result in one table
JAN 1 11 hrs
FEB 2 22 hrs
MAR 3 33 hrs

chapmandew

like this:

select * into newtable
from
(
SELECT
dateName(month,project_recd_dt) as recdMonth,
COUNT(CASE WHEN PRESALES_STATUS = 'Pending Review'
THEN 1
END) AS pending
FROM...
) a
join
(
SELECT
dateName(month,project_recd_dt) as recdMonth,
SUM(stepMins) as Time
FROM...
) b on a.datename = b.datename

Wirt

ASKER

Sorry i thought i could get off easy so i did not post as much detail. This inline fx will not work b/c I have a different cols in each Q. I've attached the Qs and their results

-- Query 1 
SELECT
dateName(month,project_recd_dt) as recdMonth,
COUNT(CASE WHEN PRESALES_STATUS = 'Pending Review'
		   THEN 1
		   END) AS pending,
COUNT(CASE WHEN PRESALES_STATUS = 'Open/In Progress'
		   THEN 1
		   END) AS openInProgress,
COUNT(CASE WHEN PRESALES_STATUS = 'Sent to Centralized / Auto Complete' or PRESALES_STATUS = 'Sent to Centralized'
		   THEN 1
		   END) AS cent,
COUNT(CASE WHEN PRESALES_STATUS = 'Sent to Remote Support'
		   THEN 1
		   END) AS remote,
COUNT(CASE WHEN PRESALES_STATUS = 'Canceled'
		   THEN 1
		   END) AS canceled,
COUNT(CASE WHEN PRESALES_STATUS = 'On Hold'
		   THEN 1
		   END) AS hold,
COUNT(CASE WHEN PRESALES_STATUS = 'Completed'
		   THEN 1
		   END) AS completed,
COUNT(PROJECT_ACTIVITY.PROJECT_ID) AS actCount
 
FROM
PROJECT_ACTIVITY JOIN PMT_USERS
ON PMT_USERS.PMT_USER_ID = PROJECT_ACTIVITY.PMT_USER_ID
 
 
WHERE
PROJECT_TYPE = 'REVENUE'
AND project_recd_dt > '1/1/2008'
 
GROUP BY datename(month,Project_Recd_DT),month(Project_Recd_DT)
ORDER By month(Project_Recd_DT)
 
-- Query 2
SELECT
dateName(month,project_recd_dt) as recdMonth,
SUM(stepMins)/60 as actTime,
SUM(CASE  WHEN substring(LEGACY_SYS_TYPE,8,1) = 'T'
			THEN PRODUCT_QTY
			END) as legacyT,
SUM(CASE  WHEN substring(LEGACY_SYS_TYPE,8,1) = 'B'
			THEN PRODUCT_QTY
			END) as legacyB,
SUM(CASE  WHEN substring(LEGACY_SYS_TYPE,8,1) = 'S'
			THEN PRODUCT_QTY
			END) as legacyS
FROM
PROJECT_ACTIVITY JOIN PMT_USERS
ON PMT_USERS.PMT_USER_ID = PROJECT_ACTIVITY.PMT_USER_ID
LEFT OUTER JOIN PMT_PRODUCT_PROCESS_CYCLE
ON PMT_PRODUCT_PROCESS_CYCLE.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
LEFT OUTER JOIN PMT_STEP_PROCESS_CYCLE
ON PMT_STEP_PROCESS_CYCLE.PROD_PROCESS_ID = PMT_PRODUCT_PROCESS_CYCLE.PROD_PROCESS_ID
 
WHERE
PROJECT_TYPE = 'REVENUE'
AND project_recd_dt > '1/1/2008'
 
GROUP BY datename(month,Project_Recd_DT),month(Project_Recd_DT)
ORDER By month(Project_Recd_DT)

Open in new window

set-1.txt
set-2.txt

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wirt

ASKER

I was wrong, thats why your the Genius, thanks!!!!