Missing Row In Simple Conditioned SELECT Statement
Nov 7, 2007
Hey there everyone,
I'm sure there's a good reason for this, I just have no idea what it is.
If I run a SELECT * statement on one of my tables, the result set is missing one of the records.
If I SELECT that specific row (by identifier or anything else really), it returns just fine.
Any kind of select (e.g. WHERE ID > X) fails to return that specific row.
Any idea why this might be happening?
Thanks in advance for any guidance!
View 4 Replies
ADVERTISEMENT
Oct 13, 2007
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.
what did i miss??
View 2 Replies
View Related
Mar 19, 2008
Hi folks,
I've got a query like this one :
select A, max(B) from table
where A = ('80')
group by A
order by A
However i need to set a condition to my filter , where the filter is only applied in certain cases
something like
where if C>1 then A = ('80')
I really dont now if its possible , is there any way to do this ?
View 2 Replies
View Related
Dec 6, 2004
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!
View 1 Replies
View Related
Nov 15, 2007
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") & "'"
If I only enter one table, I get a result.
View 12 Replies
View Related
Aug 13, 2007
Hi,
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.
View 3 Replies
View Related
Feb 4, 2008
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?
View 3 Replies
View Related
Jul 30, 2007
I have a table with the name of customers in it. The problem is that I have one column with the full name in it like
|Last First Middle|.
As you can see this is a problem for me.
I need to find a way to put these values into 3 seperate columns.
|Last| First| Middle |
Is there a function to seperate them via a space?
I have been looking on the web and have not found any functions or solutions.
Any help would be most appreciated.
Thanks in advance,
Gene
View 3 Replies
View Related
Sep 14, 2007
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
<<<<<<<<<<<<<<<<<<<<<<<<<< Results
A
KWANG
TAK
10/25/1929 12:00:00 AM
T
ETHEL
MOE
5/24/1933 12:00:00 AM
T
GILBERT
BARAJAS
1/9/1937 12:00:00 AM
A
EDILBERTO
PENA
2/10/1937 12:00:00 AM
View 11 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Jul 22, 2015
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).
The addition isn’t working. I think with a small push I can get this straightened out.
View 6 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Apr 27, 2005
Code:
<%@Language = "VBScript"%>
<%Option Explicit%>
<%
dim oRs,oConn,dateofleave,sql,uid
dateofleave = trim(request.querystring("leavedate"))
uid = trim(request.querystring("employeeID"))
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open(Server.MapPath("test.mdb"))
set oRs = Server.CreateObject("ADODB.Recordset")
sql ="INSERT INTO test (dateofleave)"
sql = sql & "VALUES ('&dateofleave') WHERE employeeID=&uid"
set oRs = oConn.execute(sql)
%>
can someone help me with this error?
Microsoft JET Database Engine- Error '80040e14'
Missing semicolon ( at end of SQL statement.
/test/booking.asp, line 18
where line 18 is "set oRs = oConn.execute(sql)"
View 4 Replies
View Related
Mar 31, 2006
I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing?
TIA
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
View 4 Replies
View Related
Apr 10, 2004
Hey
I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.
I am recieving this error and don't know why, i have the following coed below.
Dim objReaderQ as OleDBDataReader
Dim strSQLRead As String
Dim objCmd As New OleDbCommand
strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
objCmd = new OleDbCommand(strSQLRead, objConn)
objReaderQ = objCmd.ExecuteReader()
if objReaderQ.Read()
'update quantity by 1
Dim i as integer
i = objReaderQ("quantity")
i = i + 1
objReaderQ.Close()
Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
objCmdQuantity.Connection = objConn
objCmdQuantity.Parameters.Add("@quantity", OleDbType.VarChar, 255)
objCmdQuantity.Parameters("@quantity").Value = i
objCmdQuantity.ExecuteNonQuery() ' <--- Error Is Occuring On This Line
Response.Redirect("ViewBasket.aspx")
end if
I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.
Thanks you for your time
Chris
View 1 Replies
View Related
Jul 23, 2005
Here is a small sample of data from a table of about 500 rows(Using MSSqlserver 2000)EntryTime Speed Gross Net------------------ ----- -----21:09:13.310 0 0 021:09:19.370 9000 NULL NULL21:09:21.310 NULL 95 NULL21:10:12.380 9000 NULL NULL21:10:24.310 NULL 253 NULL21:11:24.370 8000 NULL NULL21:11:27.310 NULL 410 NULL21:11:51.320 NULL 438 NULL21:11:51.490 NULL NULL 10After the first row, every row has only one value of the three.I would like to replace all the NULL values with calculatedinterpolations.I can do it w/ cursors or while loops.I could do it w/ VB (I think)Can this be done w/ an Update statement using self joins?What would be the best way?The value for speed can increase or decrease over time, but can neverbe < 0Net is always less than gross, and neither can go below 0.TIA for any helpful suggestions.Thanks,BM
View 9 Replies
View Related
Apr 19, 2004
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
select firstname||lastname from table
that errors, what is the right syntax?
Thanks
View 1 Replies
View Related
Jan 16, 2005
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)
any Ideas? Do I use the set comand somehow here?
Thanks for any code...
View 1 Replies
View Related
Apr 23, 2006
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
View 5 Replies
View Related
Jun 6, 2008
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?
View 2 Replies
View Related
Aug 16, 2006
Hi.
Please help me.
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?
Thanks a lot!
View 1 Replies
View Related
Jan 20, 2015
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)
2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...
What I want is this (as is shown above). Is this possible?
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
View 7 Replies
View Related
Nov 13, 2007
Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity
begin transaction
insert into [tblOrderNumber] with default values
rollback ' done so no records in this table
select @OrderNumber = scope_identity()
I call this from another proc that inserts values into my order table:
procedure usp_Insert @OrderNumber int
as
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION
END CATCH
Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.
The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.
Maybe it has something to do with the rollback call in the usp_getneworder.
What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.
Thanks
View 9 Replies
View Related
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 addedTotalSales: the total sales generated since the item was added to the siteI 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 followingTotalSales / ("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.
View 2 Replies
View Related
Apr 27, 2006
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.
View 13 Replies
View Related
Apr 19, 2008
Here are my tables:
-------------------------------
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?
View 1 Replies
View Related
Aug 3, 2007
Hello well-intentionned reader,
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
Thank you for your time.
/gozu
View 4 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jul 10, 2006
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!
View 2 Replies
View Related
Mar 1, 2007
Hello,
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.
View 1 Replies
View Related
Apr 24, 2006
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!!
View 1 Replies
View Related
Apr 17, 2008
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
View 2 Replies
View Related
Mar 4, 2008
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!
View 4 Replies
View Related
