Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Group by where all subrows fullfill the requeriments

Posted on 2009-05-12
2
Medium Priority
?
281 Views
Last Modified: 2012-05-06
Hello,

I have this select

                      select c.obra, sum(d.importe)
      from d_albven d inner join c_albven c
                     on d.numero=c.numero and d.empresa=c.empresa
      where d.familia<>'HO' and c.factura is not null
      group by c.obra
      having min(fecha_fac)>=@dateini and min(fecha_fac)>=@datafi

But I want better select to extract only the c.obra where all the factura is not null, if there is a row where the factura is null then the c.obra doesn't must apper.

Best regards,
0
Comment
Question by:silviall
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24367528
this should do:
  select c.obra, sum(d.importe)
      from d_albven d inner join c_albven c 
                     on d.numero=c.numero and d.empresa=c.empresa 
      where d.familia<>'HO' 
      group by c.obra
      having min(fecha_fac)>=@dateini and min(fecha_fac)>=@datafi
         and sum(case when c.factura is null then 1 else 0 end) = 0

Open in new window

0
 
LVL 6

Expert Comment

by:bokist
ID: 24368083
perhaps this will work

select c.obra, sum(d.importe)
  from d_albven d,  c_albven c
where d.numero=c.numero
    and d.empresa=c.empresa
    and c.obra is not null
    and d.familia<>'HO'
 group by c.obra
having min(fecha_fac)>=@dateini
           and min(fecha_fac)>=@datafi
 
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Screencast - Getting to Know the Pipeline
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question