SQL query_select different column based on condition

Question

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')
INSERT INTO [dbo].[Table1] values(54333,1205,'2021-01-01 00:00:00.000','2021-05-31 00:00:00.000') 
INSERT INTO [dbo].[Table1] values(76553,1205,'2021-01-01 00:00:00.000','2021-12-31 00:00:00.000')
INSERT INTO [dbo].[Table1] values(55555,1205,'2021-08-01 00:00:00.000','2021-09-30 00:00:00.000')
INSERT INTO [dbo].[Table1] values(55555,1205,'2021-11-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)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,1,1) 
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,2,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,3,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,4,2) 
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,6,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,8,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,9,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,10,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,11,1)
INSERT INTO [dbo].[Table2] VALUES(54333,1205,NULL,12,1)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,1,1)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,2,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,3,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,4,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,5,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,6,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,7,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,8,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,9,2) 
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,10,2)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,11,NULL)
INSERT INTO [dbo].[Table2] VALUES(76553,1205,NULL,12,NULL)

My desired output is:

Case #1: EmployeeId = 12345

AllMonths column is NULL. Startdate Jan and EndDate Jun. All these months from jan-jun have the same value in table 2. So the 'EmployeeClass' value is 1.
Case #2: EmployeeId = 23211

AllMonths column is NULL. Startdate Jan and EndDate May and Startdate July and EndDate Sep. 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 'EmployeeClass' value is 1 and Apr-May value is 2.Also for July-Sep, have a value of 2 so there is one more row for July-Sep with value 2.
Case #3: EmployeeId = 23141

AllMonths column is NOT NULL. So 'Final' value is 1 from AllMonths column.
Case #4: EmployeeId = 54333

AllMonths column is NULL. Startdate Jan and EndDate May. jan have a value of 1 and feb-may have a value of 2 in table 2. So in the output it should be split into two rows, Jan-Jan the 'EmployeeClass' value is 1 and Feb-May value is 2.
Case #5: EmployeeId = 55555

two rows for this employee in table1. But no rows in table 2. So these rows shld be availble in the output with 'EmployeeClass' value 'NULL'.
Case #6: EmployeeId = 76553

AllMonths column is NULL. Startdate Jan and EndDate Dec. jan have a value of 1 and feb-oct have a value of 2 and nov-dec have a value of 3 in table 2. So in the output it should be split into three rows, Jan-Jan the 'EmployeeClass' value is 1 and Feb-Oct value is 2 and Nov-Dec value is 'NULL'.

Need some help on achieving this.

KumarHarsh · Answer

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

wnutt · Answer

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

SQL query_select different column based on condition

2 Answers