Divide Problem - SQL Server Forums

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)

Divide Problem

Author

Topic

johnadonaldson
Starting Member

3 Posts

Posted - 2012-02-22 : 11:31:19

I am a Developer not a DBA person, so know enough SQL to get me in trouble. What I was given is

SUM Count(Days(Formal Close Date - Formal File Date))
- Year(Formal Close Date) = {Selected GFY}
- Formal Case Number Is Not Null

Divided by

'TOTAL DAYS:
- SUM Count()
- Year(Formal Close Date) = {Selected GFY}
- Formal Case Number Is Not Null

What I came up with is

select
q1.count / q2.count as Result
from (
Select hrEEO.dbo.EEO_CASE.park, SUM(count(days(FORMAL_CLOSE_DATE - FORMAL_FILE_DATE)))
from hrEEO.dbo.EEO_CASE
where YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate()) and
(FORMAL_CASE_NUMBER != null)
group by hrEEO.dbo.EEO_CASE.park
) as q1
inner join (
select hrEEO.dbo.EEO_CASE.park, SUM(count(*))
from hrEEO.dbo.EEO_CASE
where YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate()) and
(FORMAL_CASE_NUMBER != null)
group by hrEEO.dbo.EEO_CASE.park
) as q2
on q1.park = q2.park

I get two errors. "Days" is not a function" and syntax error near "as"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 12:27:23

Use DATEDIFF function to find the number of elapsed days:


...
      SUM(COUNT(DATEDIFF(day,FORMAL_FILE_DATE,FORMAL_CLOSE_DATE)))
...

However, there are other problems in the code - for example, you cannot do this: SUM(COUNT(*)). That is because you cannot nest aggregations.

johnadonaldson
Starting Member

3 Posts

Posted - 2012-02-22 : 14:09:10

With help from here and other people, I have gotton it down to this

select
q1 / q2 as Result
from (
Select SUM(q1)
from
(
select count(Day(FORMAL_CLOSE_DATE - FORMAL_FILE_DATE)) as q1
from hrEEO.dbo.EEO_CASE
where (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate())) and
(FORMAL_CASE_NUMBER != null)
) as a
inner join (
select SUM(q2)
from (
select count(*) as q2 from hrEEO.dbo.EEO_CASE
where (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate())) and
(FORMAL_CASE_NUMBER != null)
) as b
)
) as c

Now I get this error at the "as c" line
Msg 102, Level 15, State 1, Line 20 Incorrect syntax near ')'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 14:50:47

Before I post what I am about to, I must admit that I have only a vague understanding of what you are trying to do. But, would this do what you are looking to do? In the code, you first declare a variable and set the fiscal year that is of interest to you. Then, use that info to calculate the ratio.

DECLARE @fiscalYear INT;
SET @fiscalYear = 2011;

SELECT
	COUNT(DATEDIFF(DAY,FORMAL_FILE_DATE, FORMAL_CLOSE_DATE))
	/
	COUNT(*) AS Result
FROM
	hrEEO.dbo.EEO_CASE
WHERE
	YEAR(FORMAL_CLOSE_DATE) = @fiscalYear
	AND (FORMAL_CASE_NUMBER != NULL);

If this does not do what you are looking for, can you post some more details - such as your table DDL, some sample input data, and the expected output? Take a look at Brett's blog here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

johnadonaldson
Starting Member

3 Posts

Posted - 2012-02-22 : 15:44:15

Hopefully this will explain what is needed. I have two select statements.

select count(Day(FORMAL_CLOSE_DATE - FORMAL_FILE_DATE)) as q1
from hrEEO.dbo.EEO_CASE
where (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate())) and
(FORMAL_CASE_NUMBER != null)

and

select count(*) as q2
from hrEEO.dbo.EEO_CASE, hrEEO.dbo.CASE_INVESTIGATION
where (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate())) and
(FORMAL_CASE_NUMBER != null) and
(FAD_CMPLNT_TYPE_REQUEST = 'WH')

I want to divide q1 by q2.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 17:10:05

[code]SELECT q1/q2 AS Result
FROM
(
SELECT COUNT(DATEDIFF(DAY,FORMAL_FILE_DATE, FORMAL_CLOSE_DATE)) AS q1
FROM hrEEO.dbo.EEO_CASE
WHERE (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(GETDATE()))
AND (FORMAL_CASE_NUMBER != NULL)
) a CROSS JOIN
(
select count(*) as q2
from hrEEO.dbo.EEO_CASE, hrEEO.dbo.CASE_INVESTIGATION
where (YEAR(FORMAL_CLOSE_DATE) = hrEEO.dbo.FiscalYear(getdate())) and
(FORMAL_CASE_NUMBER != null) and
(FAD_CMPLNT_TYPE_REQUEST = 'WH')
) b[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 06:25:23

Where is your correlation between table EEO_CASE and table CASE_INVESTIGATION?

DECLARE	@FromDate DATE = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101'),
	@ToDate DATE = DATEADD(YEAR, DATEDIFF(YEAR, '18991231', GETDATE()), '19000101')

;WITH cteSource(theDays, theCount)
AS (
	SELECT	DATEDIFF(DAY, FORMAL_FILE_DATE, FORMAL_CLOSE_DATE) AS theDays,
		CASE
			WHEN FAD_CMPLNT_TYPE_REQUEST = 'WH' THEN 1
			ELSE 0
		END AS theCount
	FROM	hrEEO.dbo.EEO_CASE
	WHERE	FORMAL_CLOSE_DATE >= @FromDate
		AND FORMAL_CLOSE_DATE < @ToDate
		AND FORMAL_CASE_NUMBER IS NOT NULL
)
SELECT	theDays / NULLIF(theCount, 0)
FROM	cteSource

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources