SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Getting date average with condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 07/23/2013 :  21:38:48  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/23/2013 :  22:49:40  Show Profile  Reply with Quote
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



Edited by - Villanuev on 07/23/2013 22:51:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/24/2013 :  01:01:06  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/24/2013 :  20:09:48  Show Profile  Reply with Quote
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

Edited by - Villanuev on 07/24/2013 20:13:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2013 :  00:49:48  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/25/2013 :  01:39:58  Show Profile  Reply with Quote
THanks Visakh..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/25/2013 :  04:02:49  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/30/2013 :  03:05:13  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/30/2013 :  03:52:50  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/30/2013 :  04:33:52  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/30/2013 :  04:45:06  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/30/2013 :  04:49:29  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/30/2013 :  06:09:28  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/30/2013 :  06:43:41  Show Profile  Reply with Quote
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

407 Posts

Posted - 07/30/2013 :  21:25:24  Show Profile  Reply with Quote
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

Edited by - Villanuev on 07/30/2013 22:35:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/31/2013 :  02:18:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000