Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Query Database Using SQL statement and bring to Excel

Hi Anyone,
I need to bring into Excel 2013 a daily Trial Balance from a SQL database of financial figures.
I have the SQL statement;
DECLARE @ReportDate DateTime
SELECT TOP 1 @ReportDate = HotelDate From tblHotels
SET @ReportDate = @ReportDate + {Offset:number}
DECLARE @ReportDatePlusOneDay DateTime
DECLARE @PostingType_Sale Int
DECLARE @PostingType_VAT Int
SET @ReportDatePlusOneDay=@ReportDate+1;
SET @PostingType_Sale=1;
SET @PostingType_VAT=3;
SELECT
      tblAccounts.Code,
      tblAccounts.Description,
      GrossAmount = (
            SELECT ISNULL(SUM(tblPostings.Credit - tblPostings.Debit),0)
            FROM tblPostings
            INNER JOIN tblJournals jtev ON jtev.JournalID = tblPostings.JournalID
            WHERE (tblPostings.AccountID = tblAccounts.AccountID)
            AND (tblPostings.PostingType <> @PostingType_VAT)
            AND (jtev.JournalDate >= @ReportDate)
            AND (jtev.JournalDate < @ReportDatePlusOneDay))
      -- VAT
      + (
            SELECT ISNULL(SUM(tblPostings.Credit - tblPostings.Debit),0)
            FROM tblPostings
            INNER JOIN tblJournals jtiv ON jtiv.JournalID = tblPostings.JournalID
            WHERE EXISTS (SELECT *
                                FROM tblPostings
                                WHERE (tblPostings.AccountID = tblAccounts.AccountID)
                                AND (jtiv.JournalID = tblPostings.JournalID)
                                AND (tblPostings.PostingType = @PostingType_Sale))
            AND (tblPostings.PostingType = @PostingType_VAT)
            AND (jtiv.JournalDate >= @ReportDate)
            AND (jtiv.JournalDate < @ReportDatePlusOneDay))
FROM tblAccounts
ORDER BY tblAccounts.Code

BUT - I need to base the whole query on an automated date in a cell in Excel and not the date at the top of tblHotels

Therefore the question is, how do I set the parameter in this instance?

Thanks,
Andrew
0
Andrew Prior
Asked:
Andrew Prior
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's an article on how to hotwire Excel into SQL Server to pass cell-based parameters into a SQL Stored Procedure, and return the result set back to Excel, complete with a whole lot of images to show how it's done, and the doc containing source code at the end.
0
 
RayData AnalystCommented:
" & Range("D1").Value & "

Change the cell value from "D1" to whatever cell you want the value from.  For example if D1 contained the value: TEST

then
SQL =  " from " & Range("D1").Value & ".TableName "

would be passed to sql as
from TEST.TableName
0
 
Andrew PriorAuthor Commented:
Really need the date format converted from Excel date
0
 
RayData AnalystCommented:
Converted how? From what to what?  It should pass to sql in the same format as the cell it comes from. So likely you can just change the cell format and get what you need.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now