Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter suppliers that dindn't sell Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:06 AM
Points: 10, Visits: 29
I have the following query:
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM
fc(nolock) left join fo on fc.fostamp=fo.fostamp
WHERE
fc.fano1 <> 0
Group By
fc.no, fc.nome
Order By
fc.nome

This query returns an error that fano1 isn't found. It works flawlessly without the WHERE clause. But i need it to ensure that i don't get lines with suppliers that didn't sell...
Is there any other way i can rule out the rows where fano1 is equal to 0?

Other info:
fc.no      => int
fc.nome => varchar
fc.datalc => datetime
fc.ecred => float

Post #957419
Posted Thursday, July 22, 2010 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:06 AM
Points: 10, Visits: 29
Solved like this:
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM
fc(nolock) left join fo on fc.fostamp=fo.fostamp
Group By
fc.no, fc.nome
HAVING
SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End) <> 0
Order By
fc.nome

Post #957427
Posted Thursday, July 22, 2010 12:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:56 AM
Points: 822, Visits: 1,204
try this. The problem comes from the fact that fan01 does not physically exist it is a named output of the query.

select t.* from 
(
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM fc(nolock)
left join fo on fc.fostamp=fo.fostamp
) t
WHERE
t.fano1 <> 0
Group By
t.no, t.nome
Order By
t.nome



Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #957434
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse