SQL Server 2012 :: Create A New Column By Dividing 2 Columns In The Query?
Mar 12, 2015
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...
I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?
Example--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float)) FROM [dbo].[LaborCosts] WHERE ppty = 'ga' AND PL = 'allctd ktchn expns' AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A) 2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B) 2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd cross join INFORMATION_SCHEMA.COLUMNS ic where sd.name = 'ODS1stage' order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.
Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.
The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:
Line_1_Address 101 E Commerce ST 120 E Commerce ST 2 Po Box 301 W. Bel Air Ave West Main Street, PO Box 1388
I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.
[Number] [varchar](15) NOT NULL [Person_ID] [varchar](12) NULL
Here is the query we are doing: Select Number,Person_ID From TableName where LP='ABC123'
The result I get back is the following: Number:1 Person_ID:13864
The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.
When we do this query on our copy we get back Number:1 Person_ID:
Which is exactly what we should see as the result.
Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now SkillTypeID column has data 1 or 2 .......in here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID SKILL TYPE 21 222 123 424 1IN SHORT: I want to count the number of particular skill types and display them against their respective course id. You need to display one column data of database into two columns as output. Hope you people will help me in this regard.Thanks in advanceRameshRaj
I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.
For (hypothetical) example:
There is an existing table with following info in three columns: userid record recordtag 1 joe 1 1 j 2 1 jr 3 2 bob 1 2 a 2 2 sr 3 where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)
I need to query these records for a report so it the output is:
userID firstname middleinitial suffix 1 joe j jr 2 bob a sr
What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test (esn nvarchar(35), dateaudit datetime, opid int) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5) insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.