Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

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

0
Rogero
Asked:
Rogero
  • 8
  • 6
1 Solution
 
momi_sabagCommented:
what is the output you wish to receive?
0
 
RogeroAuthor Commented:
Status  Job Count  Completed
Works    503     194

Thanks, Roger
0
 
DimitrisSenior Solution ArchitectCommented:
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
momi_sabagCommented:
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

0
 
RogeroAuthor Commented:
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


0
 
momi_sabagCommented:
i did not understand you
can you post sample data?
0
 
RogeroAuthor Commented:
Sample data attached
files.zip
0
 
RogeroAuthor Commented:
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

0
 
RogeroAuthor Commented:
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.
0
 
momi_sabagCommented:
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
0
 
RogeroAuthor Commented:
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.
0
 
momi_sabagCommented:
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

0
 
RogeroAuthor Commented:
I get

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
0
 
momi_sabagCommented:
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  fcjob j
 join (select max(completedate) as completedate, invoiceno from  fcmeasures group by invoiceno) 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

0
 
RogeroAuthor Commented:
Brilliant, thankyou very much!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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