Comparing with Group By? - 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)

Comparing with Group By?

Author

Topic

slider
Starting Member

11 Posts

Posted - 2008-02-08 : 16:22:03

I have this table with three columns

personid int,
phase int,
drugid int

phase is year in question i.e. 2000, 2001, 2008 etc
drugid is the code for any given drug

so a person may be on some number of drugs in a given year

what i want to know is this ....... find me all people whose count of drugs as compared to preceding year is out of ordinary

For example, if someone was taking 10 drugs in year 2007 but the number of drugs he/she is on is 5 or 15 in 2008, meaning any number i can chose for the range. Idea is that i may have missed recording some drugs for some people. It would be nice to compare all the years etc etc but i am just looking to compare two years for now.

This will give me a running count for each phase but i want to find just people who seem to be out of sync with number of drugs

select personid,phase,count(drugid) as NoOfDrugs from drugs
group by personid,phase

Any help is greatly welcome.

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-08 : 16:47:14

[code]DECLARE @difference INT
SET @difference = 7

SELECT *, a.cnt-b.cnt AS Diff FROM (
SELECT personid, phase, count(*) AS NoOfDrugs from drugs
GROUP BY personid, phase )a
JOIN (
SELECT personid, phase, count(*) AS NoOfDrugs from drugs
GROUP BY personid, phase )b ON a.personid = b.personid
AND a.phase+1 = b.phase
WHERE a.NoOfDrugs-b.NoOfDrugs >= @difference OR a.NoOfDrugs-b.NoOfDrugs <= -1*@difference[/code]

slider
Starting Member

11 Posts

Posted - 2008-02-08 : 17:05:59

Thank you very Much!

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 17:36:23

Here is a solution that lets you see all differences between adjacent periods. First the setup:


Declare @table Table (Person_ID int, Phase_ID int, Drug_ID int)
Insert Into @table
Select 1, 2007, 1 Union Select 1, 2007, 2 Union Select 1, 2007, 3 Union
Select 1, 2007, 4 Union Select 1, 2007, 5 Union Select 1, 2008, 1 Union
Select 1, 2008, 2 Union Select 1, 2008, 3 Union Select 1, 2008, 4 Union
Select 1, 2008, 5 Union Select 1, 2008, 6 Union Select 1, 2008, 7 Union
Select 1, 2006, 1 Union Select 1, 2006, 2 Union Select 1, 2006, 3 Union
Select 1, 2009, 1 Union Select 1, 2009, 2 Union Select 1, 2009, 3 Union
Select 1, 2009, 4 Union Select 1, 2009, 5 Union Select 1, 2009, 6 Union
Select 1, 2009, 7 Union Select 1, 2009, 8 Union Select 1, 2009, 9 Union
Select 1, 2009, 10 Union Select 1, 2009, 11 Union Select 1, 2009, 12

Next, the solution:


Select Person_ID, Phase_ID, 
Row_Number() Over (Partition By Person_ID Order By Phase_ID) As PhaseNumber,
Count(Distinct Drug_ID) As NumberOfDrugs
Into #CountsPerTrial
From @table
Group By Person_ID, Phase_ID

Select Person_ID, Phase_ID, NumberOfDrugs,
NumberOfDrugs - IsNull((
		Select NumberOfDrugs From #CountsPerTrial CntPrev
		Where CntPrev.PhaseNumber = CntMaster.PhaseNumber - 1
), NumberOfDrugs) As DiffFromPrevious,
IsNull((
		Select NumberOfDrugs From #CountsPerTrial CntNext
		Where CntNext.PhaseNumber = CntMaster.PhaseNumber + 1
), NumberOfDrugs) - NumberOfDrugs As DiffFromNext
From #CountsPerTrial CntMaster

Drop Table #CountsPerTrial

Data:


Person_ID   Phase_ID    Drug_ID
----------- ----------- -----------
1           2006        1
1           2006        2
1           2006        3
1           2007        1
1           2007        2
1           2007        3
1           2007        4
1           2007        5
1           2008        1
1           2008        2
1           2008        3
1           2008        4
1           2008        5
1           2008        6
1           2008        7
1           2009        1
1           2009        2
1           2009        3
1           2009        4
1           2009        5
1           2009        6
1           2009        7
1           2009        8
1           2009        9
1           2009        10
1           2009        11
1           2009        12

