February 18, 2008 at 7:57 pm
hi,
what happens here is I create a #tmp0,which is the working table of mine.
i create many temp tables and update the #tmp0 tables and retrieve the records from there to generate reports.The following is my third #tmp3 table.
when i generate the report, i get zero for ReceiptsThisClaim column
where jr.ReceiptDate <=x.InvoiceDate
but when i write the code like below,i get some non zero columns.
--where Convert(varchar(12),jr.ReceiptDate,103) <='16/02/2008' --this should be avoided.
what could it be wrong?I know it's hard to say without looking at database.
but try your best.
create table #tmp3 (
InvoiceNumber int,
ReceiptsToDate numeric(15,4) default 0,
LastReceiptDate datetime,
ReceiptsThisClaim numeric(15,4) default 0,
ReceiptDateThisClaim datetime)
insert into #tmp3
select x.InvoiceNumber,
sum(jr.FCAmount),
max(jr.ReceiptDate),
sum(case when jr.InvoiceNumber = x.InvoiceNumber then jr.FCAmount else 0 end),
max(case when jr.InvoiceNumber = x.InvoiceNumber then jr.ReceiptDate else 0 end)
from #tmp0 x
join JobInvoices ji on ji.JobCode = x.ProjectCode
join JobReceipts jr on jr.InvoiceNumber = ji.InvoiceNumber
where jr.ReceiptDate <=x.InvoiceDate
--where Convert(varchar(12),jr.ReceiptDate,103) <='16/02/2008' --x.InvoiceDate
group by x.InvoiceNumber
update #tmp0 set
ReceiptsToDate = y.ReceiptsToDate,
LastReceiptDate = y.LastReceiptDate,
ReceiptsThisClaim=y.ReceiptsThisClaim,
ReceiptDateThisClaim=y.ReceiptDateThisClaim
from #tmp0 x, #tmp3 y
where x.InvoiceNumber = y.InvoiceNumber
....
...
regards,
ts
February 18, 2008 at 8:40 pm
Your issue is that you're comparing strings to strings and not dates to dates.
in your case '15/04/2099'<='16/01/2008' (because '15'<='16')
What you want to be doing is CAST('15/04/2099' as datetime) which is >= CAST('16/01/2008' as datetime)
----------------------------------------------------------------------------------
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?
February 19, 2008 at 5:40 am
Also, not related to the issue you're describing, but worth mentioning, you're performing a non-ansi join between the two temp tables at the end of your query there.
FROM temp#1 x,temp#3 y
WHERE x.InvoiceId = y.InvoiceId
Instead, you should be doing this
FROM temp#1 x
JOIN temp#3 y
ON x.InvoiceId = y.InvoiceId
The order and logic used by the query engine is such that you could, depending on the data, see very different execution plans between these two. Not to mention, if you had to do OUTER JOINS, 2005 does not support that style of joining.
Also, from the code snippets, why are you loading all the data into temp tables first? You're generating a bunch of unnecessary I/O. Even if you can't see, or don't have, a clear method to simply perform the join between the data in temp#1 and temp#3, you could, instead used derived tables. Simply move the SELECT statement that loads temp#3 within parenthesis and supply the alias. You'll see a massive performance increase in your query. Something like this:
FROM temp#1 AS x
JOIN (SELECT....) AS y
ON x.InvoiceId = y.InvoiceId
You can probably do the same with temp#1 depending on how that data is being put together.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 19, 2008 at 3:49 pm
hi mate,
thank you very much for your reply and notifying me the corrections.
It is giving the output that it had to.
regards,
ts:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply