Question

EshMat on Mon, 11 Aug 2014 09:17:46


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


Sponsored



Replies

HuaMin Chen on Mon, 11 Aug 2014 09:25:11


Check all criteria in the definition of the view.

EshMat on Mon, 11 Aug 2014 09:29:54


Hi There

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

Latheesh NK on Mon, 11 Aug 2014 09:30:45


You may check your VIEW definition, there must be a condition to filter the records for the date 01/08/2014.

Latheesh NK on Mon, 11 Aug 2014 09:31:38


Please do not cross post, you may follow the other thread:

http://social.msdn.microsoft.com/Forums/en-US/0160c7ba-b522-4291-8706-108a052cc008/sql-2008r?forum=sqlsearch

EshMat on Mon, 11 Aug 2014 09:54:53


Hi Thanks for the reply how do I do that

EshMat on Mon, 11 Aug 2014 09:55:09


Hi There

How do I do that

Vaibhav Chaudhari on Mon, 11 Aug 2014 09:58:32


What is WHERE condition inside your View definition?

Latheesh NK on Mon, 11 Aug 2014 10:02:58


sp_helptext 'viewname' and check the definition for any filter.

EshMat on Mon, 11 Aug 2014 10:15:14


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

Latheesh NK on Mon, 11 Aug 2014 11:57:16


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...

EshMat on Mon, 11 Aug 2014 13:00:20


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

EshMat on Mon, 11 Aug 2014 13:01:16


I have provided the from and where section as I could not post all earlier

scott_morris-ga on Thu, 14 Aug 2014 13:30:08


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.

EshMat on Tue, 19 Aug 2014 11:31:19


Thanks for your response much appreciated.

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