Selecting a row based on the latest Report date an

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 2000 Forums

SQL Server Development (2000)

Selecting a row based on the latest Report date an

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-17 : 18:14:46

Hello All,

I have table that I want to select a row based on the color field and the latest report_date field. Below are the conditions that I’m looking for. I’ve tried several query but getting the desired solution. Does anyone know how to accomplish this task? Please advice.

Thanks.

Example:
- If you have multiple rows matches with the same report_date value and the same color value then Amount field will be grouped
- If you have multiple rows matches where the Report_date values are different with the same Color value then select the latest report_date value and group the Amount value.
- If you have a single value for color with a single report_date value then list the record

BEFORE
ID REPORT_DATE AMOUNT COLOR
11 08/29/07 30 BLUE
11 08/29/07 30 BLUE
11 09/12/07 30 BLUE
12 09/10/07 34 GREEN
12 09/13/07 30 GREEN
12 09/11/07 75 GREEN
12 09/13/07 34 GREEN
12 09/13/07 45 GREEN
13 09/13/07 80 PURPLE
13 09/13/07 80 PURPLE
14 09/13/07 30 BROWN
15 06/12/07 10 WHITE
15 09/14/07 20 WHITE
15 09/14/07 20 WHITE
16 09/17/07 100 RED
16 09/17/07 100 RED
16 09/17/07 100 RED
17 04/20/07 63 YELLOW

AFTER
ID REPORT_DATE AMOUNT COLOR
11 09/12/07 30 BLUE
12 09/13/07 109 GREEN
13 09/13/07 80 PURPLE
14 09/13/07 30 BROWN
15 09/14/07 20 WHITE
16 09/17/07 100 RED
17 04/20/07 63 YELLOW

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 18:41:28

[code]DECLARE @Sample TABLE (ID INT, REPORT_DATE DATETIME, AMOUNT INT, COLOR SYSNAME)

INSERT @Sample
SELECT 11, '08/29/07', 30, 'BLUE' UNION ALL
SELECT 11, '08/29/07', 30, 'BLUE' UNION ALL
SELECT 11, '09/12/07', 30, 'BLUE' UNION ALL
SELECT 12, '09/10/07', 34, 'GREEN' UNION ALL
SELECT 12, '09/13/07', 30, 'GREEN' UNION ALL
SELECT 12, '09/11/07', 75, 'GREEN' UNION ALL
SELECT 12, '09/13/07', 34, 'GREEN' UNION ALL
SELECT 12, '09/13/07', 45, 'GREEN' UNION ALL
SELECT 13, '09/13/07', 80, 'PURPLE' UNION ALL
SELECT 13, '09/13/07', 80, 'PURPLE' UNION ALL
SELECT 14, '09/13/07', 30, 'BROWN' UNION ALL
SELECT 15, '06/12/07', 10, 'WHITE' UNION ALL
SELECT 15, '09/14/07', 20, 'WHITE' UNION ALL
SELECT 15, '09/14/07', 20, 'WHITE' UNION ALL
SELECT 16, '09/17/07', 100, 'RED' UNION ALL
SELECT 16, '09/17/07', 100, 'RED' UNION ALL
SELECT 16, '09/17/07', 100, 'RED' UNION ALL
SELECT 17, '04/20/07', 63, 'YELLOW'

SELECT s.ID,
s.COLOR,
s.REPORT_DATE,
SUM(s.AMOUNT) AS AMOUNT
FROM (
SELECT ID,
MAX(REPORT_DATE) AS RD
FROM @Sample
GROUP BY ID
) AS d
INNER JOIN (
SELECT DISTINCT ID,
REPORT_DATE,
AMOUNT,
COLOR
FROM @Sample
) AS s ON s.ID = d.ID AND s.REPORT_DATE = d.RD
GROUP BY s.ID,
s.COLOR,
s.REPORT_DATE[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 18:42:25

Simpler

SELECT		s.ID,
		s.COLOR,
		s.REPORT_DATE,
		SUM(DISTINCT s.AMOUNT) AS AMOUNT
FROM		(
			SELECT		ID,
					MAX(REPORT_DATE) AS RD
			FROM		@Sample
			GROUP BY	ID
		) AS d
INNER JOIN	@Sample AS s ON s.ID = d.ID AND s.REPORT_DATE = d.RD
GROUP BY	s.ID,
		s.COLOR,
		s.REPORT_DATE

E 12°55'05.25"
N 56°04'39.16"

pras2007
Posting Yak Master

216 Posts

Posted - 2007-09-19 : 00:27:14

Peso,

Thanks for the reply, but when I tried your code I'm getting this error:

Server: Msg 409, Level 16, State 2, Line 1
The sum-unique aggregate operation cannot take a nvarchar data type as an argument.

Do you know what I'm doing wrong? Thanks once again.

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 00:42:00

You are trying to use SUM() on a column which is nvarchar, rather than numeric

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 02:15:19

Try CASTing the Amount column to MONEY.
I have to ask you, why on earth are you storing numeric data in a NVARCHAR column?

SELECT		s.ID,
		s.COLOR,
		s.REPORT_DATE,
		SUM(DISTINCT CAST(s.AMOUNT AS MONEY)) AS AMOUNT
FROM		(
			SELECT		ID,
					MAX(CAST(REPORT_DATE AS DATETIME)) AS RD
			FROM		@Sample
			GROUP BY	ID
		) AS d
INNER JOIN	@Sample AS s ON s.ID = d.ID AND s.REPORT_DATE = d.RD
GROUP BY	s.ID,
		s.COLOR,
		s.REPORT_DATE

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources