Item Master And Office Stock Details (was Query Problem)
Sep 15, 2006
Hi,
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
--------------
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------------------------------------
FOR OFFICE - 1
--------------------------------------------
ITEMCODE ITEMNAME OFFICEID
--------------------------------------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid itemname stock c1 coconut 2 p1 peanut 2orderdetail tableno_order itemid quantity 1 c1 5itemmoment tableitemid itemname stock c1 coconut 0 p1 peanut 0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid itemname stock c1 coconut -3 p1 peanut 2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid itemname stock c1 coconut 0 p1 peanut 2and in itemmoment table became:itemid itemname stock c1 coconut 3 p1 peanut 0my store procedure like:ALTER PROCEDURE [dbo].[orders]( @no_order as integer, @itemid AS varchar(50), @quantity AS INT)ASBEGIN BEGIN TRANSACTION DECLARE @currentStock AS INT SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid) UPDATE [item] SET [Stock] = @currentStock - @quantity WHERE [itemid] = @itemid COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table. Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
I have a report with details grouping on table. What i need to do is put row number only on Parent row and skip the child row. When i use RowNumber("GroupName") of course it gives me a current RowNumber. Is there a way to count only parents?
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
Master table (tlbProduct) having productID as Primary key and which acts as reference key for table (tlbCategory).Fields for tlbProduct are productID,productNameFields for tlbCategort are productID,CategoryID(primary key),CategoryName,Prizeboth productID,CategoryID are autoincrementing.but when i write two inserts simultaneously as follows insert into tlbProduct(productName)values(@productName)insert into tlbCategory(CategoryName,Prize) using sqldatasourceI get the error that ProductID value is null which is not providedBut (productId in both is autoincremented) and relationship is there in both tables How to resolve this problem without adding ProductID in second Insert?SWati
HI, I'm using Visual Web Developer and SQL Server 2005 Express Editions. I have a database with two tables, say AccountsTable and PersonsTable, with a one to many relationship respectively. Given a particular selected record in PersonsTable, I need to look up the corresponding master record in AccountsTable. Here's what I've done: SqlDataSource1 has a "SELECT * FROM PersonsTable" and a GridView1 which uses this as its datasource and the 'enable Selection' checkbox is On. This works fine. Then, I create a second SqlDataSource2. When I click on the WHERE button in the Configure Data Source page, I have chosen the following parameters: Column:PrimaryKeyField; Operator:=; Source=Control; ControlID=GridView1; Default Value='I left this blank' but the Value field shows GridView1.SelectedValue when I click the ADD button. <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:bizConnectionString %>" SelectCommand="SELECT * FROM [AccountsTable] WHERE ([AccountNumber] = @AccountNumber)"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="AccountNumber" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
I have bound DetailsView1 to SqlDataSource2. When I run the page, the GridView1 is shown with Persons records OK. Then I click the 'Select' next to a record in the Gridview expecting the corresponding lookup to the master record (Account) to be shown in the DetailsView, but it does not show anything. What am I doing wrong? Is there a better way of doing this? Your help is much appreciated. Thank you. PS. I could find Tutorials showing Master/Details retrieving records in a top down fashion (1:n), but I could not find the reverse (n:1)
My question is setting up master detail pages using stored procedrues. In the tutorials C# Master-Details (Seperate Pages) example they use the following code for the master page for the navigation. <asp:HyperLinkField HeaderText="View Details..." Text="View Details..." DataNavigateUrlFields="au_id" DataNavigateUrlFormatString="DetailsView_cs.aspx?ID={0}" /> The call to the Details Page DetailsView_cs.aspx uses the following code <asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT dbo.authors.au_id, dbo.titles.title_id, dbo.titles.title, dbo.titles.type, dbo.titles.price, dbo.titles.notes FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id WHERE (dbo.authors.au_id = @au_id)" ConnectionString="<%$ ConnectionStrings:Pubs %>"> <SelectParameters> <asp:QueryStringParameter Name="au_id" DefaultValue="213-46-8915" QueryStringField="ID" /> </SelectParameters> </asp:SqlDataSource> I can replicate the example calling the detail page but I am unable to make the detail page work when using a stored procedure as the asp:SQLDataSource. Using the above sql code as a stored procedure in the <SelectParameters> I am not able to return the data set using either asp:QueryStringParameter or asp:Parameter as I have built other forms using stored procedures and have tested the procedure and know that it works. Can someone point me in the right direction. Thanks
i have go master records as well child records how do i delete all the master records so that all my corresponding child records get deleted ,as we do in oracle using undeleted cascade
Today we moved from 32bit to 64bit computing environment. 64bit Window server 2003 and SQL 2005 are now our playing ground. And 32 bit Office 2003 is also installed. Excel in Office program plays important roll in our mission.
One of our routine task is that step 1. get Excel data files from other websites and step 2. extract specific data from them and step 3. converting them into some type of data that can be compatible with SQL
Our problem occured in step 3. To convert data, we realised taht 64 bit excel driver, which we believe is not yet available, is required.
My questions are:
1. Is 64 bit excel driver (of office 2003 or office 2007) available now?
2. Is there any way OR IS IT POSSIBLE to use 32 bit excel driver with 64bit SQL 2005? If possible, please enlighten me.
I would appreciate it very much if anybody answer my questions.
Well probably not that complex for some of you out there!
I need to work out the amount of stock which was sold between @datefrom and @dateto and how much we currently have on hand (to work out if we are over ordering etc). That's the pretty easy part but I also need to include a column which works out how many items have been sold 3 months prior to @datefrom (from invoiceline). The proc I have so far works out the items sold between 2 dates so basically what I need is another column which is the amount sold (QtySold) in the 3 months prior to datefrom
This is the basic part I have so far:
ALTER PROCEDURE [dbo].[rptstockholdinglevel] -- Add the parameters for the stored procedure here @datefrom datetime, @dateto datetime, @periodname varchar(50), @percentage int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT products.productname, @periodname AS periodname, isnull(SUM(invoiceline.qty),0) AS QtySold, products.qtyonhand AS OnHand, nominals.nominalname, productcategories.categoryname, productmanufacturers.manufacturername FROM productmanufacturers RIGHT OUTER JOIN invoices INNER JOIN invoiceline ON invoices.invoiceid = invoiceline.invoiceid RIGHT OUTER JOIN products ON invoiceline.productid = products.productid ON productmanufacturers.manufacturerid = products.manufacturerid LEFT OUTER JOIN nominals INNER JOIN productcategories ON nominals.nominalid = productcategories.salesnominal ON products.categoryid = productcategories.productcategoryid WHERE (invoices.invoicedate BETWEEN @datefrom AND @dateto) OR (invoices.invoicedate is null) GROUP BY products.productname, products.qtyonhand, productcategories.categoryname, nominals.nominalname, productmanufacturers.manufacturername HAVING (SUM(isnull(invoiceline.qty,0)) < products.qtyonhand) ORDER BY nominals.nominalname, productcategories.categoryname, productmanufacturers.manufacturername END
I'd be really grateful for any pointers as I'm just going round in circles on this one.
Okay, here's an algorithm question for you TSQL gurus out there...
Due to circumstances beyond our control, our group has been tasked with a massive project and a very short timeline. And of course, timely completion is needed because our STOCK OPTIONS grants depend on this! And of course board meetings are always scheduled sooner than you expect.
Here's one of the killer questions we're trying to solve...
Given a table of employee ID's, associated supervisor ID's, and the amount of stock options given, how would you write a stored procedure to return, for any branch of the organizational tree, the sum of all the stock options in a particular branch?
What we need is something like : "sp_StockOptionsPerDepartment @SUPERVISORID=30" with a result : "1750".
Basically we're building an organizational chart of our company from this table, on the fly, and also counting up for certain branches of the org chart, the total stock options.
If a manager has two managers under him, and each sub-manager has three employees, then we want to know the total stock options that all 3+3+2+1 = 9 people possess. Basically it's the total pool of stock options for a department, or work group, or division, etc.
Got this to work for a small set of employees, but when we begin to scale up to entire departments, the query times out because it takes tooooo long...
Any ideas? ANY ideas at ALL would be helpful...
Comments (1) This is basically a tree-traversal algorithm, but conversion into SQL is not always so straightforward. Starting from an arbitrary root node, we must visit every child node underneath, walking all the way down to the leaves.
(2) We tried a brute force algorithm which is fast for smaller sets, but impossibly long for sets where we're dealing with hundreds of employees. Any cheats? Caching results as we go? Any ideas out there?
I have 3 tables. Stores, Dates and Transactions. I want to combine all Stores with all Dates in one table and then calculate Last Stock Quantity.
Stores London Paris Prague
Dates 1.1.2014 2.1.2014 3.1.2014
Transactions 1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 3.1.2014 Prague 1200
And result should look like this Last_Quantity should be Quantity for last date in Transactions table.
1.1.2014 London 1000 1.1.2014 Paris 1300 1.1.2014 Prague 1500 2.1.2014 London 800 2.1.2014 Paris 1300 2.1.2014 Prague 1500 3.1.2014 London 800 3.1.2014 Paris 1300 3.1.2014 Prague 1200
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1 @skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age' FROM (SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn ,( SELECT SUM (PurchaseItemReceive.qtyreceived) FROM bvc_product pp
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
Where is the information coming from that is displayed in queryprofiler.Is it the command that is retrieved before it is processed, or is itwhat is actually processed.
Is there any query to list all tables,column name,datatype,size,total record in each tables for particular database ex Database:Northwind Tables:categories,(categoryid varchar(50),Category name varchar(50)...............),1023kb ,25 records like this i think there is no such a simple query... Atleast tell How to get all column names and datatype of particular table and size of table
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hi! I'm creating a social network, and in one page I need to compare each result of the datalist to a value of a table in my database. For example, I have the datalist showing all the entries in the table users, and when I am showing this information, I want each dataitem to be compared to a select statement of the friends of the logged in user, so that if that datalistitem is present in the results of that other select, I will change the text of a field to say "already a friend". If the user is not present in that select, ie is not a friend of the user who is logged in, the text will say "add friend". I have this comparison working already for a specific name, but not for the database query. Can anyone please help me? The code is below... <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><script language="VB" runat="server"> Sub Page_Load(Sender As Object, E As EventArgs) Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") MyCommand = New SqlDataAdapter("select * from aspnet_Users where IsAnonymous='False'", MyConnection) 'Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|;Integrated Security=True;User Instance=True DS = New DataSet() MyCommand.Fill(DS, "aspnet_Users") MyDataList.DataSource = DS.Tables("aspnet_Users").DefaultView MyDataList.DataBind() End Sub</script><body> <br /> <ASP:DataList id="MyDataList" RepeatColumns="1" runat="server"> <ItemTemplate> <table cellpadding="10" style="font: 10pt verdana" cellspacing="0"> <tr> <td width="1" bgcolor="BD8672"/> <td valign="top"> </td> <td valign="top"> <div id="hey"><div id="dados"><b>Name: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br><b>city: </b><%#DataBinder.Eval(Container.DataItem, "City")%><br></div> <div id="photo"><b>Photo: </b><%#DataBinder.Eval(Container.DataItem, "UserName")%><br>status: </div> <p></div> <a href='<%# DataBinder.Eval(Container.DataItem, "UserName", "purchase.aspx?titleid={0}") %>' > <%#IIf(Container.DataItem("UserName") = "marta", "<a href='mailto:" & Container.DataItem("UserName") & "'>Email</a>", "")%> <img border="0" src="/quickstart/aspplus/images/purchase_book.gif" > </a> </td> </tr> </table> </ItemTemplate> </ASP:DataList> </body></asp:Content> Thanks a lot.
I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.
I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).
Is there any way of stopping these extra rows from being created using T-SQL in my query?
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date Price Status 1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore 3 11/12/2015 100 High Value
create table testmaster ( ID int, Threshold int ) create table testdetail ( ID int, Date varchar(20), Price float )
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in ** =================================== Sequence contains no elements ------------------------------ Program Location: at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke() at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
I have around 500 packages (SQL 2005) deployed in file system & all this packages are running on daily basis via SQL agent. Now I need to migrated all 500 packages into SQL server 2008 R2.
There is no inventory to track which package belogs to which team and any other information.
Now, I need a method to query the pakages connection string details with database respective. Is there any method?
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement 2015-06-26 14:00:00.000 6 to 4 2015-06-26 14:00:00.000 11 to 7 2015-06-26 15:30:00.000 9 to 6 2015-06-26 21:00:00.000 7 to 3 2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
