Select Problem - 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 2008 Forums

Transact-SQL (2008)

Select Problem

Author

Topic

Mike_h
Starting Member

7 Posts

Posted - 2010-03-22 : 03:35:56

I have an order table that has values similar to the ones below.
the first field is an order id. I am trying to write a select statement that will take the first record of each order based on the earliest date and output the results of that line but also doing a sum of the last two fields which are order value and lines

Order id cust Note Date Value Lines
0539071 CBN cust1 Urgent 180310 1008.80 114
0539071 BC cust1 190310 19.96 1
0539072 CBN cust2 180310 1271.08 20
0539072 S1 cust2 190310 79.92 1

I require the output to look like so
Order id cust Note Date Value Lines
0539071 CBN cust1 Urgent 180310 1028.76 115
0539072 CBN cust2 180310 1351.00 21

Any help would be greatly appreciated.
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 05:09:45

[code]
DECLARE @sample TABLE
(
[Order id] varchar(10),
[cust] varchar(5),
[Note] varchar(15),
[Date] int,
[Value] decimal(10,2),
[Lines] int
)
INSERT INTO @sample
SELECT '0539071', 'CBN', 'cust1 Urgent', 180310, 1008.80, 114 UNION ALL
SELECT '0539071', 'BC', 'cust1', 190310, 19.96, 1 UNION ALL
SELECT '0539072', 'CBN', 'cust2', 180310, 1271.08, 20 UNION ALL
SELECT '0539072', 'S1', 'cust2', 190310, 79.92, 1

SELECT a.[Order id], a.[cust], a.[Note],
a.[Date], a.TotValue, a.TotLines
FROM
(
SELECT *,
row_no = row_number() OVER (PARTITION BY [Order id] ORDER BY [Date]),
TotValue = SUM(Value) OVER (PARTITION BY [Order id]),
TotLines = SUM(Lines) OVER (PARTITION BY [Order id])
FROM @sample
) a
WHERE a.row_no = 1

/*
Order id cust Note Date TotValue TotLines
---------- ----- --------------- ----------- ---------------------------------------- -----------
0539071 CBN cust1 Urgent 180310 1028.76 115
0539072 CBN cust2 180310 1351.00 21

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources