Now load ID should be unique, and only the most recent entry in is valid per load ID. Please provide an Total Cost and Total Deliveries by DC and by Period
Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.
As such please provide what you are stuck on, what you have tried so far etc?
Yes, it was a past assessment question. Now I need to go over this again.
My query is like:
select DISTINCT(CostLoad.LOAD_ID), MAX(CostLoad.DATE) as Date, Calendar.PERIOD, CostLoad.DC,
COUNT(CostLoad.COSTLOAD) as CostCount, SUM(CostLoad.COSTLOAD) as CostSum, AVG(CostLoad.COSTLOAD) as CostAverage from CostLoad
INNER JOIN Dates ON Dates.GREGORIAN_DATE = CostLoad.DATE
INNER JOIN Calendar ON Dates.WM_WEEK = Calendar.WMWEEK
GROUP BY Calendar.PERIOD, CostLoad.DC, CostLoad.DATE, CostLoad.LOAD_ID
This gives me the wrong number of rows and did not give the sum of total cost by period and DC. My output is like this
Basically, I did not get the correct rows and did not generate the sums. Please help.
It looks like your excel list is completely different to your SQL output and the dates don’t match what your trying to do. You got a mix of month/date formatting issues.
What you want is 3 queries not one as you need to group by various different pieces which you can’t do in one.
If you can follow the below link and lost some easily consumable data and a the expected outcome it will be easier to show a working solution.
16 People are following this question.
SQL Native Client 11.0 config force protocol encryption
Re-config SQL 2012 job
Multiple rows returned where identity column equals ?
Perform AVG on Column if condition exist
SP execute permission