Need help with Query - 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 2012 Forums

Transact-SQL (2012)

Need help with Query

Author

Topic

Vegeta379
Starting Member

5 Posts

Posted - 2014-12-19 : 00:59:24

I've been trying to solve this SQL Query for many hours now and I've decided to ask the pros to help me.

This is the situation. Each of these Nurses have treated these people. Every line is a service at a certain moment. Nurses can treat people more than once and people can be treat by a Nurse more than once.

Query: List the Names of the Nurses who treated People who weren't treated by any other Nurse. In other words, list the people who were treated by only one nurse.

Your help will be much appreciated :)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-12-19 : 01:10:24

please post the table structure and the query which you have tried till now

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-12-19 : 04:09:44

[code]
CREATE TABLE #Table (ID_Nurse int,Name_Nurse varchar(max),ID_Person int,Name_Person varchar(max))

INSERT INTO #Table
VALUES
(300,'Jhon',100,'Jim'),
(300,'Jhon',109,'Jeorge'),
(301,'Albert',101,'Tiffinay'),
(301,'Albert',105,'Abel'),
(302,'Brain',102,'Cassie'),
(302,'Brain',119,'Kate'),
(303,'Michel',110,'David'),
(304,'Mary',102,'Cassie'),
(304,'Mary',105,'Abel'),
(304,'Mary',118,'Joseph'),
(305,'Sarah',116,'Will'),
(305,'Sarah',118,'Joseph'),
(308,'Jack',118,'Joseph'),
(309,'Martha',103,'Ryan')

--SELECT * FROM #Table

SELECT * FROM
(
SELECT *,COUNT(*)OVER(PARTITION BY (t.Name_Person)) Cn
FROM dbo.#Table t

)a
WHERE a.Cn = 1 ORDER BY a.ID_Nurse

DROP TABLE #TABLE
[/code]

Note :Hope you post table structure next time

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Vegeta379
Starting Member

5 Posts

Posted - 2014-12-19 : 06:26:53

Murali Krishna, I cannot tell you how much you helped me! Thank you so much for everything!!!! :D

Vegeta379
Starting Member

5 Posts

Posted - 2014-12-20 : 10:56:26

I just discovered something wrong with the query when I checked it in my complete table :s The query that Murali created doesn't take into account the people who were treated only by the same nurse, more than once. It only counts the number of people who were treated by only one nurse once and doesn't consider the people who were treated by the same nurse twice or more times.

For example, if I insert the row:
(309,'Martha',103,'Ryan')

There will be 2 of these rows (two situations where Ryan treated Martha)

The query that Murali created will not show Martha, even though Martha wasn't treated by multiple Nurses, but because she was treated twice by Ryan, this query will not consider Martha :/ and it normally should.

I'm in desperate need of help!

Vegeta379
Starting Member

5 Posts

Posted - 2014-12-20 : 12:28:32

In other words, for this table:

(300,'Jhon',100,'Jim'),
(300,'Jhon',109,'Jeorge'),
(301,'Albert',101,'Tiffinay'),
(301,'Albert',105,'Abel'),
(302,'Brain',102,'Cassie'),
(302,'Brain',119,'Kate'),
(303,'Michel',110,'David'),
(304,'Mary',102,'Cassie'),
(304,'Mary',105,'Abel'),
(304,'Mary',118,'Joseph'),
(305,'Sarah',116,'Will'),
(305,'Sarah',118,'Joseph'),
(308,'Jack',118,'Joseph'),
(309,'Martha',103,'Ryan'),
(309,'Martha',103,'Ryan')

The Nurse Ryan will not appear with Murali's query. And Ryan should appear because the question is "List the Names of the Nurses who treated People who weren't treated by any other Nurse".

Please help!

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-12-21 : 23:37:32

[code]
CREATE TABLE #Table (ID_Nurse int,Name_Nurse varchar(max),ID_Person int,Name_Person varchar(max))

INSERT INTO #Table
VALUES
(300,'Jhon',100,'Jim'),
(300,'Jhon',109,'Jeorge'),
(301,'Albert',101,'Tiffinay'),
(301,'Albert',105,'Abel'),
(302,'Brain',102,'Cassie'),
(302,'Brain',119,'Kate'),
(303,'Michel',110,'David'),
(304,'Mary',102,'Cassie'),
(304,'Mary',105,'Abel'),
(304,'Mary',118,'Joseph'),
(305,'Sarah',116,'Will'),
(305,'Sarah',118,'Joseph'),
(308,'Jack',118,'Joseph'),
(309,'Martha',103,'Ryan')

--SELECT * FROM #Table

Method 1 :

SELECT * FROM
(
SELECT *,COUNT(*)OVER(PARTITION BY (t.Name_Person)) Cn
FROM
( select DISTINCT * FROM dbo.#Table )t --Changed Line
)a
WHERE a.Cn = 1 ORDER BY a.ID_Nurse

DROP TABLE #TABLE

Method 2:

SELECT DISTINCT * FROM
(
SELECT CC.*,
(SELECT COUNT(*) FROM #Table WHERE Name_Person =CC.Name_Person AND Name_Nurse <> CC.Name_Nurse
) AS CLASHES
FROM #Table AS CC
) a WHERE a.CLASHES = 0
[/code]

---------------
Murali Krishna
You live only once ..If you do it right once is enough.......

Vegeta379
Starting Member

5 Posts

Posted - 2014-12-25 : 16:21:22

Wow... just wow :D You're the best Murali! Thank you very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-12-28 : 12:11:14

SELECT ID_Person FROM dbo.Table1 GROUP BY ID_Person HAVING MIN(ID_Nurse) = MAX(ID_Nurse);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Ishan
Starting Member

1 Post

Posted - 2015-05-05 : 01:09:17

Hi,

I need a help for certain to get the result for certain table as shown below.

Codes Values
16580 171958.79
58962 156607
16580 128843.92
16580 90552
57590 80079.91
90740 78000.77
16580 49263.71
57610 47039.41
90530 40000
58962 36349.47
58962 33275.76
57590 30600
16580 29637.34
90740 29124.63
90740 28181.8
16580 27818
16580 27818
16580 27668.63
90740 27268.36

Here the codes are repeating and each has a certain value.
I need the code to be repeated only once and the value for each code should be added up as shown below.

Codes Values
16580 553560.39
57590 110679.91
57610 47039.41
58962 226232.23
90530 40000
90740 162575.56

Your hep will be much appreciated.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2015-05-05 : 02:31:12

Ishan, please start a new thread for your question.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2015-05-05 : 02:31:15

Ishan, please start a new thread for your question.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources