Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Getting date average with condition

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 21:38:48
Hi Guys,

i Have a requirements to sum up the wip using asustatus 1,2,5 and fail using 6. then i need also to get the avg between createddate and lastupdate for wip and failed using asustatus. then the final requirements is the overall total and over all date avg. all of thid will be group by wrkctrid. i have my initial query but could not get the desired result. need your help guys. thanks.

here is the complete DDL and desired result:


create table #sample
(wrkctrid nvarchar(35),
createddate datetime,
lastupdate datetime,
asustatus int)
go
insert into #sample
select 'Assembly 2','2013-07-10','2013-07-19',5 union all
select 'Assembly 2','2013-07-14','2013-07-23',5 union all
select 'Assembly 2','2013-07-17','2013-07-23',1 union all
select 'Assembly 2','2013-05-14','2013-05-30',6 union all
select 'Assembly 2','2013-07-20','2013-07-22',6 union all
select 'Assembly 2','2013-07-20','2013-07-23',6 union all
select 'Assembly 2','2013-07-18','2013-07-23',2 union all
select 'Assembly 2','2013-07-15','2013-07-20',6



;with cte as
(
select
wrkctrid,
createddate,
lastupdate,
asustatus,
datediff(dd,createddate,lastupdate) as nodays
from #sample
order by asustatus
)
Select
wrkctrid,
Sum(Case When ASUSTATUS=1 OR ASUSTATUS=2 OR ASUSTATUS=5 Then 1 else 0 End) As WIPQty,
Sum(Case When ASUSTATUS=6 Then 1 else 0 End) As FailQty,
Case When ASUSTATUS=1 OR ASUSTATUS=2 OR ASUSTATUS=5 then avg(nodays) End as avgdayswip,
Case When ASUSTATUS=6 then AVG(nodays) End as avgdaysfail,
COUNT(wrkctrid) as GrandTotal
from cte
group by wrkctrid, ASUSTATUS


desired result:
----------------

Wrkctrid-------wipqty---failqty---avg days wip----avg days fail----total qty---tot ave days
--------------------------------------------------------------------------------------------
Assembly 2-------4---------4--------7.25--------------6.5 -------------8------------6.87

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-23 : 22:49:40
Hi Visakh

I fixed already the error from the query. just place a SUM after the datediff to get the overall total avg days.
btw, and its working already. anyway, can i ask a favor, can you please give me a little bit explanation on how the avgdaysfail, avgdayswip and totalavgdays get the desired result. why also you did not use the "avg". thanks a lot.




SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS avgdayswip,
SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS avgdaysfail,
COUNT(*) AS totalqty,
SUM(DATEDIFF(dd,createddate,lastupdate))*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 01:01:06
quote:
Originally posted by Villanuev

Hi Visakh

I fixed already the error from the query. just place a SUM after the datediff to get the overall total avg days.
btw, and its working already. anyway, can i ask a favor, can you please give me a little bit explanation on how the avgdaysfail, avgdayswip and totalavgdays get the desired result. why also you did not use the "avg". thanks a lot.




SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS avgdayswip,
SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END)*1.0/SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS avgdaysfail,
COUNT(*) AS totalqty,
SUM(DATEDIFF(dd,createddate,lastupdate))*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid





The DATEDIFF portion gives you the days elapsed for each of the cases between the two dates. I'm doing a sum of days to get total days elapsed and then divving it with count of instances to get the average.
You can also use average function to get same result after casting the values to decimal to get decimal result

like

AVG(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate)*1.0 ELSE 0.0 END)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-24 : 20:09:48
Thank you so much Visakh.

Btw, I put a nullif in the begenning of SUM. just encounter an error for dividing a zero value.

SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
NULLIF(SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END),0)*1.0/NULLIF(SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END),0) AS avgdayswip,
NULLIF(SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END),0)*1.0/NULLIF(SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END),0) AS avgdaysfail,
COUNT(*) AS totalqty,
SUM(DATEDIFF(dd,createddate,lastupdate))*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 00:49:48
quote:
Originally posted by Villanuev

Thank you so much Visakh.

Btw, I put a nullif in the begenning of SUM. just encounter an error for dividing a zero value.

SELECT wrkctrid,
SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END) AS wipqty,
SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END) AS failqty,
NULLIF(SUM(CASE WHEN asustatus IN (1,2,5) THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END),0)*1.0/NULLIF(SUM(CASE WHEN asustatus IN (1,2,5) THEN 1 ELSE 0 END),0) AS avgdayswip,
NULLIF(SUM(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate) ELSE 0 END),0)*1.0/NULLIF(SUM(CASE WHEN asustatus = 6 THEN 1 ELSE 0 END),0) AS avgdaysfail,
COUNT(*) AS totalqty,
SUM(DATEDIFF(dd,createddate,lastupdate))*1.0/COUNT(*) AS totalavgdays
FROM #sample
GROUP BY wrkctrid


yep...you need to do that if you dont always have a record for partcular set of values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-25 : 01:39:58
THanks Visakh..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 04:02:49
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-30 : 03:05:13
Hi Visakh,

Upon coverting to SSRS this report has incorrect result set. upon checking the result set in excel file comparing to the
output of SSRS, they are not the same result when i validate the records. thanks.

This is what i did in the SSRS report expression:
For WIP
=AVG(Fields!avgdayswip.Value)

For Failed
=avg(Fields!avgdaysfail.Value)

FOr Totalavg
=AVG(Fields!totalavgdays.Value)

create table #sample(avgdayswip numeric(28,12), avgdaysfailed numeric(28,12), totalavg numeric(28,12))
insert into #sample
select 3.2,7.0,5.0 union all
select 1.4,6.8,3.4 union all
select 3.5,6.0,5.1 union all
select 1.7,5.9,3.8 union all
select 6.8,4.9,5.2 union all
select 2.5,3.8,3.1 union all
select 9.0,3.6,6.5 union all
select 0.0,3.4,3.4 union all
select 1.8,3.4,2.6 union all
select 2.9,3.0,2.9 union all
select 3.2,2.7,3.1 union all
select 2.2,2.6,2.2 union all
select 1.3,2.5,1.8 union all
select 2.5,2.0,2.4 union all
select 2.1,1.8,2.1 union all
select 0.8,1.5,1.2 union all
select 1.5,0.8,0.8 union all
select 0.4,0.6,0.6 union all
select 0.1,0.4,0.2 union all
select 0.0,0.0,0.0 union all
select 0.4,0.0,0.4 union all
select 0.0,0.0,0.0 union all
select 2.3,0.0,2.3 union all
select 0.3,0.0,0.3 union all
select 1.5,0.0,1.5 union all
select 1.0,0.0,1.0 union all
select 0.0,0.0,0.0


Actual result from SSRS:
avgwip---avgfailed---totalavg
-----------------------------
2.3------3.1---------2.3


In getting the avg using EXCEL----This should be the result set in SSRS
------------------------------
avgwip---avgfailed---totalavg
-----------------------------
1.9---------2.3---------2.3

 
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 03:52:50
you cant take Average of Average. That will always be wrong. You need to calculate it as TotalSum/ TotalCount using IIF expression

something like

IIF(Inscope("YourGroupName"),Fields!avgdayswip.Value,SUM(Fields!YourActualField.value)/COUNT(Fields!YourActualField.value))

YourActualfield is field using which you calculated avgdayswip etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-30 : 04:33:52
this is what i did. under row groups in =(Details1), i did right click and choose the add group and click the parent group
then i create wrkctrid as my group the group name is group_wrkctrid.
then place the arrow to =(Details1) and click right then click add total after. still the result is wrong 2.3 for wip and 3.1 for failed.

i place this to my expression value of avgdayswip
=IIF(Inscope("Group_Wrkctrid"),Fields!avgdayswip.Value,SUM(Fields!avgdayswip.Value)/COUNT(Fields!avgdayswip.Value))

this is for the failed.
=IIF(Inscope("Group_Wrkctrid"),Fields!avgdaysfail.Value,SUM(Fields!avgdaysfail.Value)/COUNT(Fields!avgdaysfail.Value))
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-30 : 04:45:06
Hi Visakh..

i got the correct result. i replace COUNT(Fields!avgdayswip.Value)) with COUNT(Fields!WRKCTRID.Value)).

=IIF(Inscope("Group_Wrkctrid"),Fields!avgdayswip.Value,SUM(Fields!avgdayswip.Value)/COUNT(Fields!WRKCTRID.Value))

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 04:49:29
yep exactly...that was the whole point. You need to put actual source field against avgdayswip. also it should be SUM(Fields!ActualFieldhere.Value) and not avgdayswip

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-30 : 06:09:28
what is this SUM(Fields!ActualFieldhere.Value), this should be also the SUM(Fields!WRKCTRID.Value), but this is a string not a numeric value.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 06:43:41
nope...that should be sum of field whose average you want to find out. so in this case it should be sum of wip days

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-07-30 : 21:25:24
Thanks Visakh..

Btw, I have another question with diff scenario. what if i will get the totaverage between avgdayswip and avgdaysfailed by row in SSRS..sample below


ctrwrkid--avgdayswip---avgdaysfailed--totavg
--------------------------------------------
EOL--------3.2--------------7.0---------?
PD---------1.4--------------6.8---------?

This is what i did to my ssrs expression.
=(Fields!avgdayswip.Value+Fields!avgdaysfail.Value)/2
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 02:18:07
ideally the way to do this is

(total days in wip status) + (total days in failed status)/(count of wip occurance) + (count of failed instances)

as i told before taking average of average quantoties will give wrong result

you need to have sum and count values of individual sections to calculate overall average correctly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -