try it: select * from scheme.podetm d left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product where d.qty_received >0 and d.inv_value_posted =0 and (s.analysis_a not in ('LNG','INFANREUST') or s.analysis_a is null)

Having to deal with defective XML files is always fun... My 5 for seeing that little difference. Alternatively to your solution, write the file back line by line, just omitting the "bad" line. With that file, some tools could generate classes for its content, and thus making the access to the properties easier. The Japanese encoding could cause some more fun later on, be prepared for that.

You're welcome. :) Best regards, Andrius Leonavicius

Thanks Jorgen Unfortunately it not my database to add columns. I tried your query, but it was also slow, however that got me thinking more about the data I am querying. The vektronix table is actually quite large already after only a few months of production (about 8 rows for each unit produced) so I tried limiting the rows from vektronix in the where clause - that worked! ;WITH Dates AS ( SELECT GETDATE()as DateValue UNION ALL SELECT DateValue -1 FROM Dates WHERE DateValue -1 >= dateadd(d,-90,GETDATE()) ) SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty FROM Dates D left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t where [TrackingTypeId] = '1' and DateCreated >= dateadd(d,-90,GETDATE()) group by convert(date,DateCreated)) as trk on trk.TrkDate = convert(date,DateValue) group by DateValue,trk.Tot order by datevalue desc OPTION (MAXRECURSION 32747) Thanks for your help!

GuyThiebaut wrote: I take it there there is one central instance of an SQL Server rather than an instance on each workstation. If there is one central instance then try to get it off the OS drive(probably the C: drive) so that disk access is not competing between OS calls and SQL Server calls - does that make sense? Yes - one server instance on the server. Any yes I see what you mean. It makes sense, and I do remember reading something like that, but when I installed, I seemed to have missed where I could specify a different install location, so it installed on the C: drive (default).. At the moment there are way more serious performance hits in the app itself (populating too many datagrid rows at a time etc) - Only realized this when I got a copy of their data a year after the initial installation... I'd been testing with a bare bones DB on my machine - so yeah what those poor users have been going though - aw shame!

Yip, agreed, see the reply I posted to my own question with speed tests on various approaches, and reference to Eddy's link - Thanks!

Your relationship with MS Access is going to get worse - sorry it really is a crap tool for database work. There are a number of schools of though for the distributed database. Some like to us GUIDs, others like the location/ID concatenation. There are some where you have a master server for such things as products, only HO can add a code etc. I don't even think there are good guidelines on which to use where. Personally I prefer the location/id solution, I know of at least 1 respected member here who would recommend the GUID path (PITA to read the ID) so it will be your choice. You should look into replication before you start, designing so the data can be consolidated from the start is always a GREAT idea. Depending on your business requirements I would opt for a parallel application and write a script that can migrate your data to the new structure. This allows you to completely redesign your structure getting rid of the crap design you have now. Don't finalise your DB design until the bulk of yor app development is done, then make sure your migration script works. It should be possible to migrate your data at any time and repeat until the dev is complete. Consider using a web based solution if the local speed is acceptable - eliminates the distributed problems but introduces others (uptime issues) and criticallity. I recommend not using the cloud if your data is both critical and confidential. Good Luck you have interesting time ahead of you. Never underestimate the power of human stupidity RAH

Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'

Thanks Mycroft - will give it a try tonight

Mycroft Holmes wrote: Why would you not use WharehouseID-ProductID in a concatenated field as a primary key. That's a possibility; then again, it introduces a concatenation-action, and we'd be storing redundant information. It'd also affect performance; having a large varchar-based key (as two bigints as Id's or Guids would be concatenated to a varchar) would be not-nice for your indexes. Or, in the words of my teacher; it would no longer be an atomic value. Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Thanks PIEBALDconsult The problem was amongst other things the dates !! This works now: SELECT user_id, SUM(CASE WHEN (stkhstm.transaction_type = 'RECP' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end) , SUM(CASE WHEN (stkhstm.transaction_type = 'SRET' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end) FROM vektron.scheme.stkhstm stkhstm GROUP BY user_id 'BETWEEN' would be a bit more elegant I guess :)

Hello David Thanks very much for the suggestion The full DB was not as large as I though it would be so just used a full backup, took that home and restored it - all perfect! Thanks Richard

Hi Michael I had wondered about multiple warehouses :) In this particular case the product can exist in other warehouses, however the cost in the others does not need to be the same - only warehouse 01 and 02 must be the same. I did test your query, and it works perfectly! Thanks as I'm sure I'll need something like this in the future

SELECT b.RowNumber, iSrno, vUserCode, dLoggedDate FROM ( SELECT iSrno, vUserCode, dLoggedDate, ROW_NUMBER() OVER ( PARTITION BY vUserCode ORDER BY vUserCode DESC ) 'RowNumber' FROM HOUserLog) b WHERE RowNumber = 1 /* Here HOUserLog is the table from where the Group By MAX needs to be pick. ROw_Number() OVER ( PARTITION BY .... ) will generate the RowNumber according to the Group By Column you provided in ORDER BY ..... DESC Clause. Now pick the values for every RowNumber Column = 1 will get you the solution. MARK AS ANSWER IF WORKS - Happy Coding - Vishal Vashishta

Also at work, but on my way home, will test when I get home - and yes, using 2008

Richard.Berry100 wrote: but I was wondering if there was a way to eliminate declaring a DataTable, and then equating the DataRow to the first row from the DataTable? Why? You can simply load the reader in your datatable and return the first row from a method. That way the table is out of scope and discarded. If you really think that's consuming too much memory, then you should not use a DataRow, but an array of values[^]. Take a look at the section[^] that says "Working with DataReaders". Bastard Programmer from Hell :suss:

I haven't done any tests on record locking, so I may not be the best person to ask. but as I understand it, there are two type of locking (optimistic and pessimistic). Pessimistic only allows one user to use it at a time and will hold the lock. Optimistic, however, only locks the record at the moment it is updating then releases it. I am not sure what type of locking is implemented by the command line instruction above, but if it is optimistic it may not be possible to test it reliably. Sorry I can't be more helpful on that. Jack of all trades ~ Master of none.

Or Change your stock table to have an allocated field and increment/decrement that as the request is processed or cancelled/completed. It will depend on your business process, if the sales process takes minutes or a longer period this may suit. You have to deal with crash edge cases where you have orphaned allocated values. Never underestimate the power of human stupidity RAH