February 6, 2018 at 7:37 am
ContractName StartMonth EndMonth TargetMonth1 TargetMonth2 TargetMonth3... up to.... TargetMonth12
Contract1 01//2017 12/2017 20 30 40 10
Contract2 01//2017 12/2017 25 20 19 10
Contract3 03//2017 02/2018 20 30 40 10
Contract4 06//2017 05/2018 40 40 40 40
TargetMonth1 will apply to the first month the contract started, so in the case of Contract1 it will be Jan 2017, Contract3 will be March 2017 and so on. Then each target month figure after that will need to be applied to each subsequent month. So TargetMonth2 for Contract1 will apply to Feb 2017 whereas TargetMonth2 for Contract4 will apply to June 2017 up until the EndMonth.
ContractName MonthYear Target
Contract1 01//2017 20
Contract1 02//2017 30
Contract1 03//2017 40
Contract1 04//2017 10
Contract3 03//2017 20
Contract3 04//2017 30
Contract3 05//2017 40
Contract3 06//2017 10
I've been racking my brains and haven't a clue how to do this in SQL. Any help will be appreciated.
February 6, 2018 at 8:00 am
It looks like a fairly standard UNPIVOT. What are you having difficulties with?
The main issue that I see is that you are storing your dates in a non-standard format. Or are you having trouble translating the column to the date offset?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 6, 2018 at 8:02 am
you have posted in "SQL Server 7,2000 ".....what MS SQL version are you really running?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 6, 2018 at 9:15 am
J Livingston SQL - Tuesday, February 6, 2018 8:02 AMyou have posted in "SQL Server 7,2000 ".....what MS SQL version are you really running?
- »
Apoogies, using 2016.
February 6, 2018 at 9:17 am
drew.allen - Tuesday, February 6, 2018 8:00 AMIt looks like a fairly standard UNPIVOT. What are you having difficulties with?The main issue that I see is that you are storing your dates in a non-standard format. Or are you having trouble translating the column to the date offset?
Drew
The date formats are no problem, I can sort those. I wasn't sure of how to turn the TargetMonth1, TargetMonth2 etc into a Jan017, Feb2017 etc and have each of the targets assigned to those months? Unfortunately Month1 isn't always going to be January and that's one of the things confusing me.
February 6, 2018 at 9:46 am
I would use the Table Value Constructor version instead of the standard UNPIVOT, because it's more flexible and you need that flexibility here. I've used a sample where the StartDate is in a datetime format, you can replace that with whatever you're using to handle the dates.
SELECT ContractName, DATEADD(MONTH, n, StartMonth), [Target]
FROM tblTarget
CROSS APPLY
(
VALUES
(0, TargetMonth1),
(1, TargetMonth2),
(2, TargetMonth3),
(11, TargetMonth12)
) v(n, [Target])
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 6, 2018 at 10:01 am
drew.allen - Tuesday, February 6, 2018 9:46 AMI would use the Table Value Constructor version instead of the standard UNPIVOT, because it's more flexible and you need that flexibility here. I've used a sample where the StartDate is in a datetime format, you can replace that with whatever you're using to handle the dates.
SELECT ContractName, DATEADD(MONTH, n, StartMonth), [Target]
FROM tblTarget
CROSS APPLY
(
VALUES
(0, TargetMonth1),
(1, TargetMonth2),
(2, TargetMonth3),
(11, TargetMonth12)
) v(n, [Target])Drew
This seems to have done the trick. thank you so much for your help. Would never have figured that one out for myself.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply