Need help to calculate work experience in months

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)

Need help to calculate work experience in months

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-08-12 : 15:25:59

I've table and employment data as following,


CREATE TABLE [dbo].[x_CV_Employment](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[CVID] [int] NOT NULL,
	[Company] [nvarchar](100) NOT NULL,
	[StartDte] [datetime] NULL,
	[EndDte] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[x_CV_Employment] ON
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (1, 1449, N'Intel', CAST(0x0000A0C000000000 AS DateTime), CAST(0x0000A1CF00000000 AS DateTime))
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (2, 1449, N'Sony', CAST(0x0000A1D000000000 AS DateTime), CAST(0x0000A28F00000000 AS DateTime))
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (3, 1449, N'Yamaha', CAST(0x0000A2BA00000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[x_CV_Employment] OFF

If EndDate is NULL, it's mean - Present Date

Using DATEDIFF(month,StartDte,EndDte), my working experience on each row as following,



1449  | Intel   | 8 months
1449  | Sony    | 6 months
1449  | Yamaha  | 7 months

How to built single query to calculate work experience? As a result, our resultset as following,



CVID  | Working Experience
------------------------------------
1449  | 21 months

Please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 15:34:57

[code]
;with cte (WorkExperience)
as
(
select WorkExperience = case when [EndDte] is null then DATEDIFF(month,[StartDte],getdate()) else DATEDIFF(month,[StartDte],[EndDte]) end
from [x_CV_Employment]
)
select sum(WorkExperience) from cte
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-08-12 : 15:56:21

quote:
Originally posted by tkizer

;with cte (WorkExperience)
as
(
select WorkExperience = case when [EndDte] is null then DATEDIFF(month,[StartDte],getdate()) else DATEDIFF(month,[StartDte],[EndDte]) end
from [x_CV_Employment]
)
select sum(WorkExperience) from cte

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,

How about data as follows


CREATE TABLE [dbo].[x_CV_Employment](
	[idx] [int] IDENTITY(1,1) NOT NULL,
	[CVID] [int] NOT NULL,
	[Company] [nvarchar](100) NOT NULL,
	[StartDte] [datetime] NULL,
	[EndDte] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[x_CV_Employment] ON
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (1, 1449, N'Intel', CAST(0x0000A0C000000000 AS DateTime), CAST(0x0000A1CF00000000 AS DateTime))
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (2, 1449, N'Sony', CAST(0x0000A1D000000000 AS DateTime), CAST(0x0000A28F00000000 AS DateTime))
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (3, 1449, N'Yamaha', CAST(0x0000A2BA00000000 AS DateTime), NULL)
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (4, 1925, N'Google', CAST(0x0000A15900000000 AS DateTime), NULL)
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (5, 9002, N'GSK', CAST(0x0000A03D00000000 AS DateTime), CAST(0x0000A22C00000000 AS DateTime))
INSERT [dbo].[x_CV_Employment] ([idx], [CVID], [Company], [StartDte], [EndDte]) VALUES (6, 9002, N'ChipPac', CAST(0x0000A30000000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[x_CV_Employment] OFF

Now, have 3 Employee which is 1449, 1925, and 9002. I need to display the CVID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 15:57:44

[code]
;with cte (CVID, WorkExperience)
as
(
select CVID, WorkExperience = case when [EndDte] is null then DATEDIFF(month,[StartDte],getdate()) else DATEDIFF(month,[StartDte],[EndDte]) end
from [x_CV_Employment]
)
select CVID, sum(WorkExperience) as WorkExperience
from cte
group by CVID
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-08-12 : 16:24:12

tq mam :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 17:00:17

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources