Update Query With Sub-queries To Find The Values
Jul 20, 2005
Help, please. I am trying to update a
table with this structre:
CREATE TABLE Queue (PropID int, EffDate smalldatetime,
TxnAmt int)
INSERT Queue (PropID) SELECT 1
INSERT Queue (PropID) SELECT 2
INSERT Queue (PropID) SELECT 3
....from this table...
CREATE TABLE Txns (PropID int, TxnDate smalldatetime,
TxnType char(1), TxnAmt int)
INSERT Txns SELECT 1 '20000201', 'B', 100000
INSERT Txns SELECT 1 '20020515', 'B', 110000
INSERT Txns SELECT 1 '20020515', 'A', 120000
INSERT Txns SELECT 1 '20020615', 'c', 130000
....only certain txn types are okay, and they have an order
of preference...
CREATE TABLE GoodTxnTypes (GoodTxnType char(1), Pref)
INSERT GoodTxnTypes SELECT 'A', 1
INSERT GoodTxnTypes SELECT 'B', 2
The idea is to fill in the NULL fields in the Queue table,
according to a rule -- the transaction must be the latest
transaction within a date window, it must be one of the good
txn types, and if there are two txns on that date, choose
the txn by the preferred txn type (A is preferred over B,
according to the field Pref).
If the time window were 20020101 to 20030101, the txn
selected to update the Queue table would be this one:
INSERT Txns SELECT 1 '20020515', 'A', 120000 -- there are
two in the time window that are type A or B; they are
both on the same day, so the 'A' is preferred.
If the time window were 20000101 to 20010101, this would
be selected because it is the only A or B type txn in
the interval:
INSERT Txns SELECT 1 '20000201', 'B', 100000
I'm looking for a statement that starts...
UPDATE Queue SET EffDate = ...., TxnAmt = .... (EffDate,
in this table, is the same as TxnDate in the Txn table).
Assume we have @FirstDate and @LastDate available.
Help, please. I'm getting stuck with (a) a sub-query to
find the relevant Txn records, and (b) another sub-query
within that to find the MAX(TxnDate) within the time
window. Filtering the Txn records on the basis of the
GoodTxnTypes table is easy, as is ordering what is returned.
But I'm having trouble joining the sub-queries back to the
Queue table on the basis of PropId.
View 1 Replies
ADVERTISEMENT
Jul 20, 2015
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
View 2 Replies
View Related
Jul 20, 2005
HiI have the following tables and stored procedure. I need to pass a value tothe stored procedure and have it use the value in a query. After runningthat query it will return an ID which is then used in an insert statement.At present the values in @MovieId int, @UserID int are left empty (see myoriginal code at the bottom of posting). I think this is due to an issuewith when the sql is executed (?).I get the feeling I should have something like this instead, but not sure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieName@MovieId = exec MovieName2Id @MovieName-- Find UserID from UserEmail@UserEmail = exec Email2UserId @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)Thanks in advance.========MY CODE=============Tables:CREATE TABLE [dbo].[movies] ([movieID] [int] IDENTITY (1, 1) NOT NULL ,[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[movieDateAdded] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[transactions] ([userID] [int] NULL ,[movieID] [int] NULL ,[FrameNumber] [int] NOT NULL ,[transDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[users] ([userID] [int] IDENTITY (1, 1) NOT NULL ,[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[userDateRegistered] [datetime] NULL) ON [PRIMARY]GOStored Procedure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieNameSELECT @MovieId = MovieIdFROM MoviesWHERE MovieName = @MovieName-- Find UserID from UserEmailSELECT @UserID = UserIDFROM UsersWHERE UserEmail = @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 2 Replies
View Related
Dec 12, 2013
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
View Related
Mar 15, 2007
I have faced a situation that when i try to update a page. Some values can be updated while some cannot. I try to print the executed SQL query and get the following1 "UPDATE orders SET
2 cust_id=15,po_code='PO20060610',
3 po_amt=10000.0000,
4 add_charges=0,
5 commission='eeeeeee',
6 lab_charges=0,
7 fty_dis=0,
8 pay_trm='adasds',
9 cust_dis=0,
10 trade_trm_desc='',
11 curr_rate=1,
12 ship_expense=0,
13 shipmark='eng ship mard new2',
14 sidemark='Eng Side Mark new333'
15 ,inner_box='Eng Inner Box new333',
16 confirmation='rend confirmation2',
17 contract='end contact23',
18 internal_remark='testing testing 26/6/2007 333',
19 rec_curr_rate=0,rec_amt=0,shipmark_attach='',
20 sidemark_attach='',inner_box_attach='',
21 ord_type=1,status=2,ord_confirm_code='',
22 commission_type=1,sidemark_lang='English',
23 curr_code='HKD',unit_code='PCS',
24 trade_trm='FOB Hong Kong',rec_curr='USD',
25 ord_date='2006/06/10', po_date='01/01/2007',exp_delivery_date='01/01/2007',
26 act_delivery_date='01/01/2007', pay_start_date='10/10/06',pay_end_date='10/10/06',upd_time='2007/03/15 15:41:14' WHERE ord_id=292;Set @ord_id=292;"
The fields sidemark, inner_box, internal_remark cannot update, while others can.
I think it's really strange.... since i have no idea why some can be updated while some others and the SQL seems to me is correct.
Please give me some advices on solving this.
Thank you.
View 4 Replies
View Related
Nov 4, 2004
I am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.
I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].
I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.
First, the html:
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="SIU_Assign.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>SIU Interface Scenario Assignments</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="scenariosDataGrid" runat="server" CellPadding="4" AutoGenerateColumns="False"
OnUpdateCommand="dg_Update" OnCancelCommand="dg_Cancel" OnEditCommand="dg_Edit" DataKeyField="Scenarios">
<ItemStyle BackColor="#00DDDD" ForeColor="#000000" />
<HeaderStyle BackColor="#003366" ForeColor="#FFFFFF" />
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="Scenarios" HeaderText="Scenario" ReadOnly="True" />
<asp:TemplateColumn>
<HeaderTemplate>
Machine
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("Machines") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtMachine" Runat=server Text='<%# Container.DataItem("Machines") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:Label id="resultLabel" runat="server"></asp:Label></form>
</body>
</HTML>
Then the CodeBehind:
Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs)
Dim strMachineName, strScenarioName As String
Dim intResult As Integer
strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex))
strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text
cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _
"WHERE Scenarios=@Scenario", conn)
cmd.Parameters.Add("@Machine", strMachineName)
cmd.Parameters.Add("@Scenario", strScenarioName)
conn.Open()
intResult = cmd.ExecuteNonQuery()
resultLabel.Text = "The result was " & intResult & "."
conn.Close()
scenariosDataGrid.EditItemIndex = -1
BindData()
End Sub
The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.
Any suggestions?
Thanks!
John
View 5 Replies
View Related
Nov 8, 2013
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City
21 X PA Chn
2 Sathish PA Chn
3 Shiva A Cbe
17 Venkat M Hyd
22 Y SM Cbe
18 Vignesh SA Hyd
Table:
CREATE TABLE [dbo].[AppInvent_Test](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Code] ....
View 4 Replies
View Related
Nov 18, 2013
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.
View 5 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Feb 6, 2007
Because forum HTML codes are OFF I had to put my question between code tags. Because to tell my question I had to show the database tables and wanted output. I succeeded retrieving the datas but combining these three tables together with SQL or ASP codes is too complex. Please help me determining the table. As I am subscribed to this forum you can ask me anything about my question. I can clarify my question if you need. Your answers will be replied immediately.
(To see my question pls save the codes below as htm file or see it with a wysiwyg editor.)
<p><b><font size="2">tbl_PRODUCTIONS</font></b></p>
<table border="1" width="971" id="table1">
<tr>
<td height="19" width="54"><b><font size="2">fld_DATE</font></b></td>
<td width="161" height="19"><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
<td height="19" width="124"><b><font size="2">fld_PRODUCT_CODE</font></b></td>
<td height="19" width="222"><b><font size="2">fld_DAILY_PRODUCTION_PROGRAM</font></b></td>
<td height="19" width="176"><b><font size="2">fld_PRODUCTION_QUANTITY</font></b></td>
<td height="19" width="136"><b><font size="2">fld_STOCK_QUANTITY</font></b></td>
<td height="19" width="52"><b><font size="2">fld_SELL</font></b></td>
</tr>
<tr>
<td width="54" >......</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">.....</td>
<td width="52">....</td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">115</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">190</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">350</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">150</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">100</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">135</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">18.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">80</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">99</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">210</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">309</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">155</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">100</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">19.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">60</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">100</font></td>
<td width="52"><font size="2">10</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="222"><font size="2">200</font></td>
<td width="176"><font size="2">190</font></td>
<td width="136"><font size="2">200</font></td>
<td width="52"><font size="2">54</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="222"><font size="2">300</font></td>
<td width="176"><font size="2">350</font></td>
<td width="136"><font size="2">450</font></td>
<td width="52"><font size="2">745</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="222"><font size="2">150</font></td>
<td width="176"><font size="2">156</font></td>
<td width="136"><font size="2">4664</font></td>
<td width="52"><font size="2">55</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="222"><font size="2">100</font></td>
<td width="176"><font size="2">120</font></td>
<td width="136"><font size="2">234</font></td>
<td width="52"><font size="2">77</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="222"><font size="2">125</font></td>
<td width="176"><font size="2">140</font></td>
<td width="136"><font size="2">654</font></td>
<td width="52"><font size="2">80</font></td>
</tr>
<tr>
<td width="54" ><font size="2">20.01.2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="222"><font size="2">75</font></td>
<td width="176"><font size="2">90</font></td>
<td width="136"><font size="2">233</font></td>
<td width="52"><font size="2">101</font></td>
</tr>
<tr>
<td width="54" >...</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">....</td>
<td width="52">....</td>
</tr>
</table>
<p>Â </p>
<p><b><font size="2">tbl_ANNUAL_PRG</font></b></p>
<table border="1" width="552" id="table2">
<tr>
<td width="62"><b><font size="2">fld_YEARS</font></b></td>
<td><b><font size="2">fld_PRODUCER_UNITCODE</font></b></td>
<td><b><font size="2">fld_PRODUCT_CODE</font></b></td>
<td width="177"><b><font size="2">fld_PRODUCTION_PROGRAM</font></b></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="177"><font size="2">15000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="177"><font size="2">22000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="177"><font size="2">35000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="177"><font size="2">50000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="177"><font size="2">170000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="177"><font size="2">12000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2006</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="177"><font size="2">123000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.10</font></td>
<td width="177"><font size="2">18000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IST</font></td>
<td width="124"><font size="2">1.20</font></td>
<td width="177"><font size="2">25000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">2.110.12</font></td>
<td width="177"><font size="2">32000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">ANK</font></td>
<td width="124"><font size="2">12.128.43</font></td>
<td width="177"><font size="2">55000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">6.142.42</font></td>
<td width="177"><font size="2">190000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">9.86.50</font></td>
<td width="177"><font size="2">10000</font></td>
</tr>
<tr>
<td width="62"><font size="2">2007</font></td>
<td width="161" ><font size="2">IZM</font></td>
<td width="124"><font size="2">12.43.250</font></td>
<td width="177"><font size="2">129000</font></td>
</tr>
</table>
<p>Â </p>
<p><b><font size="2">tbl_PRODUCT_GROUPS<br>
(This table gives the names to the product of the tbl_PRODUCTIONS table)</font></b></p>
<table border="1" width="366" id="table3">
<tr>
<td><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
<td width="175"><b><font size="2">fld_PRODUCT_GROUP_CODE</font></b></td>
</tr>
<tr>
<td width="175"><font size="2">1.10</font></td>
<td width="175"><font size="2">Pencil</font></td>
</tr>
<tr>
<td width="175"><font size="2">1.20</font></td>
<td width="175"><font size="2">Rubber</font></td>
</tr>
<tr>
<td width="175"><font size="2">2.110.12</font></td>
<td width="175"><font size="2">Ruler</font></td>
</tr>
<tr>
<td width="175"><font size="2">12.128.43</font></td>
<td width="175"><font size="2">Pencil Box</font></td>
</tr>
<tr>
<td width="175"><font size="2">6.142.42</font></td>
<td width="175"><font size="2">School Bag</font></td>
</tr>
<tr>
<td width="175"><font size="2">9.86.50</font></td>
<td width="175"><font size="2">Pen</font></td>
</tr>
<tr>
<td width="175"><font size="2">12.43.250</font></td>
<td width="175"><font size="2">Calendar</font></td>
</tr>
</table>
<p>Â </p>
<p><font size="2">These 3 tables are in Oracle. I am trying to access the tables
with an asp page. I succeed to retrieve the values to a page, but processing the
values and joining the tables together is too complex for me. The table I want
is like this. Would you help me creating <b>ASP</b> page and <b>SQL</b> queries
to create this table:</font></p>
<p><font size="2">Parameters to send the asp page that process the SQL queries:<br>
startdate=18.01.2007<br>
finishdate=20.01.2007</font></p>
<p><b><font size="2">The Productions of -18.01.2007-20.01.2007</font></b></p>
<table border="1" width="1066" id="table4">
<tr>
<td><font size="2">Comes From tbl_PRODUCTIONS table</font></td>
<td><font size="2">Comes From tbl_PRODUCT_GROUPS table</font></td>
<td width="155"><font size="2">Comes from tbl_ANNUAL_PRG table</font></td>
<td width="208"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
<td width="179"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
<td width="108"><font size="2">Comes from tbl_PRODUCTIONS retrieving the
values for "finishdate". Not cumulative like other values!</font></td>
<td width="187"><font size="2">Comes from tbl_PRODUCTIONS table summing
each value for each product</font></td>
</tr>
<tr>
<td><b><font size="2">Producer Locations</font></b></td>
<td><b><font size="2">Products</font></b></td>
<td width="155"><font size="2"><b>Annual Production Program</b></font></td>
<td width="208"><font size="2"><b>Production Program for wanted period</b></font></td>
<td width="179"><font size="2"><b>Production Quantity for wanted period</b></font></td>
<td width="108"><font size="2"><b>Stock Quantity</b></font></td>
<td width="187"><font size="2"><b>Selling Quantity for wanted period</b></font></td>
</tr>
<tr>
<td width="119" ><font size="2">IST</font></td>
<td width="61"><font size="2">Pencil</font></td>
<td width="155"><font size="2">18000</font></td>
<td width="208"><font size="2">300</font></td>
<td width="179"><font size="2">304 </font></td>
<td width="108"><font size="2">100</font></td>
<td width="187"><font size="2">30</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IST</font></td>
<td width="61"><font size="2">Rubber</font></td>
<td width="155"><font size="2">25000</font></td>
<td width="208"><font size="2">600</font></td>
<td width="179"><font size="2">590</font></td>
<td width="108"><font size="2">200</font></td>
<td width="187"><font size="2">162</font></td>
</tr>
<tr>
<td width="119" ><font size="2">ANK</font></td>
<td width="61"><font size="2">Ruler</font></td>
<td width="155"><font size="2">32000</font></td>
<td width="208"><font size="2">900</font></td>
<td width="179"><font size="2">1009</font></td>
<td width="108"><font size="2">450</font></td>
<td width="187"><font size="2">2235</font></td>
</tr>
<tr>
<td width="119" ><font size="2">ANK</font></td>
<td width="61"><font size="2">Pencil Box</font></td>
<td width="155"><font size="2">55000</font></td>
<td width="208"><font size="2">450</font></td>
<td width="179"><font size="2">461</font></td>
<td width="108"><font size="2">4664</font></td>
<td width="187"><font size="2">165</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">School Bag</font></td>
<td width="155"><font size="2">190000</font></td>
<td width="208"><font size="2">300</font></td>
<td width="179"><font size="2">320</font></td>
<td width="108"><font size="2">234</font></td>
<td width="187"><font size="2">231</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">Pen</font></td>
<td width="155"><font size="2">10000</font></td>
<td width="208"><font size="2">375</font></td>
<td width="179"><font size="2">365</font></td>
<td width="108"><font size="2">654</font></td>
<td width="187"><font size="2">240</font></td>
</tr>
<tr>
<td width="119" ><font size="2">IZM</font></td>
<td width="61"><font size="2">Calendar</font></td>
<td width="155"><font size="2">129000</font></td>
<td width="208"><font size="2">225</font></td>
<td width="179"><font size="2">230</font></td>
<td width="108">233</td>
<td width="187"><font size="2">303</font></td>
</tr>
</table>
OS: WinXP Pro
DB: Oracle (Version ?)
Script: ASP
View 4 Replies
View Related
Sep 12, 2000
Is there a way to find out total number of queries ran i.e by day/hour or since the database was brought up in SQL Server 7?
Thanks in advance,
View 1 Replies
View Related
Apr 22, 2015
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies
View Related
Aug 3, 2005
For some reason my Stored Procs are not recognizing NULL values within my database:For example:Select *From ResultsWhere(home_Phone IS NULL)All the home_Phone vaules that are NULL are not being picked up by the query. Any ideas are appriciated. Thanks in advance everyone. RB
View 6 Replies
View Related
Mar 18, 2008
Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 1 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
Sep 30, 2014
I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.
select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')
[code]....
is it possible to get the numbers from keys 1 & 2 on the same row in a new query?
Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.
View 3 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Jan 8, 2008
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
View 1 Replies
View Related
Mar 30, 2015
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
View 3 Replies
View Related
Dec 30, 2014
How to find the list of queries currently running in the Database with User Login Information.
Since my database application is running slow, to find the slow queries.
View 8 Replies
View Related
Feb 11, 2004
Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
View 3 Replies
View Related
Mar 21, 2004
I have a page that will require several hundred update queries to be sent to the database. How much of a performance increase will i get by joining them all into one statement and sending them as a batch instead of running them one by one?
Thanks.
View 5 Replies
View Related
Jul 23, 2005
In Access, if I want to update one table with information from another,all I need to do is to create an Update query with the two tables, linkthe primary keys and reference the source table(s)/column(s) with thedestination table(s)/column(s). How do I achieve the same thing in SQL?RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
May 22, 2008
Hi,
Trying to update a single value within a table, thus eliminating nulls. Another words, if the value is NULL update it with the next preceeding non-null value. In this example, 1 should be CO, 2 should be CO, 6 should be CO, 8 should be TT, and 10 should be TT.
For example,
1 NULL
2 NULL
3 CO
4 CO
5 CO
6 NULL
7 TT
8 NULL
9 TT
10 NULL
Any ideas? Thanks.
View 2 Replies
View Related
Jul 23, 2005
I'm new to adp w/ sql server but I have to use it on a project i'mdoing...One of the MUSTS for this project is the ability to update a 00 - 09text value with the appropriate text description from another table...Easy as pie in .mdb. Of course In the stored procedure it barks at meand tells me that an update query can only have one table.. ouch thathurts...I'm currently reading on the subject but this group has been veryhelpful in the past.....I found this link...http://www.sqlservercentral.com/col...stheeasyway.aspUnfortunetly I'm using MSDE not Enterprise so I don't think I can usethe query analyser.. But I tryed it in my Access ADP anywayit barked at me..I tried to go from this....SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1FROM dbo.TEST INNER JOINdbo.LU_SEX ON dbo.TEST.SEX_DP1 =dbo.LU_SEX.SEX_DECTo this...UPDATE dbo.TEST.SEX_DP1SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODEFROM dbo.LU_SEX INNER JOINdbo.TEST ON dbo.LU_SEX.SEX_DEC =dbo.TEST.SEX_DP1Maybe I need a good book on this?Thanks,Charles
View 2 Replies
View Related
Apr 10, 2007
Background
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
View 6 Replies
View Related
Sep 8, 2007
I'm writing an application for Windows Mobile 5 / Pocket PC using VB.NET 2005. The database is connected using an instance of SqlCeConnection and updated by an SqlCeCommand.
The application can perform select queries on data originally entered into the database through Visual Studio, or perform update / insert queries at run time. Anything inserted or updated can be returned by a select query whilst the application is running, however, anything I have inserted or updated doesn't appear to be written to the SDF file and hence is not in the database after restarting the application.
Am I missing something that's different between performing queries on an SQL CE database on Pocket PC and an ODBC source in a normal Windows application?
View 13 Replies
View Related
Mar 9, 2008
Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies
View Related
May 28, 2008
I have written this script: What I want to do now is, with the new JOINED table find and display all the duplicates custID WHERE the price is either 100 OR 200. Can anyone help? I am very new to all this and can't see how to do it. Thanks!
SELECT
*
FROM
table_customer T1
INNER JOIN
table_itemsBought T2
ON
T2.custID = T1.custID
WHERE
price = '100'
OR price = '200';
View 2 Replies
View Related
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
View Related
Dec 7, 2007
I was wondering how to do a find and replace with SQL? Would I use a SET statement?
I need to find a specific value and replace all of the results with a different value.
Thanks for all responses.
View 1 Replies
View Related