Date to month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date to month

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 10:17:41

I have a 2 date fields 1 is start_date and the other is expire_date. I need to have a expire_date - start_date give me total months. Then I need to calculate the Annual_Value. Anything under 12 months should just show Price as Annual_value. Anything over 12 months should be (Price/Months)*12. Annual_Value and Months are new fields being created from the calculations.

Example is
Start_date Expire_date Months Price Annual_Value
1/31/2013 3/26/2013 2 450.00 450.00
1/31/2013 3/24/2016 34 6000.00 2117.65

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-12 : 10:39:03

Look at DATEDIFF(MONTH, Start_date, Expire_date)

djj

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 13:00:13

works and also doesn't work I notice something I should have taken into consideration. example.

start date exp date Months
5/1/2013 4/30/2016 35(this actually should be 36 because its the whole month of April) How would I fix this.

SELECT

DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) AS Contract_Months
,dbo.sa.price
,CASE WHEN DATEDIFF(MONTH, [rnu-date], [exp-date]) > '12' THEN ((price / DATEDIFF(MONTH,[rnu-date], [exp-date])) * 12) ELSE price END AS Annual_Value

FROM dbo.sa LEFT OUTER JOIN
dbo.customer ON dbo.sa.[cust-no] = dbo.customer.[Cust-no]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-12 : 13:21:41

1 + DATEDIFF(, , ) ???

Because DATEDIFF(DAY, ThisDay, ThisDay) equals 0.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 13:29:03

Ok so I was thinking about this a little more and decided that this might work with a Case when I just need help writing it.

Case when DATEDIFF(DAY, [rnu-date], [exp-date])< '15' THEN DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) ELSE (DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) + 1) End AS Contract_Months

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 13:37:58

Ok my code worked but now I need to figure out how to fix the next piece by nesting case in the next line of code.

Case when DATEDIFF(DAY, [rnu-date], [exp-date])< '15' THEN DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) ELSE (DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) + 1) End AS Contract_Months

This Line needs to reflect the same example as the line above it

CASE WHEN DATEDIFF(MONTH, [rnu-date], [exp-date]) > '12' THEN ((price / DATEDIFF(MONTH,[rnu-date], [exp-date])) * 12) ELSE price END AS Annual_Value

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-12 : 13:54:27

You may want to use a cte where you calculate the number of months (once) and can you that value in your case statements.

djj

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 14:04:52

How would I write that.

SELECT dbo.sa.[cust-no], dbo.customer.name, dbo.sa.[sa-no], dbo.sa.[sslspn-code], dbo.salesper.[Slspn-name], CONVERT(VARCHAR(10), dbo.sa.[orig-date], 101)
AS Create_date, CONVERT(VARCHAR(10), dbo.sa.[rnu-date], 101) AS Start_date, CONVERT(VARCHAR(10), dbo.sa.[exp-date], 101) AS [exp-date], dbo.sa.Status,
dbo.sa.[div-code], CASE WHEN DATEDIFF(DAY, [rnu-date], [exp-date]) < '15' THEN DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) ELSE (DATEDIFF(MONTH,
dbo.sa.[rnu-date], dbo.sa.[exp-date]) + 1) END AS Contract_Months, dbo.sa.price, CASE WHEN DATEDIFF(MONTH, [rnu-date], [exp-date])
> '12' THEN ((price / DATEDIFF(MONTH, [rnu-date], [exp-date])) * 12) ELSE price END AS Annual_Value
FROM dbo.sa LEFT OUTER JOIN
dbo.salesper ON dbo.sa.[sslspn-code] = dbo.salesper.[Slspn-code] LEFT OUTER JOIN
dbo.customer ON dbo.sa.[cust-no] = dbo.customer.[Cust-no]
WHERE (dbo.sa.[div-code] = '23')

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 14:11:46

WITH sa_CTE
AS
(SELECT dbo.sa.[cust-no], dbo.customer.name, dbo.sa.[sa-no], dbo.sa.[sslspn-code], dbo.salesper.[Slspn-name], CONVERT(VARCHAR(10), dbo.sa.[orig-date], 101)
AS Create_date, CONVERT(VARCHAR(10), dbo.sa.[rnu-date], 101) AS Start_date, CONVERT(VARCHAR(10), dbo.sa.[exp-date], 101) AS [exp-date], dbo.sa.Status,
dbo.sa.[div-code], CASE WHEN DATEDIFF(DAY, [rnu-date], [exp-date]) < '15' THEN DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) ELSE (DATEDIFF(MONTH,
dbo.sa.[rnu-date], dbo.sa.[exp-date]) + 1) END AS Contract_Months, dbo.sa.price
FROM dbo.sa LEFT OUTER JOIN
dbo.salesper ON dbo.sa.[sslspn-code] = dbo.salesper.[Slspn-code] LEFT OUTER JOIN
dbo.customer ON dbo.sa.[cust-no] = dbo.customer.[Cust-no]
WHERE (dbo.sa.[div-code] = '23'))

SELECT [cust-no], name, sa-no],[sslspn-code], Slspn-name], Create_date, Start_date, [exp-date], Status, [div-code], Contract_Months, price, CASE WHEN Contract_Months
> '12' THEN ((price / Contract_Months) * 12) ELSE price END AS Annual_Value
From sa_CTE

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 14:11:56

like that?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-12 : 14:16:01

That should work.

djj

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 14:18:23

nope lol

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-09-12 : 14:22:47

Stupid mistake. figured it out. I forgot to add the Fields after the First With sa_CTE

WITH sa_CTE ([cust-no], name, [sa-no],[sslspn-code], [Slspn-name], Create_date, Start_date, [exp-date], Status, [div-code], Contract_Months, price)
AS
(SELECT dbo.sa.[cust-no], dbo.customer.name, dbo.sa.[sa-no], dbo.sa.[sslspn-code], dbo.salesper.[Slspn-name], CONVERT(VARCHAR(10), dbo.sa.[orig-date], 101)
AS Create_date, CONVERT(VARCHAR(10), dbo.sa.[rnu-date], 101) AS Start_date, CONVERT(VARCHAR(10), dbo.sa.[exp-date], 101) AS [exp-date], dbo.sa.Status,
dbo.sa.[div-code], CASE WHEN DATEDIFF(DAY, [rnu-date], [exp-date]) < '15' THEN DATEDIFF(MONTH, dbo.sa.[rnu-date], dbo.sa.[exp-date]) ELSE (DATEDIFF(MONTH,
dbo.sa.[rnu-date], dbo.sa.[exp-date]) + 1) END AS Contract_Months, dbo.sa.price
FROM dbo.sa LEFT OUTER JOIN
dbo.salesper ON dbo.sa.[sslspn-code] = dbo.salesper.[Slspn-code] LEFT OUTER JOIN
dbo.customer ON dbo.sa.[cust-no] = dbo.customer.[Cust-no]
WHERE (dbo.sa.[div-code] = '23'))

SELECT [cust-no], name, [sa-no],[sslspn-code], [Slspn-name], Create_date, Start_date, [exp-date], Status, [div-code], Contract_Months, price, CASE WHEN Contract_Months
> '12' THEN ((price / Contract_Months) * 12) ELSE price END AS Annual_Value
From sa_CTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources