October 21, 2015 at 12:22 pm
Hi All,
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time
and some other columns.
One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00
My query condition is complex and that is why Im facing problem retrieving the output.
I need a cursor for getting distinct invoice number from the table and for each invoice number I need to
get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0
begin
insert into #temp2
select invoiceNumber , Group from #getInvoice
where InvoiceExpirationDate is NULL or InvoiceExpirationDate> GETDATE()
and CreationDateTime= (select MAX(CreationDateTime ) from #getInvoice
where ClaimStatus=2)
fetch next from myData into @invoiceNumber
end
close myData
deallocate myData
This query runs forever and doesnot stop.
Please help me in writing a cursor for achieving my desired output.
October 21, 2015 at 1:17 pm
Why do you think you need a cursor? You're not even using the variable inside the cursor.
I'm not sure about your requirements as they're confusing and your sample data won't help. However, it should be something similar to this:
insert into #temp2
select distinct invoiceNumber , Group
from #getInvoice
where (InvoiceExpirationDate is NULL
or InvoiceExpirationDate> GETDATE())
and CreationDateTime = (select MAX(CreationDateTime )
from #getInvoice
where ClaimStatus=2)
October 21, 2015 at 1:22 pm
No cursor required as Luis said, but I think this may be what you are after, based on the description.
Perhaps if you could provide some expected output from that data, we could be sure.
with cte as
(
select *, row_number() over(partition by [invoiceNumber], [Group] order by CreationDateTime desc) as rn
from #getInvoice
)
select *
from cte
where rn = 1 -- this is the last entry for each invoiceNumber/Group
and (InvoiceExpirationDate is NULL or InvoiceExpirationDate> GETDATE())
and ClaimStatus=2
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 21, 2015 at 1:29 pm
By the way - you might care to post the test data in a consumable way, so that those helping can access it quickly. I did it for you this time:
drop table #getinvoices
go
create table #getinvoices (
invoicenumber int not null,
[group] char(2) not null,
InvoiceStatus int,
InvoiceExpirationDate datetime,
CreationDateTime datetime
)
Insert into #getinvoices(InvoiceNumber,[Group], InvoiceStatus, InvoiceExpirationDate, CreationDateTime)
select 579312,'01',3,NULL, '2003-03-24 00:00:00' union all
select 579312,'01',2,'2015-12-14 00:00:00', '2005-12-24 00:00:00' union all
select 579312,'02',2,'2003-12-21 00:00:00', '2005-10-12 00:00:00' union all
select 321244,'01',2,'2015-12-21 00:00:00', '2005-10-12 00:00:00' union all
select 321244,'01',3,'2010-12-21 00:00:00', '2010-12-21 00:00:00'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 22, 2015 at 6:15 am
Hi Luis,
Thanks very much for the query ! . Certain Records seems to be missing while running the query.
Hi Matt,
Thanks Very much for formatting it. I am new to Sql , that is why i did not know how to post data in correct way.
Hi Mister,
Thanks very much for the query. The query is working correctly as expected. But after running this query I found another problem.
The query you gave is below
with cte as
(
select *, row_number() over(partition by [invoiceNumber], [Group] order by CreationDateTime desc) as rn
from #getInvoice
)
select *
from cte
where rn = 1 -- this is the last entry for each invoiceNumber/Group
and (InvoiceExpirationDate is NULL or InvoiceExpirationDate> GETDATE())
and ClaimStatus=2
The above query picks the record which has the latest creationdatetime. But in my table there are invoice number which has more than one record with same Creation Date time.
So I have to get the record which has latest creation datetime and latest transaction eff date.
Below is the sample input
InvoiceNumber Group InvoiceStatus TransactionEffDate InvoiceExpirationDate CreationDateTime
579312 01 2 2002-02-12 NULL 2002-02-20
579312 01 2 2002-03-12 NULL 2002-02-20
In these two records creation date and time same. so in this scenario,the above query which you provided picks the first record from above two records . But it should take the second record because transaction effective date is latest in the second record .
Please explain how to modify the above query in such a way that it will pick the record which has latest creationdatetime and latest transactioneffdate.
The rest of the conditions are working perfectly.
Thanks in advance !
October 22, 2015 at 7:31 am
You just need to include TransactionEffDate in the ORDER BY clause of the Row_number() function.
with cte as
(
select *
, row_number() over(partition by [invoiceNumber], [Group]
order by CreationDateTime desc, [highlight="#ffff11"]TransactionEffDate desc[/highlight]
) as rn
from #getInvoice
)
select *
from cte
where rn = 1 -- this is the last entry for each invoiceNumber/Group
and (InvoiceExpirationDate is NULL or InvoiceExpirationDate> GETDATE())
and ClaimStatus=2
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 22, 2015 at 10:01 am
Hi Mister,
Thanks Very much for your help. Its working !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply