Solved: Complicated SQL for a VB user

[cssamerican]

I am using Excel to access a Sql database in an effort to get the report I am looking for. However, I believe I have bit off a little more than I can handle when it comes to writing the sql statement this is what I have so far:


strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND Customers.ClassificationID = '1' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"


This gives me the records that I want; however, I am trying to back out a certain cost that is tied to resource type. These are the other fields that I think are needed to accomplish this are the following:

ResourceFPSDetail.ResourceClickCharge
ResourceFPSDetail.ResourceID
OrderStockDetail.NumImpressions
OrderStockDetail.OrderID
OrderStockDetail.ResourceID
Equipment.ResourceID
Equipment.ResourceType

What I would like is for it to take OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge WHERE Equipment.ResourceType <>3 and Sum that total for each record and create a column for that total. Then create a column for Orders.QuotePrice minus that Sum.

Is this possible or is this so complicated that I should find another way?

[malik641]

Hey,

This might work...though I'm not sure how efficient it will be...


SELECT
SUM( CASE e.ResourceType WHEN '3' THEN (osd.NumImpressions * rFPSd.ResourceClickCharge) END ) AS [Total]
FROM
((ResourceFPSDetail rFPSd INNER JOIN OrderStockDetail osd ON rFSPd.ResourceID=osd.ResourceID)
INNER JOIN Equipment e ON e.ResourceID=rFPSd.ResourceID)

You'll have to rebuild the string into your variable, by the way.
Oh, and you don't need to put "vbNewLine". The SQL query can be one long line without breaks.

[cssamerican]

This is the where I am at before you posted.


That worked...
So this is what I have now for output:

Query 1
Date|Job Number|Department|Account Number|Full Amount

Query 2
Job Number|Partial Amount

There are some job numbers that will not have a partial amount in which case I want that amount to be 0.

The final report should look like this:

Date|Job Number|Department|Account Number||(Amount-Partial Amount)|Partial Amount|Amount

Anymore ideas on how to get that?

strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"

strSQLTEST = "SELECT Orders.JobNumber, SUM(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)" & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "' AND OrderStockDetail.ResourceID <7" & vbNewLine & _
"GROUP BY Orders.JobNumber"

This is my atempt at integrating that CASE statement

strSQLJETEST = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"SUM(CASE OrderStockDetail.ResourceID WHEN < 7 THEN(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)END ) AS [Total] " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"

I am getting a syntax error though...Any help would be appreicated :banghead:


PS...I use the new lines so I can see everything on the screen when I am editing my code.

[malik641]

I'm a little busy at the moment, but at a quick glance:

CASE OrderStockDetail.ResourceID WHEN < 7 THEN

I believe should be:

CASE WHEN OrderStockDetail.ResourceID < 7 THEN

Let's see what that brings.

[cssamerican]

Still getting a syntax error near the keyword 'CASE'

Thanks for the help

[malik641]

I'm a little busy at the moment, but at a quick glance:

CASE OrderStockDetail.ResourceID WHEN < 7 THEN
I believe should be:

CASE WHEN OrderStockDetail.ResourceID < 7 THEN
Let's see what that brings.
Sorry...


CASE WHEN OrderStockDetail.ResourceID < '7' THEN
Note the '7' instead of just 7.

Try that. Good luck.

[cssamerican]

Same error:dunno
strSQLJETEST = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice " & vbNewLine & _
"SUM(CASE WHEN OrderStockDetail.ResourceID < '7' THEN(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)END ) AS [Total] " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'"

[malik641]

I'll have to look at this tonight or tomorrow morning. I'm not sure what's causing the error.

[cssamerican]

strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, " & vbNewLine & _
"SUM(CASE WHEN OrderStockDetail.ResourceID < '7' THEN(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)END ) AS [XTotal], SUM(CASE WHEN OrderStockDetail.ResourceID < '7' THEN(Orders.QuotePrice-(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge))END ) AS [MSTotal] , Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'" & vbNewLine & _
"GROUP BY Orders.JobNumber, Orders.StatusDate, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice"

Solved This is the final query...The error was coming from a missing comma

[malik641]

Aha! Man, and it was such a simple error, too. lol

So are the results what you were looking for?

[cssamerican]

I made a few changes to get the breakdown to my liking, but now I have exactly what I wanted!


strSQLJE = "SELECT Orders.StatusDate, Orders.JobNumber, Customers.CompanyName, Orders.AccountNo, " & vbNewLine & _
"SUM(CASE WHEN OrderStockDetail.ResourceID < '7' THEN(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)END ) AS [XTotal] , Orders.QuotePrice- SUM(CASE WHEN OrderStockDetail.ResourceID < '7' THEN(OrderStockDetail.NumImpressions * ResourceFPSDetail.ResourceClickCharge)ELSE 0 END ) AS [MSTotal], Orders.QuotePrice " & vbNewLine & _
"FROM Orders " & vbNewLine & _
"INNER JOIN Customers " & vbNewLine & _
"ON Orders.CustomerID = Customers.CustomerID " & vbNewLine & _
"INNER JOIN OrderStockDetail " & vbNewLine & _
"ON Orders.OrderID = OrderStockDetail.OrderID " & vbNewLine & _
"LEFT JOIN ResourceFPSDetail " & vbNewLine & _
"ON OrderStockDetail.ResourceID = ResourceFPSDetail.ResourceID " & vbNewLine & _
"WHERE Orders.StatusID = '3' AND StatusDate BETWEEN '" & ReportForm.SDate.Value & "' AND '" & ReportForm.EDate.Value & "'" & vbNewLine & _
"GROUP BY Orders.JobNumber, Orders.StatusDate, Customers.CompanyName, Orders.AccountNo, Orders.QuotePrice"


Thank A LOT! :cloud9:

[malik641]

Nice :thumb

Glad to help :)