May 11, 2015 at 8:49 am
I can't figure out why this won't work. POST_DT is nvarchar(50). I know it should be datetime but, I have no control over it. That's why I'm creating a temp table. But, I want to only insert the most recent invoice. Any thoughts? There is code to create test data at the end.
create table #FSC589
(
GRP__2 char(1),
INV_NUM char(8),
MRN varchar(10),
POST_DT datetime
)
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT)
select GRP__2, INV_NUM, MRN, max(convert(datetime, POST_DT, 121))
from IDX_INCOME
where INV_FSC__1='CREDIT APPROVED'
group by GRP__2, INV_NUM, MRN, POST_DT
test data
create table #FSC589
(
GRP__2 char(1),
INV_NUM char(8),
MRN varchar(10),
POST_DT datetime
)
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17977646','83894','05/31/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/08/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/08/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899418','90591','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17899774','90591','05/08/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17918283','88926','05/23/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17940377','88926','05/10/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17940377','88926','05/10/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17941937','92866','05/11/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911367','96040','05/20/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911367','96040','05/20/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921469','96040','05/12/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17944086','96040','05/12/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17944086','96040','05/12/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17955465','94766','05/19/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17962333','94766','05/22/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17967631','94766','05/24/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976082','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976083','94766','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17794582','94798','05/30/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17901511','103779','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17901511','103779','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','15758688','697868','02/23/2011')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17928953','94725','05/03/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17928953','94725','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17199540','117798','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17223802','117798','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17238636','117798','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17420392','117798','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17567282','117798','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17830827','117798','05/19/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17830827','117798','05/19/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17933335','117798','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17954406','117798','05/19/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960518','117798','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17980032','117798','05/30/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17958747','100601','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920488','681846','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920488','681846','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17882995','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17882995','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17888908','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17888908','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17889801','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17889801','102297','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960408','102297','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960408','102297','05/28/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960410','102297','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960410','102297','05/28/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960413','102297','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17960414','102297','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976750','102297','05/30/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17911493','117950','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17926238','117950','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17974201','78795','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17974201','78795','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17906030','68222','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17922194','68222','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17922194','68222','05/16/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17977689','98177','05/30/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17622709','104619','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17622709','104619','05/07/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920966','104619','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17920966','104619','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921050','104619','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921050','104619','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17955484','104619','05/19/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17963614','104619','05/22/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17968169','104619','05/25/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976601','104619','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976601','104619','05/29/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17249407','855691','06/26/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17249407','855691','06/26/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17208515','32139','04/05/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17235554','32139','04/20/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17235554','32139','04/22/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17236850','32139','04/22/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '6','17236850','32139','04/22/2013')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932361','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17932363','81244','05/06/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903191','16953','05/09/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17907257','81694','05/13/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17907257','81694','05/13/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921233','81694','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17921233','81694','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17950253','81694','05/15/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17976893','81694','05/30/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17873961','44219','05/07/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17883058','44219','05/14/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/02/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17890983','44219','05/14/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/07/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/07/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17898969','44219','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/08/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/08/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17900453','44219','05/21/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903924','44219','05/09/2014')
insert into #FSC589(GRP__2, INV_NUM, MRN, POST_DT) values( '7','17903924','44219','05/09/2014')
May 11, 2015 at 9:08 am
Remove POST_DT from the GROUP BY.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 11, 2015 at 9:21 am
That was easy! Thanx.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply