SQL_Enthusiast (5/7/2013)

---

I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLE

I need to know how to select everything LEFT of the ~...

Abu Dina (5/7/2013)

---

Morning all,

Could someone help me generate the following grouping please? Sample data plus expected output below:

Input Table: SiteMatches

Expected Output: SiteMatches_Output

CREATE TABLE dbo.SiteMatches (RetainedRID BIGINT, DroppedRidd BIGINT, Country VARCHAR(100))

INSERT...

Try to run queries like this:

select * from FK_Table F

WHERE NOT EXISTS (

select * from PK_Table P

where P.PK_Column1 = F.FK_Column1 and P.PK_Column2 = F.FK_Column2

It should give you the entries (so...

Jeff Moden (5/6/2013)

---

But, the OP had this...

select sum(CONVERT(float, ft.amount))

I could be wrong but if the precision goes past 15 digits, won't you get the same kind of rounding "errors"...

Yes, it is correct.

In fact, "RETURN @pSTN " was...

Hi niladri.primalink,

Using correlated subqueries (especially so many of them) is as bad as having an open bottle of rum on the front seat of your car or carrying a gun...

Do you actually mean "the last record"?

If you're after the current address, than it must be the...

Can you define "first record"?

And what do those duplicates mean - same pupil lives in multiple addresses...

sqlfriends (5/5/2013)

---

The above query runs very slow, i think the reason is in the order by there is the case statement,

can anyone help make this query more efficient, but still...

niladri.primalink,

this one does not look right:

(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status

TOP 1 with no ORDER BY means "random".

If there are 2 or more statuses for any...

Jeff Moden (5/3/2013)

---

My biggest concern is the conversion to FLOAT to do the sum. FLOAT only has a precision of 15 significant digits. If you go any higher...

If 850 is the default collation for the datanase then the...

SELECT E.empnum, RTRIM(C.lname) + ', ' + RTRIM(C.fname) AS Name,

RTRIM(SUBSTRING(C.dept,1,3)) AS Office,

...

You may use COLLATE DATABASE_DEFAULT for columns coming from other databases.

Unless some tables in the database have not DB default collation.

Same for temp tables:

CREATE TABLE ##TS_ACCT (

[TS_ID] [nvarchar](100) COLLATE DATABASE_DEFAULT...

I recon Lynn's code can be simplified.

Since both CTE's use the same grouping they may be merged in one:

with cte as (

select

Name,

...