SQL Server 2012 :: Check Records Which Have Negative Capacity Value
Jul 17, 2015
I have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords UUUDUUUDUUUD1230123012301175 ------------0000000000001163 UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords UUDUUUDUUUUU-----------07 UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.
Something along the lines of this condition:
(select count(ClovekAutoID) from TableOfClovekNames tCN where JeHlavni = 1 group by ClovekAutoID having COUNT(JeHlavni ) > 1) = 1In fact,
I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Is there anyway to check if server is having disk latency or IO issues?Found below in SQL error log
Date10/1/2014 8:28:58 AM LogSQL Server (Current - 10/1/2014 12:00:00 AM)
Sourcespid10s
Message SQL Server has encountered 8500 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Fin.mdf] in database [Fin] (5). The OS file handle is 0x0000000000001368. The offset of the latest long I/O is: 0x0001104a7da000
I have a job I want to run everyday but before this job starts and I want to check and see if another job has completed before I start this job. i would like to do this in the job steps in SSMS. step 1 is job 'xxxxxxx' running if no go to step 2 if yes exit
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?
I am looking for the best way to check to see if any columns are still NULL in a record. I have a form that gets filled out by users and the values entered into TableA. There are 6 columns in the table, 5 are responses and column6 indicates if the record is complete. So I want a way to see if all of the first 5 columns are NOT NULL and, if so, mark column6 with a 1.
I am thinking this would be a good thing for a trigger on INSERT or UPDATE to check to see if the first 5 columns are filled in and then mark the record as complete.
I know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.
Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like
"You email address is XXX" YYYYY not found "You email address is ZZZZ"
I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]
/****** Select ALL if where an email address is present in the list ******/ SELECT EmailAddressID,EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress] WHERE EmailAddress IN ( 'ken0@adventure-works.com', --1 'terri0@adventure-works.com', --2
[Code] ....
-- Test to see if a single email address is present
IF EXISTS ( SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress] WHERE EmailAddress IN ('25rob0@adventure-works.com') ) BEGIN SELECT 'Email address is presnt'
[Code] ....
When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.
Hi, any1 know how many records can be stored in a table in sql server? is there any way to maximise it? will table capacity (number of records) vary due to our table stucture or database structure? is there any documentation bout tis? Cheerslife's Ng
I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?
**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.
example table a id country area 1 China Asia 2 Thailand Asia 3 Jamaica SouthAmerica 4 Japan Asia
example table b id country area 1 China Asia 2 Thailand SouthEastAsia 3 Jamaica SouthAmerica 5 USA America
Expected output id country area Match 1 China Asia Y 2 Thailand SouthEastAsia Y 3 Jamaica SouthAmerica Y 4 Japan Asia N
I have been working on SQL Server Capacity Planning for a few weeks now and have gathered a lot of materials, but non of thes materials contain recommended best practices on SQL Server capacity planning and also they do not contain operational guidelines.
I would be glad if anyone can recommend a website or book that contain information on SQL Server Capacity Planning Best Practices.
I am just starting to build my apps in asp.net 2.0. I have build apss in v. 1.x before using sql server 2000. However, before i make the shift, I would like to know the advantages and disadvantages of using the sql server 2005 express edition then using the sql server 2000. for example;+ How many data can it store?+ How many concurrent users it supports.+ Advantages and disadvantages of using sql server 2005 express ed.+ Any other relevant information that developers should know / beware / watch out for when using sql server 2005 express edition. Note: I am not asking about the actual SQL Server 2005 but the express edition! Regards and thanks in advance
My SQL server disk space is getting close to full capacity which is causing certain reports that we run via the SQL server to time out because I don't think there is enough space on the server.
Any tips on cleaning out a SQL server? Are there any folders that can absolutely be deleted to clear space? I know on a local computer that the %temp% folder can be cleaned out. I know when dealing with servers you do not want to make to many changes because it can cause major problems down the road.
I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.
I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data..I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.
Attempt with CTE: --With CTE ;WITH cte AS (SELECT Borrower1 = BorrowerName , Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder) , RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)
[code]...
Is there a better, cleaner way to do this? Or is the CTE the best way to go?
I have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...
Is there any software or any tool which will provide encrypted records of database?
With this query i get only the records i need, but i would like to output in this way
1 - 20 21 - 30 31 - 40
of course in the real environment the ID are not consecutive, this is just one example of data.
declare @temp table (ID int) declare @i int = 1 while(@i<1000) begin insert into @temp values (@i) set @i=@i+1 end select ID from ( select ID, row_number() over (order by ID) as rn from @temp ) q where (rn % 20=0) OR (rn % 20=1)
I have a series of records based on empid where I want to identify the empid that may have discrepancies listed. I have some empids that are listed more than once and have different DOB's. In the example I am trying to Create a DOB_ERROR column and either say yes if the DOB doesn't match the other records in the file with the same empid.
SELECT Empid, DOB, CASE WHEN DOB = DOB THEN 'No' ELSE 'Yes' END AS DOB_ERROR, City, St, Gender FROM Emp WHERE EMPID IN
I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500 W 21-30 400 W 31-40 200 A 17-20 100 H 41-50 250 H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
I'm inserting records from a table to another table, having multiple columns
SampleQuery :
insert into tableB (col1,col2....col10) select col1,col2....col10 from tableA
* while executing this query im getting error. (like varchar cant convert to numberic) * Here I have no clue in which column it is * also dont know which row causes it
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems]( [ID] [int] IDENTITY(1,1) NOT NULL, [PurchaseOrderDetailID] [int] NULL, [Qty] [int] NULL) SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
I am having three table tblTest,tblTestQuestion,tblAnswers
Each test can have multiple question and each Question can have multiple answers.
Now I am already having records in database. I wants to create clone copy of existing test except testdetails in tblTest because the test will be unique, and then insert questions and answers into their respective tables.
I was trying to create SP but stuck.
Please find below tables structure
[code=" SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblAnswer](
DECLARE @Date SET @Date = 2012 DECLARE @Year int SET @Year = (SELECT DATEPART(yyyy,@Date)) SELECT @Year AS Year --SELECT * -- FROM [Orders].[dbo].[Orders] od -- WHERE .Orderl_Date < @Date; ----WHERE DATEPART(yyyy,@Date)= @Year ----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
