Grouping on MAX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Grouping on MAX

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 12:46:20

Good afternoon, I have a query set from my previous post that I now want to group by the "matter" and only return 1 row from what might be 1-12 rows (billing per month). The last month would then have the correct amount that was billed.

Here is my query so far: (not working correctly)

SELECT [Bill_Year]
,MAX(CAST([Bill_Month] AS INT)) as Bill_Month
,[Bill_Period]
,[Client]
,[RelClient]
,[Sort]
,[Matter]
,[Matter_Desc]
,MAX([Billed_YTD]) as Billed_YTD
FROM [Datapump_Staging].[dbo].[Elite_Matters]
WHERE Matter = '00000003-7000'
GROUP BY Matter,
bill_year,bill_month,bill_period,
Matter_Desc,
client,relclient,sort
ORDER BY bill_year,
bill_month,
Matter

This returns 12 rows in the example I am using, where I only want the single row with the greatest value, and only one row if all billed_ytd values are 0

Data Sample:

Bill_Year Bill_Month Bill_Period Client RelClient Sort Matter Matter_Desc Billed_YTD
2010 1 1009 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 115325.64
2010 2 1109 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 218326.21
2010 3 1209 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 272254.06
2010 4 0110 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 426491.95
2010 5 0210 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 469225.10
2010 6 0310 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 578301.03
2010 7 0410 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 666837.65
2010 8 0510 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 697156.75
2010 9 0610 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 796365.87
2010 10 0710 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 848248.65
2010 11 0810 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Subcontract with RG for State of Confusion 908671.32
2010 12 0910 00000003 00000003 ABC Corp - Loui 00000003-7000 XYZ Sub to RG for ABCDEF: OCD/DRU 1005167.47

Thanks in advance as always

Bryan Holmstrom

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-20 : 13:08:47

Just do a SELECT TOP 1 and order by the MAX([Billed_YTD]) DESC

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186283
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 13:18:36

This only gets me one record. I need the largest amount for each set of matter #'s ?

SELECT TOP 1
[Bill_Year]
,CAST([Bill_Month] AS INT) as Bill_Month
,[Bill_Period]
,[Client]
,[RelClient]
,[Sort]
,[Matter]
,[Matter_Desc]
,MAX([Billed_YTD]) as Billed_YTD
FROM [Datapump_Staging].[dbo].[Elite_Matters]
-- WHERE Matter = '00000003-7000'
GROUP BY Matter,
bill_year,bill_month,bill_period,
Matter_Desc,
client,relclient,sort,billed_ytd
ORDER BY [Billed_YTD] DESC

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 13:54:54

Good afternoon, I have a query set from a previous post that I now want to group by the "matter" and only return 1 row from what might be 1-12 rows (billing per month) per matter code. The last month of each matter group would then have the correct amount that was billed.

Bryan Holmstrom

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-20 : 14:13:14

If you want better help please see the links I posted previoiusly on how to ask your question so that we can help you better.

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 14:37:22

DDL

USE [Datapump_Staging]
GO

/****** Object: Table [dbo].[Elite_Matters] Script Date: 06/20/2013 14:36:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Elite_Matters](
[Bill_Year] [nchar](10) NULL,
[Bill_Month] [nchar](10) NULL,
[Bill_Period] [nchar](10) NULL,
[Client] [nchar](20) NULL,
[RelClient] [nchar](20) NULL,
[Sort] [varchar](100) NULL,
[Matter] [varchar](50) NULL,
[Matter_Desc] [varchar](100) NULL,
[Billed_YTD] [money] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 14:46:42

Data:

insert into elite_matter(
[Bill_Year],
[Bill_Month],
[Bill_Period],
[Client],
[RelClient],
[Sort],
[Matter],
[Matter_Desc],
[Billed_YTD]

) values

'2010','1','1009','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','115325.64'
'2010','2','1109','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','218326.21'
'2010','3','1209','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','272254.06'
'2010','4','0110','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','426491.95'
'2010','5','0210','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','469225.10'
'2010','6','0310','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','578301.03'
'2010','7','0410','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','666837.65'
'2010','8','0510','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','697156.75'
'2012','1','1009','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','195325.64'
'2012','2','1109','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','298326.21'
'2012','3','1209','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','292254.06'
'2012','4','0110','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','496491.95'
'2012','5','0210','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','499225.10'
'2012','6','0310','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','598301.03'
'2013','1','1209','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','292254.06'
'2013','2','0110','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','496491.95'
'2013','3','0210','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','499225.10'
'2013','4','0310','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','598301.03'

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-06-20 : 14:47:59

Expected Results:

'2010','8','0510','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','697156.75'
'2012','6','0310','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','598301.03'
'2013','4','0310','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','598301.03'

Bryan Holmstrom

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-20 : 16:01:26

Here is one wya to do it. I also fixed up the inserting of the data if it helps anyone else:

SELECT 
	*
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY Matter ORDER BY Bill_Year DESC, Bill_Month DESC) AS RowNum
		FROM
			Elite_Matters
	) AS T
WHERE
	RowNum = 1


insert into elite_matters(
[Bill_Year],
[Bill_Month], 
[Bill_Period],
[Client],
[RelClient],
[Sort],
[Matter],
[Matter_Desc],
[Billed_YTD]

) values

('2010','1','1009','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','115325.64'	 )
,('2010','2','1109','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','218326.21' )
,('2010','3','1209','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','272254.06' )
,('2010','4','0110','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','426491.95' )
,('2010','5','0210','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','469225.10' )
,('2010','6','0310','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','578301.03' )
,('2010','7','0410','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','666837.65' )
,('2010','8','0510','00000003','00000003','ABC Corp - Loui','00000003-7000','XYZ Subcontract with RG for State of Confusion','697156.75' )
,('2012','1','1009','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','195325.64' )
,('2012','2','1109','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','298326.21' )
,('2012','3','1209','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','292254.06' )
,('2012','4','0110','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','496491.95' )
,('2012','5','0210','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','499225.10' )
,('2012','6','0310','00000023','00000023','ABC Corp - Loui','00000003-7520','XYZ Subcontract with RG for State of Confusion','598301.03' )
,('2013','1','1209','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','292254.06' )
,('2013','2','0110','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','496491.95' )
,('2013','3','0210','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','499225.10' )
,('2013','4','0310','00000123','00000123','cfe Corp - Loui','00000203-7520','XYZ Subcontract with RG for State of Confusion','598301.03' )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources