question

yyhh_2000 avatar image
yyhh_2000 asked

Provide total costload and delivery by DC and by Period

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

sql-questions-data.png

sql2012
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image anthony.green commented ·
This sounds like a homework/interview question.

As such please provide what you are stuck on, what you have tried so far etc?



0 Likes 0 ·
yyhh_2000 avatar image yyhh_2000 anthony.green commented ·

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

ORDER BY Max(CostLoad.DATE) asc, 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

my-qurey-result.pngBasically, I did not get the correct rows and did not generate the sums. Please help.

0 Likes 0 ·
my-qurey-result.png (112.0 KiB)
anthony.green avatar image anthony.green yyhh_2000 commented ·

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.

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.