Remove trailing zeros from decimal in SQL Server
I have a column DECIMAL(9,6) i.e. it supports values like 999,123456. But when I insert data like 123,4567 it becomes 123,456700 How to remove those zeros? ...
tsql
Update String Columns without Using Single Quotations - General Question
UPDATE CustomerPhone SET PhoneTypeID = 7, PhoneNumber = 999-444 WHERE CustomerID = 500 AND PhoneNumber = 9-1-1; PhoneNumber is of type varchar(20) whereas PhoneTypeID and CustomerID are of type int. I'm running the above statement in SQL Server, it works fine. I wonder how come it works? I thought any string value has to be put betwe...
How do I accurately handle a batch separator for SQL from C#
For Data Explorer I would like to add support for a Batch separator. So for example if users type in: select 'GO' go select 1 as go Go select 100 I would like to return the three result sets. Its clear that I need some sort of parser here, my hope is that this is a solved problem and I can just plug it in. (writing a full T-S...
SQL Server combining 2 rows into 1 from the same table
Hi, I have a table with an JobID (PK), EmployeeID (FK), StartDate, EndDate containing data such as: 1, 10, '01-Jan-2010 08:00:00', '01-Jan-2010 08:30:00' 2, 10, '01-Jan-2010 08:50:00', '01-Jan-2010 09:05:00' 3, 10, '02-Feb-2010 10:00:00', '02-Feb-2010 10:30:00' I want to return a record for each EndDate for a Job and then the same emp...
need help in aggregate select
Hi, i have a problem with selecting some values from my DB. DB is in design stages so i can redesign it a bit of needed. You can see the Diagram on this image Basically what i want to select is select c.campaignID, ct.campaignTypeName, c.campaignName, c.campaignDailyBudget, c.campaignTotalBudget, c.campaignCPC, c.date, cs.campaignSta...
Dynamically call a stored procedure from another stored procedure
I want to be able to pass in the name of a stored procedure as a string into another stored procedure and have it called with dynamic parameters. I'm getting an error though. Specifically I've tried: create procedure test @var1 varchar(255), @var2 varchar(255) as select 1 create procedure call_it @proc_name varchar(255) as ...
SQL: How to Return One DB Row from Two That Have The Same Values In Opposite Columns Using the MAX Function?
I have three columns in a table - ID, Column1, Column2 - with this example data: ID Column1 Column2 ---------------------- 1 1 2 2 2 1 3 4 3 4 3 4 Since, in the first two rows, Column1 and Column2 have the same values (but in different columns), I want my MAX query to return an ID of 2. Same thi...
Get top 'n' records by report_id
I have a simple view in my MSSQL database. It consists of the following fields: report_id INT ym VARCHAR -- YYYY-MM keyword VARCHAR(MAX) visits INT I can easily get the top 10 keyword hits with the following query: SELECT TOP 10 * FROM top_keywords WHERE ym BETWEEN '2010-05' AND '2010-05' ORDER BY visits DESC Now where it gets tric...
Help Converting T-SQL Query to LINQ Query
I am new to LINQ, and so am struggle over some queries that I'm sure are pretty simple. In any case, I have been hiting my head against this for a while, but I'm stumped. Can anyone here help me convert this T-SQL query into a LINQ query? Once I see how it is done, I'm sure I'll have some question about the syntax: SELECT BlogTitle FRO...
Exclude records matching subquery
There is probably an obvious answer to this question, but I am having one heck of a time getting anywhere with it. Consider the query: SELECT * FROM reports AS r JOIN reportvalues AS rv ON rv.report_id = r.report_id JOIN metrics AS m ON m.metric_id = rv.metric_id WHERE r.report_id NOT IN( SELECT DISTINCT report_id FROM exclude...
Question about Transact SQL syntax
Hi guys, The following code works like a charm: BEGIN TRY BEGIN TRANSACTION COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity int; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(); ...
SELECT SQL Variable - should i avoid using this syntax and always use SET?
Hi All, This may look like a duplicate to here, but it's not. I am trying to get a best practice, not a technical answer (which i already (think) i know). New to SQL Server and trying to form good habits. I found a great explanation of the functional differences between SET @var = and SELECT @var = here: http://vyaskn.tripod.com/diff...
SQL question - Cursor or not?
Hi, I have a query which returns 2+ rows. In those results is a column which we can call columnX for now. Lets look at those example results: columnX 100 86 85 70 null null I get 6 rows for example, some of them are null, some of them are not null. Now I want to go through those results and stop as soon as I find a row which is <> null...
Find the period of over speed ?
Just something interesting come in my mind. Assume that we have a table (in SQL Server) like this: Location Velocity Time for example: Location Velocity Time 1 40 1:20 2 35 2:00 3 45 2:05 4 50 2:30 5 60 2:45 6 48 2...
Hi can anyone try how to solve the query for this table structure???
Employee table structure first_name varchar2(100) last_name varchar2(100) hire_date date show all the employee who hired on the day of the week on which highest number of employees were hired? ...
Better way to summarize data about stop times?
This question is close to this: http://stackoverflow.com/questions/2947963/find-the-period-of-over-speed Here's my table: Longtitude Latitude Velocity Time 102 401 40 2010-06-01 10:22:34.000 103 403 50 2010-06-01 10:40:00.000 104 405 0 2010-06-01...
T-Sql Multiple Criteria On Where Clause
Scenario I'm using SQL Server 2005 I have a T-Sql query that has multiple criteria to search on the WHERE clause. I'm certain that I'm doing this in a very inefficient and long way round. How can I ensure that I don't have to manually type out all the criteria? Instead I want to pass in the criteria via a SELECT * clause. Current Query...
Getting dates based on week number
Hello. I have some rows in my database that contains some dates. I need to select all dates based on a weeknumber, how is this possible? ...
What does "foo" mean in this SQL Server Query ?
for eg... SELECT * FROM ( SELECT RANK() OVER (ORDER BY stud_mark DESC) AS ranking, stud_id, stud_name, stud_mark FROM tbl_student ) AS foo WHERE ranking = 10 Here foo is present...actually what it does ?.. ...
Why does ROW_NUMBER OVER (ORDER BY column) return a different result order than just ORDER BY column?
I'm on SQL Server 2008, using NHibernate as persistence layer (although this problem is purely SQL, I believe). I've boiled down my problem to the following SQL statement: SELECT TOP 2 this_.Id as Id36_0_, this_.Name as Name36_0_, ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder FROM Campsites this_ ORDER BY this...