Jeff Moden (5/9/2012)

---

BWAAA-HAAA!!! This will do it! :-P:-D Makes a "great" interview question. :sick:

WITH F(x) AS (SELECT TOP (CHECKSUM(66-ASCII('A'))) '/'+CONVERT(CHAR(8),GETDATE(),CAST(EXP(0) AS INT)) FROM sys.objects)

SELECT STUFF(REPLACE(LEFT(x,POWER(73,1/2)*POWER(25,1/2.0)),'/0','/')

...

I have a rather strange alternative for you here. I say strange because the query plan looks ridiculously more complicated than either the one produced by Lynn's or by...

If you don't want duplicates in ColdCoffee's suggestion:

-- Replace:

SELECT *

-- With:

SELECT DISTINCT Ac_no, Code_list

Or, another alternative with a cheaper execution plan cost would be this (also eliminates duplicates):

SELECT *

FROM @Account...

The datetime datatype stores the data differently that the way you think it is being displayed. That's controlled by a SQL Server default setting.

To "store" in US format as...

ColdCoffee is right. You shouldn't burden your helpers with having to set up your DDL and sample data for you. Today I was feeling generous.

Can do with PIVOT...

I guess if you want to run the query on SQL Server and you are content to return a character string to your MS Access application, you can do something...

Three other options are:

SELECT CountofFailure = SUM(CAST(void AS INT))

,CountofSuccess = SUM(CAST(~void AS INT))

FROM test

SELECT CountofFailure = SUM(0 + void)

,CountofSuccess = SUM(1 - void)

FROM test

SELECT CountofFailure =...

Actually, I have a better idea. If you need to worry about ClientIDs that have accounts but no purchases (the above query won't work), use this.

;WITH CTE (ClientID, AccountNumber,...

I would do this with a correlated subquery, thusly:

SELECT ClientID

,(SELECT COUNT(AccountNumber)

FROM #ClientAccounts ca

WHERE ca.ClientID = cp.ClientID

GROUP BY ClientID) AS NumAccounts

,NumPurchases, AmtPurchases

FROM (

SELECT ClientID, COUNT(ClientID) AS NumPurchases,...

ChrisM@Work (5/8/2012)

---

Interpreting the results from this isn't straightforward, at least not to me. I prefer the output formatted like this:

SELECT Strings, x.Goodchars, y.Badchars

FROM #t

CROSS APPLY (

SELECT SUBSTRING(Strings,n,1)

FROM (SELECT TOP...

Is this the result you are looking for:

DECLARE @l TABLE (ID INT, [Name] VARCHAR(30), Location VARCHAR(100))

DECLARE @d CHAR(1)

SET @d = ','-- Delimiter

INSERT INTO @l (ID, [Name], Location)

SELECT 1, 'Ronaldo', 'Country:Spain,...

My humblest apologies sir!

Seems I made several mistakes on this thread this day. Clearly the Force was not with me.

Probably should stick to playing with my cats.

Seemingly I have been challenged here.

Rising to the occasion, I can take Jeff's wonderful articles on generating random data and concoct an approach of my own to generate random gaps....

Jeff Moden (4/30/2012)

---

Samrat Bhatnagar (4/28/2012)

---

These two part series were really useful. Thanks.

Any suggestions on how to generate test data for following scenarios:

1. Two tables linked using PK-FK relationship e.g. Product...

Actual execution plan costs.

Before you say again that the actual execution plan...