year and month filter - 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 2005 Forums

Transact-SQL (2005)

year and month filter

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-11 : 14:15:26

If I run the below query , it always return the whole months even though I put month < 9

it returns 1 ~ 12 2006.... what I am doing wrong here.. why i can not filter the month... can you help me?

SELECT c.region as Region_num, b.Unit_abbr, b.Unit_name,b.Financial_Class_code, b.Financial_Class_desc
,sum(isnull(b.gross_revenue,0)) as gross_revenue,
sum(isnull(b.bad_debt_writeoffs,0) ) as bad_debt_writeoffs
, sum(isnull(b.system_contractual_adjustments,0)) as system_contractual_adjustments
, sum(isnull(b.other_contractual_adjustments,0)) as other_contractual_adjustments
, sum(isnull(b.outof_period_adjustments,0)) as outof_period_adjustments

, sum(isnull(b.Adjustment_Total,0) ) - sum(isnull(b.bad_debt_writeoffs,0) ) as Adjustment_Total
,sum(isnull(rollbacks,0)) as rollbacks
, sum(isnull(b.System_Net_Revenue,0)) - sum(isnull(b.bad_debt_writeoffs,0) )as System_Net_Revenue,
sum(isnull(a.Medical_UNH,0)) as Medical_UNH ,
sum(isnull(a.Supp_Contractual_Allowances,0) ) as Supp_Contractual_Allowances,
sum(isnull(b.Treatments,0))as Treatments ,
sum(isnull(( isnull(b.Hdeq, 0) + isnull(a.Treatment_allowances, 0) ),0)) as HDeq,

sum(isnull(b.Payments,0))as Payments
, ((case when sum(( isnull(b.Hdeq, 0) + isnull(a.Treatment_allowances, 0) )) = 0 then 0 else convert(numeric(38,3) ,( ( sum(isnull(b.system_Net_Revenue,0) - isnull(b.bad_debt_writeoffs,0) ) ) / sum(isnull(( isnull(b.Hdeq, 0) + isnull(a.Treatment_allowances, 0) ),0)) ) ) end) ) as SNPT
, ((case when sum(( isnull(b.Hdeq, 0) + isnull(a.Treatment_allowances, 0) )) = 0 then 0 else convert(numeric(38,3), ( ( sum(isnull(b.system_Net_Revenue,0) - isnull(b.bad_debt_writeoffs,0) ) + ( sum(isnull(a.medical_unh,0)) + sum(isnull(a.Supp_Contractual_Allowances,0) )) ) / sum(isnull(( isnull(b.Hdeq, 0) + isnull(a.Treatment_allowances, 0) ),0)) )) end) ) as RNPT
,'Cumulative Year' as yeard
,a.month, a.year
,sum(isnull(b.refunds,0))as refunds
FrOM [Revenue_Aggregate_Assertions] a

join Revenue_aggregate b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
left join regions c on b.region_num = c.region_num
where ( ( b.[year] < year(getdate()) or b.year = year(getdate()) and b.month <= 9)) and a.unit_abbr = 'A01'
group by b.unit_abbr, c.region,b.unit_name, b.financial_class_code, b.financial_class_desc,a.year, a.month
order by unit_abbr,year,month, c.region, b.Financial_Class_code

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-11 : 14:46:21

You do know that Year(getdate()) probably returns 2007, right ?

Also, please make at least a small effort to format and indent your SQL statements, not only for when you post here, but also for the people who need to read and maintain your SQL.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources