Need Help For A Simple Formula For An Sql Statement
Apr 6, 2007
I have a products database on my online store that records 2 columns for each product:
DateAdded: the datetime the item was added
TotalSales: the total sales generated since the item was added to the site
I have already figured out how to calculate best sellers with this formula:
TotalSales / TotalDays
I am adding a computed column to my database, I need to calculate the following
TotalSales / ("this is where i need something to use today's date and calculate how many days it has been since it was added")
Then if I can do that, I will automatically have a column that will give me a number ranking each of my items.
I am trying to concatenate two columns together firstname, lastname i have done some sql in oracle, but i'm noticing that mssql is different what i have seen is
I am attempting to do an insert on the DataGrids Delete command. The problem I am running into is that im not very good at SQL inserts.
Can anyone tell me how to insert a record into a table based on the ID of a record in another table? I want to do this.
As a record (datagrid Row) is deleted, I want to run code (that I dont know how to write) that does this...
Insert into Table-Interface-LOG (fields), from Table-Interface (fields) Where Interface.Interface_ID = @interface_id
I just need the SQL to do the insert, no ADO code or anything. I dont understand how to use the SET in a SQL command. What I have so far that Doesnt work.. not even sure why it doesnt work..
INSERT INTO InterfaceLog SELECT Interface_ID, App_ID, SendsToApp_ID, InterfaceName, Description, ITContact_ID, Type, Frequency, DataFormat, Status, CommMethod, Complexity, ProdEntryDate, ProdExitDate FROM Interface WHERE (Interface_ID = @interface_id)
Hi, I feel a difficulty to understand the following procedure, it is simple but i am not sure the logic, could anyone give me some explain / hints , then I know how to write the similiar procedure next time. thanks for your time. I extract the code from the Classifieds Starter Kit For the procedure of GetCategoryByParentIdWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL) For the procedure of GetParentCategoryById WHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%' ALTER PROCEDURE GetCategoryByParentId(@ParentCategoryId int = 0)ASSET NOCOUNT ON;SELECT [Id], [ParentCategoryId], [Name], NumProductsFROM CategoryWHERE (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)ORDER BY NameALTER PROCEDURE GetParentCategoryById @Id intASSELECT Id, ParentCategoryId, Name, NumProductsFROM CategoryWHERE (SELECT Path FROM Category WHERE Id = @Id) LIKE Path + '%'ORDER BY Path Thanks a lot, Trevor
I need to see whether there's any unapproved timecards, and if so compile a list of supervisors that need to make approvals.
my original statement was: SELECT DISTINCT EmployeeMaster.SupervisorNumber , SupervisorMaster.EmailAddress , SupervisorMaster.EmployeeMasterNumber , RTRIM(SupervisorMaster.FirstName) + ' ' + RTRIM(SupervisorMaster.LastName) AS SupervisorName FROM Cards LEFT OUTER JOIN EmployeeMaster ON Cards.EmployeeNumber = EmployeeMaster.EmployeeNumber LEFT OUTER JOIN SupervisorMaster ON EmployeeMaster.SupervisorNumber = SupervisorMaster.EmployeeNumber WHERE (Cards.SupApp = 0)
And this worked great until they asked for a date range.
If I am correct the distinct will look for the first occurrence that meets the select criteria and ignore the rest of the rows that meet the criteria. Leading to the possiblity that the first row is not a timecard that meets the date requirement, and even though other timecards exist that will satisfy the condition, they will not be tested because of the Distinct clause.
What's the best way to handle a situation like this?
Table 'Counter' has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this?
Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.
Here is the few lines of interest
System.Data.SqlClient.SqlCommand command; command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);
dataReader = command.ExecuteReader();
This is the error I am coming up with.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.
I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!
I have a textbox with the id of txtName it is a name text box. and I have a submit button. How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.
------------------------------- Events ------------------------------- ID | E_EventName -------------------------------
------------------------------- Photos ------------------------------- ID | P_EventID | P_Filename -------------------------------
Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?
I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2) MANAGER_ID NUMBER(6)
My statement SQL> SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6 WHERE EMPLOYEE.LAST_NAME = .King.) * ERROR at line 6: ORA-00936: missing expression
Hi everyone and thank for taking the time to read, I am new to this forum and to SQL to. I am taking a programmers course, i learned mainly C# for now, but i also know a bit of C and C++ and linux programming, i am going through my SQL school book, (SQL Server 2000 design and implementation) and i am trying a simple IF statement. heres how it goes DECLARE @P CHAR SET @P=NULL
SELECT @P=Powerlck FROM vehicle IF (@P=NULL) PRINT 'got you'
powerlck is a collum taking char(1) which is either Null or 1. there is five entry all but one is set to 1, null is the default
when i run this script, it does not print 'got you'
but if i run select * from vehicle it will show one with <NULL> value.
I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type). All I want to do is pull out one recordset (if it's in that table) from each of the tables.
This is what I have: "SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"
hi, everyone, When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen? Can somebody help me? Thanks in advance!
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value. Right now if the value is anything other than 1 or 0, it will leave the cell blank. CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus Thank You.
Hello and thanks for looking! I have this sql stament:
Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"
It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:
WHERE Username = 'grogo21'
I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!
This should be terribly simply, but I just can't seem to figure out where I'm going wrong. I have a table that logs usage of a few software licenses in my division, so it has columns for the username, the license, and the date/time checked out and date/time checked in. I have written a quick SQL query that totals how much time all the users have spent in three of the licenses during this work week. It works fine. But now I just want to limit that query to those users who have used those licenses for more than two hours.
Sounds simple enough, but I must be overlooking something obvious. I have tried [Minutes]>120 in both a WHERE and a HAVING statement, but neither worked. I have also tried changing the name of the column to something other than Minutes in the off case that there might be some kind of keyword confusion, but it didn't work. I have also tried various other unlikely fixes but continually get the error "Invalid Column Name 'Minutes'".
Any help would be very appreciated since this is driving me nuts. The code that works is below.
Declare @datMin datetime Declare @datMax datetime
set @datMin=DateAdd(mi, -(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),--Gets the current minute of the day DateAdd(dd,-datepart(dw,GetDate())+1,GetDate()))--Gets First Day of the Week at this time
set @datMax=DateAdd(mi, 1439-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())), GetDate())
Select UserName, Sum(Case When DateOut<@datMin and DateIn<@datMax and DateIn>@datMax Then datediff(n, @datMin, DateIn) When DateOut<@datMin and DateIn>@datMax Then datediff(n, @datMin, @datMax) When DateOut>@datMin and DateOut<@datMax and DateIn>@datMax Then datediff(n, DateOut, @datMax) Else datediff(n, DateOut, DateIn) End) as [Minutes] From [Log] Where ((DateOut>=@datMin and DateOut<=@datMax) or (DateIn>=@datMin and DateIn<=@datMax) or (DateOut<@datMin and DateIn>@datMax)) And (License='Viewer' or License='ARC/INFO' or License = 'Editor') --And ([Minutes]>120) Group by UserName --Having ([Minutes]>120) Order by [Minutes] desc
I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:
ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)
AS
SELECT ID, UserID, description, URL FROM links WHERE UserID = @UserID
When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:
"No rows affected. (1 row(s) returned)"
and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)
I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.
Any help would be greatly appreciated, I'm using SQL Express.
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong. select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.
UPDATE dbo.Load_AC_Install SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason FROM (Load_AC_Install JOIN Load_AC_Install_Rejected ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"
Update #Load_Install SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason FROM (#Load_Install JOIN Load_AC_Install_Rejected ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )
So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!
Anyone know why?
Thanks for response and please ask me if you still have any questions!
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate FROM Employees FULL OUTER JOIN People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007') ORDER BY People.BirthDate
I've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since. We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down. My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID. How are these duplicate records inserted with a simple insert statement & update ?? Is this a Locking problem or Transaction problem I have no idea. Any ideas will be of great help Thanks in Advance Stored Procedure Used
CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO
In Excel they are showing Sale as -ve quantity and purchase as +ve quantity.
The database has quantity always as +ve figure and a separate column "isPurchase" set to true or false depending on whether purchase or sale.
So I need Derived Column to return a bolean (or int) depending whether quantity is positive or negative. I tried each of the following in the Expression but all of them were invalid expressions.
CASE WHEN [TotalQuantity] > 0 THEN 0 ELSE 1 END
If [TotalQuantity] > 0 THEN 1 ELSE 0 END
IIF ([TotalQuantity] > 0, 1,0)
Can anyone help me with correct syntax, or correct Data Flow Transformation if Derived column is wrong.
hello it seems lack of c# skills is getting the better of me here...iv been trying to get this to work for hours now without success. I have just started working in c# so i am a beginner :) all i want to do it query the database, check to see if a title exists, if yes then populate textbox saying 'title in use' but if the title doesnt exists then continue executing rest of code to add the title. You'll notice iv tried to use string variables and sqlDataAdapter but i cant get these to work either...please help so far i have this that keeps giving me the error that my button click is undefined: protected void bookButton_Click(object sender, EventArgs e){ //string title;try{SqlConnection conn1 = new SqlConnection(); conn1.ConnectionString = "Data Source=Gemma-PC\SQLEXPRESS;" +"Initial Catalog=SoSym;" + "Integrated Security=SSPI;";SqlCommand findTitle = new SqlCommand("SELECT title FROM Publication WHERE title='" + titleTextBox.Text + "';", conn1);
conn1.Open(); findTitle.ExecuteNonQuery(); /* DataSet myDataSetTitle = new DataSet("PublicationTitle"); myDataSetTitle.Clear(); myDataAdapterTitle.Fill(myDataSetTitle);//myDataSet contains results from above SELECT statement title = myDataSetTitle.Tables[0].Rows[0]["title"].ToString();*/ }catch(Exception ex) {TitleInvalidMessage.Text = "Title Not Accepted: This title is already in use by another publication" +ex.Message; } //title does not exist in table so continue and add to database //rest of code here
I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that? please help me? any idea or tutorial..example anything...
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application thanx
