|
I have been trying to make this work, but so far no luck. Probably an easy one for someone with a bit more experience. Here is a simplified explanation.
I have two tables, the primary table is an inventory table listing items identifed by a unique inventory number and the second is a record of transactions. A record is added to the second table when a transaction occurs that affects an item in the first table. For instance when an item is sold, a transaction is created in the transaction table - "Sold" designated by the letter "S", a new item received is designated with the letter "R" etc. Occasionally an item is listed as "received" that is already in the inventory table or an item is listed as "sold" that is was not entered in the inventory table.
I need to do two things: (1) Find the records in the second table (t_log) that do not have a matching inventory number in the Inventory table and insert appropriate text into a comment field. (2) find the records in the transaction table with 'R' transaction type and insert an appropriate comment in the transaction table. In each case I need to set a boolean to to True (error).
The tables look like this:
----------------
Inventory Table (Inv)
-----------------------
InventoryNo
Name, etc.
-------------------
TransactionLog (T_Log)
-----------------------
InvNo
TransactionType
Comment
Error (Boolean)
------------------
For the records that are in the T_Log but not in the inventory (Table A), the query below displays the records that I want to update, but I don't know how to update just those records.
(select Inv.InventoryNo,Inv.ItemName,T_Log.id, T_Log.Invno,t_log.transactiontype, t_log.commment
FROM Inv
RIGHT OUTER JOIN T_Log on InventoryNo = Invno
WHERE Inv.InventoryNo is NULL )
Thanks for any help
|
|
|
|
|
Your select should woth with a LEFT outer join
Also try this
SELECT *<br />
FROM t_log<br />
WHERE invno NOT IN (SELECT Inventryno from inv)<br />
AND transactiontype = 'R/S'<br />
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's not the select that I am having trouble with it is how to update the transaction log (comments and error)
|
|
|
|
|
I always test an update with a select to see what the results are before I commit to the update, saves restoring a databse if I get the filter wrong. So naturally I assume everyone does this, you'll note the R/S needs to be modified as well naturally.
UPDATE t_log SET Comment = 'Thinking is good for you'<br />
--SELECT *<br />
FROM t_log<br />
WHERE invno NOT IN (SELECT Inventryno from inv)<br />
AND transactiontype = 'R/S'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK This did it for part one.
UPDATE T_Log
set Commment='Not in main table'
WHERE invno NOT IN (SELECT Inventoryno from Inv)
AND transactiontype = 'T'
Thanks
|
|
|
|
|
Hi All
Please could someone help me out here I am battling slightly.
I can understand SQl but this is a mind breaker.
I have three tables.
[Customer]
CustomerID - Primary Key
CustomerName
[CustomerLog]
CustomerID
TransTimeStamp
Status
[Statuses]
StatusId - Primary Key
StatusDescription
Example Data
[Customer]
CustomerID CustomerName
1 John
2 Peter
[CustomerLog]
CustomerID TransTimeStamp Status
1 2008-04-12 11:53:01 2
1 2008-04-13 10:01:02 3
1 2008-04-14 08:30:32 2
2 2008-04-12 10:45:23 2
1 2008-04-15 22:23:12 3
2 2008-04-13 08:34:12 3
[Statuses]
StatusId StatusDescription
1 Tea Break
2 Start Work
3 End Work
4 Other Things
I need to create a query that will give me the following
CustomerName
StartWorkTime (First Ocurrence Of Start Work)
EndWorkTime (Last Occurrence Of End Work)
Data Should be something like this
John 2008-04-12 11:53:01 2008-04-13 10:01:02
John 2008-04-14 08:30:32 2008-04-15 22:23:12
Peter 2008-04-12 10:45:23 2008-04-13 08:34:12
Please can someone give me advise on this or where to begin.
Regards
|
|
|
|
|
Lets break this down:
To get the earliest occurrence of a date, grouped by the customer, for the start of work
SELECT CustomerID, MIN(TransTimeStamp) AS StartTime
FROM CustomerLog
WHERE Status = 2
GROUP BY CustomerID
And the latest occurrence
SELECT CustomerID, MAX(TransTimeStamp) AS EndTime
FROM CustomerLog
WHERE Status = 3
GROUP BY CustomerID
Now, that has to be joined up with the customer table
SELECT CustomerName, StartTime, EndTime
FROM Customer AS c
INNER JOIN (SELECT CustomerID, MIN(TransTimeStamp) AS StartTime
FROM CustomerLog
WHERE Status = 2
GROUP BY CustomerID) AS s ON s.CustomerID = c.CustomerID
INNER JOIN (SELECT CustomerID, MAX(TransTimeStamp) AS EndTime
FROM CustomerLog
WHERE Status = 3
GROUP BY CustomerID) AS e ON e.CustomerID = c.CustomerID
|
|
|
|
|
Hi Colin, thanks for the help. I was getting confused with the nested SELECTS.
I have tried your example but it only returns one row, the very MIN and the very MAX.
I need multiple rows retured for each instance of 2 and 3 i.e
John 2008-04-12 11:53:01 2008-04-13 10:01:02
John 2008-04-14 08:30:32 2008-04-15 22:23:12
Peter 2008-04-12 10:45:23 2008-04-13 08:34:12
I think this would have to work with the timestamp. I have tried a ORDER BY, but SQL does't like that to much.
Any ideas, thanks again for getting bck to me
|
|
|
|
|
Do the nested queries work on their own? (i.e. the first two queries I wrote)
|
|
|
|
|
They do work, but only return a single row. Therefore the main SELECT only gets one row.
Really appreciate the help
|
|
|
|
|
Very odd. It should only do that if there is only one customer ID. You should be getting one row per customer ID (that's what the GROUP BY clause does)
|
|
|
|
|
Yip it is doing that, your query works fine, but even if there is one customer i.e John. He has many Starts and Stops, therefore John could have more than one row. Your query is taking the first instance (MIN) and very last instance (MAX). What needs to happen is that it will find the first START and the the very next END and so forth.
John could have Started and Stopped work multiple times as recorded in the log file. The status will change accordingly.
I was trying something like this but it doesn't return a row for a row.
LEFT (INNER) JOIN will return duplicates with the right side.
SELECT CustomerID, StartUp, SwitchOff
FROM Customers
INNER JOIN
(SELECT TOP 100 PERCENT CustomerID AS Cus1, timestamp AS SwitchOff
FROM Log
WHERE status = 2
ORDER BY timestamp) AS Q1 ON Q1.Cus1 = Customers.CustomerID
INNER JOIN
(SELECT TOP 100 PERCENT CustomerID AS Cus2, timestamp AS StartUp
FROM Log
WHERE status = 3
ORDER BY timestamp) AS Q2 ON Q2.Cus2 = Customers.CustomerID
Another problem I may have is the data has inconsistencies i.e. the system may have recorded that he stopped worked twice in a row and then only started again.
Thanks again for your help
<div class="ForumMod">modified on Friday, August 15, 2008 9:57 AM</div>
|
|
|
|
|
Hi,
Another approach to this is to use correlated scalar queries. Grouping would be usable here but by using scalars, it may be easier to read and modify. For example:
SELECT CustomerName,
(SELECT MIN(TransTimeStamp) FROM CustomerLog cl WHERE cl.CustomerID = c.CustomerID) AS StartTime,
(SELECT MAX(TransTimeStamp) FROM CustomerLog cl WHERE cl.CustomerID = c.CustomerID) AS EndTime
FROM Customer c
If you want to find corresponding pairs you could use something like this:
SELECT CustomerName,
(SELECT MIN(TransTimeStamp) --min to find first stop after each start
FROM CustomerLog cl2
WHERE cl2.CustomerID = c.CustomerID
AND cl2.Status = 3
AND cl2.TransTimeStamp > cl.TransTimeStamp) AS EndTime
FROM Customer c,
CustomerLog --starts
WHERE cl.CustomerID = c.CustomerID
AND cl.Status = 2
There may be several typos in this but you get the idea.
Mika
|
|
|
|
|
I've been having trouble when trying to add a User Instance SQL database to a Visual Studio 2008 solution. The error is shown in this[^] image.
Connections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component of download from the URL: http:
Note that SQL Server works quite happily as long as it's not a user instance.
Does anybody have any ideas on how to solve this problem?
I doubt it. If it isn't intuitive then we need to fix it. - Chris Maunder
|
|
|
|
|
Hi,
Never seen this, but few questions:
- is SQL Browser running
- can you connect using Server Explorer
Mika
|
|
|
|
|
Mika Wendelius wrote: is SQL Browser running
Yes
Mika Wendelius wrote: can you connect using Server Explorer
If it's not a user instance then yes. I.e. if I set it up in SQL Management Studio and connect like a "full" server then everything works.
|
|
|
|
|
Okay, by Server Explorer I actually meant Visual Studio Server Explorer.
I would believe that the problem is in user instance behaviour. Since it does not use the default port (1433) and the instance name usually must be specified, there's a problem in acquiring this info in VS. Also if I remember correctly, user instances must be explicitely allowed during installation but in this case I believe you have done that.
I'll try to install named instance and test this but it will take some time. Meanwhile could you test that if you have an active connection in VS Server Explorer, is the behaviour different or can you create the database to VS project from Server Explorer (never used server explorer this way so it may be a dumb suggestion)
Mika
|
|
|
|
|
Mika Wendelius wrote: Okay, by Server Explorer I actually meant Visual Studio Server Explorer.
This is exactly the same message regardless of anything inside Visual Studio. My guess is that although I'm running the x64 edition of SQL Server it's only got the "bindings" for an x86 version.
Mika Wendelius wrote: Also if I remember correctly, user instances must be explicitely allowed during installation but in this case I believe you have done that.
Done that.
Mika Wendelius wrote: Meanwhile could you test that if you have an active connection in VS Server Explorer
I have an active connection in Server Explorer to the "proper" SQL database. If I detach that database and then point Server Explorer at the database file rather than the connection then the same message pops up.
Mika Wendelius wrote: is the behaviour different or can you create the database to VS project from Server Explorer (never used server explorer this way so it may be a dumb suggestion
You can and I think the method is the same whether you create it through Server Explorer or Add New Item. Add New Item will automatically add a connection to the database to Server Explorer.
One thing I haven't tried recently is installing the x86 version of SQL Server (I can uninstall or reinstall SQL quite happily but don't particularly fancy uninstalling VS2008).
Mika Wendelius wrote: I'll try to install named instance and test this but it will take some time
I've got a trial install of Vista x64 Ultimate inside a VMWare virtual machine if you'd like a copy for testing (will take quite a while since even compressed as hard as it'll go it's over 4GB).
|
|
|
|
|
Mika Wendelius wrote: Okay, by Server Explorer I actually meant Visual Studio Server Explorer.
This is exactly the same message regardless of anything inside Visual Studio. My guess is that although I'm running the x64 edition of SQL Server it's only got the "bindings" for an x86 version.
Mika Wendelius wrote: Also if I remember correctly, user instances must be explicitely allowed during installation but in this case I believe you have done that.
Done that.
Mika Wendelius wrote: Meanwhile could you test that if you have an active connection in VS Server Explorer
I have an active connection in Server Explorer to the "proper" SQL database. If I detach that database and then point Server Explorer at the database file rather than the connection then the same message pops up.
Mika Wendelius wrote: is the behaviour different or can you create the database to VS project from Server Explorer (never used server explorer this way so it may be a dumb suggestion
You can and I think the method is the same whether you create it through Server Explorer or Add New Item. Add New Item will automatically add a connection to the database to Server Explorer.
One thing I haven't tried recently is installing the x86 version of SQL Server (I can uninstall or reinstall SQL quite happily but don't particularly fancy uninstalling VS2008).
Mika Wendelius wrote: I'll try to install named instance and test this but it will take some time
I've got a trial install of Vista x64 Ultimate inside a VMWare virtual machine if you'd like a copy for testing (will take quite a while since even compressed as hard as it'll go it's over 4GB).
[edit]
In the meantime I've set up an XP virtual machine to use for all this work anyway and it feels "fun" to go back to such a retro OS (although I have installed an add-on which gives me the Vista Start Menu).
It'd just be nice to have this thing up and running on an x64 machine since everything else works perfectly. Maybe it's time for a re-install  .
[/edit]
|
|
|
|
|
Hi again,
I installed another instance (with such an original name as Test ) After that I tried to add a new item and then in the dialog box from data node "Service-based Database". This failed with an error mesagge saying that the conection cannot be established because the server cannot be found. This is quite obvious since there's no dialog box asking where do I want to connect and the second instance is not listening on default ports and so on.
What I'm currently wondering is that the error I get is different from yours. Am I doing this differently?
Mika
|
|
|
|
|
I did some tests on the service-based database. When adding it to the project it actually starts SQL Server in desktop session. So the database is not added to service controlled sql server at that time. This didn't make any sense since one of the startup parameters is instance name. How can Visual Studio start the instance without knowing relevant parameters . Then I found this:
http://msdn.microsoft.com/en-us/library/ms345154.aspx#sseover_topic18[^]
Visual Studio Integration
SQL Server Express is installed with all editions of Visual Studio. Visual Studio installs SQL Server Express using the instance name SQLEXPRESS. In SQL Server Express, the applications rely on the SQLEXPRESS instance name. The goal of Visual Studio and SQL Server Express integration is to make database access with SQL Server Express as simple and easy as working with Jet. This is true not only for client applications but also for ASP.NET Web server scenarios.
This makes me wonder if there actually is any way to use non-default instances with Visual Studio...
Mika
|
|
|
|
|
Hmm, I'll try installing an instance named SQLExpress and see what happens. With regards to the error message it's a different one I think, it simply refuses to try and connect.
BTW, thanks for all the help that you're providing me with ![Beer | [beer]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/beer.gif)
|
|
|
|
|
Renaming the instance to SQLExpress fixes it. What a situation!
Thanks for your help and the research, I definitely owe you a
|
|
|
|
|
You're welcome
In the meantime I investigated the templates for new items under Visual Studio ItemTemplates, but I didn't find any way to define a new template against another instance name nor to change the default name of the instance to connect to. Seems that they have hardcoded this into VS. Nice... 
Mika
|
|
|
|
|
Mika Wendelius wrote: Seems that they have hardcoded this into VS. Nice...
Perhaps it's because only the express edition supports user instances? Maybe they just haven't got around to refactoring it out 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
