Need help filtering a dataset - 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 filtering a dataset

Author

Topic

sphilip
Starting Member

8 Posts

Posted - 2010-05-11 : 12:10:14

I am trying to create a view which will bring me back the most recent FiscalCalYear and FiscalCalPeriod from a table along with the rest of the details in the row.

It needs to be dynamic so I thought using a max() would work, however, unless I hard code in the where FiscalCalYear = 2010 I can't get the rows back. I've tried a myriad of ideas, but they all bring back all rows for all years and periods. Any help would be greatly appreciated.


select 	ItemCode,
	WareHouseCode,
	FiscalCalYear,
	FiscalCalPeriod,
	BeginningBalQty,
	BeginningBalDollarAmt,
	BeginningBalExtendedStdCost,
	PeriodChangeDollarAmt,
	PeriodChangeExtendedStdCost
from #temp
--where FiscalCalYear = 2010
Group by ItemCode,
	WareHouseCode,
	FiscalCalYear,
	FiscalCalPeriod,
	BeginningBalQty,
	BeginningBalDollarAmt,
	BeginningBalExtendedStdCost,
	PeriodChangeDollarAmt,
	PeriodChangeExtendedStdCost
having 	FiscalCalYear = max(FiscalCalYear)
	and FiscalCalPeriod = max(FiscalCalPeriod)
order by FiscalCalYear,
	FiscalCalPeriod,
	WareHouseCode

sphilip
Starting Member

8 Posts

Posted - 2010-05-11 : 12:54:28

I think I got it, thanks to anyone who read or was working on a response. I must have spent a day and a half working on this one. If there is a better way of doing this, I'd be happy to read it.

Again, Thanks.

select 	ItemCode,
	WareHouseCode,
	FiscalCalYear,
	FiscalCalPeriod,
	BeginningBalQty,
	BeginningBalDollarAmt,
	BeginningBalExtendedStdCost,
	PeriodChangeDollarAmt,
	PeriodChangeExtendedStdCost
from #temp a
where FiscalCalPeriod = 
	(
	select max(c.FiscalCalPeriod)
	from #temp c
	where FiscalCalYear = 
		(
		select max(b.FiscalCalYear)
		from #temp b
		where b.FiscalCalYear <> 2017
		)
	)
and FiscalCalYear = 
	(
	select max(b.FiscalCalYear)
	from #temp b
	where b.FiscalCalYear <> 2017
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources