I have 3 tables. I am basically trying to figure out how to display my data which I assume will be done using a Pivot script. But I am really clueless on how to achieve this having multiple set of columns for each Disbursement Interval. The desired output is to show 3 disbursement Intervals for each project name. As you can for each project name, there is a possibility of having 3 disbursement intervals. Each interval has a numeric number after each interval. The intervals are defined as:
ProviderRequestDate
ProviderRequestAmount
CommissionResponseDate
CommissionResponse
DisbursementDate
DisbursementAmount
I assume there will be some type of partition of intervals from the ProjectDisbursement table based on the CreateDate. Based on when the record was created it will be assigned a numeric disbursement level interval.
Here are my tables and sample records
Declare @ProjectIdentifier Table
(
[ProjectIdentifierId] [int] IDENTITY(1,1) NOT NULL
, [ProjectIdentifierName] [varchar](200) NOT NULL
, [FiscalYearId] [int] NOT NULL
)
Insert Into @ProjectIdentifier ([ProjectIdentifierName],[FiscalYearId]) Values ('18-00123',22)
Insert Into @ProjectIdentifier ([ProjectIdentifierName],[FiscalYearId]) Values ('19-00456',23)
Insert Into @ProjectIdentifier ([ProjectIdentifierName],[FiscalYearId]) Values ('20-00789',24)
Insert Into @ProjectIdentifier ([ProjectIdentifierName],[FiscalYearId]) Values ('21-00111',25)
Insert Into @ProjectIdentifier ([ProjectIdentifierName],[FiscalYearId]) Values ('22-00222',26)
Declare @Project Table
(
[ProjectId] [int] IDENTITY(1,1) NOT NULL
,[ProjectIdentifierId] [int] NOT NULL
,[ServiceProviderId] [int] NOT NULL
,[ProjectName] [varchar](200) NOT NULL
,[ProjectAwardAmount] [decimal](18, 2) NOT NULL
)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (3, 224, 'New Tower', 600000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (3, 224, 'Fiber', 900000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (3, 254, 'Drive', 300000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (3, 254, 'Anderson', 750000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (4, 285, 'Buffalo Rd', 450000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (4, 224, 'Rock Tower', 120000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (4, 224, 'Field Tower', 150000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (4, 161, 'Golf Course', 180000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (5, 224, 'Lake Tower', 210000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (5, 257, 'Lake Project', 240000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (5, 257, 'Valle Project', 60000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (5, 371, 'Broadband Project', 24000)
Insert Into @Project ([ProjectIdentifierId], [ServiceProviderId], [ProjectName], [ProjectAwardAmount]) Values (5, 672, 'Pecos Project', 375000)
Declare @ProjectDisbursement Table
(
[ProjectDisbursementID] [int] IDENTITY(1,1) NOT NULL
,[ProjectID] [int] NULL
,[ProviderRequestDate] [datetime] NULL
,[ProviderRequestAmount] [decimal](18, 2) NULL
,[CommissionResponseDate] [datetime] NULL
,[CommissionResponse] [varchar](200) NULL
,[DisbursementDate] [datetime] NULL
,[DisbursementAmount] [decimal](18, 2) NULL
,[CreateDate] [datetime] NULL
)
--24 FiscalID, ProjectDisbursement Details
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (1, '2020-12-29',200000,'2021-01-13','Direct Disburse', '2021-01-14', 200000, '2020-12-30 08:30:00.250')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (2, '2020-12-29',300000,'2021-01-13','Direct Disburse', '2021-01-14', 300000, '2020-12-30 09:35:00.150')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (3, '2021-10-01',100000,'2021-10-27','Direct Disburse', '2021-10-28', 100000, '2021-10-02 08:00:00.250')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (3, '2022-06-07',100000,'2022-07-13','Direct Disburse', '2022-07-18', 100000, '2022-06-09 09:30:00.250')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (3, '2022-08-09',100000,'2022-08-24','Direct Disburse', '2022-08-25', 100000, '2022-08-10 10:32:00.550')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (4, '2021-06-30',250000,'2021-10-27','Direct Disburse', '2021-07-30', 250000, '2021-07-01 08:30:00.250')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (4, '2021-11-17',250000,'2022-01-05','Direct Disburse', '2022-01-06', 250000, '2021-11-18 09:15:00.350')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (4, '2021-12-13',250000,'2022-01-05','Direct Disburse', '2022-01-06', 250000, '2022-12-15 11:25:00.150')
--25 FiscalID, ProjectDisbursement Details
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (5, '2022-07-06',150000,'2022-07-21','Direct Disburse', '2022-07-26', 150000, '2022-07-10 08:25:00.150')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (5, '2023-01-05',150000,'2023-01-25','Direct Disburse', '2023-01-26', 150000, '2023-01-05 10:25:00.250')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (6, '2022-07-06',40000,'2022-07-21','Direct Disburse', '2022-07-26', 40000, '2022-07-05 08:25:00.150')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (7, '2022-07-06',50000,'2022-07-21','Direct Disburse', '2022-07-26', 50000, '2022-07-05 08:26:00.450')
Insert Into @ProjectDisbursement ([ProjectID], [ProviderRequestDate], [ProviderRequestAmount], [CommissionResponseDate], [CommissionResponse], [DisbursementDate], [DisbursementAmount],[CreateDate]) Values (8, '2022-09-30',60000,'2022-10-12','Direct Disburse', '2022-11-04', 60000, '2022-10-01 08:15:00.350')
This is my desired output
Select '20-00789' As [ProjectIdentifierName],24 As [FiscalID], 224 As [ServiceProviderID], 'New Tower' As [ProjectName], cast(600000 as decimal(18,2)) As [TotalAward], '12/29/2020'as [ProviderRequestDate1], cast(200000 as decimal(18,2)) As [ProviderRequestAmount1], '01/13/2021' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '01/14/2021'as [DisbursementDate1], cast(200000 as decimal(18,2)) As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponse2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponse3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '20-00789' As [ProjectIdentifierName],24 As [FiscalID], 224 As [ServiceProviderID], 'Fiber' As [ProjectName], cast(900000 as decimal(18,2)) As [TotalAward], '12/29/2020'as [ProviderRequestDate1], cast(300000 as decimal(18,2)) As [ProviderRequestAmount1], '01/13/2021' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '01/14/2021'as [DisbursementDate1], cast(300000 as decimal(18,2)) As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponse2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponse3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '20-00789' As [ProjectIdentifierName],24 As [FiscalID], 254 As [ServiceProviderID], 'Drive' As [ProjectName], cast(300000 as decimal(18,2)) As [TotalAward]
,'12/29/2020'as [ProviderRequestDate1], cast(100000 as decimal(18,2)) As [ProviderRequestAmount1], '10/01/2021' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '10/27/2021' as [DisbursementDate1], cast(100000 as decimal(18,2)) As [DisbursementAmount1]
,'06/07/2022'as [ProviderRequestDate2], cast(100000 as decimal(18,2)) As [ProviderRequestAmount2], '07/13/2022' as [CommissionResponseDate2],'Direct Disburse' AS [CommissionResponse2], '07/18/2022' as [DisbursementDate2], cast(100000 as decimal(18,2)) As [DisbursementAmount2]
,'08/09/2022'as [ProviderRequestDate3], cast(100000 as decimal(18,2)) As [ProviderRequestAmount3], '08/24/2022' as [CommissionResponseDate3],'Direct Disburse' AS [CommissionResponse3], '08/25/2022' as [DisbursementDate3], cast(100000 as decimal(18,2)) As [DisbursementAmount3]
Union ALL
Select '20-00789' As [ProjectIdentifierName],24 As [FiscalID], 254 As [ServiceProviderID], 'Anderson' As [ProjectName], cast(750000 as decimal(18,2)) As [TotalAward]
,'06/30/2021'as [ProviderRequestDate1], cast(250000 as decimal(18,2)) As [ProviderRequestAmount1], '07/30/2021' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '08/02/2021' as [DisbursementDate1], cast(250000 as decimal(18,2)) As [DisbursementAmount1]
,'11/17/2021'as [ProviderRequestDate2], cast(250000 as decimal(18,2)) As [ProviderRequestAmount2], '01/05/2022' as [CommissionResponseDate2],'Direct Disburse' AS [CommissionResponse2], '01/06/2022' as [DisbursementDate2], cast(250000 as decimal(18,2)) As [DisbursementAmount2]
,'12/13/2021'as [ProviderRequestDate3], cast(250000 as decimal(18,2)) As [ProviderRequestAmount3], '01/05/2022' as [CommissionResponseDate3],'Direct Disburse' AS [CommissionResponse3], '01/06/2022' as [DisbursementDate3], cast(250000 as decimal(18,2)) As [DisbursementAmount3]
Union ALL
Select '21-00111' As [ProjectIdentifierName],25 As [FiscalID], 285 As [ServiceProviderID], 'Buffalo Rd' As [ProjectName], cast(450000 as decimal(18,2)) As [TotalAward]
, '07/06/2022'as [ProviderRequestDate1], cast(150000 as decimal(18,2)) As [ProviderRequestAmount1], '07/21/2022' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '07/26/2022'as [DisbursementDate1], cast(150000 as decimal(18,2)) As [DisbursementAmount1]
, '01/05/2023'as [ProviderRequestDate1], cast(150000 as decimal(18,2)) As [ProviderRequestAmount2], '01/25/2023' as [CommissionResponseDate2],'Direct Disburse' AS [CommissionResponse2], '01/26/2023'as [DisbursementDate2], cast(150000 as decimal(18,2)) As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponse3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '21-00111' As [ProjectIdentifierName],25 As [FiscalID], 224 As [ServiceProviderID], 'Rock Tower' As [ProjectName], cast(120000 as decimal(18,2)) As [TotalAward], '07/06/2022'as [ProviderRequestDate1], cast(40000 as decimal(18,2)) As [ProviderRequestAmount1], '07/21/2022' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '07/26/2022'as [DisbursementDate1], cast(40000 as decimal(18,2)) As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponse2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '21-00111' As [ProjectIdentifierName],25 As [FiscalID], 224 As [ServiceProviderID], 'Field Tower' As [ProjectName], cast(150000 as decimal(18,2)) As [TotalAward], '07/06/2022'as [ProviderRequestDate1], cast(50000 as decimal(18,2)) As [ProviderRequestAmount1], '07/21/2022' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '07/26/2022'as [DisbursementDate1], cast(50000 as decimal(18,2)) As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponse2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponse3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '21-00111' As [ProjectIdentifierName],25 As [FiscalID], 161 As [ServiceProviderID], 'Golf Course' As [ProjectName], cast(180000 as decimal(18,2)) As [TotalAward], '09/30/2022'as [ProviderRequestDate1], cast(60000 as decimal(18,2)) As [ProviderRequestAmount1], '10/12/2022' as [CommissionResponseDate1],'Direct Disburse' AS [CommissionResponse1], '11/04/2022'as [DisbursementDate1], cast(60000 as decimal(18,2)) As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponse2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponse3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '22-00222' As [ProjectIdentifierName],26 As [FiscalID], 224 As [ServiceProviderID], 'Lake Tower' As [ProjectName], cast(210000 as decimal(18,2)) As [TotalAward]
,NULL as [ProviderRequestDate1], NULL As [ProviderRequestAmount1], NULL as [CommissionResponseDate1], NULL AS [CommissionResponseDate1], NULL as [DisbursementDate1], NULL As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponseDate2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '22-00222' As [ProjectIdentifierName],26 As [FiscalID], 257 As [ServiceProviderID], 'Lake Project' As [ProjectName], cast(240000 as decimal(18,2)) As [TotalAward]
,NULL as [ProviderRequestDate1], NULL As [ProviderRequestAmount1], NULL as [CommissionResponseDate1], NULL AS [CommissionResponseDate1], NULL as [DisbursementDate1], NULL As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponseDate2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '22-00222' As [ProjectIdentifierName],26 As [FiscalID], 257 As [ServiceProviderID], 'Valle Project' As [ProjectName], cast(60000 as decimal(18,2)) As [TotalAward]
,NULL as [ProviderRequestDate1], NULL As [ProviderRequestAmount1], NULL as [CommissionResponseDate1], NULL AS [CommissionResponseDate1], NULL as [DisbursementDate1], NULL As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponseDate2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '22-00222' As [ProjectIdentifierName],26 As [FiscalID], 371 As [ServiceProviderID], 'Broadband Project' As [ProjectName], cast(24000 as decimal(18,2)) As [TotalAward]
,NULL as [ProviderRequestDate1], NULL As [ProviderRequestAmount1], NULL as [CommissionResponseDate1], NULL AS [CommissionResponseDate1], NULL as [DisbursementDate1], NULL As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponseDate2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
Union ALL
Select '22-00222' As [ProjectIdentifierName],26 As [FiscalID], 672 As [ServiceProviderID], 'Pecos Project' As [ProjectName], cast(375000 as decimal(18,2)) As [TotalAward]
,NULL as [ProviderRequestDate1], NULL As [ProviderRequestAmount1], NULL as [CommissionResponseDate1], NULL AS [CommissionResponseDate1], NULL as [DisbursementDate1], NULL As [DisbursementAmount1]
,NULL as [ProviderRequestDate2], NULL As [ProviderRequestAmount2], NULL as [CommissionResponseDate2], NULL AS [CommissionResponseDate2], NULL as [DisbursementDate2], NULL As [DisbursementAmount2]
,NULL as [ProviderRequestDate3], NULL As [ProviderRequestAmount3], NULL as [CommissionResponseDate3], NULL AS [CommissionResponseDate3], NULL as [DisbursementDate3], NULL As [DisbursementAmount3]
| col1 | col2 |
|---|---|
| data 1 | data 2 |
This will be something like:
with joined_and_numbered as (
select row_number() over (partition by p.projectid
order by pd.projectdisbursementid) rn,
pi.ProjectIdentifierName, pi.FiscalYearId, p.ServiceProviderID,
p.ProjectName, p.ProjectAwardAmount,
pd.ProviderRequestDate, pd.ProviderRequestAmount
from project p
join projectidentifier pi on p.ProjectIdentifierId = pi.ProjectIdentifierId
left join ProjectDisbursement pd on pd.projectid = p.projectid )
select ProjectIdentifierName, FiscalYearId, ServiceProviderID,
ProjectName, ProjectAwardAmount,
max(case rn when 1 then ProviderRequestDate end) ProviderRequestDate1,
max(case rn when 1 then ProviderRequestAmount end) ProviderRequestAmount1,
max(case rn when 2 then ProviderRequestDate end) ProviderRequestDate2,
max(case rn when 2 then ProviderRequestAmount end) ProviderRequestAmount2,
max(case rn when 3 then ProviderRequestDate end) ProviderRequestDate3,
max(case rn when 3 then ProviderRequestAmount end) ProviderRequestAmount3
from joined_and_numbered
group by ProjectIdentifierName, FiscalYearId, ServiceProviderID,
ProjectName, ProjectAwardAmount
Here I pivoted two columns, ProviderRequestDate and ProviderRequestAmount, the rest goes according to this pattern. These tables are complicated, I hope that I made joins correctly, you have to check this logic.
Important is how row_number works, partitioning and ordering, because on these values columns from table ProjectDisbursement are pivoted. But results corresponds with desired output, so I think it's OK.