Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Grouping on 2 levels but only returning conditional data Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 3:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:08 AM
Points: 277, Visits: 329
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.
Post #1568703
Posted Wednesday, May 7, 2014 4:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 3,806, Visits: 8,560
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1568726
Posted Wednesday, May 7, 2014 6:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:08 AM
Points: 277, Visits: 329
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.
Post #1568737
Posted Thursday, May 8, 2014 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:44 AM
Points: 48, Visits: 157
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?

Post #1568884
Posted Thursday, May 8, 2014 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:45 PM
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'
Post #1568900
Posted Thursday, May 8, 2014 8:40 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 6,960, Visits: 7,088
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.

Post #1568934
Posted Thursday, May 8, 2014 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:08 AM
Points: 277, Visits: 329
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?
Post #1568976
Posted Thursday, May 8, 2014 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:45 PM
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.
Post #1568979
Posted Thursday, May 8, 2014 9:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 1,917, Visits: 19,609
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
Post #1568983
Posted Thursday, May 8, 2014 9:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:08 AM
Points: 277, Visits: 329
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?
Post #1568987
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse