How to base one report on mulitpule queries.

Janet95 · Jul 19, 2007

Hi want to create one crystal report that returns the values for several items. I opened up a new report and the only thing I added was a command. In the command I added the following quieries:

Code:

select Count (*)As CallsIn from heat.calllog 
where 
                      (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')






SELECT     COUNT(*) AS CallsClosed
FROM HEAT.calllog
WHERE      (heat.calllog.callstatus = 'Closed')and 
		      (HEAT.CallLog.CS_Application = 'Encore' OR HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
		     





SELECT     COUNT(*) AS CallWaitingReplicationForWeek
FROM HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE      (heat.calllog.callstatus = 'open')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
		      AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '')
		      


SELECT     COUNT(*) AS CallWaitingReplicationGrandTotal
FROM HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE      (heat.calllog.callstatus = 'open')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
		      AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '')





SELECT     COUNT(*) AS AssignEngineeringMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Engineering Maint') AND 
                      (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')


		    


 




SELECT     COUNT(*) AS AssignFormsMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Forms Maint') AND 
                      (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')

		    

SELECT     COUNT(*) AS AssignPCMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'PC Maint') AND 
                      (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')



SELECT     COUNT(*) AS ClientRejected
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Client Rejected') AND 
                      (HEAT.CallLog.CS_Application = 'Encore' OR
                      HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')



	

SELECT     COUNT(*) AS ChangeRequest
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Change Requests')

This doesn't look correct, it will only return the first query. What am I doing wrong? I also tried to do this in a crosstab but that didn't work. I know this can be done however I just need someone to point me in the right direction.
Any help would be much appreciated.

Thanks a million in advance....

Thanks
Janet [ponytails2]

asknelson19 · Jul 19, 2007

Janet,

Below code might help you out..
i might have missed couple of open/close braces...
cheers

select
sum(case when HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
then 1 end) as CallsIN,
sum(case when (heat.calllog.callstatus = 'Closed')and
(HEAT.CallLog.CS_Application = 'Encore' OR HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
then 1 end ) as CallsClosed,

sum(case when (heat.calllog.callstatus = 'open')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
HEAT.Asgnmnt.AssgState = 'Submitted' OR
HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv = '')
then 1 end) as CallWaitingReplicationForWeek,
sum(case when (heat.calllog.callstatus = 'open')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
HEAT.Asgnmnt.AssgState = 'Submitted' OR
HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv = '')
then 1 end) as CallWaitingReplicationGrandTotal,
sum(case when (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint')
AND (HEAT.Asgnmnt.Assignee = 'Engineering Maint') AND
(HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')

then 1 end) as AssignEngineeringMaint
sum(case when (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint')
AND (HEAT.Asgnmnt.Assignee = 'Forms Maint') AND
(HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
then 1 end) as AssignFormsMaint,
sum( case when (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint')
AND (HEAT.Asgnmnt.Assignee = 'PC Maint') AND
(HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
then 1 end) as AssignPCMaint,
sum(case when (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Product Maint')
AND (HEAT.Asgnmnt.Assignee = 'Client Rejected') AND
(HEAT.CallLog.CS_Application = 'Encore' OR
HEAT.CallLog.CS_Application = 'Encore Timeline Tracker' or HEAT.CallLog.CS_Application = 'Encore Encounter Tracker')
then 1 end ) as ClientRejected,

sum(case when (HEAT.CallLog.CallStatus = 'Open') AND (HEAT.Asgnmnt.GroupName = 'Change Requests')
then 1 end ) as ChangeRequest

From
Heat.CallLOg inner join Heat.Asgnmnt on HEAT.CallLog.CallID=HEAT.Asgnmnt.CallID

Janet95 · Jul 19, 2007

Thanks, I'm trying it now. There are a few open/closed bracket errors. I'll see if this works. So I should just place this in the same command as I had the last one, right?

Thanks
Janet [ponytails2]

asknelson19 · Jul 20, 2007

Yep, Just copy the whole query into one command. And when you go to field explorer, you should be able to see all the columns by clicking command.

Janet95 · Jul 20, 2007

Yes, That worked!!!!!!

Thank you soooooooooooooooooooooooooo

MUCH!!!!

your a life saver!!!!

Thanks
Janet [ponytails2]

asknelson19 · Jul 24, 2007

You are welcome...
which version u r using by the way..

Janet95 · Jul 27, 2007

OK new issue. The first two sum/select statments only pull of the heat.calllog table. The rest pull off the join of two tables. So I can't get that part to work correctly.
Also, I want to be able to use the between statement for the query for dates, I have 3 date fields though and each select/sum statement pulls from one of the three. 'StartDate' and 'EndDate' in the query below are just place holders. I want to have a user defined parameter in Crystal prompt for that info.

Code:

HEAT.calllog.Recvddate between 'StartDate' and  'EndDate'
HEAT.calllog.closedDate Between 'StartDate' and 'EndDate'
HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate'

Here is the main queiry I have placed in a command that I need to work.

Code:

select 
-- Calls In  (for the week)
sum(case when HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker'
   And Recvddate between 'StartDate' and  'EndDate'
         then 1 end) as CallsIN,


-- Calls Closed (for the week)
sum(case when (heat.calllog.callstatus = 'Closed')and 
              (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
   AND (HEAT.calllog.closedDate Between 'StartDate' and 'EndDate')         
	then 1 end ) as CallsClosed,


-- Waiting C/S I/R for Week           callWaitingReplication for week only note whoresolv = null   
sum(case when (heat.calllog.callstatus <> 'Closed')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
   AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '') AND (HEAT.Asgnmnt.DateAssign Between 'StartDate' and 'EndDate')
           then 1 end) as CallWaitingReplicationForWeek,



-- Waiting C/S I/R Total           Grand Total  note whoresolv = null
sum(case when (heat.calllog.callstatus <> 'Closed')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
   AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '')
        then 1 end) as CallWaitingReplicationGrandTotal,



-- Moved to Encore Maint           This one works Calls currently assigned to Encore Maint with Encore or TT or Encore Encounter Tracker
sum(case when (HEAT.CallLog.CallStatus <> 'Closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Engineering Maint') AND 
                      HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker'
   AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate') 
         then 1 end) as  AssignEngineeringMaint,



-- Moved to Forms Maint           This one works Calls currently assigned to Forms Maint with Encore or TT or Encore Encounter Tracker
sum( case when (HEAT.CallLog.CallStatus <> 'Closed') and (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Forms Maint') AND 
                      HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker'
   AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate')	
          then 1 end) as AssignFormsMaint,

-- Moved to PC Maint           This one works Calls currently assigned to PC Maint with Encore or TT or Encore Encounter Tracker
sum( case when (HEAT.CallLog.CallStatus <> 'Closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'PC Maint') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
   AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate')
          then 1 end) as AssignPCMaint,



-- Moved to Client Rejected           This one works Calls currently assigned to PC Maint with Encore or TT or Encore Encounter Tracker
sum(case when (HEAT.CallLog.CallStatus <> 'Closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Client Rejected') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
   AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate')    
	 then 1 end ) as ClientRejected,

-- Moved to Change Request           This one works Calls currently assigned to PM Change Request
sum(case when (HEAT.CallLog.CallStatus <> 'Closed') AND (HEAT.Asgnmnt.GroupName = 'Change Requests') 
   AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' and 'EndDate') 

        then 1 end ) as  ChangeRequest

From 
Heat.CallLOg inner join Heat.Asgnmnt on HEAT.CallLog.CallID=HEAT.Asgnmnt.CallID

This has been racking my brain for a week now so any help would be greatly appreciated.

Thanks Janet

Thanks
Janet [ponytails2]

lbass · Jul 27, 2007

Create the {?startdate} and {?enddate} parameters within the command, and then use them like:

HEAT.calllog.closedDate >= {?startdate} and
HEAT.calllog.closedDate < {?enddate} + 1

Not sure what your other issues are.

-LB

Janet95 · Jul 27, 2007

Hi lbass,

My other issue is basically I have 9 seprate queries I want to be in one CR. I'm like super new at CR so I'm almost clueless on how to do this.

I want to have the results of all 9 show up in one CR.

asknelson19 was kind enough to place all 9 in one big select case statement which I added into a command( See second response in this thread)
The problem with his

I have pasted the orginal queries below.

Code:

-- Calls In  (for the week)

select Count (*)As CallsIn from heat.calllog 
where Recvddate between 'StartDate' and  'EndDate'  AND 
                     (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')




-- Calls Closed (for the week)
SELECT     COUNT(*) AS CallsClosed
FROM HEAT.calllog
WHERE      (heat.calllog.callstatus = 'Closed')and 
		      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
		      AND (HEAT.calllog.closedDate Between 'StartDate' and 'EndDate')




-- Waiting C/S I/R for Week           callWaitingReplication for week only note whoresolv = null
SELECT     COUNT(*) AS CallWaitingReplicationForWeek
FROM HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE      (heat.calllog.callstatus <> 'closed')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
		      AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '')
		      AND (HEAT.Asgnmnt.DateAssign Between 'StartDate' and 'EndDate')



-- Waiting C/S I/R Total           Grand Total  note whoresolv = null
SELECT     COUNT(*) AS CallWaitingReplicationGrandTotal
FROM HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE      (heat.calllog.callstatus <> 'closed')and (HEAT.Asgnmnt.AssgState = 'Investigate/Replicate' OR
                      HEAT.Asgnmnt.AssgState = 'Submitted' OR
                      HEAT.Asgnmnt.AssgState = 'Working') AND (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
		      AND (HEAT.Asgnmnt.GroupName = 'Towson Support') and (heat.asgnmnt.whoresolv =  '')



-- Moved to Encore Maint           This one works Calls currently assigned to Encore Maint with Encore or TT or Encore Encounter Tracker

SELECT     COUNT(*) AS AssignEngineeringMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus <> 'closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Engineering Maint') AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')

		    


 

-- Moved to Forms Maint           This one works Calls currently assigned to Forms Maint with Encore or TT or Encore Encounter Tracker


SELECT     COUNT(*) AS AssignFormsMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus <> 'closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Forms Maint') AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')
		    
-- Moved to PC Maint           This one works Calls currently assigned to PC Maint with Encore or TT or Encore Encounter Tracker

SELECT     COUNT(*) AS AssignPCMaint
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus <> 'closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'PC Maint') AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')

-- Moved to Client Rejected           This one works Calls currently assigned to PC Maint with Encore or TT or Encore Encounter Tracker

SELECT     COUNT(*) AS ClientRejected
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus <> 'closed') AND (HEAT.Asgnmnt.GroupName = 'Product Maint') 
                      AND (HEAT.Asgnmnt.Assignee = 'Client Rejected') AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' AND 'EndDate') AND 
                      (HEAT.CallLog.CS_Application <> 'Tranquility' or HEAT.CallLog.CS_Application  <>'Clarity' 
		or HEAT.CallLog.CS_Application <>'EZ FORMS (Non-Encore)' or HEAT.CallLog.CS_Application <> 'EZ FORMS (Non-Enc)'
		or HEAT.CallLog.CS_Application <>'Encore Claims Tracker')


		    
-- Moved to Change Request           This one works Calls currently assigned to PM Change Request

SELECT     COUNT(*) AS ChangeRequest
FROM         HEAT.Asgnmnt INNER JOIN
                      HEAT.CallLog ON HEAT.Asgnmnt.CallID = HEAT.CallLog.CallID
WHERE     (HEAT.CallLog.CallStatus <> 'closed') AND (HEAT.Asgnmnt.GroupName = 'Change Requests') 
                      AND (HEAT.Asgnmnt.DateAssign BETWEEN 'StartDate' and 'EndDate') 
[code/]
		    

Thanks 
Janet [ponytails2]

lbass · Jul 27, 2007

Please explain the issue instead of showing all this code. What was the problem with the combined case statement?

-LB

cmmrfrds · Jul 27, 2007

It might be easier to crosstab these columns in an Union query. Union quieries require each query to have the same number of columns with the same data type. Usually, NULL can sit in for any data type. For example.

select count(*) as callins,
null,
null,
null,
null,
null,
null,
null,
null
from heat.calllog
Union ALL
select null,
callsclosed,
null,
null,
null,
null,
null,
null,
null
from heat.calllog
Union ALL
select null,
null,
callswaitingforweek,
null,
null,
null,
null,
null,
null
from heat.calllog
etc........

Janet95 · Jul 27, 2007

Bascially, I think it's just the from statement.

The select query is for the first two quieries should only pull from the heat.calllog table. all the others should pull from the join below

From
Heat.CallLOg inner join Heat.Asgnmnt on HEAT.CallLog.CallID=HEAT.Asgnmnt.CallID

Thanks
Janet [ponytails2]

lbass · Jul 27, 2007

But in what sense is the query not working? Are you getting incorrect results? Error messages? If so, what are they?

-LB

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to base one report on mulitpule queries.

Janet95

Technical User

asknelson19

Programmer

Janet95

Technical User

asknelson19

Programmer

Janet95

Technical User

asknelson19

Programmer

Janet95

Technical User

lbass

Technical User

Janet95

Technical User

lbass

Technical User

cmmrfrds

Programmer

Janet95

Technical User

lbass

Technical User

Similar threads

Part and Inventory Search

Sponsor