asked on

Help with SQL

Hi there,
I'm looking for some help with SQL.
When I run this
Select       Count(J.Invoiceno) as JobCount,
      js.DESCRIPTION as jobstatus
from      dbo.JOB      j (nolock)
join      dbo.JOBSTATUSCODE      js (nolock) on j.status = js.code
join      dbo.SURTYP s (nolock) on j.surveycode = s.code
where      j.marketcode in (5,85)
Group By      js.DESCRIPTION

I get the following results

305      Allocated
1224      Cancelled
1      Hold
1      Panelled
82      Rejected
503      Works

I need to bring in another tabe, "products", however because there are more than 1 prduct per job, it makes the counts bigger (I need to bring in the max(completedate) of the product for type "Works"
I tried this
select      js.description as Status,
      count(j.invoiceno) as Jobcount,
      sum(case when m.completedate is not null then 1 else 0 end) as Completed
from      job j
      join product m on j.invoiceno = m.invoiceno
      join groups k on m.servicecode = k.code
      join jobstatuscode js on j.status = js.code
where      j.marketcode in (5,85)
      and js.description = 'Works'
group by js.description, case when m.completedate is not null then 1 else 0 end

and get the following reaults
Status Jobcount Completed
Works      194      0
Works      517      517

Can anyone help with this please?
Thankyou, Roger

momi_sabag

what is the output you wish to receive?

Rogero

ASKER

Status Job Count Completed
Works 503 194

Thanks, Roger

Dimitris

you can use the COUNT(DISTINCT column) clause.

They the code bellow

select js.description as Status,
       count(DISTINCT j.invoiceno) as Jobcount
from      job j
      join product m on j.invoiceno = m.invoiceno
      join groups k on m.servicecode = k.code
      join jobstatuscode js on j.status = js.code
where      j.marketcode in (5,85)
      and js.description = 'Works'
group by       js.description --, case when m.completedate is not null then 1 else 0 end

Open in new window

momi_sabag

why not

select js.description as Status,
count(distinct j.invoiceno) as Jobcount,
sum(case when m.completedate is not null then 1 else 0 end) as Completed
from job j
join product m on j.invoiceno = m.invoiceno
join groups k on m.servicecode = k.code
join jobstatuscode js on j.status = js.code
where j.marketcode in (5,85)
and js.description = 'Works'
group by js.description

Rogero

ASKER

Hi momi, yours gives the following output
Status Jobcount Completed
Works 503 1644

Which is great for the job count, but completed is giving the total product count. Each job has > 1 product, but I want to just bring through say the max(installdate) to give the following output

Status Jobcount Completed
Works 503 250

momi_sabag

i did not understand you
can you post sample data?

Rogero

ASKER

Sample data attached
files.zip

Rogero

ASKER

In file fcmeasures the columns are invoiceno, completed dats (of measure) and servicecode (the partno of the measure) .. in fcjob the columns are invoiceno, jobstatus, and marketcode

Rogero

ASKER

I'm looking for something like this
Status Jobcount Completed
Works 503 250

The numbers may be a bit smaller as I have only sent you sample data, but essentially the completed count criterion is if any of the measures have a complted date then count that as 1 in the completed column. I want the count by jobs rather than measures.

momi_sabag

try

select js.description as Status,
count(distinct j.invoiceno) as Jobcount,
sum(case when max(m.completedate) over(partition by j.invoiceno) is not null then 1 else 0 end) as Completed
from job j
join product m on j.invoiceno = m.invoiceno
join groups k on m.servicecode = k.code
join jobstatuscode js on j.status = js.code
where j.marketcode in (5,85)
and js.description = 'Works'
group by js.description

Rogero

ASKER

I ran
select js.description as Status,
count(distinct j.invoiceno) as Jobcount,
sum(case when max(m.completedate) over(partition by j.invoiceno) is not null then 1 else 0 end) as Completed
from fcjob j
join fcmeasures m on j.invoiceno = m.invoiceno
join fcjobstatuscode js on j.status = js.code
where j.marketcode in (5,85)
and js.description = 'Works'
group by js.description

I get

Msg 8120, Level 16, State 1, Line 1
Column 'fcjob.INVOICENO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 4109, Level 15, State 1, Line 1
Windowed functions cannot be used in the context of another windowed function or aggregate.

momi_sabag

select js.description as Status,
count(distinct j.invoiceno) as Jobcount,
sum(case when (select max(m.completedate) from fcmeasures m where j.invoiceno = m.invoiceno) is not null then 1 else 0 end) as Completed
from fcjob j
join fcjobstatuscode js on j.status = js.code
where j.marketcode in (5,85)
and js.description = 'Works'
group by js.description

Rogero

ASKER

I get

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rogero

ASKER

Brilliant, thankyou very much!