time periods

Assuming a person joined a firm on 10-01-10

I need to derive the following time periods - 10-1-10 , 9-30-11, 10-1-11 , 9-30-12, 10-1-12 , 9-30-13, pls suggest the SQL

for ex:

Customer_no        DateAdded
 123            10-01-10      

Result should be as below:


Customer_no        DateAdded           T1              T2             T3             T4              T5             T6  

   123            10-01-10            10-1-10       9-30-11     10-1-11      9-30-12     10-1-12     9-30-13
sqlcuriousAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What will the relevant dates be if he had joined on 15 October; the same or different?
sqlcuriousAuthor Commented:
10-15-10 , 10-14-11, 10-15-11 , 10-14-12, 10-15-12 , 10-14-13

These will be the relevant ones if he joined on 10-15-10
Scott PletcherSenior DBACommented:
SELECT Customer_no,
    DateAdded AS T1,    
    DATEADD(DAY, -1, DATEADD(YEAR, 1, DateAdded)) AS T2,
    DATEADD(YEAR, 1, DateAdded) AS T3,
    DATEADD(DAY, -1, DATEADD(YEAR, 2, DateAdded)) AS T4,
    DATEADD(YEAR, 2, DateAdded) AS T5,
    DATEADD(DAY, -1, DATEADD(YEAR, 3, DateAdded)) AS T6
FROM (
    SELECT 123 AS Customer_no, CAST('20101001' AS date) AS DateAdded UNION ALL
    SELECT 234, '20141015'
) AS test_data
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Chris LuttrellSenior Database ArchitectCommented:
Try this:
-- Given table 
CREATE TABLE Cust (Customer_no INT, DateAdded DATE);
INSERT INTO dbo.Cust ( Customer_no, DateAdded )
VALUES  ( 123, -- Customer_no - int
          '10/1/10'  -- DateAdded - date
          ),
		  ( 124, -- Customer_no - int
          '10/15/10'  -- DateAdded - date
          );

--Example 1
SELECT C.Customer_no, C.DateAdded, C.DateAdded T1, DATEADD(YEAR, 1, DATEADD(DAY, -1, C.DateAdded)) T2, DATEADD(YEAR, 1, C.DateAdded) T3, DATEADD(YEAR, 1, DATEADD(DAY, -1, C.DateAdded)) T4, DATEADD(YEAR, 1, C.DateAdded) T5, DATEADD(YEAR, 1, DATEADD(DAY, -1, C.DateAdded)) T6
FROM dbo.Cust C;

Open in new window

Results in this: Example of results
sqlcuriousAuthor Commented:
Thanks so much Chris , but have a small issue here
requirement says that If the person was added 10-15-12, we would need time periods 10-15-12, 10-14-13 and 10-15-13 only.
So only T1, T2 and T3 will have the date values, rest of them will be null as we donot have 10-15-14 yet(it's a date in future), how would I handle that?
Chris LuttrellSenior Database ArchitectCommented:
OK, with the same setup table you can set up a CTE with your ranges and then use a PIVOT to get these results:
Query Resultshere is code for the CTE and PIVOT:
WITH YearRanges(Label, YearsToAdd, DaysToSubtract) AS 
(
SELECT 'T1', 0, 0
UNION ALL
SELECT 'T2', 1, 1
UNION ALL
SELECT 'T3', 1, 0
UNION ALL
SELECT 'T4', 2, 1
UNION ALL
SELECT 'T5', 2, 0
UNION ALL
SELECT 'T6', 3, 1
)
SELECT * FROM 
(
SELECT  C.Customer_no, 
        C.DateAdded, 
		YearRanges.Label,
        DATEADD(YEAR, YearRanges.YearsToAdd, DATEADD(DAY, -YearRanges.DaysToSubtract, C.DateAdded)) d
FROM dbo.Cust C
CROSS JOIN YearRanges
WHERE DATEADD(YEAR, YearRanges.YearsToAdd, DATEADD(DAY, -YearRanges.DaysToSubtract, C.DateAdded)) <= GETDATE()
) p
PIVOT (MIN(d) FOR Label IN ([T1], [T2], [T3], [T4], [T5], [T6])) pvt

Open in new window


The CTE is just a convenient way to create the work table, you could just as easy create a permanent helper table to hold those time period range values and use it to accomplish the same results.

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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nice :-)
sqlcuriousAuthor Commented:
Thanks
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
Microsoft SQL Server

From novice to tech pro — start learning today.