none
Sql 2008R

    Question

  • Hi There

    Please Help!

    I have a view that only display data upto the 01/08/2014 and thereafter nothing  why would it be. I Have checked all the tables it is getting data from and they all ok

    Please help Urgent

    Monday, August 11, 2014 9:17 AM

Answers

All replies

  • Check all criteria in the definition of the view.

    Many Thanks & Best Regards, Hua Min

    Monday, August 11, 2014 9:25 AM
  • Hi There

    My view is only displaying data upto the 01/08/2014 and nothing thereafter , what could be the problem

    • Merged by Shanky_621MVP Monday, August 11, 2014 10:07 AM duplicate thread
    Monday, August 11, 2014 9:29 AM
  • You may check your VIEW definition, there must be a condition to filter the records for the date 01/08/2014.
    Monday, August 11, 2014 9:30 AM
  • Hi Thanks for the reply how do I do that
    Monday, August 11, 2014 9:54 AM
  • Hi There

    How do I do that

    Monday, August 11, 2014 9:55 AM
  • What is WHERE condition inside your View definition?

    -Vaibhav Chaudhari

    Monday, August 11, 2014 9:58 AM
  • sp_helptext 'viewname' and check the definition for any filter.

    Monday, August 11, 2014 10:02 AM
  • Hi There

    See below View

    CREATE

    VIEW [dbo].[DailyMarginReport]

    AS

    SELECT

         dbo.ArTrnDetail.TrnYear, dbo.ArTrnDetail.TrnMonth, dbo.ArTrnDetail.Mass, dbo.ArTrnDetail.NetSalesValue AS Sales, dbo.ArTrnDetail.CostValue,

                          dbo

    .TblCustomerClass.Description, dbo.SalProductClass.Description AS DetProdGrp, dbo.SalProductClassDes.Description AS SubDesc,

                          dbo

    .SalProductClassDes.PCAT AS MasterGroup,(CASE WHEN RIGHT(ArTrnDetail.StockCode, 3) = '789' THEN (QtyInvoiced * 2) ELSE QtyInvoiced END) AS Qty,

                         

    RIGHT(LEFT(dbo.ArTrnDetail.StockCode, 3), 1) AS KitType, CONVERT(varchar(4), dbo.ArTrnDetail.GlYear) + RIGHT('0' + CONVERT(VARCHAR, dbo.ArTrnDetail.GlPeriod),

                          2

    ) AS Period, dbo.ArCustomer.Name, dbo.ArCustomer.ShipToAddr4 AS Town, dbo.ArCustomer.ShipToAddr5 AS Province, dbo.InvMaster.Description AS Product,

                          dbo

    .InvMaster.AlternateKey2 AS Grade, ronnieb.VT_Bed_Range.RangeName AS Range, dbo.InvMaster.StockCode, dbo.VT_Reps.Name AS Rep,

                         

    RIGHT(LEFT(dbo.SalGlIntSale.SalesGlCode, 4), 2) AS Division,(CASE WHEN LEFT(SalProductClass.ProductClass, 2) = 'FQ' THEN RIGHT(SalProductClass.ProductClass,


                           1

    ) ELSE '' END) AS Size, dbo.TblArTerms.DiscPct / 100 * dbo.ArTrnDetail.NetSalesValue AS Disc,

                          dbo

    .VT_VariableCosts.Rebate * dbo.ArTrnDetail.NetSalesValue AS Rebates,

                         

    CASE WHEN Insurables.AlphaValue = 'INI' THEN dbo.ArTrnDetail.NetSalesValue * .0056 ELSE 0 END AS CreditInsurance,

                         

    CASE WHEN RIGHT(LEFT(dbo.ArTrnDetail.ProductClass, 3), 2) = 'QD' OR


                         

    RIGHT(LEFT(dbo.ArTrnDetail.ProductClass, 3), 2)

                         

    = 'QG' THEN dbo.ArTrnDetail.NetSalesValue * dbo.VT_Reps.CommissionPct / 100 * 2 ELSE dbo.ArTrnDetail.NetSalesValue * dbo.VT_Reps.CommissionPct / 100 END + CASE


                          

    WHEN RIGHT(LEFT(dbo.ArTrnDetail.ProductClass, 3), 2) = 'QD' OR


                         

    RIGHT(LEFT(dbo.ArTrnDetail.ProductClass, 3), 2) = 'QG' AND

                          dbo

    .ArTrnDetail.Salesperson = '069' THEN dbo.ArTrnDetail.NetSalesValue * dbo.VT_Reps.CommissionPct / 100 * - 1 ELSE 0 END AS Commissions,

                         

    REPLACE(REPLACE(REPLACE(dbo.SorMasterRep.ShippingInstrs, ' ', '<>'), '><', ''), '<>', ' ') AS ShippingInstrs,

                         

    CASE WHEN (dbo.SorMasterRep.ShippingInstrs <> 'VF' AND dbo.SorMasterRep.ShippingInstrs <> 'OT') AND LEFT(dbo.ArTrnDetail.ProductClass, 2)

                         

    = 'FE' THEN Economy / 100 * dbo.ArTrnDetail.NetSalesValue ELSE 0 END AS ExtEcon, CASE WHEN (dbo.SorMasterRep.ShippingInstrs <> 'VF' AND

                          dbo

    .SorMasterRep.ShippingInstrs <> 'OT') AND LEFT(dbo.ArTrnDetail.ProductClass, 2) = 'FQ' THEN Quilt / 100 * dbo.ArTrnDetail.NetSalesValue ELSE 0 END AS ExtQuilt,


                          

    CASE WHEN dbo.SorMasterRep.ShippingInstrs = 'VF' THEN Vita / 100 * dbo.ArTrnDetail.NetSalesValue ELSE 0 END AS Internal, dbo.ArTrnDetail.Invoice,

                          dbo

    .ArTrnDetail.InvoiceDate, dbo.ArTrnDetail.Salesperson, ronnieb.Insurables.AlphaValue, ronnieb.Insurables.Name AS Expr1

    Monday, August 11, 2014 10:15 AM
  • EshMat, you have not provided the complete query,so we would not be able to help you much with this.

    Could you share us your complete code including FROM....where...

    Monday, August 11, 2014 11:57 AM
  • FROM

             dbo.TblCustomerClass INNER JOIN


                          dbo

    .ArCustomer ON dbo.TblCustomerClass.Class = dbo.ArCustomer.CustomerClass INNER JOIN


                          dbo

    .TblArTerms ON dbo.ArCustomer.TermsCode = dbo.TblArTerms.TermsCode INNER JOIN


                          dbo

    .VT_VariableCosts ON dbo.TblCustomerClass.Class = dbo.VT_VariableCosts.Class LEFT OUTER JOIN


                          ronnieb

    .Cartage ON dbo.ArCustomer.ShipToAddr5 = ronnieb.Cartage.Province RIGHT OUTER JOIN


                          dbo

    .InvMaster LEFT OUTER JOIN


                          dbo

    .VT_Bed_Range ON dbo.InvMaster.StockCode = dbo.VT_Bed_Range.StockCode RIGHT OUTER JOIN


                          dbo

    .Insurables INNER JOIN


                          dbo

    .ArTrnDetail INNER JOIN


                          dbo

    .SalProductClass ON dbo.ArTrnDetail.Branch = dbo.SalProductClass.Branch AND dbo.ArTrnDetail.ProductClass = dbo.SalProductClass.ProductClass INNER JOIN


                          dbo

    .SalProductClassDes ON dbo.SalProductClass.ProductClass = dbo.SalProductClassDes.ProductClass INNER JOIN


                          dbo

    .SalGlIntSale ON dbo.ArTrnDetail.Branch = dbo.SalGlIntSale.Branch AND dbo.ArTrnDetail.ProductClass = dbo.SalGlIntSale.ProductClass ON

                          dbo

    .Insurables.Customer = dbo.ArTrnDetail.Customer INNER JOIN


                          dbo

    .VT_Reps ON dbo.ArTrnDetail.Salesperson = dbo.VT_Reps.Salesperson LEFT OUTER JOIN


                          dbo

    .SorMasterRep ON dbo.ArTrnDetail.Invoice = dbo.SorMasterRep.InvoiceNumber AND dbo.ArTrnDetail.TrnYear = dbo.SorMasterRep.TrnYear AND

                          dbo

    .ArTrnDetail.TrnMonth = dbo.SorMasterRep.TrnMonth ON dbo.InvMaster.StockCode = dbo.ArTrnDetail.StockCode ON

                          dbo

    .ArCustomer.Customer = dbo.ArTrnDetail.Customer


    WHERE    

    (dbo.ArTrnDetail.TrnYear >= 2015) AND(RIGHT(LEFT(dbo.SalGlIntSale.SalesGlCode, 7), 2) = '30') AND(dbo.ArTrnDetail.TransactionGlCode <> '')


    GO

    Monday, August 11, 2014 1:00 PM
  • I have provided the from and where section as I could not post all earlier
    Monday, August 11, 2014 1:01 PM
  • Given that your view definition contains "(dbo.ArTrnDetail.TrnYear >= 2015)", I don't see how that matches your description ("only display data up to the 01/08/2014").  To me, it seems like your view should not include ANY data before 2015 (though perhaps that value is a fiscal year and not a calendar year).  In addition, the view name includes the word "daily" which suggests that you might be using it incorrectly - "daily" and historical data don't seem to be a good fit to me. 

    Sadly, no one can help you without understanding your schema, your query, and the data.  Given all the left and right outer joins, you have a mess of a schema and query.  Your view definition is difficult to understand since the original developer decided to cram all of the join criteria at the end of the query rather than with each join.  The only thing I can offer is that perhaps you expect data that does not exist for the criteria you apply to your query.   

    One last comment - when you see the same logic repeated many times in a query, that is a clue that something is missing.  This view has multiple instances of "RIGHT(LEFT(dbo.ArTrnDetail.ProductClass, 3), 2) " - it would probably be a good idea to materialize this bit of information in some fashion.

    Thursday, August 14, 2014 1:30 PM
  • Thanks for your response much appreciated.

    But However recreated the Design and it is now working 100%

    Tuesday, August 19, 2014 11:31 AM