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

x
?
Solved

Group by where all subrows fullfill the requeriments

Posted on 2009-05-12
2
Medium Priority
?
282 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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