Where the experts are..urgent query help

--------------------------------------------------------------------------------

union or subquery? how can i put these two queries together to get one result below.
I'm trying to count total records and also total escalation from total record perday/per agent for previous day. first query gives me total escalation and 2nd one gives me total records. how can i put them together in one query?
thanks
here my 2 queries
.......
SELECT Count( DISTINCT Create_Date) 'Total Escalation',
Submitter 'Remedy Id',
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager,
(getdate() - 1) as stampdate

FROM RemWebReports.dbo.raw_data
inner join WebForms.dbo.CNS on Submitter = remedy_ID
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1 and Status <> 'closed'
GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST),Group_Assigned_To, Submitter
ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST),Group_Assigned_To,Submitter

query 2

SELECT Count( DISTINCT Create_Date) 'Total call vol',
Submitter 'Remedy Id',
(EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS Manager,
(getdate() - 1) as stampdate

FROM RemWebReports.dbo.raw_data
inner join WebForms.dbo.CNS on Submitter = remedy_ID
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1
GROUP BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter
ORDER BY (MGR_FRST+' '+MGR_LAST), (SUP_FRST+' '+SUP_LAST), (EMP_FRST+' '+EMP_LAST), Submitter ASC
.........
basically same querie with diff where clause
i want to get result together like
'Total Escalation' 'Total call vol' 'Remedy Id', Agent,Supervisor, Manager, stampdate

b./c im trying to calculate percentage b/w 'Total Escalation' and 'Total call vol'

Join those 2 queries from a seperate outer query, and just select the total escalation and total call volume based on the Agent, sup, mgr match. I had time to burn...

This may be loaded w/syntax errors, but the idea of the logic should get you what you want. Of course, there's always the concept of temp tables.

thanks for your reply
but

EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST,
MGR_FRST, MGR_LAST, Remedy_ID is on diff database/table i.e WebForms.dbo.CNS
Remedy_iD is related to Submiter in RemWebReports.dbo.raw_data, i have to use join to get names/sup/manager b/c they are not available in RemWebReports.dbo.raw_data.
......................
let's take out join to make it easier. i will join my results and create view..
my new query shoud look like this..

query 1.

SELECT Count( DISTINCT Create_Date) 'Total Escalation',
Submitter 'Remedy Id',
Group_Assigned_To,
(getdate() - 1) as stampdate
FROM RemWebReports.dbo.raw_data
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1 and Status <> 'closed'
group by Submitter, Group_Assigned_To
ORDER BY Submitter, Group_Assigned_To

query 2.

SELECT Count( DISTINCT Create_Date) 'Total Call vol',
Submitter 'Remedy Id',
(getdate() - 1) as stampdate
FROM RemWebReports.dbo.raw_data
WHERE datediff(day, Create_Date, GetDate()) = 1 and IsDate(Create_Date) = 1
group by Submitter, Group_Assigned_To
ORDER BY Submitter, Group_Assigned_To


wanna combine both to get
result like
'Total Escalation' 'Total call vol' 'Remedy Id', 'Group_Assigned_To' 'stampdate'

Ahhh, ok, my mistake then. So the fields I had in green here...

...aren't actually in RemWebReports.dbo.raw_data @ all? I see earlier that the Submitter is what's joining to 2 DB's together...ok. I'm kinda tied up, but I'll give it another shot later on.

Ok, I made another attempt...I just edited my 1st query. Both of your queries looked exactly the same, except I finally saw the...

status <> 'Closed'

...criteria. Since that's the only diff between the 'Total Call Vol' and the 'Total Escalation' calc and all the other criteria is the same, I wrote one qry and joined to the other keeping the Submitter ID, Agent, Sup, and Mgr relations the same. I'm assuming your using SQL Server??...if so I have an idea using a CASE to extract both of the calc'd fields based off the status field values, not sure if it would be correct but if it is, it may save some room.

Hopefully the edit was on w/what you're looking for, the concept isn't that hard, but b/c there's redundancy in the subquery, I'm hesitant to say that there isn't a better solution...maybe someone else can chime in.

nevermind...thought I had a solution w/a CASE but no, edited.

what is the deal with counting distinct valid dates?

wouldn't DATEDIFF barf if there actually was an invalid date?

what's the datatype of the Create_Date column?

and assuming that DATEDIFF actually does work as intended, only on valid dates, then there will only ever be 1 distinct date in the result, so why is there a DISTINCT in the COUNT() expression?

Create_Date is varchar which is bad, im thnking about converting to datetime
what seem to work so far is...
i created package for each query to separate table.. then
i create a view to join them but i cant' seem to join 2 date columns unless i convert it to char.
im still verifiying data to make sure it accurate