Link to home
Start Free TrialLog in
Avatar of Stevie Zakhour
Stevie Zakhour

asked on

Excel - Error Importing SQL Data

Hi Guys

I built the below query and set it up as a Stored Procedure in SQL 2008, query:

USE [Cosmos]
GO

/****** Object:  StoredProcedure [dbo].[Tour]    Script Date: 9/16/2017 10:28:34 PM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:     
-- Create date: 
-- Description:
-- =============================================

ALTER PROCEDURE [dbo].[TourDesk] 
@TourStartDate AS DATETIME, @TourEndDate AS DATETIME

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
   SET NOCOUNT ON;
-- Query
SELECT 
	t.sale_no as [Sales Number]
	,t.trans_no as [Transaction Number]
	,t.department as [Department]
	,t.category as [Category]
	,t.item as [Product Name]
	,CAST(t.date_time as DATETIME) as [Date of Sale]
	,t.init_price as [Initial Price]
	,t.quantity as [Quantity]
	,t.disc_amt as [Discount Amount]
	,t.disc_flat as [Discount Flat]
	,t.tax_amount as [Tax Amount]
	,t.extension as [Extension]
	,t.pcsplit_1 as [Profit Center EX GST]
	,SUM(CASE WHEN t.item in ('PJB.EB.AD', 'PJB.CO.CH', 'PJB.EB.CH', 'PJB.EB.FAM', 'PJB.PM.AD', 'PJB.PM.CH', 'PJB.PM.FAM') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item IN ('AQDUCK.FAM', 'AQDUCK.SEN', 'AQDUCK.AD', 'AQDUCK.CH') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item in ('SC.GW.AD', 'SC.GW.CH', 'SC.GW.SEN', 'SC.HOP.AD', 'SC.HOP.CH', 'SC.HOP.SEN', 'SC.NB.AD', 'SC.NB.CH', 'SC.NB.SEN', 'SC.OT.AD', 'SC.OT.CH', 'SC.OT.SEN', 'SC.WW.AD', 'SC.WW.CH', 'SC.WW.SEN') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item in ('FIRE4.AD', 'FIRE4.CH', 'FIRE4.FAM') THEN (t.quantity*(t.init_price*0.75)/1.1)
	WHEN t.item in ('WHALES.AD', 'WHALES.CH', 'WHALES.FAM', 'WHALES.SEN') THEN (t.quantity*(t.init_price*0.75)/1.1)
	WHEN t.item in ('DOLPH.AD', 'DOLPH.CH', 'DOLPH.FAM', 'DOLPH.SEN', 'DOLPH.T.BR', 'DOLPH.T.GC') THEN (t.quantity*(t.init_price*0.75)/1.1)
	WHEN t.item in ('BUGGY.FAM', 'BUGGY.SING', 'BUGGY.TWIN') THEN (t.quantity*(t.init_price*0.85)/1.1)
	WHEN t.item in ('DRAC.DIN.B', 'DRAC.FRI.A', 'DRAC.FRI.B', 'DRAC.VIP.A') THEN (t.quantity*(t.init_price*0.85)/1.1)
	WHEN t.item in ('GORIDE.AD', 'GORIDE.CH') THEN (t.quantity*(t.init_price*0.75)/1.1)
	WHEN t.item in ('HAUNT.AD', 'HAUNT.CH', 'HAUNT.FAM') THEN (t.quantity*(t.init_price*0.85)/1.1)
	WHEN t.item in ('MAGIC.AD', 'MAGIC.CH', 'MAGIC.V.AD', 'MAGIC.V.CH') THEN (t.quantity*(t.init_price*0.70)/1.1)
	WHEN t.item in ('JSKI.1.5HR', 'JSKI.1HR', 'JSKI.2.5HR') THEN (t.quantity*(t.init_price*0.75)/1.1)
	WHEN t.item in ('HOTAIR.30A', 'HOTAIR.30C', 'HOTAIR.60A', 'HOTAIR.60C') THEN (t.quantity*(t.init_price*0.70)/1.1)
	WHEN t.item in ('SCUBA.DIVE', 'SCUBA.SNOR') THEN (t.quantity*(t.init_price*0.80)/1.1)
	WHEN t.item in ('GCDIVE') THEN (t.quantity*(t.init_price*0.80)/1.1)
	WHEN t.item in ('TICKET') THEN (t.quantity*(t.init_price*0.85)/1.1)
	WHEN t.item in ('CONNECT') THEN (t.quantity*(t.init_price*0.83)/1.1)
	END) AS [Operator Revenue EX GST]

	,SUM(CASE WHEN t.item in ('PJB.EB.AD', 'PJB.CO.CH', 'PJB.EB.CH', 'PJB.EB.FAM', 'PJB.PM.AD', 'PJB.PM.CH', 'PJB.PM.FAM') THEN (t.quantity*(t.init_price*0.3)/1.1)-t.disc_amt
	WHEN t.item IN ('AQDUCK.FAM', 'AQDUCK.SEN', 'AQDUCK.AD', 'AQDUCK.CH') THEN (t.quantity*(t.init_price*0.3)/1.1)-t.disc_amt
	WHEN t.item in ('SC.GW.AD', 'SC.GW.CH', 'SC.GW.SEN', 'SC.HOP.AD', 'SC.HOP.CH', 'SC.HOP.SEN', 'SC.NB.AD', 'SC.NB.CH', 'SC.NB.SEN', 'SC.OT.AD', 'SC.OT.CH', 'SC.OT.SEN', 'SC.WW.AD', 'SC.WW.CH', 'SC.WW.SEN') THEN (t.quantity*(t.init_price*0.3)/1.1)-t.disc_amt
	WHEN t.item in ('FIRE4.AD', 'FIRE4.CH', 'FIRE4.FAM') THEN (t.quantity*(t.init_price*0.25)/1.1)-t.disc_amt
	WHEN t.item in ('WHALES.AD', 'WHALES.CH', 'WHALES.FAM', 'WHALES.SEN') THEN (t.quantity*(t.init_price*0.25)/1.1)-t.disc_amt
	WHEN t.item in ('DOLPH.AD', 'DOLPH.CH', 'DOLPH.FAM', 'DOLPH.SEN', 'DOLPH.T.BR', 'DOLPH.T.GC') THEN (t.quantity*(t.init_price*0.25)/1.1)-t.disc_amt
	WHEN t.item in ('BUGGY.FAM', 'BUGGY.SING', 'BUGGY.TWIN') THEN (t.quantity*(t.init_price*0.15)/1.1)-t.disc_amt
	WHEN t.item in ('DRAC.DIN.B', 'DRAC.FRI.A', 'DRAC.FRI.B', 'DRAC.VIP.A') THEN (t.quantity*(t.init_price*0.15)/1.1)-t.disc_amt
	WHEN t.item in ('GORIDE.AD', 'GORIDE.CH') THEN (t.quantity*(t.init_price*0.25)/1.1)-t.disc_amt
	WHEN t.item in ('HAUNT.AD', 'HAUNT.CH', 'HAUNT.FAM') THEN (t.quantity*(t.init_price*0.15)/1.1)-t.disc_amt
	WHEN t.item in ('MAGIC.AD', 'MAGIC.CH', 'MAGIC.V.AD', 'MAGIC.V.CH') THEN (t.quantity*(t.init_price*0.30)/1.1)-t.disc_amt
	WHEN t.item in ('JSKI.1.5HR', 'JSKI.1HR', 'JSKI.2.5HR') THEN (t.quantity*(t.init_price*0.25)/1.1)-t.disc_amt
	WHEN t.item in ('HOTAIR.30A', 'HOTAIR.30C', 'HOTAIR.60A', 'HOTAIR.60C') THEN (t.quantity*(t.init_price*0.30)/1.1)-t.disc_amt
	WHEN t.item in ('SCUBA.DIVE', 'SCUBA.SNOR') THEN (t.quantity*(t.init_price*0.20)/1.1)-t.disc_amt
	WHEN t.item in ('GCDIVE') THEN (t.quantity*(t.init_price*0.20)/1.1)-t.disc_amt
	WHEN t.item in ('TICKEt') THEN (t.quantity*(t.init_price*0.15)/1.1)-t.disc_amt
	WHEN t.item in ('CONNECT') THEN (t.quantity*(t.init_price*0.17)/1.1)-t.disc_amt
	END) AS [Recognised Revenue EX GST]

	,SUM(CASE WHEN t.item in ('PJB.EB.AD', 'PJB.CO.CH', 'PJB.EB.CH', 'PJB.EB.FAM', 'PJB.PM.AD', 'PJB.PM.CH', 'PJB.PM.FAM') THEN (t.quantity*(t.init_price*0.7))
	WHEN t.item IN ('AQDUCK.FAM', 'AQDUCK.SEN', 'AQDUCK.AD', 'AQDUCK.CH') THEN (t.quantity*(t.init_price*0.7))
	WHEN t.item in ('SC.GW.AD', 'SC.GW.CH', 'SC.GW.SEN', 'SC.HOP.AD', 'SC.HOP.CH', 'SC.HOP.SEN', 'SC.NB.AD', 'SC.NB.CH', 'SC.NB.SEN', 'SC.OT.AD', 'SC.OT.CH', 'SC.OT.SEN', 'SC.WW.AD', 'SC.WW.CH', 'SC.WW.SEN') THEN (t.quantity*(t.init_price*0.7)/1.1)
	WHEN t.item in ('FIRE4.AD', 'FIRE4.CH', 'FIRE4.FAM') THEN (t.quantity*(t.init_price*0.75))
	WHEN t.item in ('WHALES.AD', 'WHALES.CH', 'WHALES.FAM', 'WHALES.SEN') THEN (t.quantity*(t.init_price*0.75))
	WHEN t.item in ('DOLPH.AD', 'DOLPH.CH', 'DOLPH.FAM', 'DOLPH.SEN', 'DOLPH.T.BR', 'DOLPH.T.GC') THEN (t.quantity*(t.init_price*0.75))
	WHEN t.item in ('BUGGY.FAM', 'BUGGY.SING', 'BUGGY.TWIN') THEN (t.quantity*(t.init_price*0.85))
	WHEN t.item in ('DRAC.DIN.B', 'DRAC.FRI.A', 'DRAC.FRI.B', 'DRAC.VIP.A') THEN (t.quantity*(t.init_price*0.85))
	WHEN t.item in ('GORIDE.AD', 'GORIDE.CH') THEN (t.quantity*(t.init_price*0.75))
	WHEN t.item in ('HAUNT.AD', 'HAUNT.CH', 'HAUNT.FAM') THEN (t.quantity*(t.init_price*0.85))
	WHEN t.item in ('MAGIC.AD', 'MAGIC.CH', 'MAGIC.V.AD', 'MAGIC.V.CH') THEN (t.quantity*(t.init_price*0.70))
	WHEN t.item in ('JSKI.1.5HR', 'JSKI.1HR', 'JSKI.2.5HR') THEN (t.quantity*(t.init_price*0.75))
	WHEN t.item in ('HOTAIR.30A', 'HOTAIR.30C', 'HOTAIR.60A', 'HOTAIR.60C') THEN (t.quantity*(t.init_price*0.70))
	WHEN t.item in ('SCUBA.DIVE', 'SCUBA.SNOR') THEN (t.quantity*(t.init_price*0.80))
	WHEN t.item in ('GCDIVE') THEN (t.quantity*(t.init_price*0.80))
	WHEN t.item in ('TICKET') THEN (t.quantity*(t.init_price*0.85))
	WHEN t.item in ('CONNECT') THEN (t.quantity*(t.init_price*0.83))
	END) AS [Amount Payable to Operator INC GST]

	,t.message as [Message]
	,pmt.descrip as [Payment Method]
	,pmt.pmtype_no as [Payment Type ID]
	,cc.card_type as [Credit Card Type]
	,cc.host_msg as [Credit Card Tran MSG]
	,r.first_name as [Customer First Name]
	,r.last_name as [Customer Surname]
	,t.finalized as [Finalized]
	,t.salespoint as [POS]
	,t.operator as [Customer Service Name]

	FROM SiriusSQL_P.dbo.tr_save t

	LEFT JOIN SiriusSQL_P.dbo.resrvatn r ON r.reserv_no = t.reserv_no
	LEFT JOIN SiriusSQL_P.dbo.address a ON a.guest_no = r.guest_no
	LEFT JOIN SiriusSQL_P.dbo.usercod1 uc1 ON uc1.pri_key = r.user_code1
	LEFT JOIN SiriusSQL_P.dbo.usercod2 uc2 ON uc2.pri_key = r.user_code2
	LEFT JOIN Sirius_POSTransactions POS ON POS.sale_no = t.sale_no -- Working
	LEFT JOIN SiriusSQL_P.dbo.pmt_type pmt ON pmt.pmtype_no = pos.pmt_type
	LEFT JOIN SiriusSQL_P.dbo.cc_trans cc on cc.sale_no = t.sale_no

	WHERE t.quantity <> 0 AND t.extension <> 0
	AND t.date_time BETWEEN @TourStartDate AND @TourEndDate 
	AND t.category = 'tours'
	AND t.finalized <> 0

	group by 
	t.sale_no
	,t.trans_no
	,t.department
	,t.category
	,t.item
	,t.date_time
	,t.init_price
	,t.quantity
	,t.disc_amt
	,t.disc_flat
	,t.tax_amount
	,t.extension 
	,t.pcsplit_1 
	,t.message 
	,pmt.descrip 
	,pmt.pmtype_no
	,cc.card_type
	,cc.host_msg
	,r.first_name
	,r.last_name
	,t.finalized
	,t.salespoint
	,t.operator

	order by t.date_time
	END
	GO

Open in new window


Running the above query works well in SQL however what I'm trying to achieve is to allow a end user to enter a date range in Excel and then hit the generate report button which will run the above stored procedure in SQL. Creating a connection from Excel 2016 to SQL, I modified Connection Properties>Definitions>Command type: SQL and Command text: "Cosmos"."dbo"."Tour" ","

After modifying the Command text I clicked OK and then ended up getting an error message that says
Error converting data type nvarchar to datetime
.

How do I resolve this?

Any help is greatly appreciated!

Thanks
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
First the parameter character in Excel's MSQuery is ?, so you would type the code as "EXEC your_proc ?,?" to pass two parameters in theory.  However, I vaguely remember doing this before and it falling under the category of queries that Excel cannot visualize.  If Excel cannot visualize it, it doesn't allow parameters.  You can give it a shot though.

Once I get to my computer, I will test this out as I think I used a table-valued function in one instance.  I know views work but my guess is the data needs the date parameters before the grouping to come out correctly.  If not, simple solution is the create a view of the data unbound by date or using dynamic dates to lessen the data say to last five years or whatever history you want users to search then in Excel select from view with where clause.
Avatar of Stevie Zakhour
Stevie Zakhour

ASKER

Thanks Kevin, sorry, I believe there was a typo. In Excel>Connection Properties>Command text I have [code]EXEC "Cosmos"."dbo"."Tour" ","[/code]
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
No worries.  Note I wasn't saying you had the procedure call incorrect.  The part that has to change is the '',''.  You currently are sending two empty strings.  What you need to send are your dates.  To have those be parameters, the syntax is ?,? but some queries do not allow parameters in Excel because MSQuery cannot visualize the query (i.e., it is too complex a SQL expression).  The final solution will be one that simplifies expression for Excel.  Hence the reason a view with a simple where clause works for sure.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Stevie Zakhour
Stevie Zakhour

ASKER

That worked! Thanks Kevin, appreciate it!