Results:


Person_ID   Phase_ID    NumberOfDrugs DiffFromPrevious DiffFromNext
----------- ----------- ------------- ---------------- ------------
1           2006        3             0                2
1           2007        5             2                2
1           2008        7             2                5
1           2009        12            5                0

slider
Starting Member

11 Posts

Posted - 2008-02-08 : 18:04:19

This is great except i dont have SQL 2005 , could you please modify it for SQL 2000. thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 00:06:55

[code]SELECT t.Person_ID,t.Phase_ID,t.NumberOfDrugs ,
(SELECT COUNT(*) + 1 FROM
(
SELECT Person_ID,
Phase_ID,
COUNT(Drug_ID) AS NumberOfDrugs
FROM Table
GROUP BY Person_ID,Phase_ID
)t1
WHERE t1.Person_ID =t.Person_ID
AND t1.Phase_ID < t.Phase_ID
) AS RowNo
INTO #Temp
FROM
(
SELECT Person_ID,
Phase_ID,
COUNT(Drug_ID) AS NumberOfDrugs
FROM Table
GROUP BY Person_ID,Phase_ID
)t

SELECT t1.Person_ID,
t1.Phase_ID,
t1.NumberOfDrugs,
CASE WHEN t2.NumberOfDrugs IS NULL THEN 0
ELSE t1.NumberOfDrugs-t2.NumberOfDrugs
END AS DiffFromPrevious ,
CASE WHEN t3.NumberOfDrugs IS NULL THEN 0
ELSE t3.NumberOfDrugs-t1.NumberOfDrugs
END AS DiffFromNext
FROM #Temp t1
LEFT OUTER JOIN #Temp t2
ON t2.RowNo=t1.RowNo-1
LEFT OUTER JOIN #Temp t3
ON t3.RowNo=t1.RowNo+1[/code]

slider
Starting Member

11 Posts

Posted - 2008-02-10 : 15:25:51

Thanks visakh16, but your solution is VERY time consuming on a very large table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-10 : 17:25:13

If @year1 and @year2 are the two years you wish to compare, then:


select person_id
  sum(case when phase = @year1 then 1 else 0 end) as Year1Count,
  sum(case when phase = @year1 then 1 else 0 end) as Year2Count
from
  yourtable
where
  phase in (@year1,@year2)
group by 
  personID

Will return the two year counts of drugs side by side very efficiently and simply. Then, you can query the above and return anything you want by comparing the two columns.

For example, this will return all person's in which the different coutns from the two years are greater than 10:


select x.*
from  ( the above sql goes here) x
where abs(x.year1count - x.year2count) > 10

In short, avoid self-joins when a simple GROUP BY will do the trick; more here:
http://weblogs.sqlteam.com/jeffs/archive/2007/06/12/60230.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-11 : 01:34:44

Here is my solution :-
I guess this is much more simple & less time consuming.

Declare @table Table (Person_ID int, Phase_ID int, Drug_ID int)
Insert Into @table
Select 1, 2007, 1 Union Select 1, 2007, 2 Union Select 1, 2007, 3 Union
Select 1, 2007, 4 Union Select 1, 2007, 5 Union Select 1, 2008, 1 Union
Select 1, 2008, 2 Union Select 1, 2008, 3 Union Select 1, 2008, 4 Union
Select 1, 2008, 5 Union Select 1, 2008, 6 Union Select 1, 2008, 7 Union
Select 1, 2006, 1 Union Select 1, 2006, 2 Union Select 1, 2006, 3 Union
Select 1, 2009, 1 Union Select 1, 2009, 2 Union Select 1, 2009, 3 Union
Select 1, 2009, 4 Union Select 1, 2009, 5 Union Select 1, 2009, 6 Union
Select 1, 2009, 7 Union Select 1, 2009, 8 Union Select 1, 2009, 9 Union
Select 1, 2009, 10 Union Select 1, 2009, 11 Union Select 1, 2009, 12

