SQL Get first and last date from Period Table

Hello Experts Exchange
I have a SQL Server 2012 table with a finance period in it. Please see attached Excel for example data.

I need SQL Script that gives me the first and last date of the pervious period.

So for example the pervious period for today is period 8 and the first date is 02/08/2015 and the last date is 29/08/2015.

How do I get these dates using SQL.

Regards

SQLSearcher
Company-Period.xls
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT MIN(t1.Date), MAX(t1.Date)
FROM tableName t1
WHERE t.Period = (SELECT TOP 1 t2.Period-1 
                         FROM tableName t2
                         WHERE t2.Date = GETDATE())

Open in new window

Éric MoreauSenior .Net ConsultantCommented:
It is a bit more complex than what Vitor posted simply because your periods are repeating each year.

Maybe something like this would work:
declare @CurrentPeriod int
declare @CurrentYear int
declare @PreviousPeriod int
declare @PreviousYear int

select top 1 @CurrentPeriod = Period, @CurrentYear = Year 
from #periods 
where Date <= getdate()
order by Date desc

SELECT TOP 1 @PreviousPeriod = Period, @PreviousYear = Year 
FROM #periods
WHERE CAST(Year AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(Period AS VARCHAR(2)), 2) < CAST(@CurrentYear AS VARCHAR(4)) + '-' + RIGHT('00' + CAST(@CurrentPeriod AS VARCHAR(2)), 2)
ORDER BY Date DESC

SELECT MIN(Date), MAX(date)
FROM #periods
WHERE Year = @PreviousYear
AND Period = @PreviousPeriod

Open in new window

Vikas GargAssociate Principal EngineerCommented:
Hello,

You can try like this

SELECT MIN(Date), MAX(Date)
FROM tableName
WHERE Period = (SELECT period FROM tableName t2 WHERE t2.Date = GETDATE()) -1

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Eric, good point about the year but I would put all in a single select:

WITH CTE_Period AS
    (SELECT TOP 1 
         CASE t2.Period
              WHEN 1 THEN 12 -- If January, returns December
              ELSE t2.Period-1 -- otherwise returns previous month
         END AS Previous_Period, 
         CASE t2.Period
              WHEN 1 THEN YEAR(t2.Date)-1 -- If January, returns last year
               ELSE YEAR(t2.Date) -- otherwise returns current year
         END AS Period_Year
     FROM tableName t2
     WHERE t2.Date = GETDATE())

SELECT MIN(t1.Date), MAX(t1.Date)
FROM tableName t1
    INNER JOIN CTE_Period p ON t1.Period = p.Previous_Period AND t1.year = p.period_year

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
Vikas, your query won't return result in January!
SQLSearcherAuthor Commented:
Thank you for your help.
Vikas GargAssociate Principal EngineerCommented:
Hi,

Thanks Eric for your comment.

I didn't realize that possibility ..
PortletPaulEE Topic AdvisorCommented:
There is a column [Period_Count_AV] which increments by 1 for each change of period.

Locate the value of [Period_Count_AV] where [Date] = cast(getdate() as date) -- (today)
deduct 1 from that value
locate rows where [Period_Count_AV] = that value
get the min and max dates from those rows

CREATE TABLE Table1
    ([DatesID] int, [Period_Count_AV] int, [Date] date)
;
    
INSERT INTO Table1
    ([DatesID], [Period_Count_AV], [Date])
VALUES
    (5693, 188, '2015-08-02 00:00:00'),
    (5694, 188, '2015-08-03 00:00:00'),
    (5695, 188, '2015-08-04 00:00:00'),
    (5696, 188, '2015-08-05 00:00:00'),
    (5697, 188, '2015-08-06 00:00:00'),
    (5698, 188, '2015-08-07 00:00:00'),
    (5699, 188, '2015-08-08 00:00:00'),
    (5700, 188, '2015-08-09 00:00:00'),
    (5701, 188, '2015-08-10 00:00:00'),
    (5702, 188, '2015-08-11 00:00:00'),
    (5703, 188, '2015-08-12 00:00:00'),
    (5704, 188, '2015-08-13 00:00:00'),
    (5705, 188, '2015-08-14 00:00:00'),
    (5706, 188, '2015-08-15 00:00:00'),
    (5707, 188, '2015-08-16 00:00:00'),
    (5708, 188, '2015-08-17 00:00:00'),
    (5709, 188, '2015-08-18 00:00:00'),
    (5710, 188, '2015-08-19 00:00:00'),
    (5711, 188, '2015-08-20 00:00:00'),
    (5712, 188, '2015-08-21 00:00:00'),
    (5713, 188, '2015-08-22 00:00:00'),
    (5714, 188, '2015-08-23 00:00:00'),
    (5715, 188, '2015-08-24 00:00:00'),
    (5716, 188, '2015-08-25 00:00:00'),
    (5717, 188, '2015-08-26 00:00:00'),
    (5718, 188, '2015-08-27 00:00:00'),
    (5719, 188, '2015-08-28 00:00:00'),
    (5720, 188, '2015-08-29 00:00:00'),
    (5721, 189, '2015-08-30 00:00:00'),
    (5722, 189, '2015-08-31 00:00:00'),
    (5723, 189, '2015-09-01 00:00:00'),
    (5724, 189, '2015-09-02 00:00:00'),
    (5725, 189, '2015-09-03 00:00:00'),
    (5726, 189, '2015-09-04 00:00:00'),
    (5727, 189, '2015-09-05 00:00:00'),
    (5728, 189, '2015-09-06 00:00:00'),
    (5729, 189, '2015-09-07 00:00:00'),
    (5730, 189, '2015-09-08 00:00:00'),
    (5731, 189, '2015-09-09 00:00:00'),
    (5732, 189, '2015-09-10 00:00:00'),
    (5733, 189, '2015-09-11 00:00:00'),
    (5734, 189, '2015-09-12 00:00:00'),
    (5735, 189, '2015-09-13 00:00:00'),
    (5736, 189, '2015-09-14 00:00:00'),
    (5737, 189, '2015-09-15 00:00:00'),
    (5738, 189, '2015-09-16 00:00:00'),
    (5739, 189, '2015-09-17 00:00:00'),
    (5740, 189, '2015-09-18 00:00:00'),
    (5741, 189, '2015-09-19 00:00:00'),
    (5742, 189, '2015-09-20 00:00:00'),
    (5743, 189, '2015-09-21 00:00:00'),
    (5744, 189, '2015-09-22 00:00:00'),
    (5745, 189, '2015-09-23 00:00:00'),
    (5746, 189, '2015-09-24 00:00:00'),
    (5747, 189, '2015-09-25 00:00:00'),
    (5748, 189, '2015-09-26 00:00:00'),
    (5749, 189, '2015-09-27 00:00:00'),
    (5750, 189, '2015-09-28 00:00:00'),
    (5751, 189, '2015-09-29 00:00:00'),
    (5752, 189, '2015-09-30 00:00:00')
;

Open in new window

Using this query:
select
      max(Period_Count_AV) as Period_Count_AV
    , min([Date]) as start_dt
    , max([Date]) as end_dt
from table1
where Period_Count_AV = (select Period_Count_AV-1 
                         from table1 
                         where [Date] = cast(getdate() as date)
                        )

Open in new window

sample Result
| Period_Count_AV |   start_dt |     end_dt |
|-----------------|------------|------------|
|             188 | 2015-08-02 | 2015-08-29 |

Open in new window

also see http://sqlfiddle.com/#!6/c0cfd2/3
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.