SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Grouping on MAX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  12:46:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 06/20/2013 :  13:08:47  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  13:18:36  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  13:54:54  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 06/20/2013 :  14:13:14  Show Profile  Reply with Quote
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.
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  14:37:22  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  14:46:42  Show Profile  Reply with Quote
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
Go to Top of Page

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 06/20/2013 :  14:47:59  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 06/20/2013 :  16:01:26  Show Profile  Reply with Quote
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' )

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000