• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1498
  • Last Modified:

Tax year and periods - return from last year January onwards SQL SERVER T-SQL

Hello I'm trying to return some data from a payroll system that works on tax years and periods so Period 1 = April Period 2 = May etc , i need to return data from January onwards which means i would have to look at last year period 10 and return results on from that , any ideas
HIST_PERIOD	HIST_YEAR
3	                 2011
7	                 2011
8	                 2011
9	                 2011
10	                 2011
11	                 2011
12	                 2011
1	                 2012
2	                 2012
3	                 2012
4	                 2012
5	                 2012

Open in new window

and this is using the following code
SELECT HIST_YEAR, HIST_PERIOD
FROM payroll
WHERE HIST_YEAR < dateadd(year,-1,getdate())

Open in new window

so will need to be months Jan to current which will be Aug this year.

Thanks
0
deanmachine333
Asked:
deanmachine333
  • 3
  • 2
1 Solution
 
lwadwellCommented:
Not exactly sure what you need help with ... is it a way to extract the correct year/months?  If so look at this ... three different techniques used:
with hist_periods as (
select 3  hist_period, 2011 hist_year union all
select 7  hist_period, 2011 hist_year union all
select 8  hist_period, 2011 hist_year union all
select 9  hist_period, 2011 hist_year union all
select 10 hist_period, 2011 hist_year union all
select 11 hist_period, 2011 hist_year union all
select 12 hist_period, 2011 hist_year union all
select 1  hist_period, 2012 hist_year union all
select 2  hist_period, 2012 hist_year union all
select 3  hist_period, 2012 hist_year union all
select 4  hist_period, 2012 hist_year union all
select 5  hist_period, 2012 hist_year
)
select *, 'a'
        , DATEADD(mm, hist_period, convert(varchar, hist_year)+'-03-01')           as real_date
        , CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2)  as year_offset
  from hist_periods
 where DATEADD(mm, hist_period, convert(varchar, hist_year)+'-03-01') >= '2012-01-01' 
union all
select *, 'b'
        , DATEADD(mm, hist_period, convert(varchar, hist_year)+'-03-01')           as real_date
        , CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2)  as year_offset
  from hist_periods
 where CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2) >= '201110'
union all
select *, 'c'
        , DATEADD(mm, hist_period, convert(varchar, hist_year)+'-03-01')           as real_date
        , CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2)  as year_offset
  from hist_periods
 where hist_year > 2011
    or (hist_year = 2011 and hist_period >= 10)

Open in new window

0
 
deanmachine333Author Commented:
Hello , I'm building a report which users will use from now on so every year so needs to be able to work out current year and bring the results back from Jan that current year to current date so could be running report in 2013 so will need to look for data from Year 2012 Period 10 (January) to the current date , so can't really hard code this query.
So need to build query to look at last year period 10 onwards to current date , this query will have year 2011 and 2012 so look like
Hist Year 2011 Period 10,11,12 and Hist Year 2012 1,2,3,4,5

thanks
0
 
aflockhartCommented:
Try this:  I've done it using a variable to allow for testing. When ready to use, replace all the @mydate references with getdate()


declare @mydate datetime
select @mydate='20120801'

select * from payroll
where 

-- if current month is March or earlier, show the months from period 10 in the previous financial year up to date
( month(@mydate)<=3 and hist_year =year(@mydate)-1 and hist_period>=10 and hist_period <= month(@mydate)+9)

--if current month is April or later, show all of the  months from period 10 in the previous financial year
or
( month(@mydate)>3 and hist_year =year(@mydate)-1 and hist_period>=10)

--if current month is April or later, also show all of the  months from period 1 in the current financial year up to date
or
( month(@mydate)>3 and hist_year =year(@mydate) and hist_period <= month(@mydate)-3)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
deanmachine333Author Commented:
Amazing works brilliantly thanks for your help  lwadwell !!! :-)
0
 
deanmachine333Author Commented:
Amazing works brilliantly thanks for your help  lwadwell !!! :-)
0
 
lwadwellCommented:
Using GETDATE()
with hist_periods as (
select 3  hist_period, 2011 hist_year union all
select 7  hist_period, 2011 hist_year union all
select 8  hist_period, 2011 hist_year union all
select 9  hist_period, 2011 hist_year union all
select 10 hist_period, 2011 hist_year union all
select 11 hist_period, 2011 hist_year union all
select 12 hist_period, 2011 hist_year union all
select 1  hist_period, 2012 hist_year union all
select 2  hist_period, 2012 hist_year union all
select 3  hist_period, 2012 hist_year union all
select 4  hist_period, 2012 hist_year union all
select 5  hist_period, 2012 hist_year
)
select *, 'a'
        , DATEADD(mm, hist_period, DATEFROMPARTS(hist_year,3,1))                   as real_date
        , CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2)  as year_offset
  from hist_periods
 where DATEADD(mm, hist_period, convert(varchar, hist_year)+'-03-01') >= DATEFROMPARTS(year(getdate()), 1, 1) 
union all
select *, 'c'
        , DATEADD(mm, hist_period, DATEFROMPARTS(hist_year,3,1))                   as real_date
        , CONVERT(varchar, hist_year)+RIGHT('00'+CONVERT(varchar, hist_period),2)  as year_offset
  from hist_periods
 where hist_year >= year(getdate())
    or (hist_year = year(getdate())-1 and hist_period >= 10)

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now