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 |
|
sergeant_time
Yak Posting Veteran
73 Posts |
 Posted - 2012-05-31 : 10:16:02
|
| Does anyone know a code to get group data by fiscal year? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 10:19:42
|
| Depends on whet your fiscal year is.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-31 : 10:20:05
|
you have to show us your table schema and sample data and what is the expected result
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sergeant_time
Yak Posting Veteran
73 Posts |
Posted - 2012-05-31 : 11:06:54
|
| Our fiscal year is Oct-Sept.....SELECT COUNT(abandon_time) AS ABN, CAST(SUM(CASE WHEN answer_speed <= 45 THEN 1 ELSE 0 END) AS numeric(8, 0)) / COUNT(answer_time) AS SL, COUNT(queued_time) AS CO, COUNT(answer_time) AS CH, CAST(COUNT(abandon_time) AS numeric(8, 2)) / CAST(COUNT(queued_time) AS numeric(8, 2)) AS ABN_Rate, COUNT(abandon_speed) AS Total_ABN, AVG(answer_speed) AS ASA, AVG(talk_time + work_time + hold_time) AS AHTFROM logWHERE (split IN (100, 101)) AND (queued_time IS NOT NULL) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-31 : 11:29:04
|
which is the date column ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sergeant_time
Yak Posting Veteran
73 Posts |
Posted - 2012-05-31 : 11:48:33
|
| The date column is calendar_date |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-05-31 : 11:59:06
|
| It is often useful to have a calendar table.You could just have a table with fical year, startdate, enddate and join to that but maybe more useful is a table populated with each day, have the key as date and columns fiscal year, fical year start date, fiscal year end dateYou can also add attributes like working day, end date of month, start/end of week, bank holidays, … which can save a lot of calculations and hard coding in stored procedures.To populatewith cte as(select dte = convert(datetime, '20000101'union allselect dte = dte+1 from cte where dte < '2020101')select dte, fiscalyear = year(dte) + case when month(dte)<9 then 1 else 0 endfrom cte==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-05-31 : 19:38:28
|
| GROUP BY Year(DateAdd(Month, 3, calendar_date))HTH=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
|
|
|
|
|
Fiscal Year