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
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
What will the relevant dates be if he had joined on 15 October; the same or different?
ASKER
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
These will be the relevant ones if he joined on 10-15-10
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
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
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;ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice :-)
ASKER
Thanks