prasan.dash83 (9/25/2012)

---

Hi Guys,

I have an MIS system where I have to run 15 queries and update 15 sheets which takes a lot of time as I need to make changes...

The code I posted does exactly that - it returns one randomly selected category row per project row. If this solution doesn't...

SELECT p.*, x.*

FROM Projects p

CROSS APPLY (

SELECT TOP 1 *

FROM ProjectCategory pc

WHERE pc.ProjectID = p.ProjectID

ORDER BY NEWID()

) x

High Plains Grifter (9/24/2012)

---

Ok, well I don't quite get why SQL Server only truncates some strings, but I have a way that fails to work and your example using CAST()...

There's a good explanation in BOL - the output datatype of REPLICATE depends...

High Plains Grifter (9/24/2012)

---

Thanks Chris and GSquared for replies and speed!

I see how those work; my concern is that using things like REPLACE(),LEN(),LEFT() on a text field of more than...

GSquared (9/24/2012)

---

Another possible:

IF OBJECT_ID(N'tempdb..#T') IS NOT NULL

DROP TABLE #T;

CREATE TABLE #T

(EntryID INT NOT NULL,

EntryTime DATETIME NOT...

-- sample data

DROP TABLE #Sample

CREATE TABLE #Sample (UserName VARCHAR(15), [Time] CHAR(8), FieldValue TEXT)

INSERT INTO #Sample (UserName, [Time], FieldValue)

SELECT 'Bob Hope', '12:00:00', 'We cannot do anything until 1 O''Clock - the...

You're welcome, and thanks for the feedback. Note that both the problem and the solution appear earlier in this thread;

ssurve...

-- use NULLIF to convert 0 to NULL

-- conversion code 112 matches the input style

-- Char(8) matches the input length

SELECT

aprvdt,

ApprovalDate = CONVERT(DATETIME2,CAST(NULLIF(aprvdt,0) AS CHAR(8)),112) -- 112 matches the...

An alternative;

UPDATE a SET [Status] = 1

OUTPUT inserted.Id, inserted.EntityID, inserted.EntityType, inserted.EntityOperation

FROM (SELECT TOP (1) Id, EntityID, EntityType, EntityOperation, [Status]

FROM ATH_SyncEntities a

WHERE [Status] = 0

ORDER BY EntityType,...

S_Kumar_S (9/24/2012)

---

Hi

Here is sample script:

create table a(id int, nm varchar(200))

insert into a

select 1,'a'

insert into a

select 1,NULL

select * from a where nm not in('a')

I logically expect NULL records to be returned...

Having made some sense of the mess, you can play with different ways of getting the same result;

-- note how 'shop' and 'site' can be distinguished either in the output...

There are several. Putting all those many many correlated subqueries...

a4apple (9/21/2012)

---

Hello Everyone,

I have a proc, that is used as a job previously that pulls lot of data. From the past 2 days, it is getting failed due to the...