July 16, 2012 at 9:21 am
Hi guys,
I'm having trouble to import data from microsoft query into the excel. After writing a big query and executing it successfully, I need to import this query to excel in order to send the data to my boss. However, this time I changed the way I set up my query and excel does not like to export data with this type of code. This is the code I think making the problem for excel to export data.
DECLARE @current_month INT;
DECLARE @previous_month INT;
DECLARE @previous_2_months INT;
DECLARE @previous_3_months INT;
SET @current_month =CAST(DATEPART(m,getdate())as INT);
SET @previous_month =CAST(DATEPART(m, DATEADD(m, -1, getdate()))as INT);
SET @previous_2_months =CAST(DATEPART(m, DATEADD(m, -2, getdate())) as INT);
SET @previous_3_months =CAST(DATEPART(m, DATEADD(m, -3, getdate())) as INT);
I have tried the case that what if I do not use Declare and set for datetime . Excel was able to successfully export the data. For example.
Before I change I used this :
DECLARE @current_month INT;
DECLARE @previous_month INT;
DECLARE @previous_2_months INT;
DECLARE @previous_3_months INT;
SET @current_month =CAST(DATEPART(m,getdate())as INT);
SET @previous_month =CAST(DATEPART(m, DATEADD(m, -1, getdate()))as INT);
SET @previous_2_months =CAST(DATEPART(m, DATEADD(m, -2, getdate())) as INT);
SET @previous_3_months =CAST(DATEPART(m, DATEADD(m, -3, getdate())) as INT);
SELECT
[branch_id]+' - '+[branch_description] as branch
,COALESCE(p21_view_oe_hdr.class_2id,'STORE') as invoice_status
,@current_month as current_period
,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_price] ELSE 0 END) as current_month_sales
This is how I changed:
SELECT
[branch_id]+' - '+[branch_description] as branch
,COALESCE(p21_view_oe_hdr.class_2id,'STORE') as invoice_status
,@current_month as current_period
,SUM(CASE WHEN [p21_sales_history_report_view].period = CAST(DATEPART(m,getdate())as INT THEN [detail_price] ELSE 0 END) as current_month_sales
and I had to delete the "Declare" and "Set" time as the begginning.
As the conclusion, I believe the problem comes from Declare and Set function. In order to keep it easy to read the code in SQL, declare and set function are very necessary for me. If you guys know how to fix this problem by keeping the code "before I changed". Please let me know, I really appreaciate it. As I researched for hours online and could not find an absolute solution for this.
July 16, 2012 at 9:34 am
Personally, I would create a stored procedure and call that from Excel. That way, the user cannot change the query and you can use all of the SQL features.
Jared
CE - Microsoft
July 16, 2012 at 9:39 am
Do you know how to create a procedure for the query? I do it on Excel or SQL
July 16, 2012 at 9:42 am
Use google and search for SQL Server CREATE PROCEDURE
You will create a stored procedure in SQL Server that contains all of the code needed. Then Excel will simply execute that stored procedure.
Jared
CE - Microsoft
July 16, 2012 at 10:03 am
Thanks it works.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply