count query problem

I have a table with a list of clients, some tasks associated with each and the status of each task (open, closed).

I want to return a SINGLE row for each client, along with a COUNT of the number of "open" and "closed" tasks.

The typical count statement I am using is this:

select SiteName, Count(IssueStatus) as cnt, IssueStatus,
from IssueMaster
group by SiteName, IssueStatus

It works fine, however, it returns a row for each combination of client and status. I would like it to return "Client, Count of Open, Count of Closed".

Any ideas?It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

-PatP|||I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?

Thanks!|||Like this:

SUM (CASE WHEN status='OPEN' THEN 1 ELSE 0 END) AS open_count|||Or still using COUNT:

COUNT (CASE WHEN status='OPEN' THEN 1 END) AS open_count

Could Script Task Component uses a Framework 1.1 assembly?

Could Script Task Component uses a Framework 1.1 assembly?

Yes, it can - we did some testing. But you have to copy it to SYSTEM\assembly and register it in GAC (gacutil.exe).

HTH

Could I Resolve a KPI's Data Value in a SSIS Script Task?

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

ivanchain wrote:

Hi, thanks.

I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?

SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.

Thanks.

Yep. If its .Net then you can use it in a Script Task!

-Jamie

|||

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

|||

ivanchain wrote:

But How? I don't know how to use any ADOMD.NET functions in SSIS Script Task. Because in the common VS, we need to imports the ADOMD.NET.dll into the projects if we want to use it. But in SSIS Script Task's imports list, I cound not find the ADOMD.NET.dll.

Anyone know this? Thanks!

Aha. Yes, you're absolutely right. I should have realised this before - sorry.

Read this:

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx)

-Jamie

|||Thanks, Jamie. You saved my life.