-SET &ECHO = 'ALL';
SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACESTAMP=OFF
SET XRETRIEVAL=ON

ENGINE SQLMSS SET DEFAULT_CONNECTION DW-DB
SQL SQLMSS PREPARE SQLOUT FOR
SELECT a.GTIN, a.WMItemDesc, a.WMAuthor, a.VendorCode, a.VendorName, a.InitialOnSaleDate, a.WMDeptCategoryDesc, a.WMDeptSubCategoryDesc
    , a.PeriodSoldAmt, a.PeriodSoldQty, a.PeriodConsumerAmtRank
    , lw.LWSoldAmt, lw.LWSoldQty, lw.LWConsumerAmtRank
    , bs.LWTraitSoldAmt, bs.LWTraitSoldQty, bs.LWNbrTraitedStores, bs.LWTraitSoldAmtPerStore, bs.LWTraitSoldQtyPerStore
    , bt.Trait, bt.LWSoldAmtbyTrait, bt.LWSoldQtybyTrait, bt.LWNbrStoresbyTrait, bt.LWTraitRef
    , x.TraitDesc, x.TraitDeptSubCatgDesc, x.ANDTraitCode
FROM (	SELECT g.GTIN, i.WMItemDesc, i.WMAuthor, im.VendorCode, vm.VendorName, im.InitialOnsaleDate, i.WMDeptCategoryDesc, i.WMDeptSubCategoryDesc
            , SUM(ws.SoldConsumerAmt) as PeriodSoldAmt
            , SUM(ws.SoldQty) as PeriodSoldQty
            , ROW_NUMBER() OVER (ORDER BY SUM(ws.SoldConsumerAmt) DESC, SUM(ws.SoldQty) DESC) as PeriodConsumerAmtRank
        FROM (	SELECT ti.GTIN
                FROM Sandbox.[lhe\barnesl].WCJDATraitItem ti WITH (NoLock) 
                WHERE ((ti.RetailerWEDateStart BETWEEN CONVERT(DATE,CAST(&StartWEDate as VARCHAR),121) AND CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)) 
						OR (ti.RetailerWEDateEnd BETWEEN CONVERT(DATE,CAST(&StartWEDate as VARCHAR),121) AND CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)))
                GROUP BY ti.GTIN ) g
        INNER JOIN DW.dbo.WMWeeklyStat ws WITH (NoLock) ON g.GTIN = ws.GTIN AND ws.RetailerWEDate BETWEEN CONVERT(DATE,CAST(&StartWEDate as VARCHAR),121) AND CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)
        INNER JOIN DW.dbo.WMItem i WITH (NoLock) ON ws.GTIN = i.GTIN AND ws.RetailerWEDate BETWEEN CONVERT(DATE,CAST(&StartWEDate as VARCHAR),121) AND CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)
        LEFT OUTER JOIN BDS.dbo.ItemMaster im WITH (NoLock) ON i.GTIN = LEFT(im.ISBN13,12)
        LEFT OUTER JOIN BDS.dbo.VendorMaster vm WITH (NoLock) ON im.PurchaseOfficeCode = vm.PurchaseOfficeCode AND im.VendorCode = vm.VendorCode
        GROUP BY g.GTIN, i.WMItemDesc, i.WMAuthor, im.VendorCode, vm.VendorName, im.InitialOnsaleDate, i.WMDeptCategoryDesc, i.WMDeptSubCategoryDesc ) a
LEFT OUTER JOIN (
        SELECT ws.GTIN
            , SUM(ws.SoldConsumerAmt) as LWSoldAmt
            , SUM(ws.SoldQty) as LWSoldQty
            , ROW_NUMBER() OVER (ORDER BY SUM(ws.SoldConsumerAmt) DESC, SUM(ws.SoldQty) DESC) as LWConsumerAmtRank
        FROM DW.dbo.WMWeeklyStat ws WITH (NoLock)
        WHERE ws.RetailerWEDate = CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)
        GROUP BY ws.GTIN ) lw ON a.GTIN = lw.GTIN
