USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_get_segment_values
============================================================================
 Returns dimensional (segmented) numeric facts from a filing. These are
 the rows with dimn > 0 -- the ones that have axis/member breakdowns,
 e.g. revenue by geographic region or by product line.

 Important caveat
 ----------------
 sec_fsnds.dim carries only a dimID column. The axis/member text that
 would describe what each dimID actually MEANS (e.g.
 "us-gaap:StatementBusinessSegmentsAxis / msft:ProductivityAndBusinessProcessesMember")
 is NOT stored in this database. This SP can tell you WHICH rows are
 dimensional and their dimID / dimn, but the axis/member labels are
 not available here.

 If you need the axis/member text you'll need to augment the database
 with a dim_text table keyed by dimID. The original SEC raw dim.tsv
 contains the text and can be loaded separately.

 Parameters
 ----------
   @adsh       accession, required
   @tagFilter  optional LIKE pattern on tag name, e.g. 'Revenue%'
   @top        max rows (default 1000)

 Shape
 -----
   adsh | stmt | tag | plabel | ddate | qtrs | dimID | dimn | uom | value
============================================================================
*/

IF OBJECT_ID('dbo.usp_sec_get_segment_values', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_sec_get_segment_values;
GO

CREATE PROCEDURE dbo.usp_sec_get_segment_values
    @adsh      VARCHAR(20),
    @tagFilter NVARCHAR(400) = NULL,
    @top       INT           = 1000
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @subID INT = (SELECT subID FROM dbo.sub WHERE adsh = @adsh);
    IF @subID IS NULL
    BEGIN
        RAISERROR('No sub row for adsh=%s', 16, 1, @adsh);
        RETURN;
    END;

    SELECT TOP (@top)
         @adsh AS adsh
        ,ls.stmt
        ,t.tag
        ,p.plabel
        ,n.ddate
        ,n.qtrs
        ,n.dimID
        ,n.dimn
        ,u.uom
        ,IIF(p.negating = 1, n.value * -1, n.value) AS value
    FROM dbo.pre p
    INNER JOIN dbo.lkp_stmt ls ON ls.stmtID = p.stmtID
    INNER JOIN dbo.num n
      ON  n.subID = p.subID
      AND n.tagID = p.tagID
    INNER JOIN dbo.tag     t ON t.tagID = n.tagID
    INNER JOIN dbo.lkp_uom u ON u.uomID = n.uomID
    WHERE p.subID = @subID
      AND n.dimn  > 0                     -- dimensional rows only
      AND n.value IS NOT NULL
      AND (@tagFilter IS NULL OR t.tag LIKE @tagFilter)
    ORDER BY ls.stmtID, p.report, p.line, n.dimID, n.ddate;
END;
GO

/*
============================================================================
 Example calls
============================================================================

-- All segmented rows from a filing
EXEC dbo.usp_sec_get_segment_values @adsh = '0000320193-23-000106';

-- Only segmented revenue breakdowns
EXEC dbo.usp_sec_get_segment_values
     @adsh      = '0000320193-23-000106',
     @tagFilter = N'Revenue%';
*/
