EshMat on Mon, 11 Aug 2014 09:17:46
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
Ai Guo 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
My view is only displaying data upto the 01/08/2014 and nothing thereafter , what could be the problem
SQLZealots 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.
SQLZealots on Mon, 11 Aug 2014 09:31:38
Please do not cross post, you may follow the other thread:
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
How do I do that
Vaibhav Chaudhari on Mon, 11 Aug 2014 09:58:32
What is WHERE condition inside your View definition?
SQLZealots 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
See below View
dbo.ArTrnDetail.TrnYear, dbo.ArTrnDetail.TrnMonth, dbo.ArTrnDetail.Mass, dbo.ArTrnDetail.NetSalesValue AS Sales, dbo.ArTrnDetail.CostValue,
.TblCustomerClass.Description, dbo.SalProductClass.Description AS DetProdGrp, dbo.SalProductClassDes.Description AS SubDesc,
.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),
) AS Period, dbo.ArCustomer.Name, dbo.ArCustomer.ShipToAddr4 AS Town, dbo.ArCustomer.ShipToAddr5 AS Province, dbo.InvMaster.Description AS Product,
.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,
) ELSE '' END) AS Size, dbo.TblArTerms.DiscPct / 100 * dbo.ArTrnDetail.NetSalesValue AS Disc,
.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
.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
.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,
.ArTrnDetail.InvoiceDate, dbo.ArTrnDetail.Salesperson, ronnieb.Insurables.AlphaValue, ronnieb.Insurables.Name AS Expr1
SQLZealots 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
dbo.TblCustomerClass INNER JOIN
.ArCustomer ON dbo.TblCustomerClass.Class = dbo.ArCustomer.CustomerClass INNER JOIN
.TblArTerms ON dbo.ArCustomer.TermsCode = dbo.TblArTerms.TermsCode INNER JOIN
.VT_VariableCosts ON dbo.TblCustomerClass.Class = dbo.VT_VariableCosts.Class LEFT OUTER JOIN
.Cartage ON dbo.ArCustomer.ShipToAddr5 = ronnieb.Cartage.Province RIGHT OUTER JOIN
.InvMaster LEFT OUTER JOIN
.VT_Bed_Range ON dbo.InvMaster.StockCode = dbo.VT_Bed_Range.StockCode RIGHT OUTER JOIN
.Insurables INNER JOIN
.ArTrnDetail INNER JOIN
.SalProductClass ON dbo.ArTrnDetail.Branch = dbo.SalProductClass.Branch AND dbo.ArTrnDetail.ProductClass = dbo.SalProductClass.ProductClass INNER JOIN
.SalProductClassDes ON dbo.SalProductClass.ProductClass = dbo.SalProductClassDes.ProductClass INNER JOIN
.SalGlIntSale ON dbo.ArTrnDetail.Branch = dbo.SalGlIntSale.Branch AND dbo.ArTrnDetail.ProductClass = dbo.SalGlIntSale.ProductClass ON
.Insurables.Customer = dbo.ArTrnDetail.Customer INNER JOIN
.VT_Reps ON dbo.ArTrnDetail.Salesperson = dbo.VT_Reps.Salesperson LEFT OUTER JOIN
.SorMasterRep ON dbo.ArTrnDetail.Invoice = dbo.SorMasterRep.InvoiceNumber AND dbo.ArTrnDetail.TrnYear = dbo.SorMasterRep.TrnYear AND
.ArTrnDetail.TrnMonth = dbo.SorMasterRep.TrnMonth ON dbo.InvMaster.StockCode = dbo.ArTrnDetail.StockCode ON
.ArCustomer.Customer = dbo.ArTrnDetail.Customer
(dbo.ArTrnDetail.TrnYear >= 2015) AND(RIGHT(LEFT(dbo.SalGlIntSale.SalesGlCode, 7), 2) = '30') AND(dbo.ArTrnDetail.TransactionGlCode <> '')
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%