I am trying to query only the Max date dependant on quantity
Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),
[Code] ....
Looking for a return of:
PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1
Widget A should not show because the last date the qty was changed to Zero
Closest that I have come..........(which is pulling the part 101111 which should be incorrect)
Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'
I need sql query to select max date shipment where sum of quantity required > sum of quantity shipped from sales line table but i dont know how to make my sales line table as following
Here the result of query i need is max shipment date is 07/05/2015 and quantity required is 600 and quantity shipped is 450 so tat i must select this date because quantity Required is big from quantity shippedmy question How i write the query get result above in sql server 2005.
I have a ABC service on a server1 that is dependent on MSSQLSERVER service running on server2. Does anyone know how to setup the service ABC on server1 to have a dependancy on MSSQLSERVER running on server2?
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM, INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND, INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED FROM INVENTORY_ITEM_TAB INNER JOIN INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0 ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
Suppose I have the following table:col1 col2hammet jonesjlo afflectafflect armandwills snoptarmand hammetjones smithIf someone choses armand, then I'd like to returnamand hammet jones smithThe first selection goes over to the second column, gets that valueand locates it back in column one and returns column 2 and so on.One way of doing it is to set up a separate query for each one andthen construct a new query to get them all.I'm thinking there's a more elegant way to do this. Any suggestionswould be appreciated.-David
I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode.
I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.
I have a report which uses a parameter called "Interval" with possible string values of "Daily", "Weekly", and "Monthly" and I want these values to set the default values for my StartDate and EndDate parameters. However, the default value remains static. And I have tried all of the solutions found in this forum. Any suggestions?
I am using SSRS 2005. And I have tried using an iif statement for StartDate, also tried Property->Custom Code, and also tried using stored procedure to populate Start and End Dates, but none of these provide dynamic parameter setting like I was hoping for.
And I think this should work because I know that if it is not a date that it works correctly. For example if I have one parameter called country and a subsequent one named state, every time I change the country it will let me re-pick the state. But this is not working correctly here.
Any ideas on what I am doing wrong or how to fix this?
I am not able to delete a row due to the presence of its parent table. There is no circular relationship and the child has no dependencies when I verify the Delete trigger. Any advice? See error msg below:
Msg 30010, Level 16, State 1, Procedure tD_My_child_table_name, Line 43
Cannot DELETE last My_child_table_name CI because My_parent_table_name exists.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.
I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.
We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.
Suggestions?
The query in question is: EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id) SELECT slo.vin, br.xml, br.lang_id FROM #SALE_LINEUP_ORDER slo INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")
Excuse my illiteracy in this subject, I just learned about this yesterday, I managed to create a report by using business intelligence report wizard, and when I preview it seems that I can view the fields that I wanted to view.
But in addition to that I also want to add hyperlinks to some of the fields, because I am planning to embed the whole report in a web page. For example if I go on the field that I want to add hyperlink and right click->properties->navigation tab->Jump to url, I thought that I can handle it. Actuall it works fine if I link it to http://www.microsoft.com but let's assum e the name of the field is ID, and you want to link to http://somewebsite/somepage.aspx?id= (id in that cell), how can I achieve this parametric behavior, actually I tried right click cell->properties->navigation tab->Jump to url, and filled url text box http://somewebsite/somepage.aspx?id=&(concatenation operator)=Fields!ID.Value but what happens is instead of evaluating the value of ID field, it takes expression as text as text and that's why it links to totally wrong url. Is there a way to get around this problem.
And can somebody suggest me a detailed tutorial on programmatically using reports in .net web applications using c#.
Hello I have following data and environement is SQL Server 2000.
Hello
I have following data and environement is SQL Server 2000.
MemberID
Dependent_Empid
DependentName
Benefit
1
100
Kalim
100
2
100
Brian
100
3
100
Swadia
200
4
101
Allan
200
5
101
Cynthia
200
6
103
Noreen
200
7
103
Larry
300
8
103
Mike
300
and I am desired for the following output but I dont want to use function. Can anybody help me for script for the desired output. I will be much thankful.
Greetings all: I am looking for a way to get detailed information about each created table (regardless of permanancy) in a stored procedure, similar to the information one received from using sp_help on an individual table.
Does anyone know of such an animal?
Thank you, and Happiest of New Years, Jack Cole Magellan Healthcare, Inc.
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent?
Thanks, John
(By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
I wonder if anyone can help me - I am trying to write a little application which recompiles any views dependent on a given view. I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views. I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job. Does anyone have any ideas?
Hi,DDL:-- create table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique, taskCompleteDate dateTime, taskComplete bitdefault(0));/*Business Rules:a) if taskCompleteDate is NULL (like default) then, taskComplete maynot be set to 1 (attempt to update it to 1 would fail);b) else automatically set taskComplete = 1*/I was thinking using CHECK constraint (mutual constraint if possible),along the following line:CHECK (if taskCompleteDate is null set taskComplete=0 else settaskComplete=1)Hmm, your thought?Thanks.
I have a data mining model that has a binary dependent variable (let's say 'Yes' and 'No'). I noticed that the model is very sensitive to the distribution of the binary dependent variable. I could not come up with an explaination. I know the model is not over/under fits and multicollinearity is not an issue. And I have enough data size to train the model.
I would expect some sensitivity but not to this extend:
Yes % in training data Yes % in the prediction results
with DT with LR with NN
30.0% 6.0% 11.0% 14.0%
35.0% 12.0% 15.0% 24.0%
37.5% 20.0% 23.0% 27.0%
40.0% 28.0% 33.0% 36.0%
50.0% 43.0% 52.0% 61.0%
DT - Decision Trees
LR - Logistic Regression
NN - Neural Networks
And to make things more interesting, although the precision (% of correct 'Yes's ), true positive ratio and false positive ratio is changing; the overall accuracy (and therefore the score in the mining accuracy chart) remains almost same.
If anyone has an idea it would be highly appreciated.
When a user orders an amount of a given product, I want to update the available quantity in my db. I use a loop to insert each ordered product item and the needed quantity into an ORDERED_ITEMS table (these items are tied to an order record in another table). The available_quantity is in the STOCK table. Here is my code. Can you help me out with how I can achieve this? (I know my queries should be in SP's, but for now while I'm getting everything the way I want it, it's easier to write it directly in my code.) Thanks! //insert the individual items string items_to_ship = "";
foreach (GridViewRow rw in GridView1.Rows) { if (rw.RowType == DataControlRowType.DataRow) { if (((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text != "") { int qty = Convert.ToInt32(((TextBox)rw.Cells[4].FindControl("qtyneeded")).Text); int itm = Convert.ToInt32((rw.Cells[0].Text));
SqlConnection mycn2 = new SqlConnection(myConnectionStr); SqlCommand myCommand4; SqlDataReader rdr = null; int new_qty = 0; myCommand4 = new SqlCommand ("INSERT INTO SHP_ORDERED_ITEMS (ship_id, item_id, quantity) values(@ship_id,@item_id,@quantity);" + "SELECT item_description, qty_available from shp_stock where item_id = @item_id;", mycn2);
myCommand4.Connection.Open(); rdr = myCommand4.ExecuteReader(); while (rdr.Read()) { // create a list of item descriptions and quantities of each, for use in the email items_to_ship += (string)rdr["item_description"] + " (" + qty.ToString() + ")<br>";
In a database (Access) i have many lines. In one of colomnes (Label) i have entrys "AAA", "BBB", "CCC", ... . I would like to know how many "AAA" and
how many "BBB" we have in database.
Is this possible to achieve this by using i = RecordSet.Fields (Label). ... mixad with other functions where i is an integer and RecordSet a record set?
what a SQL query cal deliver a i as Integer, where i is quantity of lines labeled with "AAA".
currently we have some shipping software that has a mqsql database locally.There are filters on the program and when an item is shipped from our warehouse it goes into a filter "Shipped".We have a SKU and also quantity for the product.
Now we have an inventory program that also has a mqsql database,this has the same SKU as the shipping software database.what we hope to do is when an SKU is shipped in our software program(ie it Goes into the "Shipped" folder),it will deduct the quantity from the adjacent SKU in the inventory program database.
Note that the 2 databases are independent from each other but would like them to be in effect linked to each other.
I need to query SQL Server Express 2012 records to find 6 serial numbers that are all assigned to one common unique number. Normally in this use case, 12 serial numbers are assigned to one common unique number, so I'm trying to find the odd entry.
Select FullItemName, Region, IssuedQuantity from Transactions.TransactionBaseMain where EnvironmentID = 34 and ModeID=2 and UnitOfIssueID=73 AND itemid=5605 and TransactionType in ('Issue') and DATEDIFF(DD,TransactionDate,GETDATE())<30 Group by FullItemName,Region,IssuedQuantity Order by FullItemName,Region,IssuedQuantity
I need to group the IssuedQuantity by region. (Add up the IssuedQuantity for the region).
In my Insert into #TempTable I need to calculate the 'qty_wasted' as difference between 'qty_received' and 'qty_used' Where would I put the calc statement?
INSERT INTO #TempTable (job_date, job_number, cost_code, qty_received, qty_used, qty_wasted, productId, plant_id) SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as productId, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, qty_received = CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END, qty_used = CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN.....
Hello all, I have an interesting question about calculating a price in my database for a quantity entered. I have a join table that I store ProductID, SizeID, Quantity and Price. The price for a particular product changes based most often on the quantity ordered. For example, if you order one unit of a widget the price is 10.00, however if you order one dozen units of the same widget the price drop to 9.75 and so on.
Here is a sample of my table....columns are in the order I specified above.
So depending on if my widget is available in certain sizes, the second column, then each product has an price for the size id and quantity at which the price break occurs.
I use this stored procedure to return the price or price range based on the input parameters entered.
CREATE PROCEDURE dbo.sp_GetPrice @ProductID INT, @QuantityID INT = NULL, @SizeID INT = NULL AS BEGIN SET NOCOUNT ON IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED SELECT DISTINCT Price AS 'Price' FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID AND Quantity = @QuantityID
IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED SELECT Price AS 'Price' FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID AND SizeID = @SizeID AND Quantity = @QuantityID IF @SizeID IS NULL AND @QuantityID IS NULL SELECT MIN(Price) AS 'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE FROM join_ProductSizeQuantityPrice WHERE ProductID = @ProductID UNION ALL SELECT MAX(Price) FROM join_ProductSizeQuantityPrice WHERE ProductId = @ProductID
END GO
So everything works great, however, when a user orders an quantity amount like 7, 13, 26 - which is not part of the table - I want to give them the discount available to them for the price break appropriate.
For example if a customer orders 16 widgets of size 2 the price break threshold they have crossed is one dozen, however they have not yet reached the next one which is two dozen. Therefore I want to offer the price associated with one dozen widgets of size id which is: $9.75. Once I have this a simple calculation of this price * 16 units would give me a total but my question is, how do I elegantly design this quantity / right price per unit calculation?
Hi There, Here I have small problem with default and rule. After create rule or default then we will bind that to any table. I bounded that rule to some of tables.If i want see the list of objects dependent on this rule or default how to see.I know sp_depends stored procedure will show the all dependent objetcs but i could not get through that.I found in help it says sp_depends works for all objects in the database like table,view and so on.But default and rule also objects i could not get it.Please let me know on this if you can give this answer as early as possible. I am very thanks to you.Please don't specify SQL-DMO Listboundedcolumns function.....
I have two procedures Sp1 and Sp2. The Sp1 is calling from Sp2. If there come new parameters or removed from the first procedure Sp1 then it will effect the Sp2 and its execution will break if required changes not done. So I want that when any changes making in SP1 then i need alert about the Sp2 changes.
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?