I have the following view which contains this data ActivityRecId RegionRecId IsExcluded 1 null 1 2 null 1 3 1 1 3 2 1 4 1 1 5 null 0 What I would like to do is join the region table to the view above to get the fo...
When running a SQL statement or a stored procedure on a database, can you connect to an external database and pull data from there? something like: SELECT a.UserID, b.DataIWantToGet FROM mydb.Users as a, externaldb.Data as b ...
We created so many inefficient stored procedure in our application, we always postpone to make it more efficient until we have serious problem with database performance. Now, I am thinking to fix it one by one order by most often executed stored procedure. What is the best way to figure out which stored procedure is the most executed? ...
How can I release a winform application to a user that takes advantage of a local sql database. I would assume that I need to install the database during some kind of setup phase, but is this kind of thing possible? Is there a free version of tsql that can be used in this way? Mysql? ...
I am trying to print a selected value, is this possible? Example: PRINT SELECT SUM(Amount) FROM Expense ...
how do i get current system time stamp in SQL Server. ...
I have a simple table CREATE TABLE a( id int IDENTITY(1,1) NOT NULL, x varchar(50) ) I found that following query works select cast (id as varchar(3))+cast (x as varchar(3)) c from a where cast (id as varchar(3))+cast (x as varchar(3))='1a' but this does not work select cast (id as varchar(3))+cast (x as varchar(3)) c...
Dear guys I get a base 64 string from a XML file which I want to insert into my SQL Server database. Which field type have the field in my database to be? varbinary(MAX)? Do I have to convert the base 64 string to another format before inserting into my database? Best regards ...
Hi, I am trying to learn SQL on SQL Server and several functions/SPs require parameters. For example, when I enter "exec sys.fn", the intellisense brings up various functions/SPs and some have brackets which describe parameters. How do I pass the parameter in? Although I saw some examples, they were for custom SPs so I couldn't quite a...
Can someone help me with this query? SELECT p.OwnerName, SUM(ru.MonthlyRent) AS PotentinalRent, SUM( (SELECT COUNT(t.ID) * ru.MonthlyRent FROM tblTenant t WHERE t.UnitID = ru.ID) ) AS ExpectedRent FROM tblRentalUnit ru LEFT JOIN tblProperty p ON p.ID = ru.PropertyID GROUP BY p.OwnerName I'm having problems with the se...
I have a trace table in the following format. CREATE TABLE [dbo].[trace]( [trcId] [bigint] IDENTITY(1,1) NOT NULL, [trcDateTime] [datetime] NULL, [trcProgram] [nvarchar](150) NULL, [trcCode] [nvarchar](8) NULL, [trcText] [nvarchar](max) NULL, [trcXML] [nvarchar](max) NULL, [trcCorrGuid] [nvarchar](36) NULL, ...
I'm trying to make the result of one of my subqueries be used in another subquery but it doesn't work. This is the query: SELECT t.TenantName, (SELECT SUM(Amount) FROM tblTransaction WHERE Amount > 0 AND TransactionDate >= '12/01/09' AND TransactionDate <= '12/31/09' AND TenantID = t.ID ...
I have two tables - Table1: id name number ------------------ 1 x1 123 2 x2 234 ...and Table2: tbl1id title rank -------------------- 1 t1 3 1 t2 2 2 t1 3 1 t3 1 Is there a way I can join them to return result as showing max title based on min rank for given user: id name ...
I have two variables, on is called PaidThisMonth, and the other is called OwedPast. They are both results of some subqueries in SQL. How can I select the smaller of the two and return it as a value titled PaidForPast? The MIN function works on columns, not variables. ...
This is one huge monster, it's going into a SP so variables are usable: SELECT OwnerName, SUM(AmountPaid) AS Paid, SUM(AmountOwedComplete) AS Owed, SUM(AmountOwedThisMonth) AS OwedMonth, SUM(PaidForPast) AS PaidPast, SUM(PaidForPresent) AS PaidPresent, SUM((AmountPaid - PaidForPast - PaidForPresent)) AS PaidFuture, [Description] FROM (...
I have a table: Date ColumnA ColumnB 2009-12-29 "abc" "abc" 2009-12-29 "abc" "abc" 2009-12-29 "abc" "abc" 2009-12-28 "abc" "abc" 2009-12-28 "abc" "abc" 2009-12-28 "abc" "abc" ,,, ,,, I want to write a query, in Microsoft SQL, that returns all rows for the latest available date in the...
Hi I have a table with following columns and data FldID | Rev | Words 10257 2 Some text is present here 10257 3 I changed this text 10258 2 Some more text for another item 10258 3 ...
Hi I have a CTE as follows WITH details AS ( SELECT FldId ,Rev ,Words ,row_number() OVER ( PARTITION BY FldId ORDER BY Rev DESC ) AS rn FROM WorkItemLongTexts WHERE ID = 2855 ) SELECT f.ReferenceName ,d.FldId ,...
I have a SQL Var @SumScore dec(9,4) I am trying to assign the variable as follows: SET @SumScore = ( SELECT Sum( ( SELECT SUM(etjs.CalculatedScore * sc.PercentOfTotal) as CategoryScore FROM tblEventTurnJudgeScores etjs INNER JOIN tblJudgingCriteria jc ON jc.JudgingCriteriaID = et...
How to know if all the cells have the same value in some column (title changed) I want to have a bit scalar value that tells me if all the values in a column equal something: DECLARE @bit bit SELECT @bit = TRUEFORALL(Name IS NOT NULL) FROM Contact UPDATE I now realized that I actually don't need the TrueForAll, what I do need is to ...