May 10, 2016 at 11:14 am
Hello,
The query below is pulling some cases which have multiple benefits during the same month, how would I only pull the cases which the benmon date is the earlier of the two.
Example below, I would like only to show the entry with the earliest issuedate. Not all entries have duplicates though. Thank you for any help.
yyyymmcircuitadminunitpasagrcvddatedispdateissuedate timely
201603 3/16/20163/23/20163/15/2016 1
201603 3/16/20163/23/20163/27/2016 1
select distinct
a.yyyymm,b.circuit,a.admin,a.unit,a.pas,a.ag,a.rcvddate, a.dispdate,C.issuedate, a.timely
from datamart.dbo.appl16_rvsd a,
datamart.dbo.distcoun b,
DATAMART.dbo.fsissue16 C
where a.county=b.county and A.ag=C.ag and a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and a.program='FS'
and a.type in ('I','R') and a.status='A'
and b.region='sc' and a.timestd=30
and a.district+a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
order by b.circuit,a.admin,a.unit,a.pas
May 10, 2016 at 11:46 am
For this type of row filtering based on TOP(x) ranking, you can use the DENSE_RANK() function like so. You'll need to experiment with the PARTITION BY and ORDER BY clause to get the exact result you need for your application.
create table #t (yyyymm char(7), a int, b int, issuedate date);
insert into #t ( yyyymm, a, b, issuedate )
values ('2016/01', 1, 3, '2016/01/13')
, ('2016/01', 3, 3, '2016/01/15')
, ('2016/01', 3, 2, '2016/01/17')
, ('2016/02', 1, 3, '2016/01/11')
, ('2016/02', 3, 3, '2016/01/12')
, ('2016/02', 3, 2, '2016/01/19');
select * from
(
select yyyymm, a, b, issuedate
, dense_rank() over (partition by yyyymm order by issuedate desc)rank_id
from #t
) x
where rank_id = 1;
yyyymmabissuedaterank_id
2016/01322016-01-171
2016/02322016-01-191
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 10, 2016 at 11:50 am
DPD0011 (5/10/2016)
Hello,The query below is pulling some cases which have multiple benefits during the same month, how would I only pull the cases which the benmon date is the earlier of the two.
Example below, I would like only to show the entry with the earliest issuedate. Not all entries have duplicates though. Thank you for any help.
yyyymmcircuitadminunitpasagrcvddatedispdateissuedate timely
201603 3/16/20163/23/20163/15/2016 1
201603 3/16/20163/23/20163/27/2016 1
select distinct
a.yyyymm,b.circuit,a.admin,a.unit,a.pas,a.ag,a.rcvddate, a.dispdate,C.issuedate, a.timely
from datamart.dbo.appl16_rvsd a,
datamart.dbo.distcoun b,
DATAMART.dbo.fsissue16 C
where a.county=b.county and A.ag=C.ag and a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and a.program='FS'
and a.type in ('I','R') and a.status='A'
and b.region='sc' and a.timestd=30
and a.district+a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
order by b.circuit,a.admin,a.unit,a.pas
Give the following a try.
Suggestions:
1) Use SQL-92 style joins not the SQL-89 style joins.
2) Use white space to make your code more readable
3) Be consistent in writing your code, don't keep changing case for the same object names such as database names, aliases, etc.
4) Terminate all statements with a semicolon (;).
with basedata as (
select
rn = row_number() over (partition by a.yyyymm, a.rcvdate, a.dispdate order by c.issuedate asc),
a.yyyymm,
b.circuit,
a.admin,
a.unit,
a.pas,
a.ag,
a.rcvddate,
a.dispdate,
c.issuedate,
a.timely
from
datamart.dbo.appl16_rvsd a
inner join datamart.dbo.distcoun b
on a.county = b.county
inner join datamart.dbo.fsissue16 c
on a.ag = c.ag
where
a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and
a.program = 'FS' and
a.type in ('I','R') and
a.status = 'A' and
b.region = 'sc' and
a.timestd = 30 and
a.district + a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
)
select
bd.yyyymm,
bd.circuit,
bd.admin,
bd.unit,
bd.pas,
bd.ag,
bd.rcvddate,
bd.dispdate,
bd.issuedate,
bd.timely
from
basedata bd
order by
bd.circuit,
bd.admin,
bd.unit,
bd.pas;
May 10, 2016 at 11:51 am
Eric M Russell (5/10/2016)
For this type of row filtering based on TOP(x) ranking, you can use the DENSE_RANK() function like so. You'll need to experiment with the PARTITION BY and ORDER BY clause to get the exact result you need for your application.
create table #t (yyyymm char(7), a int, b int, issuedate date);
insert into #t ( yyyymm, a, b, issuedate )
values ('2016/01', 1, 3, '2016/01/13')
, ('2016/01', 3, 3, '2016/01/15')
, ('2016/01', 3, 2, '2016/01/17')
, ('2016/02', 1, 3, '2016/01/11')
, ('2016/02', 3, 3, '2016/01/12')
, ('2016/02', 3, 2, '2016/01/19');
select * from
(
select yyyymm, a, b, issuedate
, dense_rank() over (partition by yyyymm order by issuedate desc)rank_id
from #t
) x
where rank_id = 1;
yyyymmabissuedaterank_id
2016/01322016-01-171
2016/02322016-01-191
The OP asked for the earliest issuedate, not the latest.
May 10, 2016 at 7:48 pm
Thank you for the tips, when I run the query I receive this error:
Msg 195, Level 15, State 10, Line 2
'row_number' is not a recognized function name.
May 11, 2016 at 8:58 am
DPD0011 (5/10/2016)
Thank you for the tips, when I run the query I receive this error:Msg 195, Level 15, State 10, Line 2
'row_number' is not a recognized function name.
What version of MS SQL Server are you using?
May 11, 2016 at 9:02 am
SQL 2012
May 11, 2016 at 9:08 am
DPD0011 (5/11/2016)
SQL 2012
Need to see the code you actually tried to run. The row_number() function has been a part of SQL Server since SQL Server 2005.
May 11, 2016 at 9:11 am
I appreciate the help, new to SQL.
select
rn = row_number() over (partition by a.yyyymm, a.rcvdate, a.dispdate order by c.issuedate asc),
a.yyyymm,
b.circuit,
a.admin,
a.unit,
a.pas,
a.ag,
a.rcvddate,
a.dispdate,
c.issuedate,
a.timely
from
datamart.dbo.appl16_rvsd a
inner join datamart.dbo.distcoun b
on a.county = b.county
inner join datamart.dbo.fsissue16 c
on a.ag = c.ag
where
a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and
a.program = 'FS' and
a.type in ('I','R') and
a.status = 'A' and
b.region = 'sc' and
a.timestd = 30 and
a.district + a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
)
select
bd.yyyymm,
bd.circuit,
bd.admin,
bd.unit,
bd.pas,
bd.ag,
bd.rcvddate,
bd.dispdate,
bd.issuedate,
bd.timely
from
basedata bd
order by
bd.circuit,
bd.admin,
bd.unit,
bd.pas;
May 11, 2016 at 9:16 am
DPD0011 (5/11/2016)
I appreciate the help, new to SQL.
select
rn = row_number() over (partition by a.yyyymm, a.rcvdate, a.dispdate order by c.issuedate asc),
a.yyyymm,
b.circuit,
a.admin,
a.unit,
a.pas,
a.ag,
a.rcvddate,
a.dispdate,
c.issuedate,
a.timely
from
datamart.dbo.appl16_rvsd a
inner join datamart.dbo.distcoun b
on a.county = b.county
inner join datamart.dbo.fsissue16 c
on a.ag = c.ag
where
a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and
a.program = 'FS' and
a.type in ('I','R') and
a.status = 'A' and
b.region = 'sc' and
a.timestd = 30 and
a.district + a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
)
select
bd.yyyymm,
bd.circuit,
bd.admin,
bd.unit,
bd.pas,
bd.ag,
bd.rcvddate,
bd.dispdate,
bd.issuedate,
bd.timely
from
basedata bd
order by
bd.circuit,
bd.admin,
bd.unit,
bd.pas;
You are missing a part of what I posted:
with basedata as ( -- << missing this part of the query.
select
rn = row_number() over (partition by a.yyyymm, a.rcvdate, a.dispdate order by c.issuedate asc),
a.yyyymm,
b.circuit,
a.admin,
a.unit,
a.pas,
a.ag,
a.rcvddate,
a.dispdate,
c.issuedate,
a.timely
from
datamart.dbo.appl16_rvsd a
inner join datamart.dbo.distcoun b
on a.county = b.county
inner join datamart.dbo.fsissue16 c
on a.ag = c.ag
where
a.yyyymm = '201603' and
c.benmon between '2016-03-01' and '2016-03-31' and
substring(a.pas,1,1) <> 'E' and
a.program = 'FS' and
a.type in ('I','R') and
a.status = 'A' and
b.region = 'sc' and
a.timestd = 30 and
a.district + a.admin not in ('0488151','2388198','1188101','1188102','0202555','0290132')
)
select
bd.yyyymm,
bd.circuit,
bd.admin,
bd.unit,
bd.pas,
bd.ag,
bd.rcvddate,
bd.dispdate,
bd.issuedate,
bd.timely
from
basedata bd
order by
bd.circuit,
bd.admin,
bd.unit,
bd.pas;
May 11, 2016 at 10:00 am
Copied and pasted with the missing part and received the error below.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 3
'row_number' is not a recognized function name.
May 11, 2016 at 10:37 am
DPD0011 (5/11/2016)
Copied and pasted with the missing part and received the error below.Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 3
'row_number' is not a recognized function name.
Okay, we need the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT INTO statements) for the table(s) involved, and expected results based on the sample data.
Before posting any code be sure to run it in an empty database to be sure it runs when we cut/paste/execute it into our own sandboxed database.
No one can test the code without the above.
The best I can do is a syntax check and that succeeds.
May 11, 2016 at 10:50 am
As far as I know, the only reason this would happen is if you're connected to a pre-2005 version of SQL Server.
I'd double-check that you're actually connected to a 2012 instance, and not just (for example) using 2012 Management Studio to connect to a 2000 instance.
What does this return when run in the same query window where you get the error?
SELECT @@version;
Cheers!
EDIT: Fixed a typo.
May 11, 2016 at 1:13 pm
Shoot the version is 2000. Sorry for the mix up.
May 11, 2016 at 1:21 pm
DPD0011 (5/11/2016)
Shoot the version is 2000. Sorry for the mix up.
We can still help you with a solution if you provide the information I requested in my last post.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply