I have two tables Position Table and Position History table

Position table has columns such as Col1,Col2,Col3,Col4,Col5...........C15

Position History table is shown below:

Position	Resource	Position Start date	Position End date	Resource start date	Resource endate
B1	R1	1/2/2015	1/2/2016	21/2/2015	3/4/2015
B1	R2	1/2/2015	1/2/2016	5/5/2015	6/6/2015
B1	R3	1/2/2015	1/2/2016	10/6/2015	8/8/2015
B2	R4	2/2/2015	2/2/2016	2/3/2015	3/4/2015
B2	R5	2/2/2015	2/2/2016	15/4/2015	16/5/2015
B2	R6	2/2/2015	2/2/2016	16/6/2015	17/7/2015
B2	R7	2/2/2015	2/2/2016	20/8/2015	19/9/2015

Calculation required for position B1: B1 Position Start date R1 Start date= D1 Days

                                                                         R1 endate R2 Start date=D2 Days

                                                                         R2 Endate- R3 Start date= D3 Days

Total Vacancy for B1 =(D1+D2+D3)/3. 3 is resource count for Position B1.

Calculation required for position B2: B2 Position Start date-R4 Start date= D1 Days

                                                                         R4 endate - R5 Start date=D2 Days

                                                                         R5 Endate - R6 Start date= D3 Days

                                                                         R6 Endate R7 Start date=D4  Days

Total Vacancy for B2=(D1+D2+D3+D4)/4. 4 is resource count for Position B2.

Position Number	Col2	Col3	Col4	Col5	Total Fill Rate for Postion
B1	some value	some value	some value	some value	Total Vacancy for B1
B2	some value	some value	some value	some value	Total Vacancy for B2

Hi SatiGR,

Please provide your sample data and desired output in form of table variable and some insert statements .

Hi,

Firstly I of course agree with Milan that we need DDL+DML, [+1]
but I think that using table variable just for the forum's question while the real table is o-disk/In-memory, is not a good idea since we better get the same structure as the original table, in order to give the best solution. Moreover, at least we need indexes for most cases and for this (Unless you use SQL 2014, which added this feature) table variable.

In short :-)

SatiGR Please post (1) queries to create the relevant tables (using as close as possible to the original structure including indexes, constrain, etc'), and (2) Please post queries to insert the sample data, instead of posting it as text, and (3) please post in the content of the message what is the expected result according to the sample data.

Thanks,

Please find the scripts for sample table

CREATE

TABLE[dbo].[PositionTable](

[Position Number] [nvarchar]

(50)NOTNULL,

[Col1] [nvarchar]

(50)NULL,

[Col2] [nvarchar]

(50)NULL,

[Col3] [nvarchar]

(50)NULL,

[Col4] [nvarchar]

(50)NULL

)

ON[PRIMARY]

GO

INSERT

INTO[dbo].[PositionTable]

([Position Number]

     

,[Col1]

     

,[Col2]

     

,[Col3]

     

,[Col4])

 

SELECT

 

'B1'

     

,'val1'

 

,'val12'

 

,'val13'

 

,'val14'

 

UNION

   

SELECT

 

'B2'

     

,'val1'

 

,'val12'

 

,'val13'

 

,'val14'

CREATE

TABLE[dbo].[PositionHistory](

[Position Number] [nvarchar]

(50)NOTNULL,

[Resource] [nvarchar]

(50)NULL,

[Position Start Date] [datetime]

NULL,

[Position End Date] [datetime]

NULL,

[Resource Start Date] [datetime]

NULL,

[Resource end date] [datetime]

NULL

)

ON[PRIMARY]

GO

Data for position history will look

Position   Number	Resource	Position Start   Date	Position End   Date	Resource Start   Date	Resource end   date
B1	R1	2/1/2015	1/31/2016	2/2/2015	5/15/2015
B1	R2	2/1/2015	1/31/2016	6/15/2015	7/15/2015
B1	R3	2/1/2015	1/31/2016	8/15/2015	NULL
B2	R4	2/1/2015	1/31/2016	3/9/2015	4/10/2015
B2	R5	2/1/2015	1/31/2016	4/20/2015	6/26/2015
B2	R6	2/1/2015	1/31/2016	3/2/2015	5/29/2015
B2	R7	2/1/2015	1/31/2016	7/21/2015	NULL

I need Dynamic SQL Query to achieve below requirement and output as shown in below table. In future there is a possibility that resource will grow for both B1 and B2. Now B1 has 3 resources, future it might have 4 or 5 or 6...so on.

Calculation required for position B1: B1 Position Start date R1 Start date= D1 Days

                                                                         R1 endate R2 Start date=D2 Days

                                                                         R2 Endate- R3 Start date= D3 Days

Total Vacancy for B1 =(D1+D2+D3)/3. 3 is resource count for Position B1.

Calculation required for position B2: B2 Position Start date-R4 Start date= D1 Days

                                                                         R4 endate - R5 Start date=D2 Days

                                                                         R5 Endate - R6 Start date= D3 Days

                                                                         R6 Endate R7 Start date=D4  Days

Total Vacancy for B2=(D1+D2+D3+D4)/4. 4 is resource count for Position B2.

Position Number	Col2	Col3	Col4	Col5	Total Fill Rate for Postion
B1	some value	some value	some value	some value	Total Vacancy for B1
B2	some value	some value	some value	some value	Total Vacancy for B2

Assuming you're on 2012+, you can use the windowing functions

LAG(Position End Date, 1, Position Start Date) over (PARTITION BY Position)

might need to tweak a touch but that should give you your "end date", subtract that from your start date, then sum up the results and divide by the count