Declare Year parameter - 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)

Declare Year parameter

Author

Topic

sunnyday126
Starting Member

5 Posts

Posted - 2007-03-08 : 13:55:25

Hi,
I'm a beginner at SQL, so please bear with me.
I am trying to declare the current year to date (YTD) AND the entire 2006. For 2006 date parameter, should I declare a StartYear (01/01/06) and EndYear (12/31/2006) Which would be the best solution?
Also, how do I set the paramCurrentYear?
Thanks in advance!!

declare @paramtoday datetime
declare @paramCurrentYear datetime
declare @paramLastStartYear datetime
declare @paramLastEndYear datetime

Set @paramtoday = Convert(varchar(10),getdate(),101)
Set @paramCurrentYear = dateadd(yy, -1, @paramtoday)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-08 : 14:36:33

what is your exact business logic requirement?
get all data for a year you choose or similar?

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sunnyday126
Starting Member

5 Posts

Posted - 2007-03-08 : 14:40:11

the business logic is to get the entire 2006 (1/1/06 to 12/31/06) and a year to date 1/1/07 to getdate()

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-08 : 14:44:19

declare @from datetime, @to datetime
declare @year char(4)
select @year = '2006'
select @from = @year + '0101', @to = dateadd(yy, 1, @from)

select *
from MyTable
where YourDateColumn >= @from and YourDateColumn < @to

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 15:30:21

are you trying to group your records per year?

select datepart(year,datestamp),...
from tableA
group by datepart(year,datestamp),...

or are you just retrieving data given a startdate and and stopdate?

select ...
from tableA
where datestamp between @startdate and @stopdate

it is best to pass the @startdate and @stopdate as parameter values

or provide more details

HTH

--------------------
keeping it simple...

sunnyday126
Starting Member

5 Posts

Posted - 2007-03-08 : 15:39:37

So, this is the query I currently have:
I need the @to = '12/31/2006' and I need to create a CurrentHC for 2006 and YTD (1/1/07 to yesterday's date) for CurrentHC, CurrentNH, CurrentTerm

Thanks!

declare @paramtoday datetime
declare @parammoend1 datetime
declare @parammoend2 datetime
declare @parammoend3 datetime
declare @parammoend4 datetime
declare @from datetime
declare @to datetime
declare @year char(4)

Set @paramtoday = Convert(varchar(10),getdate(),101)

Set @parammoend1 = DateAdd(d,-1,dateadd(d,-day(@paramtoday)+1,@paramtoday))
Set @parammoend2 = DateAdd(d,-1,dateadd(d,-day(@parammoend1)+1,@parammoend1))
Set @parammoend3 = DateAdd(d,-1,dateadd(d,-day(@parammoend2)+1,@parammoend2))
Set @parammoend4 = DateAdd(d,-1,dateadd(d,-day(@parammoend3)+1,@parammoend3))
Set @year = '2006'
select @from = @year + '0101', @to = dateadd(yy, 1, @from)

SELECT @from,
@to,
@year,
cc_org_division_no,
cc_org_division_name,
cc_org_area_no,
cc_org_area_name,
cc_org_reg_no,
cc_org_reg_name,
Sum(case when empl_term_date is null then 1
else 0 end) as CurrentHC,

Sum(case when empl_rehire_date between DateAdd(d,1,@parammoend1) and @paramtoday then 1
else 0 end) as CurrentNH,

Sum(case when empl_term_date between DateAdd(d,1,@parammoend1) and @paramtoday then 1
else 0 end) as CurrentTerm,

Sum(case when empl_rehire_date <= @parammoend1 and empl_term_date is null then 1
when empl_rehire_date <= @parammoend1 and empl_term_date > @parammoend1 then 1
else 0 end) as LastMo1HC,

Sum(case when empl_rehire_date between DateAdd(d,1,@parammoend2) and @parammoend1 then 1
else 0 end) as LastMo1NH,

Sum(case when empl_term_date between DateAdd(d,1,@parammoend2) and @parammoend1 then 1
else 0 end) as LastMo1Term,

Sum(case when empl_rehire_date <= @parammoend2 and empl_term_date is null then 1
when empl_rehire_date <= @parammoend2 and empl_term_date > @parammoend2 then 1
else 0 end) as LastMo2HC,

Sum(case when empl_rehire_date between DateAdd(d,1,@parammoend3) and @parammoend2 then 1
else 0 end) as LastMo2NH,

Sum(case when empl_term_date between DateAdd(d,1,@parammoend3) and @parammoend2 then 1
else 0 end) as LastMo2Term,

Sum(case when empl_rehire_date <= @parammoend3 and empl_term_date is null then 1
when empl_rehire_date <= @parammoend3 and empl_term_date > @parammoend3 then 1
else 0 end) as LastMo3HC,

Sum(case when empl_rehire_date between DateAdd(d,1,@parammoend4) and @parammoend3 then 1
else 0 end) as LastMo3NH,

Sum(case when empl_term_date between DateAdd(d,1,@parammoend4) and @parammoend3 then 1
else 0 end) as LastMo3Term

FROM dbo.empl INNER JOIN dbo.vu_dim_cc_org ON dbo.empl.empl_cc_nox = dbo.vu_dim_cc_org.cc_org_cc_nox

WHERE (empl_record_status_flag = 1) and cc_org_division_no <> 8

GROUP BY cc_org_division_no,
cc_org_division_name,
cc_org_area_no,
cc_org_area_name,
cc_org_reg_no,
cc_org_reg_name

ORDER BY cc_org_reg_name

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-08 : 16:03:33

the dates you mentioned are not even part of the query, just for display

or what you want is to do the computation on the @param% values?

post 5 sample rows, to get the current year : datepart(year,getdate())

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources