Transact SQL :: Updating Missing Column Gaps

Aug 24, 2015

I'm a little bit unsure...

declare @t table
(col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10) ) 
insert into @t values ('A123', 'Test', '','')
insert into @t values ('', 'Test 1', 'Y','N')

[Code] ...

Which comes out as 

col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0

but what I would like is col 1 populated As A123 until it hits A125  then populated A125 etc.   I was thinking about using the iff but not getting anywhere fast.

View 11 Replies


ADVERTISEMENT

Transact SQL :: Get Missing Values From A Column?

Nov 16, 2015

I have table with column having values 1,2,3,5,6,10.

I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.

I want to get the values 4,7,8,9.

View 8 Replies View Related

Transact SQL :: Updating Date Part In Datetime Column

Sep 18, 2015

I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.

UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID

View 4 Replies View Related

Gaps Or Whitespace Between Column Bars On The Column Chart

Mar 18, 2008

I am working on a report that has 3 bars per series. I would like to add some gapping or whitespace between each bar in each series. Also how do you deal with overlapping. I know it can be done in Excel, I can't find the property of how to do it in SSRS. Any assistance would be appreciated.

View 1 Replies View Related

Transact SQL :: Finding Gaps And Filled With Last Validate Data?

Aug 26, 2015

currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions.  I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:

I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:

The data for testing are:

DECLARE @PublicationsByUser AS TABLE
(
  Id   INT,
  PublicationId  INT,
  MetricId       INT,
  ProviderId     INT,
  DateCreated    DATE,
  Amount         FLOAT

[code]....

View 14 Replies View Related

SQL 2012 :: Gaps In Identity Column

Feb 13, 2014

The identity int column in my sql2012 db is not incrementing properly.

I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1, 2, 3, 4, 5, 1004, 1005

This is happening on a random number of tables at very random times...

View 5 Replies View Related

Queued Updating - Missing Insert Proc

Oct 17, 2007

Hi

I'm running transactional repl with updateable subscribtions - queued updating. Running SQL 2005 sp2, win 2003 sp1

I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.

The questions I have

nr 1.
While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.

nr2.
The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists.
I have changed anything on the publication so I'm not sure how this happens or why.

nr3.
I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.

Help is appreciated.

Thanks




View 3 Replies View Related

Transact SQL :: Missing Records In One Tables

Oct 11, 2015

I want to know one small query..

id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did 
10 to 

I deleted some records now my table have below mentioned rows..

id Name
1 hi
2 how
4 you
6 can
8 not
10 to 

I want to know  the missing records in my table.

OUTPUT IS. 3,7,9

how can  i do that using sql query.

View 11 Replies View Related

Transact SQL :: How To Get Missing Records From One Table

Apr 22, 2015

I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:

ID          Date          Location
1           4/1/2015        bld1
2           4/2/2015        bld1
3           4/4/2015        bld1

I want to run a query like

Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])

And the results would be:

4/3/2015   bld1

View 17 Replies View Related

Transact SQL :: Loop Though Row And Populate Missing Row

Oct 8, 2015

Col1 to Col9 represent the account information, Col10 amount, Col 11 represents the month and  Col12 represents year. According to the data there wasn't any activity for the month 1, 2 and 3.

Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
Col9
Col10
Col11
Col12

[code]....

View 3 Replies View Related

Transact SQL :: Records Inserted Missing

Apr 25, 2015

Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS  like 5,10 etc rows affected. When i check for the same records in the table none of them is present.

This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.

View 10 Replies View Related

Transact SQL :: Trying To Do A Simple Sum But Missing Transaction Number

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

Transact SQL :: Missing Index Feature Disabled?

Apr 23, 2015

I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TEST the execution plan does not specify that I'm missing an index.

View 3 Replies View Related

Transact SQL :: Insert Missing Record Based On A Condition

Sep 29, 2015

How do I get the below scenario:

EmpID DepID
12 2
17 3
17 2
13 3

Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.

Desired Output:

EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2

View 5 Replies View Related

Transact SQL :: Updating Table Set Sent To 1 For All Status IDs

Nov 30, 2015

SID statusid listindex listsize sent
1           12     25        25       1
2           12    25        50       0
3            12   75       150       1
4            14     25     25        1

I have a table like above where cid is unique but status is not for all the status id i need put 1 as they sent out .but till now i used max listindex because they used to send files sequentially but now list index is random so how to update sent to 1 for all the status ids.

View 5 Replies View Related

Transact SQL :: Updates Are Not Updating Database

Jun 12, 2015

No error is thrown, but the update is not made?  Possibly due to the dreaded inline sql being used?  The data structure for these 2 servers is horrific (but that is a story in itself).  The current structure (which needs immediate change) is each employee all 10 of them, have their own database.  If the userid is  6 or higher they are on server2 if the userid is 5 or below they are on server1.  Then they each have their own table that stores the data.  (A story for another day, but def needs overhaul).  However, here is the sql -- what needs to be altered to work with the current data structure and still get the updates needed?  Or we can scratch the update statements entirely if we can get the correct counts needed.

Declare
@employeeID varchar(50)
,@managerID varchar(50)
,@sql varchar(Max)
,@employee varchar(max)
,@itemsold varchar(max)

[Code] ....

View 13 Replies View Related

Transact SQL :: Paging Of Records Which Are Keep Updating

May 7, 2015

I would like to use the following code for querying summary records with paging.

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 10
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER,
* FROM summary
) AS TBL
WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY create_date

Paging is implemented for fast response since the data pool is very large up to 10000000.

The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.

E.g.  At 12:00pm, the result of page 1 (5 per page)  is
R20, R19, R18, R17, R16

After 2 mins, 12:02pm, the user press next page button
Since records R21, R22, R23, R24, R25, R26 are inserted
page 2 result would be R21, R20, R19, R18, R17

So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?

View 2 Replies View Related

Transact SQL :: Updating Only One Row Based On Where Condition

Sep 16, 2008

How can I update only one row doing an update query?
 
For example:
 
update from table1
set category = 'C'
where country = 'Brazil'
 
One want to update only one row with country = 'brazil'

View 5 Replies View Related

Transact SQL :: Grouping And Updating The Table

Dec 5, 2015

I have a table like this. 

AS-IS
 
Column1 Column2  count
a b 20
a b 25
c d 12
c d 22

And I need to update the same as below.

TO-BE
 
Column1 Column2  count
a b 45
c d 34

How to do it?

View 4 Replies View Related

Updating The Column Defined As 'Text' Column

Oct 31, 2002

Hi,

I have a table with col_noteText defined as 'Text' datatype column.

I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.

Does anyone know how to do this for 'Text' datatype column.

Thanks in advance.

jfk

View 1 Replies View Related

Transact SQL :: Insert Rows Into A Table For Missing Sequence Numbers

Jul 29, 2015

In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number

[Code] ....

View 10 Replies View Related

Transact SQL :: Updating A Table With 45 Million Records

Jul 21, 2015

I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach

1. The table has only one clustered index and no other indexes on the table.
2. I am updating in batches say 20000 record-wise.
3. Changed the recovery mode to bulk logged and auto-growth size is set to  300MB and there is enough space in my disk for transaction log .

But still the query is running slowly.

View 10 Replies View Related

Transact SQL :: Removing Cursor And Updating Flag For Each Row?

Jul 15, 2015

After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.

We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.

View 3 Replies View Related

Transact SQL :: Verify Complete Restore Path (check For Missing Files)

Jul 29, 2015

In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).

View 3 Replies View Related

Transact SQL :: Updating Multiple Tables In A Single Query?

Apr 27, 2015

Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.

View 8 Replies View Related

Transact SQL :: Updating Table Rows With Overlapping Dates (extend)

Dec 2, 2015

This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....

I am actually having a table as following:

Table Name: PromotionList

Note that the NULL in endDate means no end date or infinite end date.

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0

And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.

Flag = 1, success merge row. Flag = 2, fail row

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1

The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1

Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.

Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.

Original table:

ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL

Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL

Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).

By taking the min date and max date of the same group, the following table should be achieve after the table is updated.

Final outcome:

ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL

View 2 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

Updating A Column With A Column From Another Table

Apr 1, 2004

Hi, I am kinda new to this so here it goes:

table name: USERS
field names : LName, FName, EmpID


other table name:PERSONNEL
field names : (same as USERS)

The EmpID column in my USERS table is blank (I have 30 records in the table.)

I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data

how would I do this or what would the code be?

sanctos

View 2 Replies View Related

Updating Max(column) And Between_dates Column

Apr 26, 2008



Hi,

I have dataset which has max(column) and between_sale_dates columns So I would like to
update those columns. CustomerNo and Sales_date are important . This group by for these 2
colums. I can write a sproc but How can I do with SSIS?

thanks

View 5 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Missing Column In Returned Dataset.

Nov 21, 2006

I have a web page that has been in production for over a year.  On occasion the web app users report sporadic errors which last a few minutes then go away.  The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page.  The error is that a column that is expected to be in the dataset is not found.  I know this column must exist in the database because its a required field.  After we reset IIS, then all returns to normal and the errors go away.  Below is the stack trace.
 Exception: System.IndexOutOfRangeException  Message: Cannot find column LastName.  Source: System.Data     at System.Data.DataTable.ParseSortString(String sortString)     at System.Data.DataView.UpdateIndex(Boolean force)     at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter)     at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState)     at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex)     at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex)     at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e)     at System.Web.UI.WebControls.Button.OnClick(EventArgs e)     at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)     at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either.  I do not understand why resetting IIS helps the issue.  I need to find the real cause of the problems rather than just resetting IIS all the time.  Any ideas?
 

View 1 Replies View Related

CSV File Missing Column Still Processes.

Nov 15, 2007

I have a SSIS package that processes a CSV style file. For illustration purposes lets say the file is as follows:

R1C1, R1C2, R1C3
R2C1, R2C2, R2C2
R3C1, R3C2, R3C2

So I setup a flat file csv connection with comma as column delim, CR/LF as row delim, etc. Everything works as planned and the package executes.

Now lets say the file comes in wrong and has two columns instead of three. It looks like this.
R1C1, R1C2
R2C1, R2C2
R3C1, R3C2


The SSIS file manager reads the file as two rows vs failing. So it reads the above as this...
R1C1, R1C2, {LF/CR}R2C1
R2C2, {LF/CR}R3C1, R3C2


This is obviously not right. You get similar issues if they send an extra column where data is just added to the last column. How do you get SSIS to fail or error out if the column count is wrong?

View 1 Replies View Related

One Column Missing From Datasets Even After Refresh?

Sep 20, 2007

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved