Choosing the larger number of days in week

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)

Choosing the larger number of days in week

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-03-12 : 17:28:42

We have patients who are assigned insurance that changes on occasion. I'm trying to get the number of patients in all insurances by week, but of course when they change insurance they end up in two groups in that week. I want to default them to the insurance they were in the most. Since all weeks will have 7 days it would always be 6:1 or 5:2 OR 4:3.

(Not sure how SQL server deals with the first and last week of the year. If January 1 falls on a Wednesday will Week 1 also contain some days in December? I don't think this will be an issue as our small facility is usually a ghost town between Christmas and New Years)

The code below creates a table with two patients. One was assigned the same insurance all 7 days in the week and the other is split with 4 days in one insurance (hp_1) and 3 days in another (hp_2). What I would like to see is each patient having one row and any patients who are split would go to the insurance they spent the most time in that week. In this case, patient 1001 should end up being assigned to hp_1.

I'm stumped as to a simple way to do this. Any help is appreciated.

Greg


declare @tbl_hp_assign TABLE (
[patient_id] [int] NOT NULL,
[hp_name] varchar(30) NOT NULL,
[week_num] int NOT NULL
)

insert into @tbl_hp_assign
values
(1001, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_2', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_2', 1)

insert into @tbl_hp_assign
values
(1001, 'hp_2', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

insert into @tbl_hp_assign
values
(1002, 'hp_1', 1)

select [patient_id], [hp_name], COUNT(week_num) DayCount from @tbl_hp_assign
group  by [patient_id], [hp_name]
order by [patient_id]

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-12 : 20:53:52

Like following?

select
	[patient_id],
	[hp_name],
	DayCount
from (
	select
		ROW_NUMBER() over(partition by [patient_id] order by COUNT(week_num) desc) rownum,
		[patient_id],
		[hp_name],
		COUNT(week_num) DayCount
	from @tbl_hp_assign
	group  by [patient_id], [hp_name]
	) TBL
where rownum = 1
order by [patient_id]

-------------------------------------
From Japan
Sorry, my English ability is limited.

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-03-13 : 14:22:04

Thanks I will give that a try. I knew it would have something to do with (ROW_NUMBER() over(partition by [patient_id] order by COUNT(week_num) desc) rownum), but i couldn't see it.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources