January 29, 2015 at 1:54 pm
Hi: I hope you will helpme, I;m new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D
ON D.Line = R.Line
AND D.Shift = R.Shift
AND D.DatePacked = R.DatePacked
WHERE ( CONVERT(VARCHAR(10), R.DatePacked, 101) = CONVERT(VARCHAR(10), '01/27/2015' , 101)
AND D.DownTime != 0
AND D.Shift = '1')
GROUP BY D.Line, D.Shift, D.DatePacked, R.Category, D.ProductionLine
ORDER BY D.Line, D.Shift, D.DatePacked
I hope you will helpme
January 29, 2015 at 2:29 pm
Hi and welcome to the forums. We can help but you didn't actually post a question. You said the values are wrong. What should they be? What are they now?
For best results you should take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2015 at 3:42 pm
This should give you more accurate totals, although it will list every combination of ProductionLine and Category, which may not be what you want.
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, D.DownTime,
R.Category, R.AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM (
SELECT Line, Shift, DatePacked, Category,
SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime
FROM Production.DownTimeReason
WHERE (DatePacked >= '20150127'
AND DatePacked < '20150128')
GROUP BY Line, Shift, DatePacked, Category
) AS R
LEFT OUTER JOIN (
SELECT Line, Shift, DatePacked, ProductionLine,
SUM(CAST(D.DownTime AS INT)) AS DownTime
FROM Production.DownTimeHistory
WHERE (DatePacked >= '20150127'
AND DatePacked < '20150128'
AND DownTime != 0
AND Shift = '1')
GROUP BY Line, Shift, DatePacked, ProductionLine
) AS D
ON D.Line = R.Line
AND D.Shift = R.Shift
AND D.DatePacked = R.DatePacked
ORDER BY D.Line, D.Shift, D.DatePacked
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 30, 2015 at 6:56 am
Thanks a lot, it works
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply