Hi we run 2014 enterprise. i have a table whose create ddl , inserts and expected query results are shown in the code block. at least for now i'd like to try getting acct, etc, max(create date where report=a) , max (create date where report=b), max(report date where report=c) etc on the same returned row without a pivot grouped on acct, acct type, program gid, acct program instance. i'm reluctant to use pivot because i think some of these items can be null or missing. if possible i'd like to avoid a full outer join on the table itself across sub selects too. can the community help?
USE [research]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aggregateonpredicates](
[Id] [int] IDENTITY(1,1) NOT NULL,
[acct] [int] not null,
[acct type] tinyint not null,
createdate datetime,
reportnumber char(1),
[program gid] uniqueidentifier,
[acct program instance] uniqueidentifier,
CONSTRAINT [PK_aggregateonpredicates] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
declare @proggid1 uniqueidentifier = '7FD60407-7A34-4295-B04D-94F2CD59F63E'
declare @proggid2 uniqueidentifier = 'E4269072-8E76-4B07-A932-23B7B604400C'
declare @proggid3 uniqueidentifier = '0AC6DC09-0C9E-45BC-A241-08B474D5C4A0'
declare @acctgid1 uniqueidentifier = '74863A9F-C994-457A-8A1D-DC64BECEBDB1'
declare @acctgid2 uniqueidentifier = '001D615B-AFCB-4F55-9812-1E57387F42E8'
declare @acctgid3 uniqueidentifier = '63D33EEB-506F-42AC-8B95-A6EFFA8A516C'
insert aggregateonpredicates ([acct] , [acct type] , createdate , reportnumber , [program gid] , [acct program instance])
select 1,1,'1/1/2021','a',@proggid1,@acctgid1
union all
select 1,1,'1/2/2021','b',@proggid1,@acctgid1
union all
select 2,1,'1/3/2021','a',@proggid2,@acctgid2
union all
select 2,1,'1/4/2021','b',@proggid2,@acctgid2
union all
select 2,1,null,'c',@proggid2,@acctgid2
union all
select 2,2,'1/5/2021','a',@proggid3,@acctgid3
union all
select 2,2,'1/6/2021','b',@proggid3,@acctgid3
union all
select 2,2,'1/7/2021','b',@proggid3,@acctgid3
union all
select 2,2,'1/8/2021','c',@proggid3,@acctgid3
--expected results
select 1 acct,
1 [acct type],
cast('1/1/21' as datetime) reportadate,
cast('1/2/21' as datetime) reportbdate,
cast(null as datetime) reportcdate,
@proggid1 [program gid],
@acctgid1 [acct program instance]
union all
select 2 acct,
1 [acct type],
cast('1/3/21' as datetime) reportadate,
cast('1/4/21' as datetime) reportbdate,
cast(null as datetime) reportcdate,
@proggid2 [program gid],
@acctgid2 [acct program instance]
union all
select 2 acct,
2 [acct type],
cast('1/5/21' as datetime) reportadate,
cast('1/7/21' as datetime) reportbdate,
cast('1/8/21' as datetime) reportcdate,
@proggid3 [program gid],
@acctgid3 [acct program instance]
