Provide total costload and delivery by DC and by Period

Transact-SQL
1

If I have below data:

SQL_Questions_Data
SQL_Questions_Data613×698 20.4 KB

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

2

Need DDL rather than a picture. That is, CREATE TABLE and INSERT statement(s) that will produce the data shown above. Then we can write query against that table. We can't write a query against a picture :grin:.

3

do this and provide all sample data


declare @yyhh table(
load_id int, 
dateyy date, 
store_id int, dc int, 
coastload money,
weekzi int,
periodz int)

insert into @yyhh
select 2442839, '2021-06-20', 4, 4, 854.76, 21, 6 union
select 2444438, '2021-03-23', 4,4,854.76,8,4

select * From @yyhh
6
LOAD_ID DATE STORE ID DC COSTLOAD Week Period
2442839 6/20/2021 4 4 $854.76 21 6
2444438 3/23/2021 4 4 $854.76 8 4
2449869 7/9/2021 34 1 $679.05 23 6
2449910 6/18/2021 45 1 $579.78 20 5
2450347 5/8/2021 11 3 $850.14 15 5
2450490 6/1/2021 8 4 $118.18 18 5
2450536 5/11/2021 20 1 $(194.42) 15 5
2450537 4/23/2021 45 2 $864.51 12 4
2450558 6/10/2021 30 5 $423.73 19 5
2450593 7/28/2021 13 3 $(373.75) 26 6
2450626 4/16/2021 25 3 $651.24 11 4
2450629 5/21/2021 41 4 $531.04 16 5
2450685 6/1/2021 49 2 $726.39 18 5
2450723 4/19/2021 39 4 $586.99 12 4
2450787 6/3/2021 29 3 $512.11 18 5
2450792 4/29/2021 36 3 $(420.12) 13 4
2450813 6/23/2021 18 5 $531.03 21 6
2450816 3/31/2021 21 1 $131.71 9 4
2450817 4/11/2021 7 5 $579.56 11 4
2450817 6/12/2021 2 5 $585.84 20 5
2450818 7/23/2021 36 1 $367.92 25 6
2450819 4/3/2021 13 1 $928.97 10 4
2450819 5/6/2021 36 5 $888.39 14 4
2450819 7/23/2021 39 3 $468.23 25 6
2450837 7/5/2021 31 4 $544.33 23 6
2450912 3/30/2021 48 5 $198.97 9 4
2450918 6/22/2021 33 5 $790.36 21 6
2450920 4/24/2021 1 5 $906.21 13 4
2450923 5/5/2021 5 3 $254.59 14 4
2450933 3/22/2021 28 3 $232.71 8 4
2450933 4/8/2021 2 4 $59,238.03 10 4
2450935 5/3/2021 18 5 $113.52 14 4
2450935 7/23/2021 16 1 $848.85 25 6
2450937 5/9/2021 47 4 $514.17 15 5
2450941 3/30/2021 49 4 $518.67 9 4
2450941 6/14/2021 42 1 #VALUE! 20 5
2450945 6/20/2021 40 5 $616.54 21 6
2450947 6/27/2021 41 2 $707.47 22 6
2450949 7/6/2021 18 5 $141.73 23 6
2450962 4/21/2021 4 1 $922.56 12 4
2450965 5/4/2021 8 3 $204.36 14 4
2450975 4/24/2021 23 2 $188.62 13 4
2450975 6/14/2021 45 2 $723.45 20 5
2450993 4/14/2021 26 2 $968.22 11 4
2450993 6/4/2021 39 2 $534.17 18 5
2450993 7/8/2021 14 3 $285.12 23 6
2451009 7/19/2021 34 1 $208.41 25 6
2451016 7/29/2021 8 2 $672.92 26 6
2451135 7/18/2021 8 3 $414.88 25 6
2451144 6/6/2021 1 2 $981.46 19 5
2451146 4/4/2021 36 1 $297.01 10 4
2451153 7/26/2021 41 2 $131.39 26 6
2451158 6/30/2021 36 1 $657.99 22 6
2451206 5/15/2021 35 5 $326.55 16 5
2451207 6/29/2021 44 2 $416.61 22 6
2451208 7/1/2021 12 3 $353.35 22 6
2451216 6/19/2021 40 1 $138.16 21 6
2451222 4/22/2021 4 4 $620.97 12 4
2451229 6/16/2021 46 4 $784.88 20 5
2451269 3/27/2021 11 3 $248.79 9 4
2451294 6/14/2021 43 4 $123.96 20 5
2451380 6/19/2021 16 2 $168.65 21 6
2451386 4/13/2021 7 5 $486.91 11 4
2451403 5/22/2021 48 5 $535.24 17 5
2451406 3/31/2021 44 3 $648.07 9 4
2451406 5/26/2021 19 3 $342.15 17 5
2451422 7/21/2021 41 4 $484.85 25 6
2451440 4/24/2021 9 2 $827.22 13 4
2451458 5/25/2021 42 1 $182.39 17 5
2451458 6/6/2021 28 1 $523.88 19 5
2451462 4/16/2021 28 2 $104.08 11 4
2451464 6/4/2021 12 1 $449.79 18 5
2451511 5/16/2021 7 4 $666.16 16 5
2451511 5/23/2021 13 1 $585.66 17 5
2451511 7/28/2021 42 1 $779.93 26 6
2451542 4/14/2021 14 3 $547.24 11 4
2451556 4/25/2021 19 4 $302.67 13 4
2451672 3/20/2021 10 4 $316.38 8 4
2451735 6/9/2021 37 1 $972.24 19 5
2451775 7/23/2021 9 4 $317.98 25 6
2451786 3/21/2021 2 4 $600.97 8 4
2451820 4/24/2021 17 2 $989.77 13 4
2451843 5/10/2021 46 4 $134.13 15 5
2451853 7/4/2021 3 3 $731.84 23 6
2451927 6/29/2021 7 1 $548.88 22 6
2451973 6/10/2021 6 1 $805.49 19 5
2451978 6/3/2021 31 5 $829.93 18 5
2451979 4/21/2021 34 3 $810.71 12 4
2452070 6/18/2021 43 3 $742.98 20 5
2452169 4/10/2021 20 4 $379.63 11 4
2452218 7/21/2021 35 4 $556.27 25 6
2452413 5/6/2021 32 2 $138.26 14 4
2452413 6/3/2021 39 2 $114.23 18 5
2452421 4/29/2021 28 5 $453.56 13 4
2452440 5/23/2021 45 1 $695.71 17 5
2452440 6/11/2021 5 3 $788.68 19 5
2452440 7/24/2021 33 5 $431.81 26 6
2452458 7/14/2021 8 1 $668.53 24 6
2452487 7/16/2021 37 1 $175.84 24 6
2452496 5/24/2021 42 5 #VALUE! 17 5
2452567 7/15/2021 16 2 $281.57 24 6
2452583 4/11/2021 28 1 $876.56 11 4
2452608 7/24/2021 30 3 $214.73 26 6
2452620 4/27/2021 42 2 $231.25 13 4
2452637 6/18/2021 40 5 $436.37 20 5
2452639 3/24/2021 42 5 $208.88 8 4
2452639 4/4/2021 6 5 $497.02 10 4
2452649 5/16/2021 28 4 $618.71 16 5
2452681 4/1/2021 16 3 $900.88 9 4
2452682 7/22/2021 47 2 $480.34 25 6
2452684 3/20/2021 16 2 $612.83 8 4
2452686 5/18/2021 1 2 $671.55 16 5
2452686 5/27/2021 23 3 $966.53 17 5
2452692 4/10/2021 20 4 $303.29 11 4
2452694 6/19/2021 50 5 $270.04 21 6
2452697 5/27/2021 38 2 #VALUE! 17 5
2452700 5/4/2021 17 5 $267.24 14 4
2452702 7/3/2021 21 2 $322.18 23 6
2452706 4/7/2021 15 3 $270.36 10 4
2452706 5/24/2021 25 4 $132.25 17 5
2452708 5/10/2021 43 1 $818.01 15 5
2452708 7/16/2021 38 5 $566.95 24 6
2452709 4/22/2021 12 2 $630.96 12 4
2452709 4/29/2021 46 3 $280.47 13 4
2452717 6/8/2021 35 3 $356.25 19 5
2452717 6/23/2021 4 4 $498.19 21 6
2452718 4/5/2021 33 3 $574.76 10 4
2452718 7/12/2021 49 4 $436.26 24 6
2452724 6/19/2021 1 3 $165.34 21 6
2452726 6/11/2021 50 5 $773.05 19 5
2452726 7/7/2021 1 4 $889.55 23 6
2452733 7/23/2021 6 1 $932.66 25 6
2452734 3/26/2021 3 5 $826.08 8 4
2452734 6/22/2021 20 2 $389.15 21 6
2452736 3/23/2021 33 2 $433.63 8 4
2452737 5/20/2021 28 2 $748.56 16 5
2452738 4/15/2021 35 3 $860.74 11 4
2452738 7/18/2021 19 5 $658.37 25 6
2452739 4/6/2021 46 4 $240.69 10 4
2452739 5/7/2021 1 5 $416.42 14 4
2452740 7/5/2021 45 3 $664.66 23 6
2452741 4/8/2021 37 1 $756.47 10 4
2452741 5/6/2021 38 3 $990.99 14 4
2452743 5/12/2021 34 3 $867.16 15 5
2452744 6/15/2021 45 1 $976.97 20 5
2452748 5/26/2021 43 1 $275.55 17 5
2452762 6/5/2021 18 5 $107.42 19 5
2452763 7/28/2021 29 1 $615.71 26 6
2452764 4/23/2021 35 2 $660.53 12 4
2452764 7/10/2021 37 2 $223.66 24 6
2452765 7/22/2021 2 3 $403.54 25 6
2452775 4/24/2021 6 3 $366.61 13 4
2452775 5/14/2021 34 3 $118.82 15 5
2452776 6/20/2021 45 1 $816.89 21 6
2452777 5/19/2021 3 5 $783.84 16 5
2452777 7/15/2021 19 4 $923.41 24 6
2452778 7/27/2021 7 3 $556.86 26 6
2452783 7/20/2021 15 3 $491.67 25 6
2452787 5/1/2021 26 4 $478.38 14 4
2452787 7/28/2021 39 5 $135.89 26 6
2452790 5/21/2021 33 2 $804.49 16 5
2452791 4/17/2021 11 2 $457.19 12 4
2452800 7/16/2021 20 5 $162.58 24 6
2452813 5/31/2021 5 3 $662.31 18 5
2452817 5/25/2021 43 2 $102.87 17 5
2452820 5/4/2021 39 4 $427.86 14 4
2452828 5/4/2021 38 5 $806.99 14 4
2452828 7/28/2021 50 4 $229.98 26 6
2452831 6/17/2021 49 4 $179.92 20 5
2452833 6/23/2021 19 3 $225.31 21 6
2452833 7/24/2021 40 1 $590.01 26 6
2452835 3/24/2021 35 5 #VALUE! 8 4
2452835 4/25/2021 15 3 $632.34 13 4
2452835 7/11/2021 50 2 $997.44 24 6
2452849 5/22/2021 7 2 $367.04 17 5
2452851 6/9/2021 46 5 $476.47 19 5
2452856 5/27/2021 38 2 $475.25 17 5
2452856 7/2/2021 11 4 $537.79 22 6
2452856 7/14/2021 40 2 $421.03 24 6
2452857 6/11/2021 15 1 $270.66 19 5
2452858 4/24/2021 23 5 $352.04 13 4
2452858 5/30/2021 16 3 $934.79 18 5
2452860 5/29/2021 4 5 $655.96 18 5
2452861 4/19/2021 28 3 $303.06 12 4
2452862 4/21/2021 16 1 $360.26 12 4
2452862 5/9/2021 3 1 $741.27 15 5
2452863 4/8/2021 22 5 $803.66 10 4
2452866 4/12/2021 37 4 $742.64 11 4
2452866 7/13/2021 37 1 $876.62 24 6
2452868 5/24/2021 48 5 $421.19 17 5
2452872 5/18/2021 21 5 $966.51 16 5
2452874 6/1/2021 3 4 $252.04 18 5
2452879 7/18/2021 48 1 $296.03 25 6
2452880 3/24/2021 40 5 $783.46 8 4
2452881 7/3/2021 26 1 $385.32 23 6
2452882 6/14/2021 19 2 $531.86 20 5
2452887 7/26/2021 17 4 $692.97 26 6
2452887 7/30/2021 31 2 $648.61 26 6
2452888 3/24/2021 6 3 $895.98 8 4
2452890 7/25/2021 37 4 $163.49 26 6
2452893 5/24/2021 42 2 $335.31 17 5
2452894 4/18/2021 40 4 $827.46 12 4
2452894 7/8/2021 23 1 $386.68 23 6
2452894 7/11/2021 1 1 $180.43 24 6
2452896 4/30/2021 35 1 $763.19 13 4
2452896 5/28/2021 13 5 $349.43 17 5
2452897 4/4/2021 45 2 $594.78 10 4
2452897 5/2/2021 25 4 $916.63 14 4
2452897 6/12/2021 10 4 $599.17 20 5
2452898 5/20/2021 42 3 $279.76 16 5
2452899 5/1/2021 27 3 $492.97 14 4
2452899 5/12/2021 23 4 $715.67 15 5
2452900 4/29/2021 8 2 $581.25 13 4
2452900 5/18/2021 21 4 $623.98 16 5
2452902 4/17/2021 38 5 $351.17 12 4
2452904 6/16/2021 10 2 $822.83 20 5
2452904 7/29/2021 15 5 $819.65 26 6
2452906 4/17/2021 11 3 $922.98 12 4
2452906 4/19/2021 7 4 $700.97 12 4
2452907 4/17/2021 1 5 $437.71 12 4
2452907 5/24/2021 32 1 $795.43 17 5
2452908 4/15/2021 22 2 $321.01 11 4
2452908 7/11/2021 29 1 $978.38 24 6
2452910 4/22/2021 7 1 $284.76 12 4
2452911 5/19/2021 31 5 $890.39 16 5
2452911 7/8/2021 35 3 $576.54 23 6
2452912 4/4/2021 19 1 $299.46 10 4
2452915 5/13/2021 22 3 $149.49 15 5
2452915 7/3/2021 37 1 $751.85 23 6
2452931 4/4/2021 4 3 $269.47 10 4
2452931 4/13/2021 48 4 $384.32 11 4
2452931 5/7/2021 1 3 $550.45 14 4
2452959 4/10/2021 11 2 $926.35 11 4
2452961 7/25/2021 11 1 $839.72 26 6
2452962 4/4/2021 1 1 $428.94 10 4
2452970 4/5/2021 32 4 $365.87 10 4
2452973 4/27/2021 13 1 $974.04 13 4
2452973 7/2/2021 6 4 $569.88 22 6
2453051 3/20/2021 38 4 $899.93 8 4
2453051 3/24/2021 9 1 $411.38 8 4
2453051 5/3/2021 25 5 $366.09 14 4
2453051 6/5/2021 2 4 $300.47 19 5
2453089 4/18/2021 49 1 $814.23 12 4
2453089 7/20/2021 25 3 $720.55 25 6
2453110 7/15/2021 49 2 $118.97 24 6
2453113 4/8/2021 3 3 $438.59 10 4
2453116 3/22/2021 46 5 $839.29 8 4
2453116 4/20/2021 12 1 $969.93 12 4
2453120 5/10/2021 42 3 $246.56 15 5
2453120 6/18/2021 40 5 $954.54 20 5
2453124 5/19/2021 23 4 $597.84 16 5
2453124 6/20/2021 11 2 $949.43 21 6
2453127 3/25/2021 11 1 $810.62 8 4
2453127 6/5/2021 4 3 $227.48 19 5
2453131 5/15/2021 36 1 $306.72 16 5
2453132 4/13/2021 26 5 $276.57 11 4
2453141 4/10/2021 34 1 $363.99 11 4
2453143 4/1/2021 17 2 $415.77 9 4
2453143 6/5/2021 47 3 $414.13 19 5
2453146 4/10/2021 33 3 $819.98 11 4
2453155 6/17/2021 6 1 $542.63 20 5
2453186 4/18/2021 40 4 $711.24 12 4
2453186 6/2/2021 33 2 $229.76 18 5
2453192 7/3/2021 22 1 $621.92 23 6
2453194 4/1/2021 32 5 $102.43 9 4
2453194 4/22/2021 20 5 $546.05 12 4
2453265 7/5/2021 44 4 $715.98 23 6
2453266 5/9/2021 28 2 $246.98 15 5
2453345 6/11/2021 23 1 $112.19 19 5
2453345 6/12/2021 40 4 $535.16 20 5
2453381 5/19/2021 20 5 #VALUE! 16 5
2453446 5/1/2021 40 2 $751.36 14 4
2453446 6/30/2021 24 5 $430.74 22 6
2453447 4/8/2021 11 2 $171.99 10 4
2453450 6/6/2021 6 4 $794.99 19 5
2453454 6/11/2021 1 1 $249.27 19 5
2453455 5/7/2021 4 3 $579.78 14 4
2453455 6/24/2021 50 1 $416.86 21 6
2453457 6/11/2021 19 5 $829.18 19 5
2453457 7/23/2021 33 3 $630.23 25 6
2453486 4/18/2021 12 5 $734.75 12 4
2453486 7/28/2021 47 3 $778.82 26 6
2453490 7/8/2021 42 2 $317.73 23 6
2453492 6/5/2021 14 3 $474.35 19 5
2453492 7/26/2021 15 2 $620.66 26 6
2453493 3/27/2021 46 1 $854.33 9 4
2453493 5/28/2021 2 1 $233.78 17 5
2453494 7/2/2021 2 3 $813.42 22 6
2453495 5/26/2021 50 3 $730.19 17 5
2453496 5/14/2021 34 4 $837.14 15 5
2453499 5/17/2021 44 1 $242.53 16 5
2453500 5/22/2021 39 3 $835.86 17 5
2453501 6/14/2021 36 4 $673.44 20 5
2453502 5/28/2021 26 5 $849.53 17 5
2453503 3/25/2021 41 2 $233.06 8 4
2453507 5/5/2021 17 5 $541.61 14 4
2453508 5/5/2021 36 2 $262.66 14 4
2453509 5/25/2021 16 2 $530.79 17 5
2453510 6/27/2021 13 5 $572.57 22 6
2453513 5/16/2021 46 3 $828.01 16 5
2453513 5/22/2021 43 2 $650.96 17 5
2453514 6/11/2021 7 4 $772.64 19 5
2453514 6/18/2021 23 5 $137.83 20 5
2453514 7/30/2021 32 5 $324.03 26 6
2453515 4/18/2021 14 4 $843.95 12 4
2453517 6/23/2021 33 5 $344.27 21 6
2453520 4/26/2021 11 2 $567.19 13 4
2453520 4/28/2021 42 3 $202.21 13 4
2453592 6/19/2021 5 5 $935.97 21 6
2453596 4/10/2021 32 1 $576.56 11 4
2453597 6/16/2021 7 4 $935.29 20 5
2453601 5/6/2021 38 4 $877.08 14 4
2453601 5/9/2021 21 4 $721.87 15 5
2453605 6/30/2021 18 5 $607.39 22 6
2453606 7/9/2021 46 3 $211.39 23 6
2453607 4/4/2021 18 3 $120.43 10 4
2453607 6/19/2021 36 1 $452.86 21 6
2453608 5/15/2021 32 4 $706.56 16 5
2453609 5/29/2021 15 4 $546.62 18 5
2453610 7/28/2021 34 2 $206.13 26 6
2453613 5/6/2021 32 2 $839.41 14 4
2453615 7/24/2021 8 4 $562.46 26 6
2453616 7/26/2021 31 2 $529.66 26 6
2453617 4/27/2021 32 5 $595.94 13 4
2453617 6/13/2021 38 3 $590.93 20 5
2453618 5/29/2021 5 3 $914.07 18 5
2453626 5/15/2021 31 3 $524.61 16 5
2453631 5/12/2021 20 1 $827.98 15 5
2453631 5/27/2021 39 5 $853.78 17 5
2453669 6/18/2021 35 3 $396.97 20 5
2453745 6/4/2021 4 3 $756.98 18 5
2453746 5/12/2021 33 4 $869.25 15 5
2453746 7/30/2021 27 1 $643.96 26 6
2453750 5/30/2021 39 5 $134.67 18 5
2453752 4/16/2021 6 4 $207.22 11 4
2453757 4/7/2021 19 2 $723.63 10 4
2453759 5/6/2021 31 5 $412.46 14 4
2453761 4/6/2021 39 3 $838.31 10 4
2453761 5/23/2021 39 5 $500.33 17 5
2453764 6/26/2021 49 4 $840.21 22 6
2453765 4/8/2021 12 3 $896.67 10 4
2453765 7/23/2021 31 3 $893.66 25 6
2453808 5/17/2021 8 3 $187.21 16 5
2453812 6/18/2021 6 2 $269.62 20 5
2453824 4/22/2021 3 5 $275.91 12 4
2453825 4/8/2021 34 2 $714.59 10 4
2453827 6/5/2021 4 4 $550.96 19 5
2453831 7/19/2021 27 4 $838.62 25 6
2453833 6/14/2021 18 3 $699.13 20 5
2453834 6/23/2021 26 4 $105.43 21 6
2453837 7/25/2021 23 4 $480.82 26 6
2453848 7/23/2021 29 1 $893.36 25 6
2453861 3/23/2021 43 2 $931.66 8 4
2453862 4/21/2021 9 1 $671.75 12 4
2453864 6/24/2021 26 1 $617.67 21 6
2453871 7/3/2021 17 5 $246.18 23 6
2453872 6/27/2021 41 2 $757.75 22 6
2454013 3/31/2021 50 1 $935.17 9 4
2454014 7/25/2021 12 3 $504.81 26 6
2454019 4/19/2021 29 4 $686.73 12 4
2454019 4/22/2021 41 1 $209.02 12 4
2454033 7/15/2021 4 1 $295.45 24 6
2454084 6/12/2021 26 1 $411.57 20 5
2454190 5/22/2021 16 3 $498.17 17 5
2454197 4/20/2021 47 1 $719.89 12 4
2454236 6/6/2021 46 4 $720.44 19 5
2454309 6/6/2021 33 3 $383.32 19 5
2454310 7/28/2021 6 4 $595.37 26 6
2454318 4/15/2021 49 5 $867.75 11 4
2454319 5/25/2021 10 4 $422.12 17 5
2454319 6/2/2021 16 1 $594.93 18 5
2454321 4/23/2021 24 1 $265.61 12 4
2454322 5/1/2021 47 1 $570.61 14 4
2454324 6/29/2021 32 1 $423.63 22 6
2454351 7/25/2021 12 3 $205.86 26 6
2454352 4/1/2021 12 4 $681.52 9 4
2454357 6/11/2021 12 1 $506.38 19 5
2454358 4/24/2021 24 4 $653.43 13 4
2454362 3/31/2021 14 2 $509.05 9 4
2454374 3/25/2021 19 4 $561.92 8 4
2454374 7/13/2021 35 3 $308.43 24 6
2454387 7/14/2021 3 2 $659.14 24 6
2454388 5/27/2021 46 4 $370.78 17 5
2454389 4/6/2021 9 1 $451.71 10 4
2454411 5/18/2021 9 5 $454.45 16 5
2454411 7/4/2021 29 5 $748.66 23 6
2454412 7/20/2021 25 2 $947.77 25 6
2454414 6/10/2021 37 1 $250.52 19 5
2454423 4/27/2021 44 3 $432.13 13 4
2454427 6/23/2021 13 3 $619.96 21 6
2454428 3/26/2021 30 5 $514.34 8 4
2454428 7/23/2021 25 2 $540.75 25 6
2454445 5/6/2021 30 3 $409.12 14 4
2454446 7/10/2021 23 3 $331.56 24 6
2454449 5/19/2021 2 2 $851.87 16 5
2454450 5/10/2021 38 1 $362.53 15 5

