Prev: Removing text from a field, leaving a number
Next: Delete Query Cannot delete from specified tables
From: Rbirdie on 27 Jun 2008 14:17
I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
I then have a query to join them all together to see all informaition needed.
The problem I am having is there are some 00 values that are causing my AVG's
to total instead. I realize that this is a normalizing issue, but this data
is coming off of mainframe and so it is what it is. So, if I can eliminate
the zeros or tell the query to ignore them without eliminating the row that
would be great. Any ideas?
From: KARL DEWEY on 27 Jun 2008 14:28
Post your SQL.
--
KARL DEWEY
Build a little - Test a little


"Rbirdie" wrote:

> I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
> I then have a query to join them all together to see all informaition needed.
> The problem I am having is there are some 00 values that are causing my AVG's
> to total instead. I realize that this is a normalizing issue, but this data
> is coming off of mainframe and so it is what it is. So, if I can eliminate
> the zeros or tell the query to ignore them without eliminating the row that
> would be great. Any ideas?
From: Rbirdie on 27 Jun 2008 14:49
SELECT DISTINCTROW Crosstab1.Model,
Avg((NZ(Crosstab2.Actual,0)+NZ(Crosstab2.COPIED,0)+NZ(Crosstab2.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab2.[Prorated From Actual],0))) AS [Total Count of
Actuals],
Sum((NZ(Crosstab1.Actual,0)+NZ(Crosstab1.COPIED,0)+NZ(Crosstab1.[HIGH PRIOR
PRORATE],0)+NZ(Crosstab1.[Prorated From Actual],0))) AS [Avg of Actuals],
Crosstab1.Actual, Crosstab1.COPIED, Crosstab1.[HIGH PRIOR PRORATE],
Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0) AS [otal
Count of Defaults], Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN,
Crosstab1.CountOfModel, Crosstab3.[<>], Crosstab3.[Accrual Cleanup],
Crosstab3.EXCEL, Crosstab3.MAIL, Crosstab3.[PBMS READINGS],
Crosstab3.PRORATION, Crosstab3.UNKNOWN, Crosstab3.VOICE, Crosstab3.WEBSITE,
Crosstab3.ICR
FROM (Crosstab1 INNER JOIN Crosstab2 ON Crosstab1.Model = Crosstab2.Expr1)
INNER JOIN Crosstab3 ON Crosstab2.Expr1 = Crosstab3.Model
GROUP BY Crosstab1.Model, Crosstab1.Actual, Crosstab1.COPIED,
Crosstab1.[HIGH PRIOR PRORATE], Crosstab1.[Prorated From Actual],
NZ(Crosstab2.Unknown,0)+NZ(Crosstab2.[Prorated From Default],0),
Crosstab1.[Prorated From Default], Crosstab1.UNKNOWN, Crosstab1.CountOfModel,
Crosstab3.[<>], Crosstab3.[Accrual Cleanup], Crosstab3.EXCEL, Crosstab3.MAIL,
Crosstab3.[PBMS READINGS], Crosstab3.PRORATION, Crosstab3.UNKNOWN,
Crosstab3.VOICE, Crosstab3.WEBSITE, Crosstab3.ICR, Crosstab2.[Prorated From
Default], Crosstab2.UNKNOWN, Crosstab2.Actual, Crosstab2.COPIED,
Crosstab2.[HIGH PRIOR PRORATE], Crosstab2.[Prorated From Actual];


"KARL DEWEY" wrote:

> Post your SQL.
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Rbirdie" wrote:
>
> > I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
> > I then have a query to join them all together to see all informaition needed.
> > The problem I am having is there are some 00 values that are causing my AVG's
> > to total instead. I realize that this is a normalizing issue, but this data
> > is coming off of mainframe and so it is what it is. So, if I can eliminate
> > the zeros or tell the query to ignore them without eliminating the row that
> > would be great. Any ideas?
From: Rbirdie on 27 Jun 2008 14:58


>
> "KARL DEWEY" wrote:
>
> > Post your SQL.
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "Rbirdie" wrote:
> >
> > > I have a data dump (over 110k info) that is now set up as 3 crosstab queries.
> > > I then have a query to join them all together to see all informaition needed.
> > > The problem I am having is there are some 00 values that are causing my AVG's
> > > to total instead. I realize that this is a normalizing issue, but this data
> > > is coming off of mainframe and so it is what it is. So, if I can eliminate
> > > the zeros or tell the query to ignore them without eliminating the row that
> > > would be great. Any ideas?
From: Rbirdie on 27 Jun 2008 14:59


"Rbirdie" wrote:

>
>
 |  Next  |  Last
Pages: 1 2
Prev: Removing text from a field, leaving a number
Next: Delete Query Cannot delete from specified tables