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.
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)goinsert into #sampleselect 'Assembly 2','2013-07-10','2013-07-19',5 union allselect 'Assembly 2','2013-07-14','2013-07-23',5 union allselect 'Assembly 2','2013-07-17','2013-07-23',1 union allselect 'Assembly 2','2013-05-14','2013-05-30',6 union allselect 'Assembly 2','2013-07-20','2013-07-22',6 union allselect 'Assembly 2','2013-07-20','2013-07-23',6 union allselect 'Assembly 2','2013-07-18','2013-07-23',2 union allselect '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 GrandTotalfrom ctegroup by wrkctrid, ASUSTATUSdesired 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 VisakhI 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 totalavgdaysFROM #sampleGROUP BY wrkctrid |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-24 : 01:01:06
|
quote: Originally posted by Villanuev Hi VisakhI 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 totalavgdaysFROM #sampleGROUP 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 resultlike AVG(CASE WHEN asustatus = 6 THEN DATEDIFF(dd,createddate,lastupdate)*1.0 ELSE 0.0 END)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 totalavgdaysFROM #sampleGROUP BY wrkctrid |
|
|
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 totalavgdaysFROM #sampleGROUP BY wrkctrid
yep...you need to do that if you dont always have a record for partcular set of values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2013-07-25 : 01:39:58
|
THanks Visakh.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-25 : 04:02:49
|
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 #sampleselect 3.2,7.0,5.0 union allselect 1.4,6.8,3.4 union allselect 3.5,6.0,5.1 union allselect 1.7,5.9,3.8 union allselect 6.8,4.9,5.2 union allselect 2.5,3.8,3.1 union allselect 9.0,3.6,6.5 union allselect 0.0,3.4,3.4 union allselect 1.8,3.4,2.6 union allselect 2.9,3.0,2.9 union allselect 3.2,2.7,3.1 union allselect 2.2,2.6,2.2 union allselect 1.3,2.5,1.8 union allselect 2.5,2.0,2.4 union allselect 2.1,1.8,2.1 union allselect 0.8,1.5,1.2 union allselect 1.5,0.8,0.8 union allselect 0.4,0.6,0.6 union allselect 0.1,0.4,0.2 union allselect 0.0,0.0,0.0 union allselect 0.4,0.0,0.4 union allselect 0.0,0.0,0.0 union allselect 2.3,0.0,2.3 union allselect 0.3,0.0,0.3 union allselect 1.5,0.0,1.5 union allselect 1.0,0.0,1.0 union allselect 0.0,0.0,0.0 Actual result from SSRS:avgwip---avgfailed---totalavg-----------------------------2.3------3.1---------2.3In getting the avg using EXCEL----This should be the result set in SSRS------------------------------avgwip---avgfailed---totalavg-----------------------------1.9---------2.3---------2.3 |
|
|
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 expressionsomething likeIIF(Inscope("YourGroupName"),Fields!avgdayswip.Value,SUM(Fields!YourActualField.value)/COUNT(Fields!YourActualField.value))YourActualfield is field using which you calculated avgdayswip etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 groupthen 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)) |
|
|
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)) |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 belowctrwrkid--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 |
|
|
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 resultyou need to have sum and count values of individual sections to calculate overall average correctly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|