Solved

EXCEL/VBA sql

Posted on 2013-01-30
9
214 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
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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