Paging.. part 2

wyrd

wyrd

Well-known member
Joined
Aug 23, 2002
Messages
1,408
Location
California
*cries in frustration*

Okay.. here we go again with paging. I *thought* I had my paging problem solved, unfortunately I came across a new problem. To get fresh I deas I decided to start a new thread and re-explain my problem.

Here we go (buckle your seat belt).

Im keeping track of an Accounting database, very simple. There is a Category table and an Item table. Categories are to categorize items, and Items keep track of the items.

Table: Category
Column: Category_ID (primary key, non clustered)
Column: Name (unique key, clustered index)

Table: Item
Column: Item_ID (primary key, non clustered)
Column: Date --|__ (clustered index date, description)
Column: Description --|
Column: Category_ID (foreign key -> Category.Category_ID)
Column: Cost

Note about Date: I am inserting all dates as the 1st of the month. This program is only keeping track of items purchased on a monthly bases.

When querying for an item, I look for whatever the user specified (by date, category, date & category, or all items). I then order the items by Date, Description (obviously, as thats my clustered index).

Example:
SELECT Item.*, Category.Name FROM Item JOIN Category ON (Item.Category_ID = Category.Category_ID) WHERE Date = @date AND Item.Category_ID = @catid ORDER BY Date, Description

Simple enough? The problem comes into paging. For example I want to display 5 items per page, so to select the first page I just do a simple TOP 5. The next page page I do

SELECT TOP 5 ... WHERE Date > @dateOfLastItem OR (Date >= @dateOfLastItem AND Description >= @desOfLastItem AND Item_ID != @itemid) ORDER BY Date, Description

@dateOfLastItem = The date of the last item on the first page (or previous page, as we keep clicking next)
@desOfLastItem = Description of last item on the first page (or previous page, as we keep clicking next)

All is fine and dandy. This is what I had and it was working.. UNTIL! Notice how my Item.Description is NOT unique? Ohhhhh boy did I set myself up for this one. The simple solution would be "well make Description unique you moron!" Well, boohoo to me, because thats not possible with the way this program was designed. Its designed to keep track of Accounting items, and descriptions may not be unique. IE: Bought some items on 2003/05 from Wal-Mart, itd be entered as Wal-Mart. Now you buy more on 2003/06. Item entered as Wal-Mart.

Where am I going with this? The problem is when several items at the end of the page are of the same date and description. Example data inside my table;

DATE - Description - Category - Cost
03/05 - Wal-Mart - Supplies - $22.50
03/05 - Wal-Mart - Supplies - $12.50
03/05 - Wal-Mart - Supplies - $10.00
03/05 - Wal-Mart - Supplies - $23.23
03/05 - Wal-Mart - Supplies - $72.84

Yes, you read that correct, 5 duplicate items (except the price, but whos to say the price couldnt be the same too?)

So, the current query Im using;

SELECT TOP 5 ... WHERE Date > @dateOfLastItem OR (Date >= @dateOfLastItem AND Description >= @desOfLastItem AND Item_ID != @itemid) ORDER BY Date, Description

Isnt working correctly. As you notice, it checks for Description >= @desOfLastItem AND Item_ID != @itemid. This is good so I dont select the same item on the next page, but the items with the same description just.. I dont know how to explain it. They can re-show on the next page as well, which totally jacks up the paging mechanism. I did a test with 6 items (5 pages per item)..

DATE - Description - Category - Cost
03/05 - Wal-Mart - Supplies - $1.00
03/05 - Wal-Mart - Supplies - $2.00
03/05 - Wal-Mart - Supplies - $3.00
03/05 - Wal-Mart - Supplies - $4.00
03/05 - Wal-Mart - Supplies - $5.00
03/05 - Wal-Mart - Supplies - $6.00

First page comes out fine;

DATE - Description - Category - Cost
03/05 - Wal-Mart - Supplies - $1.00
03/05 - Wal-Mart - Supplies - $2.00
03/05 - Wal-Mart - Supplies - $3.00
03/05 - Wal-Mart - Supplies - $4.00
03/05 - Wal-Mart - Supplies - $5.00

The next page, though (sql statement above), comes out like this;
DATE - Description - Category - Cost
03/05 - Wal-Mart - Supplies - $1.00
03/05 - Wal-Mart - Supplies - $2.00
03/05 - Wal-Mart - Supplies - $3.00
03/05 - Wal-Mart - Supplies - $4.00
03/05 - Wal-Mart - Supplies - $6.00

:confused:

I thought of one solution, and that was to use either a GROUP BY and calculate the totals as just one Account Item, or to use an INSERT query thatll add the total to another item that has the same date/description instead of adding a new row. HOWEVER, if the person using the program needs to look back on this to make corrections, or to make sure they entered correct data, and had 5 Wal-Mart receipts, theyd have to do calculations on their own to make sure the totals were correct to make sure they entered all of their Wal-Mart costs. Rather if it displayed the duplicate items as it is now (after I fix the paging, if thats possible) they could simply juts eyeball it and go "okay, I entered 5 Wal-Mart purchases for this month" and know it was taken care of.

The other solution I thought of was to simply not allow the user to view All items, and force them to specificy a category and date, that way there wouldnt be to many items displayed (worse case I was thinking 1k items)

So.. any ideas, anyone?
 
Last edited by a moderator:
that;s confusing, first of all you need to check the entry data, as there is only one data colum so there is possiblity that all items are enterd on the same date.
please try " Inner Join Items on Items.CategoryID = Category.CategoryID "
 
Thanks for the advice, but Category has nothing to do with my problem. Your JOIN syntax is identical to mine (except youre joining items where I joined categories)

Maybe I should at least try and clear it up with a quick and dirty summary; It is not paging correctly because of duplicate data (as the example shows in the last part of my explanation).

I really wish I could explain it better, unfortunately I cannot. I have given the exact table designs and sql statements I am using along with an explanation (to the best of my ability) of what I am trying to do, and where the problem lies.

If you are confused then can you please specify the part in which you are confused about? That way I do not have to rephrase the 3 page essay in which Ive given above.

Thanks. :)
 
Possible solution from microsoft.public.sqlserver.programming:

"Wayne Sheffield" <wayne_sheffield@hotmail.com> wrote in message news:<Oz0Qw7jKDHA.2244@TK2MSFTNGP11.phx.gbl>...
> SELECT TOP 5 ... WHERE Date > @dateOfLastItem OR (Date >=
> @dateOfLastItem AND Description >= @desOfLastItem AND Item_ID !=
> @itemid) ORDER BY Date, Description

change this to:
SELECT TOP 5 ... WHERE Date > @dateOfLastItem OR (Date >=
@dateOfLastItem AND Description >= @desOfLastItem AND Item_ID >
@itemid) ORDER BY Date, Description, Item_ID

HTH,
Wayne
Click to expand...

Ill give it a shot later tomorrow and report back here with results. Im all ears for further suggestions though.
 
It is perhaps a painful thought, but have you considered taking a step back and redesign the database? Consider stepping through your database with an eye towards normaliztion and a broader view of what your database can be used for. Catagory and Item are reasonable tables for an inventory db but given the query you are trying to develop, perhaps a seperate Description table as well. Or maybe a different view all together, Orders and Purchases as seperate tables perhaps and a join on Purchases and Item if you want to track purchases by date and item category/description.

Jon
 
wyrd,

The Group By option seems the best solution without changing the database. You could offer a driil down option, and retrieve the items with the same date, description. However, you would need to provide for more than 5 rows, since there could be more than 5 duplicates. If you are displaying in a listview or listbox the scroll bar would automatically appear. If the user selects the drill down option, retrieve all rows for the date, description not just the top 5.
 
Some good suggestions. I found an answer though, but Ill get to that in a second while I try and put some logic behind my database design and why I also decided to not go with GROUP BY.

I know that Im by no means a professional, and my logic behind what Im doing is more then likely flawed in some fasion or another.

On Design:
The overall program is an Inventory program, but the part in which I am working on right now is the accounting section. The accounting section is to keep track of nothing but purchases for the business (not products, but supplies, etc that can be write offs) The categories is to keep things more organized, and the items are obviously the items youve bought. Example:

You buy computers every so often for your employees, and thats a business expense, thus should go into accounting. Since you buy new computers so often, you decide to logically create a category for em called Computers. On May 5th, 2003 you take a look at your current computers and decide its time to replace two old ones, so you go ahead and do that, using Dell computers. You now have in your accounting section:

Computers
--------------
May, 2003 - Dell Computers - $3,000.00

A week later on May 15, 2003, a computer breaks down (now a new one, one of the older ones that wasnt replaced). You get an estimate on the cost and amount of time itd take to repair it, and decide that itd just be better to replace it. So you go back to Dell as they were good to you in the past, and buy yet one more computer for the month of May. Now you have:

Computers
--------------
May, 2003 - Dell Computers - $3,000.00
May, 2003 - Dell Computers - $1,500.00

So now do you see how duplicate names come into place? Perhaps jfackler is right and this is a flawed design as you can get duplicate data. Aside from the paging problem I had (which is fixed, solution below), I was able to handle 1 million accounting items without any slow downs (except when memory constraints started to take its toll, but thats a limitation of my computer and 256mb of ram). I know I am breaking one of the rules of normalization, and that is no duplicate data, but I cant help but feel it necessary in this situation.

The Group By Solution:
As I thought about and as spebola kindly suggested, GROUP BY could be a valid solution as a quick fix for my duplicate data and paging problem. However, as I said in the last few paragraphs of my original post, I feel that it would be a mistake to take such approach.

Say the user came into a problem where they needed to go back and double check that their accounting was in order, and they bring along all of their receipts to do so. If I grouped the Computers (from the example above) by name, it would be displayed as such:

Computers
--------------
May, 2003 - Dell Computers - $4,500.00

The user looks at this, and looks at his receipts. He notices that he has two items for the month at Dell Computers but only sees one on the accounting program, but notices the total doesnt match either one of his receipts. This could cause confusion, especially if the user made 100 item purchases for the month. The user would then have to calculate up his receipts and match the two totals together. If they didnt match, hed then have to do more calculations to see which one of the receipts was not accounted for based on the difference.

Now on the other side of things, if I didnt group the items by name, the user could simply look at his two receipts and looks at the program and immediately know he recorded both of his purchases. I believe in the long wrong this is the route that would cause far less confusion and time if the user has to go back and check his expenses to insure that they were correct.

The Solution I Found:
Code: 
SELECT TOP 5 ... 
   WHERE (Date > @dateOfLastItem)
   OR (Date = @dateOfLastItem AND Description > @desOfLastItem) 
   OR (Date = @dateOfLastItem AND Description = @desOfLastItem AND Item_ID > @itemid) 
ORDER BY Date, Description, Item_ID

The suggestion given was close to the solution, I just had to add an extra OR and change a >= to >.
 
Back
Top