none
VB/Excel to run a Query RRS feed

  • Question

  • Was advised to move this here....

    Hi, first post here... give me some space and let me know if you need more info!

    Basically I am pretty inexperienced in SQL and need some help with code and possibly logistically of what I am hoping to do.

    At the moment I have some tables and have written a query which is run each day to output for each email address, a time variable and two counting fields. The emails only get selected if the counts are over a certain value inside a certain timeframe. This is outputting into a pivot table at the moment so that the time can be varied and the counts.

    What I would like to do is be able to automate to certain degree... So...

    1. Run the query possibly from excel or a VB window
    2. Print the emails onto the pivot table as already done
    3. On another excel sheet have a list of emails meeting the criteria of, for example Count1>2 and Count2>2. So have a list of all emails, historic and new.
    4. Generate an excel list of only the NEW emails added to the list in 3. on that day.

    Am I thinking of this in the correct way or should I try and do less/more with Macros and Excel.

    Any help would be appreciated!!
    Thanks

    FYI I am using SQL Server 2005, this is basically the code I have used to generate the pivot table....

    drop table analysis.dbo.email
    
    
    
    select	*,
    
    		datediff(dd,getdate(),date) as days_old
    
    into	analysis.dbo.email
    
    from	dbo.Mastertable(nolock)
    
    where	variable = variable
    
    and		datediff(dd,getdate(),date) >= -30
    
    
    
    drop table #temp
    
    go
    
    
    
    select 
    
    firstname,
    
    surname,
    
    email,
    
    ID,
    
    number,
    
    days_old
    
    into  #temp
    
    from analysis.dbo.email
    
    go	   		
    
    
    
    select	count(distinct ID) as transactions,
    
    		count(distinct number) as numbers,
    
    		email as groups,
    
    		7 as day_flag	
    
    from #temp
    
    where days_old between -7 and -1
    
    group by email, ID, number
    
    go
    
    
    Monday, July 20, 2009 4:26 PM

All replies

  • Hi, first post here... give me some space and let me know if you need more info!

    Basically I am pretty inexperienced in SQL and need some help with code and possibly logistically of what I am hoping to do.

    At the moment I have some tables and have written a query which is run each day to output for each email address, a time variable and two counting fields. The emails only get selected if the counts are over a certain value inside a certain timeframe. This is outputting into a pivot table at the moment so that the time can be varied and the counts.

    What I would like to do is be able to automate to certain degree... So...

    1. Run the query possibly from excel or a VB window
    2. Print the emails onto the pivot table as already done
    3. On another excel sheet have a list of emails meeting the criteria of, for example Count1>2 and Count2>2. So have a list of all emails, historic and new.
    4. Generate an excel list of only the NEW emails added to the list in 3. on that day.

    Am I thinking of this in the correct way or should I try and do less/more with Macros and Excel.

    Any help would be appreciated!!
    Thanks

    FYI I am using SQL Server 2005, this is basically the code I have used to generate the pivot table....

    drop table analysis.dbo.email
    
    select	*,
    		datediff(dd,getdate(),date) as days_old
    into	analysis.dbo.email
    from	dbo.Mastertable(nolock)
    where	variable = variable
    and		datediff(dd,getdate(),date) >= -30
    
    drop table #temp
    go
    
    select 
    firstname,
    surname,
    email,
    ID,
    number,
    days_old
    into  #temp
    from analysis.dbo.email
    go	   		
    
    select	count(distinct ID) as transactions,
    		count(distinct number) as numbers,
    		email as groups,
    		7 as day_flag	
    from #temp
    where days_old between -7 and -1
    group by email, ID, number
    go
    Monday, July 20, 2009 3:57 PM
  • Hi,
    This is not the right forum for this, you could check it in the T-SQL forum or the VB forums.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Monday, July 20, 2009 4:02 PM
  • Can it be moved there or should I copy and paste?
    Monday, July 20, 2009 4:09 PM
  • A lot of this code can be consolidated into a single statement.  for example, there is no reason to drop/create the table analysis.dbo.email and then load its results into a temp table.  You can select from dbo.MasterTable and apply the filters.   If the final desired result is the distinct counts, then just do your grouping then.  The bottom line is your Excel spreadsheet can use a single DSN to connect to SQL Server.  You can then create your pivot tables off the data returned by the SQL Query. 

    I also did not understand why the first filter is restricting data to 30 days and the final filter restricts the data to a week. 
    http://jahaines.blogspot.com/
    Monday, July 20, 2009 4:39 PM
    Moderator
  • moderators could do it.
    Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.
    Monday, July 20, 2009 4:42 PM
  • Hi, the main issue is with generating the tables into excel. With regard to the code... the table drops and the filters are there because it is not just the single list being produced. It involves several other lists inside several other timeframes. I thought it would be best just to drop an example section of the code.
    Tuesday, July 21, 2009 12:09 PM



  • In MS Excel you can select from the menu Data->Import External Data->New Database Query.
    If you setup a system ODBC datasource then you can have the query results displayed in excel. They will also be easy to refresh as this is just done by pressing the Refresh Data button.

    This way you can use MS Excel as a 'front end' for the SQL Data / queries as long as you have SQL Statements to get the data you need



    MCITP: Database Administrator
    ---
    Mongol General: Conan! What is best in life?
    Conan: To crush your enemies, see them driven before you, and to hear the lamentation of their women.
    Tuesday, July 21, 2009 1:48 PM