Query help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-02-05 : 11:31:44

Given 2 tables below and how can I get the results showing below. The rules are described below.
Thank you for your help in advance.

IF OBJECT_ID('Tempdb.dbo.#Client', 'u') IS NOT NULL
DROP TABLE #Client
GO

CREATE TABLE #Client
(
ClientId INT NULL,
ClientName VARCHAR(20) NULL
)
GO

IF OBJECT_ID('Tempdb.dbo.#Account', 'u') IS NOT NULL
DROP TABLE #Account
GO
CREATE TABLE #Account
(
ClientId INT NULL,
AccountId INT NULL,
AccountValue INT NULL,
Insertdt DATETIME NULL
)
GO

INSERT INTO #Client VALUES(1, 'Anil')
INSERT INTO #Client VALUES(2, 'Robin')
INSERT INTO #Client VALUES(3, 'Kristine')
INSERT INTO #Client VALUES(4, 'John')
INSERT INTO #Client VALUES(5, 'Lisa')
INSERT INTO #Client VALUES(6, 'Lam')
GO

-- DELETE #Account;
INSERT INTO #Account VALUES(1,1,100, '01/12/2008')
INSERT INTO #Account VALUES(2,1,200, '04/03/2009')
INSERT INTO #Account VALUES(2,1,201, '04/26/2009')
INSERT INTO #Account VALUES(2,1,202, '06/12/2009')
INSERT INTO #Account VALUES(3,1,300, GETDATE())
INSERT INTO #Account VALUES(4,2,100, NULL)
INSERT INTO #Account VALUES(5,2,450, '10/08/2009')
INSERT INTO #Account VALUES(5,2,400, NULL)
INSERT INTO #Account VALUES(6,2,500, NULL)
INSERT INTO #Account VALUES(6,2,550, '11/19/2009')
GO

SELECT *
FROM #Client;
go

ClientId ClientName
----------- --------------------
1 Anil
2 Robin
3 Kristine
4 John
5 Lisa
6 Lam

SELECT *
FROM #Account;
go

ClientId AccountId AccountValue Insertdt
----------- ----------- ------------ -----------------------
1 1 100 2008-01-12 00:00:00.000 * return

2 1 200 2009-04-03 00:00:00.000 *
2 1 201 2009-04-26 00:00:00.000
2 1 202 2009-06-12 00:00:00.000

3 1 300 2010-02-04 21:38:05.923 *

4 2 100 NULL *

5 2 450 2009-10-08 00:00:00.000 *
5 2 400 NULL

6 2 500 NULL
6 2 550 2009-11-19 00:00:00.000 *

--testing...

SELECT t.ClientId, ClientName, AccountValue, Insertdt
FROM ( SELECT a.ClientId
,a.ClientName
,b.AccountValue
,b.AccountId
,ISNULL(b.Insertdt, '') AS 'Insertdt'
,ROW_NUMBER() OVER (PARTITION BY b.ClientId ORDER BY COALESCE(b.Insertdt, '') ASC) AS rk
FROM #Client AS a
JOIN #Account AS b
ON a.ClientId = b.ClientId ) AS t
WHERE rk = 1
ORDER BY t.ClientId ASC;
GO

-- Rules: return the Min insertdt group by ClientId.
ex: Client_id 5 and 6 return insertdt IS NOT NULL.

-- Looks like I am stuck with Client 5 and 6 because insertdt is null and I want
-- to get the records return with NOT NULL.

-- How can I get the result below.
ClientId AccountId AccountValue Insertdt
----------- ----------- ------------ -----------------------
1 1 100 2008-01-12 00:00:00.000 -- * return
2 1 200 2009-04-03 00:00:00.000 *
3 1 300 2010-02-04 21:38:05.923 *
4 2 100 NULL *

5 2 450 2009-10-08 00:00:00.000 *
6 2 550 2009-11-19 00:00:00.000 *

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:38:06

seems like this:


SELECT c.ClientId,
a.AccountId,
a.AccountValue,
a.Insertdt
FROM #Client c
JOIN #Account a
ON a.ClientId=c.ClientId
JOIN (SELECT ClientId,MIN(Insertdt) AS Start
      FROM #Account
      GROUP BY ClientId) a1
ON a1.ClientId=a.ClientId
AND a1.Start = a.Insertdt

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-02-05 : 11:56:52

Thanks but looks like one row is missing because Insertdt is null.

ClientId AccountId AccountValue Insertdt
----------- ----------- ------------ -----------------------
4 2 100 NULL *

SELECT c.ClientId,
a.AccountId,
a.AccountValue,
a.Insertdt
FROM #Client c
JOIN #Account a
ON a.ClientId=c.ClientId
JOIN (SELECT ClientId,MIN(Insertdt) AS Start
FROM #Account
GROUP BY ClientId) a1
ON a1.ClientId=a.ClientId
AND a1.Start = a.Insertdt
go

ClientId AccountId AccountValue Insertdt
----------- ----------- ------------ -----------------------
1 1 100 2008-01-12 00:00:00.000
2 1 200 2009-04-03 00:00:00.000
3 1 300 2010-02-05 08:21:16.907
5 2 450 2009-10-08 00:00:00.000
6 2 550 2009-11-19 00:00:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:01:43

if you're considering NULL also then why you need to return

6 2 550 2009-11-19 00:00:00.000 *
for ClientId 6 rather than
6 2 500 NULL

as it is the one that comes first
can you explain that?

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-02-05 : 12:12:41

This is just the test tables because the queries I have is JOINs on other 6 complex tables. The rule is if 2 or more rows with the same clientid than return the MIN insertdt. For the ClientId = 6, return insertdt which is NOT NULL. However, the clientid = 4 is has only 1 record and insertdt is null that also tricky one.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 12:20:03

[code]SELECT c.ClientId,
a.AccountId,
a.AccountValue,
a.Insertdt
FROM #Client c
JOIN #Account a
ON a.ClientId=c.ClientId
JOIN (SELECT ClientId,MIN(ISNULL(Insertdt,'9999-12-31 23:59:59.997')) AS Start
FROM #Account
GROUP BY ClientId) a1
ON a1.ClientId=a.ClientId
AND a1.Start =ISNULL( a.Insertdt, '9999-12-31 23:59:59.997')
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources