Solved

EXCEL/VBA sql

Posted on 2013-01-30
9
216 Views
Last Modified: 2013-02-01
Hello,
I use below code in a vba code,

I need to have the
@FromZoneID = Cell A1
@ToZoneID = Cell B1

Can you please help

Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from
(Select (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) AS [PlanName],
-- (Select ServiceTypeID  from PricePlans where PricePlans.Priceplanid = PricesZone.Priceplanid) as [ServiceTypeID],
(select Description From Zones where ZoneID = ZoneFromID) AS [FromZone],
(select Description From Zones where ZoneID = ZoneToID) AS [ToZone],Price
From priceszone where PricePlanID in (3637,2834,2874,2876,2878,2880)
AND (ZONEFROMID = @ZONEFROMID OR ZONETOID = @ZONETOID)
)M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt

Thank you
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
9 Comments
 

Author Comment

by:W.E.B
ID: 38836871
Here is my full code

Sub Local()
 
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range
    Dim Dterng As Variant
    Dim FrmDate As Variant

   
    Const stADO As String = "Provider=SQLOLEDB.1; Uid=sa; Pwd=c11111;" & _
    "Initial Catalog=database;" & _
    "Data Source=Laptop"
 
    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)
     
    With wsSheet
        Set rnStart = .Range("A2")
    End With

 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from
(Select (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) AS [PlanName],
(select Description From Zones where ZoneID = ZoneFromID) AS [FromZone],
(select Description From Zones where ZoneID = ZoneToID) AS [ToZone],Price
From priceszone where PricePlanID in (3637,2834,2874,2876,2878,2880)
AND (ZONEFROMID = @ZONEFROMID OR ZONETOID = @ZONETOID)
)M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

    Set cnt = New ADODB.Connection
     
     'Delete old data
    ActiveSheet.Range("A2:Z65000").ClearContents
   
    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With
     
    With wsSheet
        Set rnStart = .Range("A2")
    End With
 
    rnStart.CopyFromRecordset rst
 
    rst.Close
    cnt.Close
 
    Set rst = Nothing
    Set cnt = Nothing
   
       MsgBox "Records Found"
       
End Sub
0
 

Author Comment

by:W.E.B
ID: 38837583
Any help is appreciated.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38842232
 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from " & _
"(Select (select Description from PricePlans " & _
"where PricePlanID = PricesZone.PricePlanID) AS [PlanName], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneFromID) AS [FromZone], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneToID) AS [ToZone],Price " & _
"From priceszone " & _
"where PricePlanID in (3637,2834,2874,2876,2878,2880) " & _
"AND (ZONEFROMID = " & wsSheet.[a1] & " OR ZONETOID = " & _
wsSheet.[b1] & ") " & _
")M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

Open in new window

0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

Author Comment

by:W.E.B
ID: 38843686
Hello,
thank you matthewspatrick,

if A1,B1 were texts , do I still use the same?
example:
cell A1 = T01(XXX)
cell B1= M01(YYY)

Thanks again.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38843714
It would change:

 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from " & _
"(Select (select Description from PricePlans " & _
"where PricePlanID = PricesZone.PricePlanID) AS [PlanName], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneFromID) AS [FromZone], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneToID) AS [ToZone],Price " & _
"From priceszone " & _
"where PricePlanID in (3637,2834,2874,2876,2878,2880) " & _
"AND (ZONEFROMID = '" & wsSheet.[a1] & "' OR ZONETOID = '" & _
wsSheet.[b1] & "') " & _
")M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

Open in new window


Note how I added single-quotes around the parameter values.
0
 

Author Comment

by:W.E.B
ID: 38843725
perfect,
thanks again.
0
 

Author Closing Comment

by:W.E.B
ID: 38843728
Thank you
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question