Are you really good at SQL programming?

[Elisha 1]

Serious question. I'm a part timer at this and rather intermediate-ish (if that) and need to create a file to dump into some 3rd party reporting system.

The file needs to be in this form:

Customer Product #_of_cases_TY #_of_cases_LY
ABC Liquor Coors 12pks 103 105
ABC Liquor Pabst 6pks 89 73

Where cases TY = "this year" rolling 12 months (i.e. 9/2013 - 8/2014) and LY = "last year" rolling 12 months (i.e. 9/2012 - 8/2013). I can get the individual aggregate periods, but don't know how join or "pivot" the two periods to get them side by side. Yes, there will be Customers and products in each period that are not in the other (i.e. need to have 0 values where one time period doesn't have data in the other).

Can our awesome dz.com crowd help?

[Arvoitus 1]

I'm not an expert with SQL but wouldn't something like:


SELECT SUM(CASE WHEN x.pdate >= DATE_ADD(NOW(), INTERVAL -1 YEAR) THEN 1 ELSE 0 END) AS p_ty, SUM(CASE WHEN x.pdate >= DATE_ADD(NOW(), INTERVAL -2 YEAR) AND x.pdate <= DATE_ADD(NOW(), INTERVAL -1 YEAR) THEN 1 ELSE 0 END) AS p_ly, y.name FROM table_with_product_purchases x LEFT JOIN table_with_product_names y ON x.pid = y.id GROUP BY x.pid;


work? I don't know your tables but assuming the table 'table_with_product_names' has an INT id and VARCHAR name and the table table_with_product_purchases has INT pid (ref to product ID) and pdate with the date it was purchased that query should work.

Your rights end where my feelings begin.

[DiverMike 5]

You can't use a LEFT JOIN and expect to get records from both tables. You will get all the records from the left table, but will not get the records in the right table that don't exist in the Left table.
You would have to use a FULL OUTER JOIN. See example:

http://www.w3schools.com/sql/sql_join_full.asp

For the same reason I jump off a perfectly good diving board.

[Elisha 1]

Arvoitus

I'm not an expert with SQL but wouldn't something like:


SELECT SUM(CASE WHEN x.pdate >= DATE_ADD(NOW(), INTERVAL -1 YEAR) THEN 1 ELSE 0 END) AS p_ty, SUM(CASE WHEN x.pdate >= DATE_ADD(NOW(), INTERVAL -2 YEAR) AND x.pdate <= DATE_ADD(NOW(), INTERVAL -1 YEAR) THEN 1 ELSE 0 END) AS p_ly, y.name FROM table_with_product_purchases x LEFT JOIN table_with_product_names y ON x.pid = y.id GROUP BY x.pid;




work? I don't know your tables but assuming the table 'table_with_product_names' has an INT id and VARCHAR name and the table table_with_product_purchases has INT pid (ref to product ID) and pdate with the date it was purchased that query should work.

I keep getting errrors around the outer ')' ...doesn't seem to make sense, as I have other CASE statements with the same syntax working fine.

[bob.dino 0]

What's the database?

[Elisha 1]

bob.dino

What's the database?

SQL Server...working on 2014 to be precise.

[Elisha 1]

Update: I was able to successfully create (or should I say 'Pivot') one of the three queries (actually, really only two - one is a slight variant of the other with just a different time period). The one I successfully did involved one quantitative field, pivoting months from vertical to horizontal.

I used this basic syntax:

Sample data:

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Here is the result set.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105

Example:
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

which produces:

Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105

By the way, I have 17 qualitative columns preceding, but basically the Customer/product combo is the basic concept (data pulled by date though).

Now, I need to pivot multiple quantitative fields as previously mentioned , but group to two time periods (This year and Last year or whatever) and then of course have units_TY, units_LY, $_TY, $_LY, etc....(about 10 quantitative fields total). I'm not sure if the above will work for several. I think I'll try a CASE statement in the select in the meantime.

Help?

[DiverMike 5]

I'll be honest with you. I have made queries for multiple time frames like you are trying to do, but I could never figure out the elegant way to do it. I created a temporary table that had the two time frames defined in it and then built records into it. The first timeframe SELECT statement was basically a INSERT INTO. The second SELECT statement I had to step through the recordset and INSERT or UPDATE the temp tabale as needed. SOME SQL languages allow a MODIFY command to add a record or update existing, but I'm pretty sure SQL 2014 does not.
Once the temp table was created, I could do a simple SELECT * to get everything, then DROP the table when I was done. As you can imagine, this is a brute force way of doing it that uses no SQL Optimization.

So I admit I am not a really good SQL Programmer , but I could do it in VB.

For the same reason I jump off a perfectly good diving board.

[Elisha 1]

Ok, it looks like I got it with the Pivot function. I had to use it 7 times though.

Anyway, I'm having some div 0 errors that I can't seem to figure out.

For the purpose of this query: TY - "This Year", LY - "Last Year", Diff - (TY-LY), and Diff pct - (TY-LY)/LY.

So for example:
IsNull(Sum(Vol_TY)/NullIf(Sum(NumUnits_TY),0),0) AS AvgUnitPrice_TY, (works fine)
IsNull(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0),0) AS AvgUnitPrice_LY, (works fine)
(IsNull(Sum(Vol_TY)/NullIf(Sum(NumUnits_TY),0),0) - IsNull(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0),0)) AS AvgUnitPrice_Diff, (works fine)
--IsNull((IsNull(Sum(Vol_TY)/NullIf(Sum(NumUnits_TY),0),0) - IsNull(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0),0))/NullIf(Sum(Vol_LY)/Sum(NumUnits_LY),0),0) AS AvgUnitPrice_Diff_Pct, (DIV 0 error AARRRRGGGHHH!!!)

(Yes, I obviously commented it out because of the error.)

The fields are dollar sales volume and number of units (to get the average price per unit).

As an aside, I asked those who were involved in creating the tool I'm dumping the data into why they want fields created for the Diff and percent and not just have the tool calculate them (like any BI tool would) and they didn't have a good answer. That as they say is another discussion...

[Elisha 1]

Nevermind - looks like I fixed it.

IsNull((IsNull(Sum(Vol_TY)/NullIf(Sum(NumUnits_TY),0),0) - IsNull(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0),0))/NullIf(Sum(Vol_LY)/Sum(NumUnits_LY),0),0) AS AvgUnitPrice_Diff_Pct, (Div 0)

IsNull((IsNull(Sum(Vol_TY)/NullIf(Sum(NumUnits_TY),0),0) - IsNull(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0),0))/(Sum(Vol_LY)/NullIf(Sum(NumUnits_LY),0)),0) AS AvgUnitPrice_Diff_Pct, (Works!)

[DeeBeeGee 0]

SQL is very good for set based operations but a bit awkward for complex aggregations as you are finding out. If you are going to be doing a lot of this then try installing SQL Server Analysis Services (it's in the box!) which is designed to do everything you are looking to do and a LOT more. There's a bit to learn but it's a great tool

[Elisha 1]

DeeBeeGee

SQL is very good for set based operations but a bit awkward for complex aggregations as you are finding out. If you are going to be doing a lot of this then try installing SQL Server Analysis Services (it's in the box!) which is designed to do everything you are looking to do and a LOT more. There's a bit to learn but it's a great tool

Yup, I'm aware of it. In my present job situation, unfortunately my time is taken up by the boring stuff (very admin/data entry). If I can convince the uppers to hand off lots of this, then time can be freed up.

This specific situation, as first mentioned, is to get a file to import into some third party app. Thems the specs - I'm just trying to figure out how to get the data into the required form.