;With Rollup(Cnt,phase_id)
as
(
select count(distinct drug_id)as Cnt,phase_id from @table group by phase_id

)
select Phase_id,cnt as NumberOfDrugs,
DiffFromPrevious =(select abs(R2.cnt-R1.cnt)from Rollup R2 where R2.phase_id=R1.phase_id-1),
DiffFromNext =(select abs(R2.cnt-R1.cnt) from Rollup R2 where R2.phase_id=R1.phase_id+1)

from Rollup R1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 01:48:13

You dont have CTE's in SQL 2000. OP was asking for a SQL 2000 compatible soln.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-11 : 01:49:27

quote:
Originally posted by visakh16

You dont have CTE's in SQL 2000. OP was asking for a SQL 2000 compatible soln.

Oops Sorry missed on that one.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-11 : 02:01:07

Here is the SQL 2000 solution.

Declare @table Table (Person_ID int, Phase_ID int, Drug_ID int)
Insert Into @table
Select 1, 2007, 1 Union Select 1, 2007, 2 Union Select 1, 2007, 3 Union
Select 1, 2007, 4 Union Select 1, 2007, 5 Union Select 1, 2008, 1 Union
Select 1, 2008, 2 Union Select 1, 2008, 3 Union Select 1, 2008, 4 Union
Select 1, 2008, 5 Union Select 1, 2008, 6 Union Select 1, 2008, 7 Union
Select 1, 2006, 1 Union Select 1, 2006, 2 Union Select 1, 2006, 3 Union
Select 1, 2009, 1 Union Select 1, 2009, 2 Union Select 1, 2009, 3 Union
Select 1, 2009, 4 Union Select 1, 2009, 5 Union Select 1, 2009, 6 Union
Select 1, 2009, 7 Union Select 1, 2009, 8 Union Select 1, 2009, 9 Union
Select 1, 2009, 10 Union Select 1, 2009, 11 Union Select 1, 2009, 12

Declare @Rollup Table (Cnt int,phase_id int)
insert into @Rollup select count(distinct drug_id)as Cnt,phase_id from @table group by phase_id

select Phase_id,cnt as NumberOfDrugs,
DiffFromPrevious =(select abs(R2.cnt-R1.cnt)from @Rollup R2 where R2.phase_id=R1.phase_id-1),
DiffFromNext =(select R2.cnt-R1.cnt from @Rollup R2 where R2.phase_id=R1.phase_id+1)
from @Rollup R1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-11 : 07:25:23

quote:
Originally posted by ayamas

Here is my solution :-
I guess this is much more simple & less time consuming.
...

Unfortunately, it is exactly the opposite; it's more complicated and less efficient than what I provided...

Also, if you want to convert a CTE from SQL 2005 to 2000-compatible code, the equivalent is not a temp table or a table variable, but a derived table. (Unless you are using recursion)

- Jeff
http://weblogs.sqlteam.com/JeffS

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-11 : 09:02:36

For 2000, replace:


Select Person_ID, Phase_ID, 
Row_Number() Over (Partition By Person_ID Order By Phase_ID) As PhaseNumber,
Count(Distinct Drug_ID) As NumberOfDrugs
Into #CountsPerTrial
From @table
Group By Person_ID, Phase_ID

With:


Create Table #CountsPerTrial (Person_ID int, Phase_ID int, PhaseNumber int identity(1,1), NumberOfDrugs int)
Insert Into #CountsPerTrial
Select Person_ID, Phase_ID, 
Count(Distinct Drug_ID) As NumberOfDrugs
From @table
Group By Person_ID, Phase_ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:15:34

You should pay attention to Jeff.

This look like a "rolling average" but is a "rolling difference" instead.
Using Jeff's suggestion, this is an alternative way to do this with expected output

-- Prepare sample data
DECLARE	@Sample TABLE (PersonID INT, PhaseID SMALLINT, DrugID INT)

INSERT	@Sample
SELECT	1, 2007, 1 UNION ALL
SELECT	1, 2007, 2 UNION ALL
SELECT	1, 2007, 3 UNION ALL
SELECT	1, 2007, 4 UNION ALL
SELECT	1, 2007, 5 UNION ALL
SELECT	1, 2008, 1 UNION ALL
SELECT	1, 2008, 2 UNION ALL
SELECT	1, 2008, 3 UNION ALL
SELECT	1, 2008, 4 UNION ALL
SELECT	1, 2008, 5 UNION ALL
SELECT	1, 2008, 6 UNION ALL
SELECT	1, 2008, 7 UNION ALL
SELECT	1, 2006, 1 UNION ALL
SELECT	1, 2006, 2 UNION ALL
SELECT	1, 2006, 3 UNION ALL
SELECT	1, 2009, 1 UNION ALL
SELECT	1, 2009, 2 UNION ALL
SELECT	1, 2009, 3 UNION ALL
SELECT	1, 2009, 4 UNION ALL
SELECT	1, 2009, 5 UNION ALL
SELECT	1, 2009, 6 UNION ALL
SELECT	1, 2009, 7 UNION ALL
SELECT	1, 2009, 8 UNION ALL
SELECT	1, 2009, 9 UNION ALL
SELECT	1, 2009, 10 UNION ALL
SELECT	1, 2009, 11 UNION ALL
SELECT	1, 2009, 12

-- Do the work
SELECT		k.PersonID,
		k.PhaseID,
		k.NumberOfDrugs,
		CASE (SELECT MIN(PhaseID) FROM @Sample)
			WHEN k.PhaseID THEN 0
			ELSE k.NumberOfDrugs - k.PreviousDrugs
		END AS DiffFromPrevious,
		CASE (SELECT MAX(PhaseID) FROM @Sample)
			WHEN k.PhaseID THEN 0
			ELSE k.NextDrugs - k.NumberOfDrugs
		END AS DiffFromNext
FROM		(
			SELECT		d.PersonID,
					d.PhaseID,
					SUM(CASE WHEN d.PhaseOffset = 0 THEN 1 ELSE 0 END) AS NumberOfDrugs,
					SUM(CASE WHEN d.PhaseOffset = -1 THEN 1 ELSE 0 END) AS PreviousDrugs,
					SUM(CASE WHEN d.PhaseOffset = 1 THEN 1 ELSE 0 END) AS NextDrugs
			FROM		(
						SELECT	PersonID,
							PhaseID,
							0 AS PhaseOffset
						FROM	@Sample

						UNION ALL

						SELECT	PersonID,
							PhaseID - 1,
							1
						FROM	@Sample

						UNION ALL

						SELECT	PersonID,
							PhaseID + 1,
							-1
						FROM	@Sample
					) AS d
			GROUP BY	d.PersonID,
					d.PhaseID
			HAVING		MIN(CASE WHEN d.PhaseOffset = 0 THEN 0 ELSE 1 END) = 0
		) AS k
ORDER BY	k.PersonID,
		k.PhaseID

E 12°55'05.25"
N 56°04'39.16"

slider
Starting Member

11 Posts

Posted - 2008-02-11 : 12:08:48

Thanks all of your for your excellent insight and help.
On a side note, phaseid is not a year per se. it is composed of year+(01/02)+studynumber e.g. 20080101 is study #1 in first half of 2008 , or 20050204 is a study #4 in later half of 2005.
Most of these solutions can made to work these phaseid's , if not all.
Jeff has an elegant approach.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 15:01:43

Too bad we didn't know from the start!
We could have saved much time knowing that.

E 12°55'05.25"
N 56°04'39.16"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-12 : 01:17:35

quote:
Originally posted by jsmith8858

quote:
Originally posted by ayamas

Here is my solution :-
I guess this is much more simple & less time consuming.
...

Unfortunately, it is exactly the opposite; it's more complicated and less efficient than what I provided...

Also, if you want to convert a CTE from SQL 2005 to 2000-compatible code, the equivalent is not a temp table or a table variable, but a derived table. (Unless you are using recursion)

- Jeff
http://weblogs.sqlteam.com/JeffS

Still a long way for me to go before I can be near to be called as a "SQL guy".But still I am trying...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources