tsql

SQL Server query to Crystal Reports

I am trying to move reports that currently run in SQL Server to Crystal Reports. Essentially the statement I want to reproduce is: SELECT DATEPART(DD,DATE), COUNT(*) WHERE FOO = 'BAR' GROUP BY DATEPART(DD,DATE) Count the occurrence of records that match a criteria, grouped by date. I have used the Selection Expert to generate a e...

Querying by a value in XML in SQL Server 2005

Let's say I have a column in a table where the datatype is XML. I have a specific value I want query for in an xml tag that is unique(no repeating) in the XML. How do I do this? Something like: select * from MyTable where XMLColumn.TagImLookingAt.Value = @QueryValue ...

T-SQL differences between SQL Server Compact Edition and real SQL Server?

Coming from an extensive SQL Server T-SQL background, I'm having trouble finding any specific documentation describing the differences in T-SQL support between SQL CE and full blown SQL Server. What are some of the differences you've run into? Is there a side-by-side T-SQL comparison somewhere? I've seen the MSDN documentation here, b...

The old IN vs. Exists vs. Left Join (Where ___ Is or Is Not Null); Performance

I have found my self in quite a pickle. I have tables of only one column (supression or inclusion lists) that are more or less varchar(25) but the thing is I won't have time to index them before using them in the main query and, depending how inportant it is, I won't know how many rows are in each table. The base table at the heart of al...

stored procedure executes correctly from management studio, but not in production

I have a stored procedure that works correctly when I execute the stored procedure from SQL Server Management Studio. Unfortunately, it doesn't behave the the same way on the production server. On an insert statement, it is only inserting some of the values and the rest are NULL. The NULL values are coming from user defined scalar func...

What's the best T-SQL syntax to filter for an ID that has a count of X or at least X or at most X in a joined table?

What's the best way to do something like this in T-SQL? SELECT DISTINCT ID FROM Members, INNER JOIN Comments ON Members.MemberId = Comments.MemberId WHERE COUNT(Comments.CommentId) > 100 Trying to get the members who have commented more than 100 times. This is obviously invalid code but what's the best way to write this? ...

Relationship in SQL Server 2005?

My situation is i have four tables Patient, Receipt, Clinic and Laboratory. CREATE DATABASE TestHosp GO USE TestHosp GO Information about patient CREATE TABLE Patient (Id INT Not NULL PRIMARY KEY IDENTITY(1,1), FirestName VARCHAR(12) NOT NULL, LastName VARCHAR(12) NOT NULL, Birthday DATETIME) CREATE TABLE Clinic (Id INT N...

full text catalog don't update after insert new row

I have full text catalog for some db and table with column "name" that have full-text-index with automatic update. So I expect automatic update full text catalog when I insert new row to that table. But in facts it don't add new data to catalog until I disable full-text-index for that table and enabled it. Also full-text-catalog size alw...

Iterating through dates in SQL

Hello, I have a table of data that looks a bit like this: Name StartTime FinishTime Work Bob 2010-08-03 08:00:00 2010-08-03 12:00:00 4 Bob 2010-08-03 13:00:00 2010-08-03 16:00:00 3 Pete 2010-08-04 08:00:00 2010-08-04 12:00:00 4 Mark 2010-08-04 10:00:00 2010-08-04 12:00:...

What could create a syntax error if you take a SQL query and perform an UNION with itself ?

I have this strange error in SQL Server 2005 where I take a working query, add the UNION keyword below it and then copy the query again. In my opinion, this should always be working, but it is not. I get the message 'Incorrect syntax near the keyword 'union'. What could create this problem ? To be more specific, here is the complete q...

Paging in T-SQL

I am trying to implement paging using Row_Number() method in this query but with no luck. The following Query uses recursive way to get Sites for a set of categories. WITH hierarchy AS ( SELECT yt.id FROM [dbo].[TH_Categories] yt WHERE yt.ID = @topicID And CultureID = @cultureID UNION ALL SELECT yt.id FROM [dbo]...

Sql 2005 get latest entry from a group of data

Problem: I have a set of data that keeps track of history of communication between clients. So each client has their own activity history (notes, emails..etc). I need to get the latest entry from each client's activity history, where each row is the latest entry from a client's activity. So every row is unique on client id and we can ca...

Sql Server Displaying Items in specific order

I have a list of items ItemName Manufacturer TopSalesUnit Item1 A 100 Item2 A 80 Item3 A 60 Item4 B 70 Item5 B 50 Item6 B 30 Item7 C 10 Item8 C 05 I would like the re...

SQL server 2008 multiple "LIKE" problem

I'm using MS SQL 2008 and I'm facing a challenge for a few day's now. My SP parameter can contain one to three words in a string (nvarchar) and I have to return matching LIKE %phrase% records for each word in a string. Example. My parameter is: "stack overflow" Records that must be returnd: miSTACKon noOVERFLOWon STACKit poOWERFLOW...

Implement ROW_Number() to a Specific query

I have the following query that returns a set of rows based on some input parameters : WITH hierarchy AS ( SELECT yt.id FROM [dbo].[TH_Categories] yt WHERE yt.ID = @topicID And CultureID = @cultureID UNION ALL SELECT yt.id FROM [dbo].[TH_Categories] yt JOIN hierarchy h ON h.ID = yt.Pa...

One INSERT with UNIONS or multiple INSERTS?

I have a sql function that returns a table. The table is populated via 6 or so reasonably complex statements. Is it better to UNION together these statement so there is only 1 insert or are these better kept separate? Or does it make no difference whatsoever? ...

How to search content of a routine/(SP-Trigger-function)

I need to search text within a routine body (Stored Procedure, function, trigger) of all routines within a database.. How do I do that.. Thanks ...

TSQL Parent > Child > Sub-Child duplication without cursor

I am creating a SQL 2008 R2 stored procedure to duplicate a row and all it's children. It's a 3-tiered setup with a Parent, Child and Sub-Child Given the ID of the parent I need to create a duplicate. I have solved it using a fast_forward cursor. I know I can also do it with a while loop through rows but I do not believe that will be...

How do i get top n records of each category

Hi guys, I am here to get records based on categories. My table foo has fields [id, name, class]. my records can be like: 1, ram, 10 2, hari, 9 3, sita, 10 4, gita, 9 5, rita, 5 6, tina, 7 8, nita, 8 9, bita, 5 10,seta, 7 ...and more... Now i would like to get result with each record from different class.. i.e something like 1, r...

How do i call a stored procedure or stored function from sql select statement

I have created following stored user defined it gets executed successfully. CREATE FUNCTION spherical_distance1(@a float, @b float, @c float , @Lat float, @Lng float) RETURNS float AS BEGIN RETURN ( 6371 * ACOS( COS( @a/@b ) * COS( @Lat/@b ) * COS( @Lng/@b - @c/@b ) + SIN( @a/@b ) * SIN( @Lat/@b ))) END The problem i am facin...