T-SQL Help [modified]

Hulicat

SQL 2005 Standard SP1 I have these two queries I am trying to join into one result set; I have been struggling with this for days and get close but can not seem to get it correct. Query 1: select count(*) as 'Total Tickets Assigned' , location_name 'Cient' from job_ticket j inner join priority_type p on p.priority_type_id = j.priority_type_id inner join tech t on t.client_id = j.assigned_tech_id inner join location l on l.location_id = j.location_id WHERE Report_Date >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8), GETDATE(),112))) AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8), GETDATE(),112))) group by l.location_name Query 2: select count(*) as 'Total Closed for Range' , location_name 'Cient' from job_ticket j inner join priority_type p on p.priority_type_id = j.priority_type_id inner join tech t on t.client_id = j.assigned_tech_id inner join location l on l.location_id = j.location_id where (last_status_update_time >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8), GETDATE(),112))) AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8), GETDATE(),112))) ) and status_type_id ='3' group by l.location_name basically all I need to get is the "count(*) as 'Total Closed for Range'" into query 2, however; I keep getting the total in each row rather than the total for each client. Also I have been trying to send the results of the query out via email.....I know this should be simple but I keep getting errors on that as well. Any help would be greatly appreciated. Thanks, Dennis

Regards, Hulicat

modified on Wednesday, August 20, 2008 1:53 PM

Blue_Boy · modified on Wednesday, August 20, 2008 1:53 PM

If you want to have single query which output result same with your two querys then post your data of tables and result which you want to have from only single query. Regards.

I Love T-SQL "Don't torture yourself,let the life to do it for you."

Hulicat

I am posting here because I am unable to make the single query output correctly I would like one result set looking like this: 'Total Tickets Assigned','Total Closed for Range', 'Client'

Regards, Hulicat

TheFM234 · modified on Wednesday, August 20, 2008 1:53 PM

If your queries return the correct data by themselves, then you should be able to join on a subquerys:

Select
total.Cient
,TotalTicketsAssigned
,TotalClosedForRange
From
(
select count(*) as 'TotalTicketsAssigned' , location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
WHERE Report_Date >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112)))
group by l.location_name
) total
Left Join
(
select count(*) as 'TotalClosedforRange'
, location_name 'Cient'
from job_ticket j
inner join priority_type p on p.priority_type_id = j.priority_type_id
inner join tech t on t.client_id = j.assigned_tech_id
inner join location l on l.location_id = j.location_id
where (last_status_update_time >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) AND
Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) ) and
status_type_id ='3'
group by l.location_name
) Closed on
total.Cient = closed.Cient

Hulicat

Thank for that; it gived me two errors one on line 11 and one on 23 the last. 1st error on 11 is a syntax error near ")" 2nd error is on line 23 the last is error near "closed" Heere it is revised a bit...this one is tuff for me thanks for the help!!! Select total.Client,TotalTicketsAssigned, TotalClosedForRangeFrom select count(*) as 'TotalTicketsAssigned' , location_name 'Client'from job_ticket jinner join priority_type p on p.priority_type_id = j.priority_type_idinner join tech t on t.client_id = j.assigned_tech_idinner join location l on l.location_id = j.location_id WHERE Report_Date >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))) AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))) group by l.location_name) total.client Left Join (select count(*) as 'TotalClosedforRange', location_name 'Client'from job_ticket jinner join priority_type p on p.priority_type_id = j.priority_type_idinner join tech t on t.client_id = j.assigned_tech_idinner join location l on l.location_id = j.location_id where (last_status_update_time >= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112))) AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8), GETDATE(),112))) ) and status_type_id ='3' group by l.location_name) Closed on total.Client = closed.Client

Regards, Hulicat

TheFM234

I copied and pasted the two queries you supplied into the join sub-query syntax, so the line 11 one is something from the query you supplied (and I'm guessing the second one is resulting from the first error). To try to clear things up for you, here's a simple structure of a sub-query join:

Select
<select list>
From
(
<query 1>
) q1
Join
(
<query 2>
) q2 on
<join list>
<other clauses (probably will not have any here)>

The select must use the names assigned to the columns, so if you have Select Name, Date as OpenDate; then you would need to use Name for name (because the name was not changed) and OpenDate for the original column Date. If you think of the two queries as tables, it might make the concept easier to grasp. If the above query was written from tables, it would be Select * From Table1 q1 Join Table2 q2 on ... Hope that helps.

Hulicat

Thanks for that!!! You made it crystal clear and I have it working now!! Omce again thanks for taking the time to teach and give an example of perspective... That will really help me moving forward. Thanks and Regards, Dennis

Regards, Hulicat