There is a column "name" common in both the tables tbcity & tbemp.
Here we are not referencing or getting the column name from tbemp
but still it will give the records from the table tbemp also.
It is returning the number of records from tbcity multiplied by
the number of records of table tbemp.
if there are 10 records in table tbcity & 5 records in table tbemp
then instead of returning only 10 records it is returning 50
records.Very strange...!
if there is no records in table tbemp then it is returning zero
records.
So please suggest me the solution and why it is behaving in such
a strange manner.
I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE where the conversation data is registered (and other columns).
Now I need to retrieve, for each client, the last N (for some clients, eventually, less then N) conversations with one T-SQL statement.
Does anyone knows how to do this? Is it possible with T-SQL only?
Hello everyone,I was wondering. Is it possible, to recive an event from ActiveX intodatabase?I was looking alredy with notification services, but I think that'sthe wrong way.Lets say, that there is a ActiveX which realize some tasks.The database trigers the ActiveX like below:================================================== =============declare @iRetValintdeclare @iObjectintdeclare @sPropertyvarchar(2560)declare @sSource varchar(1000)declare @sDescription varchar(1000)declare @sLog varchar(1000)declare @dDateEVT datetimedeclare @sText1varchar(10)declare @sText2varchar(10)declare @iProperty intset @iObject = 0set @dDateEVT = getdate()set @iRetVal = 0set @sText1 = '0000000000'set @sText2 = '0000000000'set @iProperty = 7exec @iRetVal = sp_OACreate 'MComponent.pidMess', @iObject OUTPUT,1EXEC sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTIF @iRetVal <> 0beginset @sLog = 'LOG1: No object created. Source: ' + @sSource + 'Description: ' + @sDescriptionprint @sLogend-- Methodexec @iRetVal = sp_OAMethod @iObject,'Send_FromA', @iPropertyOUT,@nMessageNr = 5, @bstrDateTime = @dDateEVT, @textFromA1 = @sText1,@textFromA2 = @sText2EXEC sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTIF @iRetVal <> 0beginset @sLog = 'LOG3: Source: ' + @sSource + ' Description: ' +@sDescriptionprint @sLogendPRINT 'Property from method:'PRINT @iProperty================================================== =============This function works properly. I recive the data into ActiveX.After the ActiveX process the data, it returns the event, wit aresponse for this call of method.Now ... how do I can get, this event into SQL server?Is it possible to do that enyhow, without acctions like:- do, that the ActiveX writes the data in to a interface table, wherefrom it will be readed.Any help would be appreciatedMateusz
i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. Â used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. Â one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. Â no effect; Â the user makes their own login id but that does nt matter either.
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
In brief, here is my quandary… I have a GridView on a page with some radio buttons, a couple of check boxes, some drop downs etc. And depending on what the user selects the Grid View needs to show different columns from the database… The easiest way I have found to do this is to have a separate Data Object for each “view�, i.e. each variation of the checkboxes and radio buttons (which determine which dropdowns to use for the variables) and then bind the GridView to the specific Data Source at runtime…
It seemed like a good idea at the time, but now I have over 7 different data connections cluttering up the design view, and more to come and I feel like I am missing a better way to achieve the same results…
Does anyone have some advice for a major novice?
Here’s the beginning of a large set of nested ifs, if this is the way it’s done that’s fine too, I’ll just keep creating data sources, I just get the feeling there must be an easier way to do this..
I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine) Insert into customer(CustomerID, LastName, FirstName, BillingAddress, City, State, Country, Zipcode, PhoneNumber, EmailAddress) select CustomerID,(left(ContactName, charindex(' ', ContactName)-1)) as LastName, (right(ContactName, charindex(' ', reverse(ContactName))-1)) as FirstName, Address, City,Region,PostalCode,Country,Phone from Northwind.dbo.Customers
I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my applicationAny help would be of great
I am running an SSIS package to extract data from a source SQL Server database to our own database. The source is on a linked server. The package worked until a column size was increased on a table on the source.
If I run a query against the linked server table I get the following error:
'OLE DB provider 'SQLNCLI' for linked server 'GSC-RPT.SVR.BANKONE.NET' returned data that does not match expected data length for column '[GSC-RPT.SVR.BANKONE.NET].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 160.'
I've tried deleting the links and recreating them. Nothing works. I don't understand why my database query is expecting a data length of 100. Why does it not pick up the definition of the field length from the linked server? How has it retained this expectation when I have deleted the linkage and recreated it?
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
Hi ALL, I am getting 2 errors for the report which has graph.
1.The chart €˜Chart_ID0EDABDA€™ contains a different number of DataPoints per ChartSeries element than the number of StaticCategory elements. If the chart contains no StaticCategory, it must contain one DataPoint per ChartSeries.
2.[rsWrongNumberOfChartDataPoints] The chart €˜Chart_ID0EDABDA€™ has a ChartData element that contains a collection of 6 DataPoint element(s), which is different from the number of expected datapoints (1). The expected number of datapoints in the definition is (Max(number of static categories, 1) * Max (number of static series, 1)).
I am converting crystal reports to SSRS.Can anybody give me some info about Data points requirments and how to get rid of these errors.I am getting these 2 error almost all the SSRS reports having Graphs. Thanks .
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
I just had this happen twice in a row. The data flow task is showing a number of records almost twice as high as the actual data that is going into the table. This happened to me in two different DFT's with different data.
I am using an OLEDB source which uses a query something like:
select * from functiona()
In the DataFlowTask I see it had around 400,000 Rows go through the path all the way to the deastionation..
However I know that isn't right. If I do:
select count(*) from functiona() I get 200000 rows. Now the weird thing is if I check the table that it inserted to, it has the right number of rows, 200,000. (Numbers are not exact).
I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.
Any idea on how to tell Excel that those columns are numbers?
oConn = New SqlClient.SqlConnection oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;" oCmd = New SqlClient.SqlCommand oCmd.Connection = oConn oCmd.CommandType = CommandType.StoredProcedure oCmd.CommandText = "TestStdInfo" 'parameters block oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString()) oParam1.Direction = ParameterDirection.Input oParam1.SqlDbType = SqlDbType.Int oCmd.Parameters.Add(oParam1) oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text)) oParam2.Direction = ParameterDirection.Input oParam2.SqlDbType = SqlDbType.DateTime oCmd.Parameters.Add(oParam2) oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text) oParam3.Direction = ParameterDirection.Input oParam3.SqlDbType = SqlDbType.VarChar oCmd.Parameters.Add(oParam3) oConn.Open() daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn) dsStudent = New DataSet daStudent.Fill(dsStudent) 'This line is highlighted when error happens oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)
INSERT INTO ##TEMP_TEST SELECT '2014-09-30','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM' UNION SELECT '2014-09-29','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM' UNION SELECT '2014-09-28','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM' GO INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULLÂ
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (TypeID = @TypeID OR 10 TypeID IS NULL) AND (LocationID = @LocationID OR 11 LocationID IS NULL) AND (Price >= @MinPrice OR 12 Price IS NULL) AND (PriceMax <= @MaxPrice OR 13 PriceMax IS NULL)
SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ "MONTH(Some_Date) >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" This is NOT: SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ Month >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" it says - Invalid column name 'Month'
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting Alter Table Employees Add Constraint CK_SNN Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _ "S (@UserID, @UserName, @UserPassword, @Name, @Email)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userID.ParameterName = "@UserID" dbParam_userID.Value = userID dbParam_userID.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_userID) Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userName.ParameterName = "@UserName" dbParam_userName.Value = userName dbParam_userName.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userName) Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userPassword.ParameterName = "@UserPassword" dbParam_userPassword.Value = userPassword dbParam_userPassword.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userPassword) Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_name.ParameterName = "@Name" dbParam_name.Value = name dbParam_name.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_name) Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_email.ParameterName = "@Email" dbParam_email.Value = email dbParam_email.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Message.Text = "Register Successed, click on the link WebCam for login"
Else Message.Text = "Failure" End If End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs) Line 53: Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Line 55: Message.Text = "Register Successed, click on the link WebCam for login" Line 56:
Hi please lok at this SP I have written and point where am I commiting mistake. The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails] ( @ProductCode Int = null )
AS
DECLARE @sqlString AS nvarchar(2000) SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode, Master.PROD_DESC_TEXT AS ProductName, Detail.PiecesPerBox, Detail.Price FROM cp_nafta_dws.PRODUCT AS Master INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM' BEGIN IF NOT (@ProductCode = NULL) BEGIN SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode END END
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. 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: Line 1: Incorrect syntax near '='.Source Error:
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color). my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across. Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");} private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"]; if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
I'm working on an assignment and I am about to give up. I can't figure out what I'm doing wrong. It seems like I have everything worked out, but when I run the SQL query i've come up with, I get errors regarding INVALID tables.
As far as I can tell, all my tables are valid. Can anyone give me any pointers on what i'm doing wrong **read: I'm not asking for my assignment to be done for me, just asking for help because I am so close to getting the right answer....i think**
I am trying to created a view and have a need for conditional logic:
Here is what I presently have (not working): ---------------------------------------------------------------------------- IF (ISDATE(COMPLETIONDATE) = 1) BEGIN CASE WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN' WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW' WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED' END AS THRESHOLDSTATUS END ELSE IF (ISDATE(COMPLETIONDATE) = 0) BEGIN CASE WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN' WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW' WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED' END AS THRESHOLDSTATUS END --------------------------------------------------------------------------
Can someone tell me what I am doing wrong?
Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".
