Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Grouping on 2 levels but only returning conditional data


Data Grouping on 2 levels but only returning conditional data

Author
Message
logitestus
logitestus
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 505
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18142
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?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
logitestus
logitestus
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 505
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.
SQL is delicious
SQL is delicious
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 174
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?
eblinn 33755
eblinn 33755
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 6
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'
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7966 Visits: 9425

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.


logitestus
logitestus
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 505
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? :-D

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?
eblinn 33755
eblinn 33755
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 6
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.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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? :-D

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

logitestus
logitestus
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 505
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search