Showing posts with label calculation. Show all posts
Showing posts with label calculation. Show all posts

Wednesday, March 28, 2012

Help with complex query

Hi Everyone,

I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.

--

Agent Name: John Doe

Date Range: 1/1/2007 - 6/30/2007

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

Assuming your table is the grouped sum's by department:

Code Snippet

create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )

insert into #t1

select 1, 26355, 52920, 'Service'

union all select 2, 9000, 52920, 'Parts'

union all select 3, 17565, 52920, 'Dispatch'

select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),

round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'

from #t1

|||

DaleJ,

The table data is not grouped. Thats what makes this query complex.

|||

SamCosta wrote:

DaleJ,

The table data is not grouped. Thats what makes this query complex.

Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||

And some additional sample data.

It's not that difficult, but would like to get it right the first time (or two )

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)


GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

I need to then group the query results by EmpID to get a total Average Productivity Level

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2

54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),

Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.

Monday, March 26, 2012

help with an expression

I need to create an expression to handle a simple calculation. Here is what I have:

=Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

What do I need to add to handle the when the MH_POS_Goal = 0?

I tried isnull and nullif and kept getting errors.

Hi

You can create a User Define function for that where you can check the MH_POS_Goal value. If it is 0 then return 1 other wise retirn the acutal value... and I hope that you know anything deiveded by 1 is by nature and don't change the value . means that

1 = 1/1

200 = 200/1

|||

Something like this?

= IF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1,Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")

,'')

|||

This expression processes ok. But I am still getting the error message. Can someone help me update it to work correctly?

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")),0)

|||

The render engine of SSRS evaluates the complete expresse you enter.

In general this means that preventing a DivByZero exception with an IIF statement to check for a zero (null) value and display something else in case it's true, isn't enough. For the 'divided by' number also a IIF statement is needed, because the complete expression is evaluated.

So in your case this would be: =IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Notice that the true-part, 1, of the last IIF statement is never used, but is only needed for the SSRS engine not to generate an error.

|||

I didn't means this ....

I mean .....

IDF_CHECK (value)
IF Value = 0
Value = 1
RETURN Value

And Now check ......

(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")/IDF_CHECK(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))

|||

I tried this:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/ IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

and I got this error: Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'

Any ideas?

|||

There was an extra ')'.

This works:

=IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")>1, (Sum(Fields!MH_POS.Value, "lexis_sales_dbdata_prd")/IIF(Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd")=0, 1, Sum(Fields!MH_POS_Goal.Value, "lexis_sales_dbdata_prd"))),0)

Thanks for the help!

|||

If I use this in a report with subtotals I get the calculation at the total level and not at the subtotal level. Any ideas how to get the calculation to perform at the subtotal level?

|||

Anyone?

Wednesday, March 21, 2012

Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Monday, March 12, 2012

Help With a Calculated Member Calculation

Hello,

I have a fact file that includes a measure called Credit Hours. I also have a dimension called DimTerm what includes time data for two terms: 05Fall and 06Fall. My goal is to get a number change between 05 and 06 terms. So I created a calculated member with the logic below but my percentage change is always %0.00. Maybe someone knows a way of doing this through MDX which would allow me to connct to Term values?

([Measures].[Credit Hours] - [Measures].[Credit Hours]) / [Measures].[Credit Hours]

Thanks!

Take a look at the ParallelPeriod function. Your calculated member will look something like

([Credit Hours] - ([Credit Hours], ParallelPeriod(...))) / [Credit Hours]

Friday, March 9, 2012

Help w/ Calculation

I need to calculate the overall GPA for a student in a particular class.

YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000

The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.

Here's my sql:

select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

Have you considered a AVG() and a GROUP BY?

|||

That worked. In the beginning the averages weren't what I expected but it was mistake on my part. Thanks and I like your signature. So true.