PatrikA Posted June 16, 2005 Share Posted June 16, 2005 Jag håller på med en subquery som jag har lite strul med. (SELECT SUM(y.Qty) FROM tOrderRow y, tOrderHead x, tCustomer z, tProductGroupRow g, tProduct f WHERE g.ProductGroupID = b.ProductGroupID AND g.ProductID = y.ProductID AND f.ProductID = y.ProductID AND y.OrderID = x.OrderID AND x.CustomerID = z.CustomerID AND z.Status = 'ACTIVE' AND x.UserID = d.UserID AND x.OrderDate <= (select convert(datetime,ParameterValue) from tReportParameter where ReportParameterName = 'ENDDATE' and ReportID = a.ReportID) AND x.OrderDate >= (select convert(datetime,ParameterValue) from tReportParameter where ReportParameterName = 'STARTDATE' and ReportID = a.ReportID) AND x.OrderStatus = 'ACTIVE' AND x.OrderType IN ('ORDER','STOREORDER') AND NOT x.OrderID IN (SELECT OrderID FROM tOrderDelete)) / (select top 1 f.RpTp from tProductGroupRow g, tProduct f WHERE g.ProductGroupID = b.ProductGroupID AND f.ProductID = g.ProductID) as Pall, Där jag först delar summan av Qty med RpTp för produkten. Men det dumma är att det blir avrundat, jag vill ha decimaler där det behövs, för det gör det på vissa ställen men dom är som sagt avrundade. Jag ser att frågan ser lite stor ut men kolla bara efter / tecknet så ser ni vart den biten som ska första ska dela med är. Link to comment Share on other sites More sharing options...
Anjuna Moon Posted June 16, 2005 Share Posted June 16, 2005 Du får typkasta de ingående delarna i divisionen, samt resultatet av divisionen till decimaltal. Ex: CAST(CAST(fld1 AS decimal(10,2)) / CAST(fld2 AS decimal(10,2)) AS decimal(10,2)) [inlägget ändrat 2005-06-16 12:11:30 av Anjuna Moon] Link to comment Share on other sites More sharing options...
PatrikA Posted June 16, 2005 Author Share Posted June 16, 2005 Hmm, får inte till den riktigt.. CAST((SELECT SUM(y.Qty) FROM tOrderRow y, tOrderHead x, tCustomer z, tProductGroupRow g, tProduct f WHERE g.ProductGroupID = b.ProductGroupID AND g.ProductID = y.ProductID AND f.ProductID = y.ProductID AND y.OrderID = x.OrderID AND x.CustomerID = z.CustomerID AND z.Status = 'ACTIVE' AND x.UserID = d.UserID AND x.OrderDate <= (select convert(datetime,ParameterValue) from tReportParameter where ReportParameterName = 'ENDDATE' and ReportID = a.ReportID) AND x.OrderDate >= (select convert(datetime,ParameterValue) from tReportParameter where ReportParameterName = 'STARTDATE' and ReportID = a.ReportID) AND x.OrderStatus = 'ACTIVE' AND x.OrderType IN ('ORDER','STOREORDER') AND NOT x.OrderID IN (SELECT OrderID FROM tOrderDelete) AS decimal(10,2)) / CAST((select top 1 f.RpTp from tProductGroupRow g, tProduct f WHERE g.ProductGroupID = b.ProductGroupID AND f.ProductID = g.ProductID) AS decimal(10,2)) AS Pall Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'as'. Server: Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'As'. Edit.. fixade det. tack! [inlägget ändrat 2005-06-16 13:10:53 av Patrik Ahlberg] Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.