October 24, 2007 at 9:18 am
Hi,
I have a query which returns a result set. In this result set, i have 3 groups basically, and I need to find min and max date for each of the groups. What is happening now is min(date) finds min date for result set and max(date) finds the max date for the result set.
See the snapshot below to understand my situation...
I need first min and max to return. min = 05/07/2007 max = 05/16/2007
second min and max should get ... min = 05/17/2007 max = 06/10/2007
third min and max should get....... min = 06/11/2007 max = 06/17/2007
right now iam getting
first min and max . min = 05/07/2007 max = 06/17/2007
second min and max should get ... min = 05/17/2007 max = 06/10/2007
third min and max should get....... min = 06/17/2007 max = 06/10/2007
here are the queries i am using
declare @company_id int, @employee_no int,@leave_no int,@coverage_string varchar(2000)
set @employee_no = 10345
set @leave_no = 1225
set @company_id = 19
declare @intake_id int
select @intake_id = intake_id from leave
where company_id = @company_id and leave_no = @leave_no
-- ===========================================
-- Coverage string declaration
set @coverage_string = ''
-- ===========================================
-- Initiate cursor
declare @scrub_table table
(policy_name varchar(40), status_code int, denial_reason varchar(50), attendance_date datetime, status varchar(25),groupBy int)
insert into @scrub_table
select policy_name, status_code, denial_reason, convert(varchar(10),attendance_date,101) as attendance_date,
case when status_code = 1 then 'Pending'
when status_code = 4 then 'Approved'
when status_code = 5 then 'Denied'
end as status, case when status_code = 1 then 1
when status_code = 4 then 2
when status_code = 5 then 3
end as groupBy
from (attendance a inner join absence_to_policy c on a.company_id = @company_id
and a.employee_no = @employee_no and a.date_id = c.date_id)
inner join policy d on d.company_id = @company_id and d.policy_id = c.policy_id
where c.leave_no = @leave_no
and c.status_code < 25
and c.absence_type != 2
order by leave_type, d.policy_id, cast(attendance_date as datetime)
select * from @scrub_table
-- this is where 3 diff result sets are formed...
--problem is that group by matches when denied is denial reason and that is
--causing the min and max date to be returned for whole result set....
declare @final_table table
(policy_name varchar(40), status_code int, denial_reason varchar(50),
min_date datetime, max_date datetime, status varchar(25))
insert into @final_table
select case when policy_name = 'Family and Medical Leave Act of 1993' then 'Federal FMLA' else policy_name end as policy_name,
5, denial_reason, min(attendance_date), max(attendance_date), 'Denied'
from @scrub_table a where denial_reason = 'Exhausted'
group by policy_name,denial_reason
select * from @final_table --1
insert into @final_table
select case when policy_name = 'Family and Medical Leave Act of 1993' then 'Federal FMLA' else policy_name end as policy_name,
5, denial_reason, min(attendance_date), max(attendance_date), 'Denied'
from @scrub_table a where denial_reason != 'Exhausted' and denial_reason is not null
group by policy_name, denial_reason
select * from @final_table --2
insert into @final_table
select case when policy_name = 'Family and Medical Leave Act of 1993' then 'Federal FMLA' else policy_name end as policy_name,
4, denial_reason, min(attendance_date), max(attendance_date), 'Approved'
from @scrub_table a where status = 'Approved' --removed condition denial_reason is null and status = 'Approved'
and (
not exists (select * from @final_table x where a.policy_name = x.policy_name and x.denial_reason = 'Exhausted') or
attendance_date < (select min_date from @final_table x where a.policy_name = x.policy_name and x.denial_reason = 'Exhausted')
)
group by policy_name,denial_reason
select * from @final_table --3
insert into @final_table
select case when policy_name = 'Family and Medical Leave Act of 1993' then 'Federal FMLA' else policy_name end as policy_name,
1, denial_reason, min(attendance_date), max(attendance_date), 'Pending'
from @scrub_table a where denial_reason is null and status = 'Pending'
and (
not exists (select * from @final_table x where a.policy_name = x.policy_name and x.denial_reason = 'Exhausted') or
attendance_date < (select min_date from @final_table x where a.policy_name = x.policy_name and x.denial_reason = 'Exhausted')
)
group by policy_name, denial_reason
select * from @final_table --4
October 24, 2007 at 9:40 am
-- Prepare sample data
DECLARE@Sample TABLE (Grp CHAR(1), dt DATETIME)
INSERT@Sample
(
Grp,
dt
)
SELECTTOP 100
CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),
18000 + ABS(CHECKSUM(NEWID())) % 20000
FROMmaster..syscolumns AS sc1
CROSS JOINmaster..syscolumns AS sc2
-- Show the expected output
SELECTGrp,
RecID,
MAX(CASE WHEN hl = 0 THEN dt ELSE NULL END) AS minDate,
MAX(CASE WHEN hl = 1 THEN dt ELSE NULL END) AS maxDate
FROM(
SELECTGrp,
dt,
0 AS hl,
ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY dt) AS RecID
FROM@Sample
UNION ALL
SELECTGrp,
dt,
1,
ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY dt DESC)
FROM@Sample
) AS d
WHERERecID IN (1, 2, 3)
GROUP BYGrp,
RecID
ORDER BYGrp,
RecID
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply