SQL Server 2012 :: Eliminating Quotations From Columns When Running A Query?

Mar 10, 2014

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?

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Running Total On 2 Columns

Jul 25, 2014

Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.

create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Query With Multiple Columns - How To Get Next Value One After Another

Aug 5, 2015

I want to know if it is possible to do the following;

I have patients that may have been transferred to different locations(see below)

location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02

i know i can do a min max to get my first and last values. I want to label the columns something like

1st location, 2nd location, 3rd location, 4th location, discharge location.

there could be 1 location or 20.

is there a way to do this?

i can do a temporary table and then an update query to add the values to those columns.

just not sure how to get the next value and then the next etc.

View 9 Replies View Related

SQL Server 2012 :: Pivot Rows And Columns In The Same Query?

Mar 26, 2015

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.

View 2 Replies View Related

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

How can I do this?

View 1 Replies View Related

SQL Server 2012 :: Query All Columns In A Database That Have The Data Type As Integer?

Feb 20, 2014

Is there a way to query all the columns in a database that have the data type as Integer.

View 9 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

Eliminating Records In A Query

Sep 29, 2014

Need to eliminate certain records from my query. The below is a simple query to illustrate my problem

My Query

Select RequestNo,Event_type from Event_log where Event_type in (10,20)

Data
RequestNo Event_type
123456 10
123457 10
123457 20
123458 10
123459 10
123459 20

This above query returns all requests that meets atleast one criteria. How do i edit my query such that i get requests that meet both criteria and the result set looks like below

Data

RequestNo Event_type
123457 10
123457 20
123459 10
123459 20

View 2 Replies View Related

Help Eliminating Duplcate Rows In My Query

Jul 13, 2007

Hello,

I'm trying to eliminate the duplicate 'URL' rows in the query:


SELECT
ni.[Id],
ni.[Abstract],
ni.[MostPopular],
ni.[URL]
FROM dbo.[NewsCategory] nc WITH (READUNCOMMITTED)
INNER JOIN dbo.[NewsItem] ni WITH (READUNCOMMITTED)
ON nc.[Id] = ni.NewsCategoryId
WHERE
--nc.[ProviderId] = @ProviderId
--AND
ni.[URL] in (
select DISTINCT URL
from dbo.NewsItem
where mostpopular = 1
-- OR mostemailed = 1
)
ORDER BY ni.[DateStamp] DESC



If you look at this line in the query :

select DISTINCT URL
from dbo.NewsItem
where mostpopular = 1


IF i run this query alone it will return 8 unique rows. I expect that the SELECT IN statemnet would help return a distinct set but it doesn't. This entire query returns like 20 rows with duplicate rows.

The reason why I can't do a distinct in the first set of columns is because the column ni.[Abstract] is TEXT and it says that data type is NOT COMPARABLE.

Thanks so much.

View 5 Replies View Related

SQL 2012 :: Obtain Two Columns In A Query?

Aug 19, 2014

i want to obtain two columns in a query but i don´t know how to.ex.

col1 col2
1 A
1 B
2 A
1 C

I need something like this.

col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y

View 6 Replies View Related

SQL 2012 :: Query Returns 13864 On A Varchar Columns

Mar 24, 2015

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?

View 2 Replies View Related

SQL Server 2012 :: Running Total With Thresholds?

Mar 18, 2015

I have a data table in below format and the need the desired output in 2nd table format

TABLE1

RANKBOOLEANREVENUE
1TRUE100
2FALSE150
3FALSE200
4FALSE250
5FALSE300

[code]....

Desired Output to be:

RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150

[code]....

View 3 Replies View Related

SQL Server 2012 :: Running Totals With OVER Clause?

May 28, 2015

A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.

How does the new OVER clause compare in terms of performance ?

DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl

View 9 Replies View Related

SQL Server 2014 :: Eliminating Distinct SORT Operation

Jun 19, 2015

I have an UPDATE statement that joins two table by SendId. One table, I'll call it T1, has a clustered index on SendId ASC. The other table I will call T2 also has a clustered index on SendID ASC. All the columns from T2 are used to update T1. The execution plan shows a Clustered index scan on T2 and a Clustered Index Seek on T1 going into a Nested Loops inner join. Immediately following is a Distinct Sort that is done on SendId ASC. Why the Distinct SORT if the tables are already ordered by SendID?

View 8 Replies View Related

SQL Server 2012 :: Reset Running Total To X Amount

Dec 22, 2014

I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.

I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.

View 4 Replies View Related

SQL Server 2012 :: Running Totals For Previous X Days?

May 13, 2015

get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.

DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL

[code]....

View 9 Replies View Related

SQL Server 2012 :: New Column Calculation Based On Running Difference

Aug 15, 2014

