# 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:

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
###### Who is Participating?

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.

Director, Practice Manager and Computing ConsultantCommented:
What will the relevant dates be if he had joined on 15 October; the same or different?
Author 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
Senior DBACommented:
SELECT Customer_no,
FROM (
SELECT 123 AS Customer_no, CAST('20101001' AS date) AS DateAdded UNION ALL
SELECT 234, '20141015'
) AS test_data
Senior 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
),
( 124, -- Customer_no - int
);

--Example 1
FROM dbo.Cust C;
``````
Results in this:
Author 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?
Senior 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:
here 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,
YearRanges.Label,
FROM dbo.Cust C
CROSS JOIN YearRanges
) p
PIVOT (MIN(d) FOR Label IN ([T1], [T2], [T3], [T4], [T5], [T6])) pvt
``````

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