Here we go. This is a past assessment and I want to learn it. 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.png
Basically, I did not get the correct rows and did not generate the sums. Please help.

7

Need directly usable data, as stated and shown. Until then, I can't help you much. Maybe someone else is willing to set up all your data for you, but I just don't have time.

8

Do you mean post a similar script here for you to generate the table? Sorry, I am quite new to the community. If you can guide, I greatly appreciate it.

9

A CREATE TABLE and INSERT statement(s). yosiasz provided a guideline above for what that means.

10

Not sure if this is good for you:

declare @yyhh table(

load_id int,

dateyy date,

store_id int, dc int,

coastload money,

weekzi int,

periodz int)

insert into @yyhh

select 2442839, '2021-06-20', 4, 4, 854.76, 21, 6 union

select 2444438, '2021-03-23', 4,4,854.76,8,4 union

select 2450816, '2021-03-31',21,1,131.71,9,4 union

select 2450817, '2021-04-11',7,5,579.56 ,11,4 union

select 2450817, '2021-06-12',2,5,585.84 ,20,5 union

select 2450818, '2021-07-23',36,1,367.92 ,25,6 union

select 2450819, '2021-04-03',13,1, 928.97 ,10,4 union

select 2450819, '2021-05-06',36,5,888.39 ,14,4 union

select 2450819, '2021-07-23',39,3, 468.23,25,6 union

select 2450837, '2021-07-05',31,4,544.33 ,23,6 union

select 2450912, '2021-03-30',48,5, 198.97 ,9,4 union

select 2450918, '2021-06-22',33,5,790.36 ,21,6 union

select 2450920, '2021-04-24',1,5, 906.21,13,4 union

select 2450923, '2021-05-05',5,3,254.59 ,14,4

select * From @yyhh

1 Like
11

I think is what you want. If not, please let me know what is wrong with it.

I went back to the original column names, no need to add suffixes to the names just to prevent SSMS from highlighting them.

declare @yyhh table( load_id int not null, date date not null, 
    store_id int null, dc int not null, 
    coastload money null, week int null, period int not null) 
insert into @yyhh values
    ( 2442839, '2021-06-20', 4, 4, 854.76, 21, 6 ), 
    ( 2444438, '2021-03-23', 4,4,854.76,8,4 ), 
    ( 2450816, '2021-03-31',21,1,131.71,9,4 ), 
    ( 2450817, '2021-04-11',7,5,579.56 ,11,4 ), 
    ( 2450817, '2021-06-12',2,5,585.84 ,20,5 ), 
    ( 2450818, '2021-07-23',36,1,367.92 ,25,6 ), 
    ( 2450819, '2021-04-03',13,1, 928.97 ,10,4 ), 
    ( 2450819, '2021-05-06',36,5,888.39 ,14,4 ), 
    ( 2450819, '2021-07-23',39,3, 468.23,25,6 ), 
    ( 2450837, '2021-07-05',31,4,544.33 ,23,6 ), 
    ( 2450912, '2021-03-30',48,5, 198.97 ,9,4 ), 
    ( 2450918, '2021-06-22',33,5,790.36 ,21,6 ), 
    ( 2450920, '2021-04-24',1,5, 906.21,13,4 ), 
    ( 2450923, '2021-05-05',5,3,254.59 ,14,4 )

;WITH cte_most_recent_load AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY load_id 
        ORDER BY date DESC) AS row_num
    FROM @yyhh
)
SELECT dc, period,
    SUM(coastload) AS total_cost,
    COUNT(*) AS total_deliveries    
FROM cte_most_recent_load
GROUP BY dc, period
ORDER BY dc, period