I am trying to prepare a report (monthly, year to date, etc.) in a group of shipping records in our database to show the correct amounts, as well as group them in a shipping company. How can I complete all this information and prepare relevant reports?
It should be noted that all entries in this table are nvarchar (max) due to the import of text with odd characters from different sources.
The list of main databases looks something like this:
table - shipping_info
shipping_company | ship_date | shipping_category | shipping_cost UPS | 20130301 | CD | 3.50 UPS | 20130310 | Records | 4.50 UPS | 20130322 | CD | 7.50 UPS | 20130313 | Tapes | 12.44 UPS | 20130324 | CD | 3.50 UPS | 20120312 | Records | 4.50 UPS | 20120304 | CD | 5.50 UPS | 20120306 | Tapes | 3.50 UPS | 20130309 | CD | 3.50 USPS | 20130301 | Tapes | 3.50 USPS | 20130301 | CD | 5.50 USPS | 20130301 | CD | 4.50 USPS | 20130201 | Tapes | 3.50 USPS | 20130201 | CD | 3.50 USPS | 20130201 | Records | 7.50 USPS | 20130201 | Tapes | 9.50 USPS | 20130201 | CD | 12.50 USPS | 20120301 | CD | 14.50 USPS | 20120301 | Records | 3.50 USPS | 20130301 | CD | 23.50 USPS | 20120301 | Tapes | 15.50 USPS | 20120301 | CD | 34.50 DHL | 20120301 | Tapes | 35.50 DHL | 20120301 | CD | 3.50 DHL | 20130301 | Tapes | 3.50 DHL | 20130401 | CD | 3.50 DHL | 20130401 | Records | 4.50 DHL | 20130501 | CD | 4.50 DHL | 20120201 | Tapes | 5.50 DHL | 20120101 | CD | 6.50 DHL | 20120501 | CD | 3.50 DHL | 20120301 | Tapes | 7.50
My goal is to get a final list that will print the filtered shipping_company list of the total shipping cost per month. Down the line I would like to get a list from year to date and the like. In any case, what I would like to see for the monthly reports looks something like this:
UPS Shipping Cost Report for March 2013 CD - $23.50 Records - $9.00 Tapes - $15.94
What I tried first
SELECT shipping_company, ship_date, shipping_category, shipping_cost FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '') GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost
This created a great filtered table, but I still needed to calculate the amounts and group the totals together into categories.
Then I tried this ....
SELECT shipping_company, ship_date, shipping_category, SUM(shipping_cost) FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '') GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost
Since this is an NVARCHAR (MAX) table, t-sql retired with an error, telling me that I need to convert shipping_cost to int.
So ... next time I tried this.
SELECT shipping_company, ship_date, shipping_category, convert(decimal(10, 2), shipping_cost) FROM shipping_info WHERE (shipping_company = 'UPS' and shipping_cost IS NOT NULL and shipping_cost != '') GROUP BY shipping_company, ship_date, shipping_category, shipping_cost ORDER BY shipping_company, ship_date, shipping_category, shipping_cost
What kind of work and what did not.
Now I’m at a dead end, in which direction from the first example, in order to proceed with the breakdown of this list into reportable segments. Am I on the right track to receive my information in the format of a monthly report that I need?
:: EDIT / UPDATE ::
It is great and works. I spent time on every single step of this code, and now I understand the theory behind what happens here. There is one last thing that I knock on here. Since this table is NVARCHAR (MAX), I am having trouble trying to get ship_date to convert to a numeric value without success.
I took the source code below - EXAMPLE 1:
select CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL' ELSE shipping_company END AS shipping_company, CASE WHEN (GROUPING(SUBSTRING(ship_date, 1, 6)) = 1) THEN 'TOTAL' ELSE SUBSTRING(ship_date, 1, 6) END AS Date, CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL' ELSE shipping_category END AS shipping_category, sum(convert(decimal(10,2),shipping_cost)) from tbl group by shipping_company, SUBSTRING(ship_date, 1, 6), shipping_category WITH rollup
And then we added the numerical conversion commands as follows - EXAMPLE 2:
select CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL' ELSE shipping_company END AS shipping_company, CASE WHEN (GROUPING(CONVERT(numeric, (SUBSTRING(ship_date, 1, 6))) = 1) THEN 'TOTAL' ELSE (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))) END AS Date, CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL' ELSE shipping_category END AS shipping_category, sum(convert(decimal(10,2),shipping_cost)) from tbl group by shipping_company, (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))), shipping_category WITH rollup
But still, nvarchar is converted to numeric errors. Then I moved things like this. Example 3:
select CASE WHEN (GROUPING(shipping_company) = 1) THEN 'TOTAL' ELSE shipping_company END AS shipping_company, CASE WHEN (CONVERT(numeric, GROUPING(SUBSTRING(ship_date, 1, 6))) = 1) THEN 'TOTAL' ELSE (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))) END AS Date, CASE WHEN (GROUPING(shipping_category) = 1) THEN 'Yearly TOTAL' ELSE shipping_category END AS shipping_category, sum(convert(decimal(10,2),shipping_cost)) from tbl group by shipping_company, (CONVERT(numeric, SUBSTRING(ship_date, 1, 6))), shipping_category WITH rollup
and got "Argument 1 of the grouping function does not match any expressions in the GROUP BY clause", which makes sense. I really would like to study the theory not only about why the second example does not work, but also about what the correct course of action had to do in order for the transformations to work.