LEFT OUTER JOIN (
        SELECT sti.GTIN
            , SUM(ws.SoldConsumerAmt) as LWTraitSoldAmt
            , SUM(ws.SoldQty) as LWTraitSoldQty
            , COUNT(DISTINCT sti.StoreNbr) as LWNbrTraitedStores
            , ISNULL(CAST(SUM(ws.SoldConsumerAmt) as FLOAT) / NULLIF(COUNT(DISTINCT sti.StoreNbr),0),0) as LWTraitSoldAmtPerStore
            , ISNULL(CAST(SUM(ws.SoldQty) as FLOAT) / NULLIF(COUNT(DISTINCT sti.StoreNbr),0),0) as LWTraitSoldQtyPerStore
        FROM Sandbox.[lhe\barnesl].WCStoreTraitItem sti WITH (NoLock)
        LEFT OUTER JOIN DW.dbo.WMWeeklyStat ws WITH (NoLock) ON ws.WMStoreNbr = sti.StoreNbr AND ws.RetailerWEDate = sti.RetailerWEDate AND ws.GTIN = sti.GTIN
        WHERE sti.RetailerWEDate = CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)
        GROUP BY sti.GTIN ) bs ON lw.GTIN = bs.GTIN
LEFT OUTER JOIN (
        SELECT sti.GTIN, sti.Trait
            , SUM(ws.SoldConsumerAmt) as LWSoldAmtbyTrait
            , SUM(ws.SoldQty) as LWSoldQtybyTrait
            , COUNT(DISTINCT sti.StoreNbr) as LWNbrStoresbyTrait
            , CONVERT(VARCHAR,CONVERT(MONEY,SUM(ws.SoldConsumerAmt),1)) + ' (' + CAST(COUNT(DISTINCT ws.WMStoreNbr) as VARCHAR) + ')' as LWTraitRef
        FROM Sandbox.[lhe\barnesl].WCStoreTraitItem sti WITH (NoLock)
        LEFT OUTER JOIN DW.dbo.WMWeeklyStat ws WITH (NoLock) ON ws.WMStoreNbr = sti.StoreNbr AND ws.RetailerWEDate = sti.RetailerWEDate AND ws.GTIN = sti.GTIN
        WHERE sti.RetailerWEDate = CONVERT(DATE,CAST(&EndWEDate as VARCHAR),121)
        GROUP BY sti.GTIN, sti.Trait ) bt ON bs.GTIN = bt.GTIN
LEFT OUTER JOIN Sandbox.[readerlink\lbarnes].WCJDATraitXRef x WITH (NoLock) ON bt.Trait = x.TraitCode
        ORDER BY GTIN, Trait
END
TABLE FILE SQLOUT
SUM 
     MIN.LWTraitRef AS 'MIN,LWTraitRef'
BY  LOWEST TraitDeptSubCatgDesc
BY  LOWEST GTIN
BY  LOWEST WMItemDesc
BY  LOWEST WMAuthor
BY  LOWEST VendorCode
BY  LOWEST VendorName
BY  LOWEST InitialOnSaleDate
BY  LOWEST WMDeptCategoryDesc
BY  LOWEST WMDeptSubCategoryDesc
BY  LOWEST PeriodSoldAmt
BY  LOWEST PeriodSoldQty
BY  LOWEST PeriodConsumerAmtRank
BY  LOWEST LWSoldAmt
BY  LOWEST LWSoldQty
BY  LOWEST LWConsumerAmtRank
BY  LOWEST LWTraitSoldAmt
BY  LOWEST LWTraitSoldQty
BY  LOWEST LWNbrTraitedStores
BY  LOWEST LWTraitSoldAmtPerStore
BY  LOWEST LWTraitSoldQtyPerStore
ACROSS LOWEST Trait
ON TABLE SET BYDISPLAY ON 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND 'BYTOC 1'
END

quote:

The PRINT command generates a report that has a single line for each record retrieved from the data source after screening out those that fail IF or WHERE tests. When PRINT is used in conjunction with an ACROSS phrase, many of the generated columns may be empty. Those columns display the missing data symbol.

To avoid printing such a sparse report, you can use the SET ACROSSPRT command to compress the lines in the report. The number of lines is reduced within each sort group by swapping non-missing values from lower lines with missing values from higher lines, and then eliminating any lines whose columns all have missing values.

Because data may be moved to different report lines, row-based calculations such as ROW-TOTAL and ACROSS-TOTAL in a compressed report are different from those in a non-compressed report. Column calculations are not affected by compressing the report lines.

SET ACROSSPRT = COMPRESS

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

SET HIDENULLACRS=ON

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server