September 10, 2014 at 3:09 pm
This is my table and data
CVID | WorkExperience
--------------------------------
28387 36
68181 101
96568 122
113548 4
I need to convert into this result
CVID | WorkExperience
--------------------------------
28387 3 years
68181 8 years 5 months
96568 12 years 2 months
113548 4 months
Need help
September 10, 2014 at 3:34 pm
Qira (9/10/2014)
This is my table and data
CVID | WorkExperience
--------------------------------
28387 36
68181 101
96568 122
113548 4
I need to convert into this result
CVID | WorkExperience
--------------------------------
28387 3 years
68181 8 years 5 months
96568 12 years 2 months
113548 4 months
Need help
This sort of formatting is always better in the presentation layer than it is in the database layer. What you're after is the modulus of the "workexperience" to get the months and the division to get the years. With no consumable sample data and with the fact that this looks decidedly like a homework assignment, I'll get you started by giving you the "months" bit. See if you can do the years bit on your own. . . remember that in SQL, if you divide an integer by an integer you can only get an integer back.
SELECT [CVID], [WorkExperience],
ISNULL(CAST(NULLIF([WorkExperience]%12,0) AS VARCHAR(2))+SPACE(1)+'months','')
FROM [SAMPLEDATA];
September 10, 2014 at 4:06 pm
Hello sir,
How about year? I cant imagine that
September 10, 2014 at 4:11 pm
Qira (9/10/2014)
Hello sir,How about year? I cant imagine that
Really?
Cadavre (9/10/2014)
This sort of formatting is always better in the presentation layer than it is in the database layer. What you're after is the modulus of the "workexperience" to get the months and the division to get the years. With no consumable sample data and with the fact that this looks decidedly like a homework assignment, I'll get you started by giving you the "months" bit. See if you can do the years bit on your own. . . remember that in SQL, if you divide an integer by an integer you can only get an integer back
Did you read the text that accompanied my answer? Emphasis has been added to make it clearer.
What have you tried?
September 10, 2014 at 4:26 pm
Hihi. This is my code
SELECT CVID, WorkExperience, Convert(varchar(10),WorkExperience / 12) +SPACE(1)+'years' + ' ' +
ISNULL(CAST(NULLIF([WorkExperience]%12,0) AS VARCHAR(2))+SPACE(1)+'months','') as WorkExperience_2
from
(
select CVID, sum(WorkExperience) as WorkExperience
from
(select CVID, WorkExperience = case when [EndDate] is null
then DATEDIFF(month,[StartDate],getdate()) else DATEDIFF(month,[StartDate],[EndDate]) end
from [Employment])V1
group by CVID
)V1
Now need to replace 0 years with nothing
September 10, 2014 at 10:22 pm
More for fun, here is a quick alternative solution
😎
USE tempdb;
GO
DECLARE @NOW DATE = GETDATE();
;WITH CV_WORKEXP(CVID,StartDate,EndDate) AS
(
SELECT CVID,StartDate,EndDate FROM
(VALUES
(28387 ,'2008-01-01','2012-09-01' )
,(68181 ,'2012-09-01','2014-06-01' )
,(96568 ,'2014-06-01','2014-09-01' )
,(113548,'2014-09-01',NULL)) AS X(CVID,StartDate,EndDate)
)
,WORKXP_BASE AS
(
SELECT
WX.CVID
,ISNULL(CONVERT(VARCHAR(10),NULLIF(DATEDIFF(MONTH,WX.StartDate,ISNULL(WX.EndDate,@NOW)) / 12, 0 ),1) + ' years ' ,'') +
ISNULL(CONVERT(VARCHAR(10),NULLIF(DATEDIFF(MONTH,WX.StartDate,ISNULL(WX.EndDate,@NOW)) % 12, 0 ),1) + ' months','') +
ISNULL(SPACE(NULLIF(SIGN(DATEDIFF(MONTH,WX.StartDate,ISNULL(WX.EndDate,@NOW))),1)) + 'less than a month','') AS WorkExperience
FROM CV_WORKEXP WX
)
SELECT
WB.CVID
,WB.WorkExperience
FROM WORKXP_BASE WB
Results
CVID WorkExperience
----------- ------------------
28387 4 years 8 months
68181 1 years 9 months
96568 3 months
113548 less than a month
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply