T-SQL required
-
Hai all,
I 'm having a table called tblCustomerInvoice with columns as say InvoiceID Int, CustomerCode Varchar(50), InvoiceDate DateTime and InvoiceAmount Float.
Now i want a query that returns the following column values
CustomerCode, InvoiceDate, InvoiceAmount, PreviousInvoiceDate, PreviousInvoiceAmount.
I have used the following query and accept that it is not a good idea.
SELECT CustomerCode, InvoiceDate, InvoiceAmount, (SELECT TOP 1 InvoiceDate FROM tblCustomerInvoice WHERE InvoiceDate < CI.InvoiceDate ORDER BY InvoiceDate DESC) As PreviousInvoiceDate,
(SELECT TOP 1 InvoiceAmount FROM tblCustomerInvoice WHERE InvoiceDate < CI.InvoiceDate ORDER BY InvoiceDate DESC) As PreviousInvoiceAmount
FROM
tblCustomerInvoice CI
I would like to have a much better optimized query. I remember that i must use self join but not able to form one. Please help me out
Regards,
Hemant
-
Hemant
First, a couple of comments on your table design. If CustomerCode is just that, then varchar(50) is probably a bit much, especially if you are using that column to join to another table. And why not use the money data type for InvoiceAmount? float doesn't give a precise representation of numbers.
Second, let me clarify your requirement. You want to return the customer code, the invoice details and the invoice details for the previous invoice. Is that the previous invoice for that particular customer, or the previous invoice for any customer? Your query will return the latter, but I suspect that the former will be more useful.
Something like this should work. Performance would depend on how large your table is and whether you actually want to return every row.
CREATE TABLE #MyInvoices (InvID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerCode varchar(50), InvoiceDateTime datetime, InvoiceAmount float)
INSERT INTO #MyInvoices
SELECT CustomerCode, InvoiceDate, InvoiceAmount
FROM tblCustomerInvoice
ORDER BY CustomerCode ASC, InvoiceDateTime DESC
SELECT i1.CustomerCode, i1.InvoiceDate, i1.InvoiceAmount, i2.InvoiceDate as PrevDate, i2.InvoiceAmount as PrevAmount
FROM #MyInvoices i1 JOIN #MyInvoices i2
ON i1.InvID = i2.InvID + 1
AND i1.CustomerCode = i2.CustomerCode
ORDER BY i1.InvoiceDate
DROP TABLE #MyInvoices
This isn't tested, so please forgive any syntax errors. If you want to return details of previous invoice by any customer, remove the line starting AND and remove CustomerCode from the ORDER BY clause.
John
-
John,
I agree with your comments about data structure, but I'm afraid the query won't work... You join on i1.InvID = i2.InvID + 1 ... what if the previous invoice was for another customer? I suppose a derived table or some subselect will be necessary for this, but let's wait till Hemant explains the requirements.
-
Vladan
The previous invoice won't be for another customer, because the data in the temp table is ordered by customer. Have I overlooked something?
John
-
No, sorry, I have
.I didn't realize that you mean the temp table creation as part of the solution - supposed that the temp table was created just for purposes of testing (so as not to create permanent table in your DB). Only now I realized that you are inserting there from the original table.
-
Thanx John Mitchell and Vladan,
Sorry for not making my question clearer and i totally agree with your data type suggestions. Here is my clear requirement
1. The query output columns must be CustomerCode, InvoiceDate, InvoiceAmount, PreviousInvoiceDate, PreviousInvoiceAmount.
2. The PreviousInvoiceDate and PreviousInvoiceAmount should be of the same CustomerCode and not any other i.e. the previous invoice that has been generated for a customer.
3. I don't want to go with Temp table or table variables coz i' m using the resultset for the report and the data in this table is enormous.
4. The query should also not contain UDF and TOP x statement that you can see from my previous post to this thread.
5. I guess this could be achieved with pure self join on the table but don't know how
John Mitchell....I think i can make use of your logic but without using the temp table. I will try and let you know more.
Thanx & Regards,
Hemant.
-
Hemant
OK, thanks. I was correct in my supposition, as you have clarified in point 2.
Just one piece of advice: go with what works the best. Don't discount temp tables just because you've got a lot of data. Certain types of JOIN operation will create objects in tempdb in any case, so you may not be any better off if you avoid temp tables. The same goes for UDFs, TOP statements, or any other technique that may not, at first, appear to be optimal... so you should use them if they are better than the alternatives.
I take it that you are going to run this query against the whole of your tblCustomerInvoice table (ie with no rows filtered out by where clauses)? If that's the case then you're going to have a lot of repeated data. Consider the following fictional extract:
CustomerCode InvoiceDate InvoiceAmount PreviousInvoiceDate PreviousInvoiceAmount
43265 2006-11-07 10002.45 2006-08-31 6752.21
43265 2006-08-31 6752.21 2006-07-02 2857.82
You will observe that the second row repeats the date and invoice amount (in green) from the first. Is it really necessary for you to do this? Can you not just write a select statement ordered by CustomerCode? It's then a simple matter for you to glance to the previous row to see the previous invoice details. I would really only recommend the approach you are taking if you're trying to pull out previous invoice details for an individual invoice, rather than for every invoice.
One more thing: does your report need to contain data for every invoice, or can you filter out some customers, or only return invoices for the last week or month or year? All of these would reduce the size of the result set and speed up your query.
Another thing you could consider is to add two new columns PrevInvoiceDate and PrevInvoiceAmount to tblCustomerInvoice. Then create a trigger that updates a any invoice with details of the previous one. You would have to be careful then if the original invoice is changed, and make sure that the changes are reflected in both rows.
John
-
Thanx John,
That was a wonderful piece of information and i liked the way you explained and i'm convinced to make use of the temp tables now (i hope the same with table variables, performance).
Actually, there are parameters to filter the resultsets
Thanx again for your response man
Hemant.
-
Hemant
Happy to help. As for table variables, they do have a slightly different behaviour on the surface, but underneath they do the same thing: they are created in memory but moved to tempdb when they get too big. Beware of limitations with table variables... I think I might be right in saying you can't create indexes on them.
John
-
Hai John Mitchell,
I tried your following query to attain the result but unfortunatly i didn't worked as expected. I took only a particular customer and tried your query. This customer has only 22 rows in the table. The following are the outcome stats:
Customer Invoice Count = 22
Output Rows = 28484
Time Consumption = 5 Seconds
CREATE TABLE #MyInvoices (InvID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerCode varchar(50), InvoiceDateTime datetime, InvoiceAmount float)
INSERT INTO #MyInvoices
SELECT CustomerCode, InvoiceDate, InvoiceAmount
FROM tblCustomerInvoice
ORDER BY CustomerCode ASC, InvoiceDateTime DESC
SELECT i1.CustomerCode, i1.InvoiceDate, i1.InvoiceAmount, i2.InvoiceDate as PrevDate, i2.InvoiceAmount as PrevAmount
FROM #MyInvoices i1 JOIN #MyInvoices i2
ON i1.InvID = i2.InvID + 1
AND i1.CustomerCode = i2.CustomerCode
ORDER BY i1.InvoiceDate
DROP TABLE #MyInvoices
I think we need someother way to get the previous invoice match for a customer. If you come across, please let me know
Regards,
Hemant
-
Hemant
Please post the output of this query:
SELECT CustomerCode, InvoiceDate, InvoiceAmount
FROM tblCustomerInvoice
ORDER BY CustomerCode ASC, InvoiceDateTime DESC
John
-
Hi John,
Here is the output of the query you have requested. I have taken one customer (code = 100012)
Code DateTime Amount
100012 2006-04-01 00:00:00.000 162914
100012 2006-03-01 00:00:00.000 160042
100012 2006-02-02 00:00:00.000 158316
100012 2006-01-01 00:00:00.000 155076
100012 2005-12-01 00:00:00.000 153839
100012 2005-11-01 00:00:00.000 151612
100012 2005-10-01 00:00:00.000 149852
100012 2005-09-01 00:00:00.000 148330
100012 2005-08-01 00:00:00.000 146210
100012 2005-07-01 00:00:00.000 144531
100012 2005-06-01 00:00:00.000 142670
100012 2005-05-01 00:00:00.000 140926
100012 2005-04-01 00:00:00.000 138959
100012 2005-03-01 00:00:00.000 136469
100012 2005-02-01 00:00:00.000 134427
100012 2005-01-01 00:00:00.000 132560
100012 2004-12-01 00:00:00.000 131704
100012 2004-11-01 00:00:00.000 129532
100012 2004-10-01 00:00:00.000 127789
100012 2004-09-07 00:00:00.000 126119
100012 2004-08-01 00:00:00.000 122176
100012 2004-07-13 00:00:00.000 121464
-
Hemant
I've run the query with your data and got 21 rows, as expected. I suspect that what you have done is not to have cleared out the temp table before re-running the INSERT part of the query. Try a DROP TABLE #MyInvoices or DELETE FROM #MyInvoices and start again.
John
-
Thanks man 
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply