May 7, 2014 at 3:04 pm
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
Got that? 🙂
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
Compart_Product_Id TINYINT,
Compart_Quantity DECIMAL(7,1)
)
INSERT INTO #InvLogData
VALUES
(1,10000,10001,'S',1,NULL,0.0),
(2,10000,10001,'A',1,9,2500.0),
(3,10000,10001,'A',2,3,1200.0),
(4,10000,10001,'S',2,NULL,0.0),
(5,10000,10002,'E',1,9,2250.0),
(6,10000,10002,'E',2,3,1200.0),
(7,1001,2,'A',1,1,2500.0),
(8,1001,2,'S',1,NULL,0.0),
(9,1001,2,'A',2,3,2500.0),
(10,1001,2,'S',2,NULL,0.0),
(11,1001,2,'S',3,9,0.0),
(12,1001,2,'S',4,NULL,0.0),
(13,1001,3,'E',1,1,2375.6),
(14,1001,3,'E',2,3,1650.0),
(15,1001,3,'E',3,9,1000.0),
(16,1001,3,'E',4,1,1500.0),
(17,1001,3,'A',1,1,2375.6),
(18,1001,3,'A',2,3,1650.0),
(19,1001,3,'A',3,9,1000.0),
(16,1001,3,'A',4,1,1500.0)
--Ideal Return Row
--Manifest_Id, Status (based off the doc_type), Product_Id,total
/*
10000, 'Starting', 9, 1200.0
10000, 'Starting', 3, 2500.0
10000, 'Ending', 9, 2500.0
10000, 'Ending', 3, 1200.0
1001, 'Starting', 1, 2375.6
1001, 'Starting', 3, 1650.0
1001, 'Ending', 1, 3875.6
1001, 'Ending', 3, 1650.0
1001, 'Ending', 9, 1000.0
*/
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
May 7, 2014 at 4:07 pm
I can't understand your logic. There's no way to get to the first desired row: 10000, 'Starting', 9, 1200.0
Could you explain further?
May 7, 2014 at 6:10 pm
My apologies. That row shouldn't be returned as 1200.0 but as 2500.0. I can completely understand the confusion.
The logic is where document type (doc_type column) is 'S' for 'Starting', 'E' for Ending; grab the associated 'A' record.
May 8, 2014 at 7:30 am
logitestus (5/7/2014)
My apologies. That row shouldn't be returned as 1200.0 but as 2500.0. I can completely understand the confusion.The logic is where document type (doc_type column) is 'S' for 'Starting', 'E' for Ending; grab the associated 'A' record.
Grab it under what circumstances, and do what with it?
May 8, 2014 at 7:55 am
It's not quite clear what you are after, but I'll take a stab at it. This would join the "starts" to the "ends" and allow for any "adjustments" in between. From here you can do whatever math you might desire.
select * from
#InvLogData TheSs
Inner join
#InvLogData TheEs on TheSs.Manifest_Id = TheEs.Manifest_Id and TheEs.Doc_Type = 'E'
LEFT OUTER JOIN
#InvLogData TheAs on TheSs.Manifest_Id = TheAs.Manifest_Id and TheAs.Doc_Type = 'A'
where TheSs.Doc_Type = 'S'
May 8, 2014 at 8:40 am
SELECT a.Id,a.Manifest_Id,a.Doc_Num,a.Doc_Type,a.Compart_Id,
b.Compart_Product_Id,b.Compart_Quantity
FROM #InvLogData a
JOIN #InvLogData b ON b.Manifest_Id = a.Manifest_Id
AND b.Doc_Num = a.Doc_Num
AND b.Doc_Type = 'A'
AND a.Compart_Id = b.Compart_Id
WHERE a.Doc_Type IN ('S','E')
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2014 at 9:24 am
First off, I want to say how much I do appreciate those of you who are helping me.
Secondly, I do apologize if I performed an epic-fail on trying to explain what I am trying to do. Dang it, why can't you just read my mind? 😀
To boil the requirement down, it should be something along these lines:
I want the starting totals per product and the ending totals per product, regardless of compartment. But I cannot trust the automated starting and ending totals (this is due to an internal departmental conflict) so I have to grab the associated "adjust" records, which are actually non-automated physical measurements.
Does this help?
May 8, 2014 at 9:30 am
So there's always exactly 1S and 1E record? And potentially many As?
If so, join table to itself with 1 side being S and another being E. Create a CTE that sums up the As by manifest.
My query above does that except that I didn't use a CTE on the As part having assumed there could only be one A record per item. You can make that change pretty easily.
May 8, 2014 at 9:34 am
logitestus (5/8/2014)
First off, I want to say how much I do appreciate those of you who are helping me.Secondly, I do apologize if I performed an epic-fail on trying to explain what I am trying to do. Dang it, why can't you just read my mind? 😀
To boil the requirement down, it should be something along these lines:
I want the starting totals per product and the ending totals per product, regardless of compartment. But I cannot trust the automated starting and ending totals (this is due to an internal departmental conflict) so I have to grab the associated "adjust" records, which are actually non-automated physical measurements.
Does this help?
But I cannot trust the automated starting and ending totals
ok...so are you saying that all doc_type with 'S' or 'E' are to be ignored?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 8, 2014 at 9:37 am
There will be 1 S and 1 E record for each compartment. The "corresponding" A record will only exist if the compartment was measured. So even if there are 4 compartments with S records but those same compartments only have 2 A records, I only want return those A records.
Does that answer your question?
May 8, 2014 at 10:01 am
Fellow SSC'ers,
David's post was exactly what I was looking for! I was looking at the complete data set incorrectly (which was a bad assumption since every decision after that just compounded the problem). I was too obsessed with grouping the data.
Thank you so very much!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply