T-SQL (SS2K8) :: Get Last Non Null Value From Column
Apr 14, 2015
I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday
Here was my latest attempt
;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'
[Code] ....
View 8 Replies
ADVERTISEMENT
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Feb 24, 2015
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
View 4 Replies
View Related
Mar 9, 2015
I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
View 1 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
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: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
May 13, 2015
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
View 4 Replies
View Related
Jan 26, 2015
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
View 6 Replies
View Related
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
[code]....
So this would be the resultset:
1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27
View 3 Replies
View Related
May 14, 2014
I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.
ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN
[code]....
View 9 Replies
View Related
Dec 1, 2014
I am emailing data using this:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',
[Code] ....
The result displays:
ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753
Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?
View 5 Replies
View Related
Jun 24, 2015
I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?
View 5 Replies
View Related
Aug 13, 2015
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
View 5 Replies
View Related
Apr 30, 2014
I am creating web application for state and dist wise map. I've the tables like
create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')
[Code] .....
My output is depends on the dist selection so made procedure
alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0
[Code] ....
Myself doubt is when @dist_id is null or "0" how to show default value ?
Otherwise above my code is correct?
View 4 Replies
View Related
Sep 9, 2014
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.
View 9 Replies
View Related
May 2, 2009
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
[Code] ....
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
View 9 Replies
View Related
Aug 22, 2012
I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.
In the long sql, here is a part of it,
When Matched and
((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)
Then update
Set Student.Schoolid=esis.schoolid,
.....
My question is how about if the column has null values in it.for example
if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.
I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?
View 9 Replies
View Related
Mar 21, 2014
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
View 2 Replies
View Related
Apr 11, 2014
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
View 5 Replies
View Related
Apr 27, 2007
Dear folks,
please tell me the query for altering a column from not null to null
Vinod
View 15 Replies
View Related
Feb 28, 2008
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
View 3 Replies
View Related
Feb 29, 2008
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
View 3 Replies
View Related
Aug 17, 2015
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,
[Code]...
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
View 9 Replies
View Related
May 3, 2007
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies
View Related
Jun 24, 2014
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name
FROM SomeTable A
Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
View 3 Replies
View Related
Aug 20, 2014
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrialBalance](
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
View 7 Replies
View Related
Mar 4, 2014
Sample Data
MemberID Codes
00000123 012|222|123|333
00000233 012|222|332
00000244 012|211
00000332 012
I am trying to get it so as following:
MemberID Code1, Code2, Code3, Code4
I tried using the XML method but in working with an example, I actually got stuck on one of the declare fields and cant seem to work around it.
Failed Attempt:
DECLARE @x xml;
DECLARE @line VARCHAR(MAX);
SET @x = Cast(
'<field>'
+ replace(@line, '|', '</field><field>')
+ '</field>' AS XML);
[code]...
View 6 Replies
View Related
Oct 24, 2014
With cte_table (columna)
As (select a from cust)
Update columna
Set a = 'hello'
Question: would a in cust be updated even though the update is to the CTE column named columna? Or would there be an error where the column name must match?
View 2 Replies
View Related
Dec 29, 2014
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
View 5 Replies
View Related
Sep 1, 2015
Create table control_Total ( Filename varchar(1000) )
insert into control_Total values('PCH123_TLNX.account.TUED.20150831.txt Bytes:645 Records:4')
insert into control_Total values('PCH123_TLNY.account.TWED.20150831.txt Bytes:1920 Records:12')
how can I get output like this :
FileName RecordCountBytes
PCH123_TLNX.account.TUED.20150831.txt4645
PCH123_TLNY.account.TWED.20150831.txt121920
View 7 Replies
View Related
Jul 25, 2012
Suppose a table being
Create table myTable (ID int, col1 int, col2 int)
I know how to make a computed column being the sum of other column for the same ID e.g. "computed_column = col1 + col2".
Getting the average would be "computed_column = (col1 + col2)/2" But how to get the Max, Min?
Even "Sum(col1,col2)" or AVG(col1, col2) does not work as the formula for a computed column...
View 9 Replies
View Related
Mar 22, 2014
I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
[code]...
View 2 Replies
View Related
Apr 18, 2014
It is possible to covert rows into column by using tsql script, see attached file for more details...
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, ItemNo int)
GO
INSERT INTO Table1
SELECT '01', 'Camera', 100, 1
UNION ALL
SELECT '01', 'Memory 4GB', 10, 2
[code]....
View 5 Replies
View Related
