SQL query_select different column based on condition-CodePudding

These are my two tables:

Table1 has start date and end date.
Table2 has AllMonths and Month and Value column.

Scripts to create these tables.

CREATE TABLE [dbo].[Table1](
    [EmployeeId] [int] NULL,
    [CompanyId] [int] NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Table1] VALUES(12345,1205,'2021-01-01 00:00:00.000','2021-06-30 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23211,1205,'2021-01-01 00:00:00.000','2021-05-31 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23211,1205,'2021-07-01 00:00:00.000','2021-09-30 00:00:00.000')
INSERT INTO [dbo].[Table1] VALUES(23141,1205,'2021-01-01 00:00:00.000','2021-11-30 00:00:00.000')

CREATE TABLE [dbo].[Table2](
    [EmployeeId] [int] NULL,
    [CompanyId] [int] NULL,
    [AllMonths] [int] NULL,
    [Month] [int] NULL,
    [Value] [int] NULL
)

INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,2,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,3,1)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,4,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,6,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,9,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,11,2)
INSERT INTO [dbo].[Table2] VALUES(23211,1205,NULL,12,2)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(23141,1205,1,NULL,NULL)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,2,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,3,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,4,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,5,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,6,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,7,1)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,9,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,11,2)
INSERT INTO [dbo].[Table2] VALUES(12345,1205,NULL,12,2)

My desired output is:

Case #1: EmployeeId = 12345

AllMonths column is NULL. Startdate Jan and EndDate Nov. All these months from jan-nov have the same value in table 2. So the 'Final' value is 1.
Case #2: EmployeeId = 23221

AllMonths column is NULL. Startdate Jan and EndDate May. Jan-Mar have a value of 1 while Apr-May have a value of 2 in table 2. So in the output it should be split into two rows, Jan-Mar the 'Final' value is 1 and Apr-May value is 2.
Case #3: EmployeeId = 23141

AllMonths column is NOT NULL. So 'Final' value is 1 from AllMonths column.

Need some help on achieving this.

CodePudding user response：

There is 2 logic ,

i) when allmonths is null ii) allmonths is not null

First create Number table in whatever way you want,

Create Table tblNumber(Number int primary key)

INSERT INTO tblNumber
SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
FROM dbo.syscolumns tb1,dbo.syscolumns tb2

Final Script,

select *
,ROW_NUMBER()over( partition by employeeid order by companyid)rn1
into #table1
from table1

;with CTE as
(
select T1.*
,ROW_NUMBER()over(partition by rn1,employeeid order by companyid)rn
,ROW_NUMBER()over(partition by employeeid order by companyid)rn2
,DATEDIFF(month,startdate,enddate) 1monthDiff from #table1 T1
inner join [dbo].[tblNumber1] n on n.number<=(DATEDIFF(month,startdate,enddate) 1)
)
,CTE2 as
(
select T1.*
,dateadd(month,(rn-1),t1.startdate)NewEndDate
from CTE T1

)
,CTE1 as
(
select t1.companyid,t1.employeeid,t1.startdate,dateadd(month,(rn-1),t1.startdate)NewEndDate
,t1.Enddate,rn,rn1
,t2.[value] as Final 
from CTE2 t1
cross apply(select top 1 [value] from table2 t2 where t2.employeeid=t1.employeeid 
and t2.companyid=t1.companyid and month=month(NewEndDate) and allmonths is null)t2
union all
select t1.companyid,t1.employeeid,t1.startdate,dateadd(month,(rn-1),t1.startdate)NewEndDate
,t1.Enddate,rn,rn1
,t2.[allmonths] as Final 
from CTE2 t1
cross apply(select top 1 allmonths from table2 t2 where t2.employeeid=t1.employeeid 
and t2.companyid=t1.companyid  and allmonths is not null)t2
)



select companyid, employeeid,min(NewEndDate)StartDate,max(NewEndDate) EndDate,Final
from CTE1
group by companyid, employeeid,Final,rn1
order by employeeid,Final

drop table #table1

Point to note

i) I took sample data some hour ago.

ii) Test with other sample data and if it do not work with other sample data then paste that sample data seperately.

iii) How many rows are involve in all both table ?

iv) First let the output get corrected then query can be optimized.

v) First test when "allmonths is null" with various sample data

vi) Then test when "allmonths is not null" with various sample data

CodePudding user response：

This returns your desired output from the sample data you supplied:

SELECT 
    t1.CompanyId
    ,t1.EmployeeId
    ,StartDate  = FORMAT(ISNULL(DATEFROMPARTS(YEAR(StartDate), min(t2.month), 1), StartDate), 'MM/dd/yyyy')
    ,EndDate    = FORMAT(
                    ISNULL(
                        DATEFROMPARTS(YEAR(enddate), max(t2.month), 1)
                        ,DATEADD(dd, -DATEPART(dd, enddate)   1, enddate)
                    )
                    , 'MM/dd/yyyy')
    ,ISNULL(Allmonths, value) as Final
FROM [Table1] t1
INNER JOIN [Table2] t2 on t1.employeeid = t2.employeeid
                    AND t1.CompanyId = t2.CompanyId
                    AND (   (t2.allmonths IS NOT NULL) 
                            OR (t2.month BETWEEN DATEPART(MM, startdate) AND DATEPART(MM, enddate))
                        )
GROUP BY 
    t1.CompanyId
    ,t1.EmployeeId
    ,StartDate
    ,EndDate
    ,ISNULL(Allmonths, value)
ORDER BY 
    1, 3, 4