Link to home
Start Free TrialLog in
Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America

asked on

Code only returns Headers no records.......what can I look for

When a Stored Procedure Executes but only brings back the headers. I would assume that the first thing to check is the Parameters and filters.......I do not think these are factors as they are so few. Then I would think it would be something in the links that are broken. Does anyone see anything that might be suspect in this Stored Procedure.

USE [DATA_04]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_SalesSummaryAdopted_sproc_RB]    Script Date: 10/01/2013 09:31:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_SalesSummaryAdopted_sproc_RB] (@Cus_no_Start varchar(12), @Cus_no_End varchar(12)) AS

-- for testing

-- drop table #SalesSummary
-- declare @Cus_no_Start varchar(12), @Cus_no_End varchar(12)
-- set @Cus_no_Start = '0'
-- --set @Cus_no_Start = '1505'
-- set @Cus_no_End = '1999'

-- end testing

----------------------------------------------------
-- front zero fill  customer numbers
----------------------------------------------------
If @Cus_no_Start <> '*'
BEGIN
  set @Cus_no_Start = right('000000000000' + @Cus_no_Start,12)
  set @Cus_no_End   = right('000000000000' + @Cus_no_End,12)
END
-- print @Cus_no_Start
-- print @Cus_no_End

----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate       = CurrWeekEndDate,
   @CurrToDateYTD       = CurrToDateYTD,
   @CurrFromDateYTD      = CurrFromDateYTD,
   @CurrFromDate       = CurrFromDate,
   @CurrToDate             = CurrToDate,
   @CurrToDateYTD       = CurrToDateYTD,
   @CurrWeekBeginDate       = CurrWeekBeginDate,
   @CurrWeekEndDate       = CurrWeekEndDate,
   @PriorWeekBeginDate       = PriorWeekBeginDate,
   @PriorWeekEndDate       = PriorWeekEndDate,
   @PriorFromDateYTD       = PriorFromDateYTD,
   @PriorToDateYTD       = PriorToDateYTD,
   @PriorFromDate       = PriorFromDate,
   @PriorToDate       = PriorToDate
from dbo.Rpt_ReportingDates
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select
   @CurrentWeek       = CurrentWeek,
   @NumberOfWeeks       = NumberOfWeeks,
   @CurrToDate_Period       = CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary
(
      --[data_source] varchar(12),--  what database is the data coming from  macola or rams?
      PerType varchar(18) NULL,
      Terr char(2) NULL,
      Item_no varchar(15),
      Gross_Sales decimal(38,2) NULL,
      Cost decimal(38,2) NULL,
      prod_cat Varchar (12),
      Cus_No Varchar (12)
      
      
)


----------------------------------------------------
-- insert for MTD Current and Prior
----------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDate AND @CurrToDate
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,  -- added RB
    ARCUSFIL_SQL.Cus_No    
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No

------------------------------------------------------
---- insert for MTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '2 - MTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,

      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No
------------------------------------------------------
---- insert for YTD Current and Prior
------------------------------------------------------
INSERT INTO #SalesSummary
SELECT

      '3 - YTD Current',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @CurrFromDateYTD AND @CurrToDateYTD
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
      OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
      OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
      WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No

INSERT INTO #SalesSummary
SELECT

      '3 - YTD Prior',
      ARCUSFIL_SQL.Terr,
      OELINHST_SQL.Item_no,
      Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN sls_amt ELSE 0 END),
      Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDateYTD AND @PriorToDateYTD
         THEN cost_amt ELSE 0 END),
    IMITMIDX_SQL.prod_cat,
    ARCUSFIL_SQL.Cus_No
FROM OELINHST_SQL
      INNER JOIN OEHDRHST_SQL ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type AND
   OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no AND
   OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
      INNER JOIN ARCUSFIL_SQL ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no
      LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL ON
        OELINHST_SQL.item_no = IMITMIDX_SQL.item_no  -- added RB
WHERE  
      (@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat, ARCUSFIL_SQL.Cus_No
----------------------------------------------------
-- final select
----------------------------------------------------

SELECT

      
      SS.PerType,
      CM.CM_SELLZONE,
      @CurrWeekEndDate as CurrWeekEndDate,
      @CurrToDate as CurrToDate,
      @CurrToDateYTD as CurrToDateYTD,
      @CurrentWeek as CurrentWeek,
      @NumberOfWeeks as NumberOfWeeks,
      @CurrToDate_Period as CurrToDate_Period,
      @CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
      Rpt_Territory.Terr_name,
      Rpt_Territory.Report_Sort,
      SS.Terr,
      SS.Item_no,
      Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
      sum(isnull(SS.[Gross_Sales],0))  AS Gross_Sales,
      sum(isnull(SS.[Cost],0))  AS Cost,
      PD.PRD_A_GLTYPE   -- Added RB
      --,sum(isnull(SS.[Prior_Gross_Sales],0))  AS Prior_Gross_Sales,sum(isnull(SS.[Prior_Cost],0))  AS Prior_Cost
FROM #SalesSummary AS SS
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                  ON PD.PRD_PRODUCT = SS.Prod_cat
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                         ON CM.CM_CUSTKEY = SS.Cus_No
      INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
      LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no

WHERE Rpt_Territory.ActiveFlag = 'Y'
GROUP BY SS.PerType,
     CM.CM_SELLZONE,
       Rpt_Territory.Terr_name,
       Rpt_Territory.Report_Sort,
       SS.Terr,
       SS.Item_no,
          Rpt_TerritoryAdopted.Terr_id,
          PD.PRD_A_GLTYPE   -- Added RB
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of UnifiedIS
UnifiedIS
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RUA Volunteer2?
RUA Volunteer2?
Flag of United States of America image

ASKER

It was a join but both solutions were helpful thank you.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
Thanks for the split.  Good luck with your project.  -Jim