Simple query to sum the booking received per customer for the year.

Why use this query over the customer bookings report in visual?

Well, the answer is too simple. What if you wish to submit a customized report to your upper management with some intuitive graphs and data visualization.

Run the below query in MSSQL and copy paste the results in excel.

Tip: you can also change the “_YEAR” variable to some previous year.

DECLARE @_YEAR AS INT = 2021

SELECT * FROM
(SELECT CO.CUSTOMER_ID, TOTAL_AMT_ORDERED, 
CASE	WHEN DATEPART(MONTH, CREATE_DATE) = 1 THEN 'JAN' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 2 THEN 'FEB' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 3 THEN 'MAR' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 4 THEN 'APR' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 5 THEN 'MAY' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 6 THEN 'JUN' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 7 THEN 'JUL' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 8 THEN 'AUG' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 9 THEN 'SEP' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 10 THEN 'OCT' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 11 THEN 'NOV' 
	WHEN DATEPART(MONTH, CREATE_DATE) = 12 THEN 'DEC' 
	ELSE 'NULL' END AS MONTH1,
			CUS.NAME
			FROM CUSTOMER_ORDER CO
			INNER JOIN CUSTOMER CUS ON CUS.ID = CO.CUSTOMER_ID 
			WHERE DATEPART(YEAR, CREATE_DATE) = @_YEAR AND STATUS IN ('R', 'F', 'C', 'H')  ) SRC
PIVOT
( SUM(TOTAL_AMT_ORDERED) FOR MONTH1 IN ([JAN], [FEB], [MAR], [APR], [MAY], [JUN], [JUL], [AUG], [SEP] ,[OCT], [NOV], [DEC]) ) AS PIV;

Lets copy the desired output of the query and paste it in excel. Format the data into a table and plot the graphs that makes the most sense.

Automating the above report:

The similar results can also be automated with Excel or POWER Bi. All you need to do is create a “Blank Query” in excel. Once done, click on “Advanced Editor” and copy paste the code below.

Note: Please change the “SERVER” and the “DATABASE” name.

 let
    Source = Sql.Database("SERVER_NAME", "DATABASE_NAME"),
    dbo_CUSTOMER_ORDER = Source{[Schema="dbo",Item="CUSTOMER_ORDER"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_CUSTOMER_ORDER,{"ID", "CUSTOMER_ID", "TOTAL_AMT_ORDERED", "CREATE_DATE"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Date.IsInCurrentYear([CREATE_DATE])),
    #"Extracted Month Name" = Table.TransformColumns(#"Filtered Rows", {{"CREATE_DATE", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"CUSTOMER_ID", "CREATE_DATE"}, {{"Total$$", each List.Sum([TOTAL_AMT_ORDERED]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[CREATE_DATE]), "CREATE_DATE", "Total$$", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"January", "February", "March", "April"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"January", Currency.Type}, {"February", Currency.Type}, {"March", Currency.Type}, {"April", Currency.Type}})
in
    #"Changed Type"

Hit Close and Load. This will achieve the same results.

Well in the same case you can write a Macro to refresh the data connection and load live data. Attach the macro to a button and you are good to go.

Sub Refresh()
ActiveWorkbook.RefreshAll
DoEvents
MsgBox ("Finished Refreshing")
End Sub

Hope the below template helps.