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
Replies
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
Hi There
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
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?
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
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
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
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%