SQL Query - 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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

lgarcia3
Starting Member

15 Posts

Posted - 2011-05-04 : 13:05:35

Is there any way to have a query return results that skip records? I would like it to return records like this:

ID, Item, SoldDate, Revenue, TotalRevenueForPeriod
1, coffee, 5.00, 01/3/11, 14.00
2, coffee, 4.00, 01/5/11, 0.00
3, coffee, 5.00, 01,15,11, 0.00
4, Soda, 3.00, 01/8/11, 9.00
5, Soda, 3.00, 01/17/11, 0.00
6, Soda, 3.00, 01/21/11, 0.00

The problem is that I need to do some calculations that agregates the revenue per item per period as shown and use that to do some calculations against the individual items. So, this may be the ideal way to get my results. I have been thinking about using a function; but that would repeat my record... unless there is some other way.
Thanks for any ideas, comments, etc.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-04 : 13:32:13

>> Is there any way to have a query return results that skip records?
A where clause - apart from that I don't know what you mean, can you give an example.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-05-04 : 13:33:24

You need to post a sample of your data to go along with your expected output. We can't guess what you're trying to accomplish based alone on what you're expecting to see as a result.

Some days you're the dog, and some days you're the fire hydrant.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 13:40:38

Here's a start, or a guess really.

SELECT t1.id,t1.item,t1.Revenue,t1.soldDate
,[TotalRevenue] = case when t2.row = 1 then t2.TotalRevenue else 0 end
FROM yourTable t1
INNER JOIN
(
select *
,[TotalRevenue] = sum(Revenue) OVER(partition by item)
,[row] = row_number() over(partition by item order by solddate asc)
from yourTable
) t2
ON t1.id = t2.id

Jim

Everyday I learn something that somebody else already knew

lgarcia3
Starting Member

15 Posts

Posted - 2011-05-04 : 19:32:16

Jim, great! That is it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources