Solved

Group by where all subrows fullfill the requeriments

Posted on 2009-05-12
2
261 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 500 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 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