Need help to write this 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need help to write this query

Author

Topic

pmak
Starting Member

41 Posts

Posted - 2006-06-06 : 19:25:29

I have a table with the following fields, ID, State, Serial#, Version, Item, Year and Cost.

Eg of the data
1, NY, 1234, 1, Car, 2000, $5000
2, NY, 1234, 2, Boat, 2000, $12000
3, DC, 5678, 1, Airplane, 2001, $5000
4, DC, 5678, 2, Rocket, 2001, $6000
5, CA, 7896, 1, Car, 2002, $1500
6, CA, 7896, 2, Car, 2002, $2000
.
.
.
.
and so on....

I need to write a query using this table to find out for the same Serial # and on the same Year, how many records and the total cost of it as a result of the change on the fields "Item". It can use the field Version to determine the latest version on the Serial # on a given State and Year. For example ID 1 and 2, it changes the Item from Car to Boat and it constitutes to 2 records and the total cost of it is $17000. ID 5 and 6 NO CHANGE. The number of change for a Item can be more than one i.e. the Version could have more than 2 on the same Serial # and on the same year. Only change on the Item constitute a "Change"

So the query should return something like the following

NY, 2, 2000, $17000
DC, 2, 2001, $11000

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 22:03:26

[code]declare @t table ([ID] int, State varchar(2), Serial# int, Version int, Item varchar(20),
[Year] int, Cost int)
insert into @t([id],state,serial#,version,item,[year],cost)
select
1, 'NY', 1234, 1, 'Car', 2000, 5000
union select
2, 'NY', 1234, 2, 'Boat', 2000, 12000
union select
3, 'DC', 5678, 1, 'Airplane', 2001, 5000
union select
4, 'DC', 5678, 2, 'Rocket', 2001, 6000
union select
5, 'CA', 7896, 1, 'Car', 2002, 1500
union select
6, 'CA', 7896, 2, 'Car', 2002, 2000

select state,serial#,max(version),max(year),sum(cost)
from @t
group by state, serial#
having count(*)>1 and serial# not in(
select serial# from (
select state,serial#,item from @t
group by state,serial#,item
having count(*)>1
) a
)
[/code]

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 22:11:57

Is this what you want ?

declare @table table
(
	ID	int,
	State	varchar(10),
	Serial	varchar(10),
	Version	int,
	Item	varchar(20),
	Year	int,
	Cost	money
)

insert into @table
select 1, 'NY', '1234', 1, 'Car', 	2000, $5000	union all
select 2, 'NY', '1234', 2, 'Boat', 	2000, $12000	union all
select 3, 'DC', '5678', 1, 'Airplane', 	2001, $5000	union all
select 4, 'DC', '5678', 2, 'Rocket', 	2001, $6000	union all
select 5, 'CA', '7896', 1, 'Car', 	2002, $1500	union all
select 6, 'CA', '7896', 2, 'Car', 	2002, $2000

select	a.State, b.cnt, a.[Year], a.TotCost
from
(
	select 	State, [Year], sum(Cost) as TotCost
	from	@table t
	group by State, [Year]
) a inner join
(
	select	Serial, [Year], count(*) as cnt
	from	@table t
	group by Serial, [Year]
) b
on	a.[Year]	= b.[Year]

/* RESULT
State      cnt         Year        TotCost               
---------- ----------- ----------- --------------------- 
NY         2           2000        17000.0000
DC         2           2001        11000.0000
CA         2           2002        3500.0000
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources