SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Need help filtering a dataset		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sphilip
Starting Member

8 Posts
Posted - 05/11/2010 :  12:10:14  Show Profile  Reply with Quote
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 - 05/11/2010 :  12:54:28  Show Profile  Reply with Quote
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
	)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000