New column calculation

CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,

[Code] .....

---OUTPUT
--=======

INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: Running Sum With Reset Option And BULK Update

Jul 22, 2015

I have come up with an issue where I want to update data in a table using bulk/SET update to get the result shown in below code with output in column titled "Arrear Amt".

Please use this test data.

CREATE TABLE ##vOD_Calc
(
Seq_No INT ,
Contract_id INT ,
Rental_id INT ,
Actual_OD INT ,
Logic_OD INT ,
Due_dte DATETIME ,

[Code] .....

Logic required is that once the sum of column [ArrearAmt] of current row and all previous rows becomes greater than $100 then column [ChArrrearAmt] should show that summed up value and in else case the column [ChArrrearAmt] should show the same value as that of column [ArrearAmt].

Once the column [ChArrrearAmt] reaches the threshold of $100 then the same cycle should start again i.e. in above example rental#1 had $37.17 < $100 then rental#1 + rental#2 is also < $100 and at rental#3 sum of rental#1, rental#2 and rental#3 becomes $111.51 which is greater than $100 so its updated in column [CHArrrearAmt]. The same cycle start overs from rental#4 onwards however the summation of [ArrearAmt] will now begin after rental#4 onwards and not from the starting.

Below is the loop based SQL script which handles the above situation, however in BULK its a total deterioration of performance if thousands of rows are to be processed i.e. with a contract having multiple rentals.

The case here is that I have to use the result of previously updated column value of [ChArrrearAmt] to take decision for the next row, however with BULK update since the row is not yet updated with latest amount therefore the decision on next row is also giving wrong result.

This is the code with which I have achieved to update the column 'chArrear Amount', however its a loop based solution and performance killer.

INSERT INTO ##vOD_Calc_loop
( Rows_count ,
contract_id
)
SELECT COUNT(*) ,
T.Contract_id
FROM ##vOD_Calc T
GROUP BY T.Contract_id

[Code] ....

View 4 Replies View Related

Quotations In This Sql Statement

Oct 11, 2007

I'm having trouble getting the quotations right in this sql statement because of the single quotes in the displayname. It keeps breaking my application. How would you use quotes to get this to work?
 
Select description from lawschools_tbl where displayname='Certificat, L'Institut d'Études Politiques/'

View 2 Replies View Related

SQL Server 2012 :: Stored Procedure Not Running To Completion And Not Returning Results Set

May 27, 2014

I have stored procedure

ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date

--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)

SELECT a whole bunch of fields from the joined tables and CTEs...When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes ).When I call the stored procedure with the ExEC

DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value

It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some 2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.

View 3 Replies View Related

SQL Server 2012 :: Producing Running Total Column In Select Clause

Jul 27, 2014

I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:

EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek

101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26

What I have tried so far:

Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a

I got this to work one time before, but now I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 2 Replies View Related

SQL Server 2012 :: Creating Running Total Based On Single (SUM) Opening Balance

Jun 7, 2015

I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?

SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU

[Code] ....

Please see attachment to view output.

View 7 Replies View Related

SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update

Jul 20, 2015

I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.

If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.

If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.

View 2 Replies View Related

Sql Statement With Multiple Single Quotations

Apr 19, 2007

>Hi,
>
>Thanks you for quick answer but we still the same problem special with names like the following exmple:

DECLARE @L_SQLCOMM VARCHAR(8000)

SET @L_SQLCOMM = 'SELECT * FROM AMASTER where ACCTNAME = (ala'a)'

EXEC(@L_SQLCOMM

"ala'a " is Arabic name in English characters.

Regards

View 3 Replies View Related

Removing Quotations From Cvs Export In SSRS

Nov 19, 2006



I am trying to export a report from sql server reporting services in a csv format, but I do not want the text qualified with quotations. Is there any way to do this?



View 4 Replies View Related

SQL Server 2012 :: Display Columns With No Data

Dec 12, 2013

Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.

Table schematics

di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

SQL Server 2012 :: Rows To Columns Conversion

Aug 28, 2014

I would like to reach out in converting rows to cols.

Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate

I have this query. the values of each are under status.

SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status

FROM

((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Encrypted Records In All Columns

Nov 6, 2014

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?

View 1 Replies View Related

SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)

View 2 Replies View Related

SQL Server 2012 :: Splitting Data Between Two Columns

Dec 15, 2014

Here's the resultset:

Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2

Expected result:

P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A

What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).

View 3 Replies View Related

SQL Server 2012 :: Adding New Columns To A Table

Apr 28, 2015

I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.

However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?

Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, why one location is better than another location?

View 3 Replies View Related

SQL Server 2012 :: Primary Key And Identity Columns

May 23, 2015

Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.

View 9 Replies View Related







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