question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked MelissaMa-msft answered

aggregating values based on a predicate

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]
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @db042190-2983,

Welcome to Microsoft Q&A!

Please also refer below:

 select acct,[acct type],reportadate,reportbdate,reportcdate,[program gid],[acct program instance]
 ,(SELECT MAX(reportdate) FROM (VALUES (reportadate),(reportbdate),(reportcdate)) AS reportdate(reportdate)) enddate 
 from (
 select acct ,[acct type]
 , max(case when reportnumber='a' then createdate end) reportadate
 , max(case when reportnumber='b' then createdate end) reportbdate
 , max(case when reportnumber='c' then createdate end) reportcdate 
 ,[program gid], [acct program instance]
 from aggregateonpredicates
 group by acct ,[acct type],[program gid], [acct program instance]) a

99671-output.png

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (10.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered

i'm wishing i'd added one more date (call it end date) whose max i also want based on the same groupings and filters on the same row as the other max's. at the moment i dont see an option for editing the original post so i'm hoping this will get the reader's attention too.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

Try this:

 ;WITH ReportDateA AS (
     SELECT [acct],  [acct type],  MAX(createdate) AS createdate, MAX([program gid]) AS [program gid], MAX([acct program instance]) AS [acct program instance]
     FROM aggregateonpredicates
     WHERE reportnumber = 'a'
     GROUP BY [acct], [acct type]
 )
 ,
 ReportDateB AS (
     SELECT [acct],  [acct type],  MAX(createdate) AS createdate, MAX([program gid]) AS [program gid], MAX([acct program instance]) AS [acct program instance]
     FROM aggregateonpredicates
     WHERE reportnumber = 'b'
     GROUP BY [acct], [acct type]
 )
 ,
 ReportDateC AS (
     SELECT [acct],  [acct type],  MAX(createdate) AS createdate, MAX([program gid]) AS [program gid], MAX([acct program instance]) AS [acct program instance]
     FROM aggregateonpredicates
     WHERE reportnumber = 'c'
     GROUP BY [acct], [acct type]
 )
    
 SELECT a.acct, a.[acct type], a.createdate AS reportadate, b.createdate AS reportbdate, c.createdate AS reportcdate, a.[program gid], a.[acct program instance]
 FROM ReportDateA AS a
 LEFT JOIN ReportDateB AS b ON a.acct = b.acct AND a.[acct type] = b.[acct type]
 LEFT JOIN ReportDateC AS c ON a.acct = c.acct AND a.[acct type] = c.[acct type];
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 Suspended answered

I think everything you have done here is wrong. And I'm one of the people that wrote the standards for this language. By definition, identity is a table property which can never be a proper key. This is usually covered in the first week of any class on RDBMS. Identifiers such as account numbers (not just the vague generic account that you used) are on a nominal scale and therefore cannot be numeric. You have no constraints or validation on any of the columns; for example, what exactly are the valid values for the account type? Why did you fail to prevent garbage? I am also very suspicious of anyone who uses a GUID. They usually don't understand that the G stands for global, and that a schema is done locally. Over the last few decades, non-relational programmers tend to create them as pointer structures so they can mimic the original Network databases where they began their programming. It is not certain, but I just found it works out that way. Did you know that ANSI/ISO standard SQL only uses the "yyyy-mm-dd" display format for dates? Why do you like ambiguity and a violation of standards? You also seem to think that the "union all" from the early days of Sybase is still a good way to do row construction. It's been obsolete for about 20 years.

Can you find someone where you work. Who knows about relational databases and how to write good SQL? Doing this on a forum is not a great way to get an education.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.