If and how can this be done? 1

[gjsaturday]

TB1
id date fbal fsales
1 02/13/2005 38 0
1 02/14/2005 42 -16
1 02/15/2005 34 -8
1 02/16/2005 48 -10
1 02/17/2005 35 -13
1 02/18/2005 37 -21
1 02/19/2005 43 -13
2 02/13/2005 32 0
2 02/14/2005 29 -3
2 02/15/2005 28 -1
2 02/16/2005 50 -6
2 02/17/2005 43 -7
2 02/18/2005 27 -16
2 02/19/2005 30 -6

TB2
id date abal asales
1 02/13/2005 35 0
1 02/14/2005 43 -15
1 02/15/2005 40 -3
1 02/16/2005 41 -17
1 02/17/2005 0 -19
1 02/18/2005 23 -18
1 02/19/2005 31 -28
2 02/13/2005 39 0
2 02/14/2005 34 -5
2 02/15/2005 29 -5
2 02/16/2005 53 -4
2 02/17/2005 45 -8
2 02/18/2005 22 -23
2 02/19/2005 35 0

I want the following...
id avg(fbal) avg(abal) avg(fsales) avg(asales)
1 40 36 -13 -17
2 34 37 -7 -9

I need the avg calculated based on days where the amount is not zero, but don't know when that will be. FYI, these tables are actually on one table differentiated by a type (a versus f). I'm running into an issue in that these "amounts" are already the sum of 10 different "items" so I can't use the AVG function in that it divides by 70 (10 items for 7 days) (of course, I've also tried multiplying my AVG result by 10 to eliminate that piece)....

I think this is hopeless, but thought I'd put it out here for all you experts.

Thanks in advance!

 

[donutman]

I need the avg calculated based on days where the amount is not zero

Which amount is not zero, the total for the day of all the fbal items (I assume) or do you need to look at each item individually?
Whatever the case may be, this is doable. You need to treat the results you have as a derived table and group by date having sum(*)>0. You can get the count of days from that.
If you need more help, then lets see the query you have now.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Pattycake245]

Something like this:

select [id],
avg(case when fbal <> 0 then fbal end) avg_fbal,
avg(case when abal <> 0 then abal end) avg_abal,
avg(case when fsales <> 0 then fsales end) avg_fsales,
avg(case when asales <> 0 then asales end) avg_asales
from
(select [id],
isnull(a_item1 + a_item2,0) abal,
isnull(a_sales1 + a_sales2,0) asales,
isnull(f_item1 + f_item2,0) fbal,
isnull(f_sales1 + f_sales2,0) fsales
from table1
group by [id]) as results
group by [id]
order by [id]

This is assuming that all of these fields are based on calculations from ten other fields for each. Change where you need to.

Tim

 

[Pattycake245]

Sorry forgot one thing:

select [id],
avg(case when fbal <> 0 then fbal end) avg_fbal,
avg(case when abal <> 0 then abal end) avg_abal,
avg(case when fsales <> 0 then fsales end) avg_fsales,
avg(case when asales <> 0 then asales end) avg_asales
from
(select [id],
isnull(f_item1,0) + isnull(f_item2,0)... + isnull(f_item10,0) fbal,
isnull(a_item1,0) + isnull(a_item2,0)...+ isnull(a_item10,0) abal,
isnull(f_sales1,0) + isnull(f_sales2,0)... + isnull(f_sales10,0) fsales,
isnull(a_sales1,0) + isnull(a_sales2,0)...+ isnull(a_sales10,0) asales
from table1
group by [id]) as results
group by [id]
order by [id]

 

[gjsaturday]

I'll post my query in a bit. Please remember, I do not pretend to be a sql expert, just know a little, and have been ordained the sql person of my group. I cannot change the database, I have to work with what I was given, at least for now. But for my tables above, I need the avg calcuated by the following...

id 1 week of 2/13 - 2/19, the avg(fbal) is 40 (sum of all / 7 days no zeros), avg(abal) is 36 (sum of all / 6 days as 2/17 is 0), avg(fsales) is -13.5 (sum of all / 6 days as 2/13 is 0), and avg(asales) is -17 (sum of all / 6 days as 2/13 is 0)

id 2 week of 2/13 - 2/19, the avg(fbal) is 34 (sum of all / 7 days no zeros), avg(abal) is 37 (sum of all / 7 days no zeros), avg(fsales) is -7 (sum of all / 6 days as 2/13 is 0), and avg(asales) is -9 (sum of all / 5 days as 2/13 & 2/19 are 0)

My query is to follow, but please don't laugh...

 

[gjsaturday]

Ok, here goes, don't laugh...

select x.flds6, x.flds0, t.id,
cast((sum(f.bal)/(count(distinct f.date))) as decimal),
cast((sum(a.bal)/(count(distinct a.date))) as decimal),
cast((sum(f.salesin - f.salesout)/(count(distinct
f.date))) as decimal),
cast ((sum(a.salesin - a.salesout)/(count(distinct a.date))) as decimal)
from loc t
inner join loc d on d.parent = t.id
inner join locextrainfo x on t.id = x.id
inner join loc c on t.parent = c.id
inner join sales f on d.id = f.id and f.type = 2
left join sales a on f.id = a.id and f.date = a.date and a.type = 1 and a.bal <> 0
where f.date between '02/13/2005' and '02/19/2005'
group by x.flds6, x.flds0, t.Id
order by 1,2

This gives me...
col1 col2 id avg(fbal) avg(abal) avg(fsales) avg(asales)
x y 1 40 36 -12 -13
a b 2 34 37 -6 -6

avg(fsales) and avg(asales) are wrong... because they don't include the values where a.bal = 0 and they are being divided by 7 days when I only want to divide by days where the *sales <> 0

 

[gjsaturday]

Thanks Tim! I gave you a star!

I used the following...

select [id],
10*avg(case when fbal <> 0 then fbal end) avg_fbal,
10*avg(case when abal <> 0 then abal end) avg_abal,
10*avg(case when fsales <> 0 then fsales end) avg_fsales,
10*avg(case when asales <> 0 then asales end) avg_asales

so long as I always have 10 items making up my sums, I think this will work!

Thanks so very very much!!!

 

[Pattycake245]

Thanks, I'm glad you were able to use the code.

Tim