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

Connecting two sql statement

Hi, I have an sql statment that use a case :

SELECT EngSec, SUM(CASE WHEN ActionType = 'close' THEN TotalNoOfCalls ELSE 0 END) AS [Close],
SUM(CASE WHEN ActionType = 'resolved' THEN TotalNoOfCalls ELSE 0 END) AS [Resolved],
SUM(CASE WHEN ActionType = 'pending' THEN TotalNoOfCalls ELSE 0 END)  AS [Pending],
sum(case when ActionType is null then TotalNoOfCalls Else 0 end) as [Assign],
sum(case when ActionType ='cancel' then TotalNoOfCalls Else 0 end) as [Canceled]
FROM CallStatusbySec GROUP BY EngSec

and i have another statment that use a date interval for the same table:

SELECT * from CallStatusbySec WHERE
CAST(CONVERT(varchar, AssignDate, 101)as datetime)  <= '12/7/2007'
AND CAST(CONVERT(varchar, AssignDate, 101)as datetime)  >= '12/1/2007'

which is the second SQL statment must be the first.
Is there any way to link these two sql statment in one SQL statment?
please help me

0
AZZA-KHAMEES
Asked:
AZZA-KHAMEES
1 Solution
 
ee_rleeCommented:
i'm not really sure what you want in as your output. but if what you want is to get the sums (from the first table) between some dates (from the 2nd table), try this:


SELECT EngSec, SUM(CASE WHEN ActionType = 'close' THEN TotalNoOfCalls ELSE 0 END) AS [Close],
SUM(CASE WHEN ActionType = 'resolved' THEN TotalNoOfCalls ELSE 0 END) AS [Resolved],
SUM(CASE WHEN ActionType = 'pending' THEN TotalNoOfCalls ELSE 0 END)  AS [Pending],
sum(case when ActionType is null then TotalNoOfCalls Else 0 end) as [Assign],
sum(case when ActionType ='cancel' then TotalNoOfCalls Else 0 end) as [Canceled]
FROM CallStatusbySec 
WHERE
CAST(CONVERT(varchar, AssignDate, 101)as datetime)  <= '12/7/2007'
AND CAST(CONVERT(varchar, AssignDate, 101)as datetime)  >= '12/1/2007'
GROUP BY EngSec

Open in new window

0
 
AZZA-KHAMEESAuthor Commented:
this is simple i thought i have to write more complicated sql. :)
Thanks alot
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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