Solved: Complicated SQL for a VB user

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:

[vba]
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 & "'"
[/vba]

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?

Hey,

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

Code:

---

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.

This is the where I am at before you posted.

Quote:

---

Originally Posted by cssamerican

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?

[vba]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"[/vba]

---

This is my atempt at integrating that CASE statement
[vba]
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 & "'"
[/vba]
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.

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

Code:

---

CASE OrderStockDetail.ResourceID WHEN < 7 THEN

---

I believe should be:

Code:

---

CASE WHEN OrderStockDetail.ResourceID < 7 THEN

---

Let's see what that brings.

Still getting a syntax error near the keyword 'CASE'

Thanks for the help

Quote:

---

Originally Posted by malik641

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

Code:

---

CASE OrderStockDetail.ResourceID WHEN < 7 THEN

---

I believe should be:

Code:

---

CASE WHEN OrderStockDetail.ResourceID < 7 THEN

---

Let's see what that brings.

---

Sorry...

Code:

---

CASE WHEN OrderStockDetail.ResourceID < '7' THEN

---

Note the '7' instead of just 7.

Try that. Good luck.

Same error:dunno
[VBA]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 & "'"[/VBA]

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

[VBA]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"[/VBA]

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

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

So are the results what you were looking for?

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

[VBA]
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"
[/VBA]

Thank A LOT! :cloud9:

Nice :thumb

Glad to help :)