Please help with view
-
Hi,
I am pretty new to SQL but I really need to have this view working correctly soon for a project at work. I thought I had it until I noticed duplicate records in a query when I was testing it.
A brief explanation. This view is pulling from two tables in order to display the needed information. However, one of the tables that it is pulling from gets automatically updated with records every 6 min. I however, only want the most recent records at any given time.
For this particular situation, there should be no more than 36 records in my view at any given time. However, there CAN be less than 36 records in this view if a cart doesn't have a Cavity 3 in it when the records get entered. So that's OK as long as I don't have MORE than 36 records.
Each records 'Cart_Number' column should be distinct AND the Production_Date column be the most recent at all times.
I tried doing SELECT TOP 36 but it doesn't work because all 36 carts may not have a Cavity 3 at that time so it returns only the most recent records resulting in multiple records of the same Cart_Number just because it is the most recent. I don't want that.
So I am stuck and was really hoping someone would be so kind as to help me out. I hope it doesn't sound too confusing. :blush:
A little breakdown:
Basically, there are always 36 carts reporting data to this database. Each cart CAN contain 3 Cavities. A change may occur every 6 minutes to one of these Cavities. This particular view is for Cavity 3 (I created views for 1 and 2 the same way). So each Cart will usually have AT LEAST one Cavity but may not always have a 2nd and 3rd so a record may not be entered every six minutes for a particular carts cavity. So I am guessing I will have to somehow select only the most recent records (date and time) somehow instead of using "TOP".
I have attached a shot of what I have now.
Do you see how there are 2 records for Cart_Number 1, 2, 13 etc.? There should only be one of each at any given time (or none if that cart doesn't have 3 Cavities in it at the time of reporting). So the records with the older times shouldn't be in there.
I would GREATLY appreciate any help with this. I feel I'm close but now I'm not sure what the best way would be to go about it. Thanks in advance!!
-
Well, you really didn't supply quite enough table data, but if I'm understaning you correctly, you could use something like this below. Select your max info from the Transaction table and make that the inner join to the Carrier Table and select what you need.
The "ON" below will not work because I don't know what that field is based on your bitmap and could not see if this would have a negative impact on the group/max for the inner join.
Select YourColumnsHere
From tbl1to4CarrierTable CT
INNER JOIN (SELECT tblTransaction.LineNumber as Line_Number, tblTransaction.Cart as Cart_Number, MAX(tblTransaction.ProductionDate as Production_Date
FROM tblTrasnsaction Group By LineNumber, Cart) as T
ON CT.Part_Ptr = T.Cav3_PartPtr
-
I need to see the query to help you directly, but basically what you want is a sub-query that will grab the max timestamp for each of the 36 records possible, and then rejoin back to the primary table using that maxDate as part of the join.
If you can set us up sample schema and data, and show us the query you're using, as you'll find in the first link in my signature below, that'll help us tremendously in showing you how to deal with this.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thank you for your quick replies. Here is some more information.
I tried to use forum etiquette, sorry; I think I might of failed. I don't think I did too well. I will keep trying get the format looking better.
Here are the two tables in my join. This should help give you a better understanding of why I made the join in the first place. I didn't create the database or any of these tables.
tbl1to4CarrierTable (this data is entered by user (me usually))
CREATE TABLE [dbo].[tbl1to4CarrierTable](
[Part_Ptr] [smallint] IDENTITY(1,1) NOT NULL,
[Carrier_Position] [varchar](1) NOT NULL CONSTRAINT [DF_tbl1to4CarrierTable_Carrier_Position]DEFAULT ((0)),
[Part_Number] [varchar](12) NOT NULL,
[Cavity_Number] [varchar](4) NOT NULL,
[Program_Number] [smallint] NOT NULL CONSTRAINT [DF_tbl1to4CarrierTable_Program_Number] DEFAULT ((0)),
[Part_Display] [varchar](12) NULL,
[Image_Number] [varchar](12) NULL,
[Carrier_Eqv] [numeric](3, 2) NULL CONSTRAINT [DF_tbl1to4CarrierTable_Carrier_Eqv] DEFAULT ((0)),
[Part_Type] [varchar](5) NULL,
[ActivePart] [bit] NOT NULL CONSTRAINT [DF_tbl1to4CarrierTable_ActivePart] DEFAULT ((1)),
[FBE] [numeric](3, 2) NULL,
[Cost_Per_Part] [money] NULL
) ON [PRIMARY]
GO
tblTransaction (this data is entered by a robot program - there's many more columns but I don't need that data) One record contains the info on one cart (there are 36 carts) and each cart holds 4 cavities. It inserts a record for each cart every 6 minutes. But the table only holds 36 records at a time (this part is out of my scope; I think it overwrites or something) It is like a carousel. When a cart goes by, a record gets entered. So actually to clarify, the time between it inserts the same cart again will be 6 min.
CREATE TABLE [dbo].[tblTransaction](
[ProductionDate] [datetime] NOT NULL,
[Cart] [smallint] NOT NULL,
[ProgramNumber] [smallint] NOT NULL,
[LoginUser] [varchar](6) NOT NULL,
[LineNumber] [tinyint] NOT NULL,
[Shift] [tinyint] NOT NULL,
[Cav1_Code] [smallint] NOT NULL,
[Cav1_PartPtr] [smallint] NOT NULL,
[Cav2_Code] [smallint] NOT NULL,
[Cav2_PartPtr] [smallint] NOT NULL,
[Cav3_Code] [smallint] NOT NULL,
[Cav3_PartPtr] [smallint] NOT NULL,
[Cav4_Code] [smallint] NOT NULL,
[Cav4_PartPtr] [smallint] NOT NULL,
[RecordValue] [int] NOT NULL
) ON [PRIMARY]
GO
So the Cav1_PartPtr, Cav2_PartPtr, etc.. correspond to the Part_Ptr in the top table, therefore providing me with the data I need (Part Number, FBE, Cavity id number etc.) One of the tables has some of the data and the other table has the rest of the data I need to make these views. So from the best of my knowledge, I really need the join.
So this is the query I made for the view(s):
SELECT tblTransaction.LineNumber AS Line_Number, tblTransaction.Cart AS Cart_Number,
tblTransaction.ProductionDate AS Production_Date,
tbl1to4CarrierTable.Part_Number AS Part_Number,
tbl1to4CarrierTable.Cavity_Number AS Cavity,
tbl1to4CarrierTable.Carrier_Position AS Cavity_Position,
tbltransaction.ProgramNumber AS Program_Number, tbl1to4CarrierTable.FBE AS FBE
FROM tbltransaction
INNER JOIN tbl1to4carriertable
ON tbl1to4CarrierTable.Part_Ptr=tblTransaction.Cav3_PartPtr
ORDER BY tblTransaction.ProductionDate DESC
I made one of these views for Each cavity and then I created a final view to bring them all together just by using UNION ALL. This seems like it is everything I want EXCEPT for the fact that I somehow need to only include the most recent records at all times. So for this view, I only need info for the carts that have 3 cavities. There is always 36 carts going around but not always 3 cavities in those carts so this view should only show info on the 3rd cavity in any given cart.
For example, if cart 20 has 2 cavities, Cav1_PartPtr and Cav2_PartPtr will have a value corresponding to the other table. Cav3_PartPtr will have nothing.
If cart 23 has only 1 cavity, then Cav1_PartPtr will have values and Cav2_PartPtr and Cav3_PartPtr will not.
Hopefully I have explained it well. I know it may seem confusing or maybe I'm making it sound too complicated so I hope not. I thought of maybe having a sub query as well as a join but I got a little lost. I am still learning and am new to this. Thanks in advance for any help or guidance in the right direction.
-
We're getting there. Sorry this is taking a few iterations, I know you're trying.
A few questions. While trying to slap together some very limited sample data into these tables for testing, I noticed there's a Cavity_Number field in tbl1to4Carrier. Why is that, if a part is simply a part? Or do you need to join on Cavity_Number here as well to make sure your data is correct?
Part of what you're fighting in this design is non-normalized data. 3rd Normal Form means you don't repeat an entity within a row. In this case, Cavities 1 through 4 are entities and should be in a separate structure. However, you're stuck with what you've got.
So, my personal approach would be a little different. First, I'd find the last record before I started twisting things around, like so:
SELECT
t.*
FROM
tblTransaction AS t
JOIN
(SELECT Cart, MAX(ProductionDate) AS MaxDate
FROM tblTransaction
GROUP BY Cart
) AS drv
ONt.cart = drv.Cart
AND t.ProductionDate = drv.ProductionDate
That will always give you the most recent 36 carts in the table.
From there, we can get part data:
SELECT
t.*,
Cav1Prt.Part_Display AS pd1,
Cav1Prt.Image_Number AS in1,
Cav2Prt.Part_Display AS pd2,
Cav2Prt.Image_Number AS in2,
Cav3Prt.Part_Display AS pd3,
Cav3Prt.Image_Number AS in3,
Cav4Prt.Part_Display AS pd4,
Cav4Prt.Image_Number AS in4
FROM
tblTransaction AS t
JOIN
(SELECT Cart, MAX(ProductionDate) AS MaxDate
FROM tblTransaction
GROUP BY Cart
) AS drv
ONt.cart = drv.Cart
AND t.ProductionDate = drv.ProductionDate
JOIN
tbl1to4CarrierTable AS Cav1Prt
ONCav1Prt.Part_Ptr = Cav1_PartPtr
JOIN
tbl1to4CarrierTable AS Cav2Prt
ONCav2Prt.Part_Ptr = Cav2_PartPtr
JOIN
tbl1to4CarrierTable AS Cav3Prt
ONCav3Prt.Part_Ptr = Cav3_PartPtr
JOIN
tbl1to4CarrierTable AS Cav4Prt
ONCav4Prt.Part_Ptr = Cav4_PartPtr
From there, it'll depend on how you want your results. Do you want to show the 4 cavities on the same line or do you want the 'unpivoted' look that you get from doing the union query, with missing rows instead of empty entries for nulls?
Edit: Forgot column names, it'll whine from code pre-edit. Code is still untested due to lack of data.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Wow, thank you so much. I am going to try what you provided as soon as I can. Yeah this database probably is a mess. But like you said, this is what I have to work with now. It could be so much better. But I was kind of thrown into this and this view is needed for a project so that we know what exactly is on the line at all times in one central view/table.
But to answer your question:
I noticed there's a Cavity_Number field in tbl1to4Carrier. Why is that, if a part is simply a part? Or do you need to join on Cavity_Number here as well to make sure your data is correct?
Each cavity has a number to it; which is in the format of H01, H02, etc and that is what I enter into the tbl1to4CarrierTable. The column numbering of Cav1, Cav2 and Cav3 in the tblTransaction table is actually the positioning of the cavity on the cart it doesn't know what the cavity number in that position is, just that one is either present or not present in that position. However, I need to know the cavity number (H01, H02 etc) and its position and the part that is being poured into the cavity. One part number can have several cavities (or molds) so we can produce much more of that part number.
This 'cavity' is really a mold in which a part will come from (in my case foam). A cart can hold up to 4 cavities (because it has four connections). But one cavity can be as big as the whole cart, so it would just be considered as Cav1 in the tblTransaction table and position 1 in the tbl1to4CarrierTable.
A sample record that would reference each other in these tables would be:
(I'm only using columns relevant to this issue)
tblTransaction
Cart '23', ProgramNumber '10', LineNumber '1', Cav1_PartPtr '235', Cav2_PartPtr '100', Cav3_PartPtr '420'
tbl1to4CarrierTable
Part_Ptr '235', Carrier_Position '1', Part_Number '1567201' , Cavity_Number 'H01', Program_Number '10'
Part_Ptr '100', Carrier_Position '2', Part_Number '1304717' , Cavity_Number 'H01', Program_Number '10'
Part_Ptr '420', Carrier_Position '3', Part_Number '6161433' , Cavity_Number 'H02', Program_Number '10'
See, one record of the tblTransaction table can reference several tbl1to4CarrierTable records. This may help you get a better understanding of what type of data are in these records. I really appreciate all your help.
-
jd 67003 (2/23/2012)
Wow, thank you so much. I am going to try what you provided as soon as I can. Yeah this database probably is a mess. But like you said, this is what I have to work with now. It could be so much better. But I was kind of thrown into this and this view is needed for a project so that we know what exactly is on the line at all times in one central view/table.If you're going to need this information realtime continuously, once we get the logic and results desired straightened out we're going to want to look into your schema and indexing to keep this optimized. With the amount of data that's going to end up in tblTransaction a CROSS APPLY TOP 1 DESC technique with a very particular index will probably give you the best performance long-term. Well, one thing at a time.
But to answer your question:
I noticed there's a Cavity_Number field in tbl1to4Carrier. Why is that, if a part is simply a part? Or do you need to join on Cavity_Number here as well to make sure your data is correct?
Each cavity has a number to it; which is in the format of H01, H02, etc and that is what I enter into the tbl1to4CarrierTable. The column numbering of Cav1, Cav2 and Cav3 in the tblTransaction table is actually the positioning of the cavity on the cart it doesn't know what the cavity number in that position is, just that one is either present or not present in that position. However, I need to know the cavity number (H01, H02 etc) and its position and the part that is being poured into the cavity. One part number can have several cavities (or molds) so we can produce much more of that part number.
Ah! Okay, so they actually represent two completely different things. I see now. Wanted to make sure I wasn't ignoring pieces of the logic that were actually necessary. Hm. What you describe there sounds like something I'd want to sit down with business and redesign. Well, hopefully you'll be able to clear up anything I (or others) misunderstand. I can be rather dense sometimes.
A sample record that would reference each other in these tables would be:
(I'm only using columns relevant to this issue)
tblTransaction
Cart '23', ProgramNumber '10', LineNumber '1', Cav1_PartPtr '235', Cav2_PartPtr '100', Cav3_PartPtr '420'
tbl1to4CarrierTable
Part_Ptr '235', Carrier_Position '1', Part_Number '1567201' , Cavity_Number 'H01', Program_Number '10'
Part_Ptr '100', Carrier_Position '2', Part_Number '1304717' , Cavity_Number 'H01', Program_Number '10'
Part_Ptr '420', Carrier_Position '3', Part_Number '6161433' , Cavity_Number 'H02', Program_Number '10'
If you take a look at the schema you provided for tblTransaction, you'll note that the record you provided doesn't work. NOT NULL for every column.
Also, if you can put them in this format, it helps a LOT, because then we can use it directly:
Insert Into tblTransaction
(Cart, ProgramNumber, LineNumber, Cav1_PartPtr, Cav2_PartPtr, Cav3_PartPtr)
VALUES
(23, 10, 1, 235, 100, 420)
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
wow, so many great post here.Thanks I learned a lot.
-
Sorry I haven't responded in a while. I had a death in the family. But I wanted to check back in because I do still really need a solution and please know I really appreciate all your help and advice. In regards to you mentioning sitting down with the business and redesigning, I agree. But it is a bit out of my scope of knowledge right now. It might not be so bad if there wasn't a custom program connecting to this database. I am still learning about the program and the ones who developed it are no longer with the company. I was just really hoping I could get at least this view up for now and in the meantime I plan on trying to get this whole system redesigned.
So I am pretty limited right now on what I can do. If you are still willing to help me out, I can provide you with more information. I can get you more detail. Because I didn't include all of the columns because I figured since they were irrelevant to me that it wouldn't be of much help and would minimize any confusion. But those table have more columns and those records have more data. I was just showing you the columns/data I want. But you are much more skilled with SQL than I am so maybe I had the wrong idea. But yeah, still need to get this working..bad :/ Thanks for all your help though regardless.
-
Not a problem. Sorry, this fell off my radar for a bit and I got sucked into a project for a week so I hadn't reviewed old threads for a while. I'm more than willing to continue to help you, but if you can provide the schema/data in a format I can directly consume for testing, it'll help both of us out in the long run.
I've put this thread on email notification so it'll let me know when you get back.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply