sql Help - 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)

sql Help

Author

Topic

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-06-07 : 14:18:06

I have a table with the following columns:

MaterialNo
Cost
Active
CostRollVer

This question will be more easily understood if I tell you how data is populated. The MaterialNo field would normally be unique but the CostRollVer is being used to version the records. So between MaterialNo and CostRollVer if I know the CostRollVer parameter I should be able to retrieve a unique row. This table is populated from our ERP system and with that ERP data I spin through the records in the above table and compare the cost field between the table and ERP dataset if the cost field between the two is different I create a NEW record with the most recent version number populated in the CostRollVer field So now I would have 2 records both with different version numbers. Now if the ERP data spins against the table and the cost field equal each other the record is UPDATED the CostRollVer field is updated with the new version number.

So now that I told you that I can tell you what I need. As mentioned I have the CostRollVer parameter and I need to a dataset of unique MaterialNos based on that CostRollVer.

So it's just not as easy as saying "Select * from table where CostRollVer = 'parameter' " Because again I'm not always adding a new row per ERP update I'm just updating the version field CostRollVer if ERP.Cost = table.Cost. This also raises the issue of how do I account for if the a version number falls inbetween version numbers of the same MaterialNo

Sorry so long if you need anymore information let me know.

-Tom

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-06-07 : 14:51:00

I hope this helps: Here is an example of 4 CostRollVer fields:

20040101
20050101
20060101
20070101

and my CostRollVer parameter for the query would be '20060601'
The row I would need returned would be the 1 where CostRollVer = 20060101 becase that would be the relavent record at that time period.

MCP, MCSD

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 23:48:09

Hope this will help you,

declare @t table( col1 datetime)
insert @t
select '20040101' union all
select '20050101' union all
select '20060101' union all
select '20070101'

declare @val datetime
set @val = '20060601'

Select top 1 col1 from @t
order by abs(datediff(day, @val, col1))

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources