Sql-server – SQL Dependent Percentage

sql serversql-server-2008

Sample data

Excepted output

Math Operation

Sum all Period time for each EmpName as Total Employee Time
Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100%

please help me

I am using SQL Server Management 2008 and here is my try:

select EmpName,Task,Period_Time, 
sum(Period_Time)/(select sum(Period_Time) as total from dbo.TableName) 
as percentage 
from  dbo.TableName 
group by EmpName,Task,Period_Time 
order by EmpName

Best Answer

You were close with your approach. The two issues with it that I can see are:

The subquery is not correlated with the outer query. Your subquery needs to have a WHERE clause to limit the rows only to those where EmpName is the same as the outer query's EmpName.

In order to be able to reference the outer query's columns, you need to assign a different alias to either instance of dbo.TableName. It would probably be best to assign one to each – something like this:
```
SELECT
  ...
  (SELECT ... FROM dbo.TableName AS b WHERE b.EmpName = a.EmpName)
FROM
  dbo.TableName AS a
...
```
The main query does not need to group by Period_Time, because it is aggregating that value.

Just remove the Period_Time column from both the GROUP BY and SELECT.

So, this is how your query could look like after addressing both issues:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0
              / (
                  SELECT
                    SUM(b.Period_Time)
                  FROM
                    dbo.TableName AS b
                  WHERE
                    b.EmpName = a.EmpName
                )
FROM
  dbo.TableName AS a
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;

However, you could do better than using a correlated subquery here. Since SQL Server 2005, Transact-SQL has started supporting window aggregate functions. They allow you to obtain aggregate results alongside detail results and use both in various calculations – exactly what you need for your problem.

Basically, the syntax would go like this:

SELECT
  SomeKey,
  DetailData,
  TotalData = SUM(DetailData) OVER (PARTITION BY SomeKey),
  -- or you can get percentage directly, like this:
  -- Percentage = DetailData * 100.0 / SUM(DetailData) OVER (PARTITION BY SomeKey)
FROM
  dbo.SomeTable
;

There is only one issue: you cannot calculate the total Period_Time like this:

SUM(Period_Time) OVER (PARTITION BY EmpName)

because in your case Period_Time would be an invalid reference: that column is not in GROUP BY and may not be referenced without being enclosed in a (non-window) aggregate function. Your main query already aggregates it using SUM – that is how it can be used in SUM ... OVER as well:

SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)

For reference, here is an example of a complete query that calculates percentage using window aggregation instead of a correlated subquery:

SELECT
  EmpName,
  Task,
  Percentage  = SUM(Period_Time) * 100.0 / SUM(SUM(Period_Time)) OVER (PARTITION BY EmpName)
FROM
  dbo.TableName
GROUP BY
  EmpName,
  Task
ORDER BY
  EmpName
;

Related Solutions

Sql-server – How to we automatically replace a table with a view for a specific user

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
    SELECT 1 FROM sys.dm_exec_sessions 
      WHERE program_name = 'program' 
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

Is this normal for a forced join order to make the query estimates to be completely inaccurate (and thus query times unpredictable)?

The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.

Should I just expect that I'll have to either accept sub-optimal query performance, or watch it like a hawk and frequently manually edit query hints? Or maybe hint every join as well? .3s to 2s is a big hit to take.

Preferable would be to ensure the optimiser is given the information it needs to generate the best plan, without using the FORCE ORDER hint. By doing so, it should cope better with changes to the underlying data distribution without requiring manual intervention. That said, if the nature of the data is such that cardinality could vary significantly hour by hour or day by hour, consider using a plan guide to ensure the plan is fixed.

Is it obvious why the optimizer blew up after deleting rows? For example, "yes, it took a sample scan, and because I archived most of the rows earlier in the data history the sample yielded sparse results, so it underestimated the need for a sorted hash operation"?

You didn't mention the row counts in the problem tables but it's likely that the deletions either:

didn't remove enough rows to to trigger a statistics update. This should occur when 20% of rows have been modified but there is the option to use trace flag 2371 to enable a dynamic threshold.
did trigger a statistics update but the sample gathered wasn't representative. Correct this by running a manual update WITH FULLSCAN.

You could also be running into good old fashioned parameter sniffing problems, for which there are myriad options to work around. WITH RECOMPILE might be an expensive option to specify with a query this large but it's worth investigating at both procedure and statement level.

Best Answer

Related Solutions

Sql-server – How to we automatically replace a table with a view for a specific user

Sql-server – OPTION FORCE ORDER improves performance until rows are deleted

Related Question