Geek Answers Handbook

Problem combining 3 different elements of SQL tables and getting the sum

I'm having problems with the system that my dad uses for reporting in his company. I did not build it, but I try my best to hug it. This is ASP and I am not so good at it (or am writing sql for that matter).

Basically, I have three tables that I work with: Item, ItemHistory and Devices. In the tables I'm trying to do,

  • Get the items that the user worked with through the "serialnumber" column from the "Item History" table
  • Compare this serial number with the "serialnumber" column in the "Item" table to get the type of device (from the device column).
  • Use the device type to get the specified numeric value from the dots column in the Devices table
  • Save this value and add it to any other values ​​that were dropped by the query, and then display the final value.

* See update *

As you can see, it would be much simpler if Device Type were included in the ItemHistory table, but this was not so. I tried to JOIN the tables, but keep getting errors (which due to my lack of knowledge is pretty accurate). The request is based on the date range and user (from the ItemHistory table), but I think I figured it out. I just can't get it to reference all tables and add “dots” together.

Any help is much appreciated! If you need more information, let me know. Thank you in advance.

Update

So, basically I am having problems with the fact that the request continues to return with "Either BOF, or EOF - True, or the current record has been deleted. The requested operation requires the current record." which, it seems to me, is caused by the fact that it simply adds numbers to the columns of points.

I think the problem with my last question is that it seemed to me that there are “Points” on each device, but in fact there are a certain number of devices (say, five), and there may be several serial numbers, all pointing to single device. I think this is where the request got confused (but I could be wrong). In addition, my sql statement looks something like the one small bobbytables below, with the exception of my own variables added to.

This is what the database looks like.

Table element table

Date | SerialNumber | DeviceType | User ----------|--------------|-------------|------- 11/1/12 | 123-456-789 | NULL | Bill 11/1/12 | 456-123-987 | NULL | Bill 11/1/12 | 987-654-321 | NULL | Bill 11/1/12 | 216-897-631 | NULL | Bill 11/1/12 | 874-547-277 | NULL | Bill

Position table

  SerialNumber | DeviceType -------------|------------- 123-456-789 | Device1 456-123-987 | Device2 987-654-321 | Device3 216-897-631 | Device1 874-547-277 | Device2

Device table

  Device | Points ----------|---------- Device1 | 20 Device2 | 25 Device3 | 40

And I hope that the solution will be

  User | Points --------|---------- Bill | 130
+6
source share
1 answer

You can summarize the points together and group by user ID:

 SELECT [User], SUM(Points) AS Points FROM ItemHistory IH INNER JOIN Item I ON IH.SerialNumber = I.SerialNumber INNER JOIN Devices D ON I.DeviceType = D.Device WHERE IH.[Date] = @YourDateVariable GROUP BY [User]
+1
source

More articles:


All Articles