Change to user input prompt?

Question

Post reply

Change to user input prompt?

serviceaellis

SSCommitted

Points: 1865
More actions
August 25, 2015 at 11:36 am

#320878

This is the SQL:
SELECT DISTINCT
person.MembershipNumber, person.FirstName + ' ' + person.LastName AS NAME, person.FirstName, person.RegionId AS REGION,
addr.StreetOne, addr.StreetTwo, ISNULL(unit.Description, '') + ' ' + addr.SubUnit AS 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode AS 'CityStateZip',
addrState.Code AS 'State', lookup.Country.Description AS 'Country', CONVERT(char(10), pm.EndDate, 101) AS EndDate, addr.PostalCode, pm.HomeClub
FROM (SELECT attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS HomeClub, attribute.PersonMembership.MembershipTypeId,
attribute.PersonMembership.InvoiceNumber, attribute.PersonMembership.EndDate
FROM attribute.PersonMembership LEFT OUTER JOIN
USFSA.dbo.SOP10100 AS invWork ON attribute.PersonMembership.InvoiceNumber = invWork.SOPNUMBE LEFT OUTER JOIN
USFSA.dbo.SOP30200 AS invHist ON attribute.PersonMembership.InvoiceNumber = invHist.SOPNUMBE INNER JOIN
lookup.MemberTypes AS mt ON mt.Id = attribute.PersonMembership.MembershipTypeId AND mt.MemberGroup = 'Regular Member' LEFT OUTER JOIN
entity.Organization AS org ON org.Id = attribute.PersonMembership.OrganizationId
WHERE (attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE())) AND (SUBSTRING(invWork.BACHNUMB, 1, 8) >= '20150601' AND
SUBSTRING(invWork.BACHNUMB, 1, 8) <= '20150710' OR
SUBSTRING(invHist.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invHist.BACHNUMB, 1, 8) <= '20150710')) AS pm INNER JOIN
entity.Person AS person ON person.Id = pm.PersonId LEFT OUTER JOIN
lookup.TitlePrefix ON person.PrefixId = lookup.TitlePrefix.Id AND lookup.TitlePrefix.Id > 0 LEFT OUTER JOIN
lookup.TitleSuffix ON person.SuffixId = lookup.TitleSuffix.Id AND lookup.TitleSuffix.Id > 0 INNER JOIN
attribute.Address AS addr ON person.PrimaryAddressId = addr.Id LEFT OUTER JOIN
lookup.AddressSubUnit AS unit ON unit.Id = addr.SubUnitTypeId AND addr.SubUnitTypeId <> 0 LEFT OUTER JOIN
lookup.State AS addrState ON addr.StateId = addrState.Id LEFT OUTER JOIN
lookup.Country ON addr.CountryId = lookup.Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42
ORDER BY addr.PostalCode
How do I change this for user input for the date range?
WHERE (attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE())) AND (SUBSTRING(invWork.BACHNUMB, 1, 8) >= '20150601' AND
SUBSTRING(invWork.BACHNUMB, 1, 8) <= '20150710' OR
SUBSTRING(invHist.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invHist.BACHNUMB, 1, 8) <= '20150710')) AS pm INNER JOIN

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Create 2 variables for the start and end dates of your range and use those instead of the hard coded dates you used.

How is the user going to interact with the query to supply the dates?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

serviceaellis SSCommitted Points: 1865 More actions · Answer 2

Alvin Ramard (8/25/2015)
Create 2 variables for the start and end dates of your range and use those instead of the hard coded dates you used.
How is the user going to interact with the query to supply the dates?

Right, and looking for the correct way to do that.

I tried a couple different ways

[From Date] and [To Date] (Which is used in Access but not in SQL)

as well as {From Date} and {To Date}

I've tried searching a few different ways but can't seem to find something I understand / works as thought.

This code will be used in Excel. Using the External source link.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

serviceaellis (8/25/2015)
Alvin Ramard (8/25/2015)
Create 2 variables for the start and end dates of your range and use those instead of the hard coded dates you used.
How is the user going to interact with the query to supply the dates?
Right, and looking for the correct way to do that.
I tried a couple different ways
[From Date] and [To Date] (Which is used in Access but not in SQL)
as well as {From Date} and {To Date}
I've tried searching a few different ways but can't seem to find something I understand / works as thought.
This code will be used in Excel. Using the External source link.

Are you saying you don't know how to create and use variables in SQL?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

This code will be used in Excel. Using the External source link.

maybe best if you explain how you are expecting Excel and SQL to interact first....then we can move on

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

J Livingston SQL (8/25/2015)
This code will be used in Excel. Using the External source link.
maybe best if you explain how you are expecting Excel and SQL to interact first....then we can move on

If the query will come from Excel, then you might want to start here: http://lmgtfy.com/?q=Using+parameters+in+Excel+query#

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

serviceaellis SSCommitted Points: 1865 More actions · Answer 6

The issue, I think for me, is it's subquery. Therefore, the way I know how to link the SQL in Excel, you do not see those fields in question.

If they were, I know how to do the parameter in Excel against visible fields.

Don't know if that made sense.

To me, when I link the SQL or apply, etc ... the fields that are produced in Excel does not include the dates in question. So I do not know how to then set a parameter using the Add Criteria in Excel's MS Query.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

I remember that Excel is hard to deal with when you have a query that cannot be expressed visually.

I have had to create macros before, that would read the parameters from given cell to create the query that would be executed using ADO. The macro would then write the results to the worksheet.

Excel can be great sometimes, but a pain some other times.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

out of curiosity....what is the size of the data you are dealing with...eg number of rows in your tables?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

serviceaellis SSCommitted Points: 1865 More actions · Answer 9

J Livingston SQL (8/25/2015)
out of curiosity....what is the size of the data you are dealing with...eg number of rows in your tables?

in the date range there's 36,501 records

serviceaellis SSCommitted Points: 1865 More actions · Answer 10

I realize this is a SQL forum and why I've only shared the SQL.

And in SSMS it produces the results.

However in Excel there's this Function:

Sub Button1_Click()

Dim StartingBatchDate As String, EndingBatchDate As String, Split1 As String, Split2 As String, Split3 As String, Split4 As String, AdditionalMemberNumbers As String

StartingBatchDate = Range("B2")

EndingBatchDate = Range("B3")

With ActiveWorkbook.Connections("RegularMemberships").OLEDBConnection

Split1 = "SELECT DISTINCT person.MembershipNumber, " & _

"person.FirstName + ' ' + person.LastName as NAME, person.FirstName, person.RegionId as REGION" & _

"addr.StreetOne, addr.StreetTwo, ISNULL(unit.description, '') + ' ' + addr.Subunit 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode 'CityStateZip', addrState.Code 'State', Country.Description 'Country', " & _

"pm.HomeClub, CONVERT( char(10), pm.EndDate, 101 ) EndDate, addr.PostalCode "

Split2 = "FROM ( SELECT PersonId, ISNULL( OrganizationName, 'Individual' ) HomeClub, MembershipTypeId, InvoiceNumber, EndDate " & _

"FROM attribute.PersonMembership pm " & _

"LEFT JOIN USFSA.dbo.SOP10100 invWork ON InvoiceNumber = invWork.SOPNUMBE " & _

"LEFT JOIN USFSA.dbo.SOP30200 invHist ON InvoiceNumber = invHist.SOPNUMBE " & _

"JOIN lookup.MemberTypes mt ON mt.Id = PersonMembership.MembershipTypeId AND MemberGroup = 'Regular Member' " & _

"LEFT JOIN entity.Organization org ON org.Id = PersonMembership.OrganizationId " & _

"WHERE "

Split3 = " PersonMembership.CreatedDate > DATEADD( day, -120, GETDATE() ) AND " & _

" ( ( SUBSTRING( invWork.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invWork.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) " & _

"OR ( SUBSTRING( invHist.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invHist.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) ) ) pm "

Split4 = "JOIN entity.Person person ON person.Id = pm.PersonId " & _

"LEFT JOIN lookup.TitlePrefix ON person.PrefixId = TitlePrefix.Id AND TitlePrefix.Id > 0 " & _

"LEFT JOIN lookup.TitleSuffix ON person.SuffixId = TitleSuffix.Id AND TitleSuffix.Id > 0 " & _

"JOIN attribute.Address addr ON person.PrimaryAddressId = addr.Id " & _

"LEFT JOIN lookup.AddressSubunit unit ON unit.id = Addr.SubunitTypeId AND addr.SubunitTypeId <> 0 " & _

"LEFT JOIN lookup.State addrState ON addr.StateId = addrState.Id " & _

"LEFT JOIN lookup.Country ON addr.CountryId = Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42 " & _

"ORDER BY addr.PostalCode"

.CommandText = Split1 + Split2 + Split3 + Split4

End With

ActiveWorkbook.Connections("RegularMemberships").Refresh

End Sub

I cannot figure out what it is. When I click on Debug it takes me to the Module and to this line

[Select Code] copy to clipboard

ActiveWorkbook.Connections("RegularMemberships").Refresh

In the Properties under Connection, the SQL works. Verified it on SQL Server Management Studio 2014.

Help would be appreciated.

Here's the SQL that works as is in the Connection Properties for the Excel Macro:

[Select Code] copy to clipboard

SELECT DISTINCT

person.MembershipNumber, person.FirstName + ' ' + person.LastName AS NAME, person.FirstName, person.RegionId AS REGION,

addr.StreetOne, addr.StreetTwo, ISNULL(unit.Description, '') + ' ' + addr.SubUnit AS 'Unit', addr.City + ', ' + addrState.Code + ' ' + addr.PostalCode AS 'CityStateZip',

addrState.Code AS 'State', lookup.Country.Description AS 'Country', CONVERT(char(10), pm.EndDate, 101) AS EndDate, addr.PostalCode, pm.HomeClub

FROM (SELECT attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS HomeClub, attribute.PersonMembership.MembershipTypeId,

attribute.PersonMembership.InvoiceNumber, attribute.PersonMembership.EndDate

FROM attribute.PersonMembership LEFT OUTER JOIN

USFSA.dbo.SOP10100 AS invWork ON attribute.PersonMembership.InvoiceNumber = invWork.SOPNUMBE LEFT OUTER JOIN

USFSA.dbo.SOP30200 AS invHist ON attribute.PersonMembership.InvoiceNumber = invHist.SOPNUMBE INNER JOIN

lookup.MemberTypes AS mt ON mt.Id = attribute.PersonMembership.MembershipTypeId AND mt.MemberGroup = 'Regular Member' LEFT OUTER JOIN

entity.Organization AS org ON org.Id = attribute.PersonMembership.OrganizationId

WHERE (attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE())) AND (SUBSTRING(invWork.BACHNUMB, 1, 8) >= '20150601' AND

SUBSTRING(invWork.BACHNUMB, 1, 8) <= '20150710' OR

SUBSTRING(invHist.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invHist.BACHNUMB, 1, 8) <= '20150710')) AS pm INNER JOIN

entity.Person AS person ON person.Id = pm.PersonId LEFT OUTER JOIN

lookup.TitlePrefix ON person.PrefixId = lookup.TitlePrefix.Id AND lookup.TitlePrefix.Id > 0 LEFT OUTER JOIN

lookup.TitleSuffix ON person.SuffixId = lookup.TitleSuffix.Id AND lookup.TitleSuffix.Id > 0 INNER JOIN

attribute.Address AS addr ON person.PrimaryAddressId = addr.Id LEFT OUTER JOIN

lookup.AddressSubUnit AS unit ON unit.Id = addr.SubUnitTypeId AND addr.SubUnitTypeId <> 0 LEFT OUTER JOIN

lookup.State AS addrState ON addr.StateId = addrState.Id LEFT OUTER JOIN

lookup.Country ON addr.CountryId = lookup.Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42

ORDER BY addr.PostalCode

Last edited by supportservice; Today at 10:31 AM.

Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post

Today, 10:40 AM #2

judgeh59

judgeh59 is offline

Forum Expert

judgeh59's Avatar

Join Date

02-07-2013

Location

Boise, Idaho

MS-Off Ver

Excel 2010

Posts

2,255

Re: Incorrect syntax?

just a little confused....the only thing I see "weird" is, in the SQL on the line where "pm", you have a few +'s and I'm wondering if they should be &'s....

but you say the SQL is working so I'm not sure exactly what's going on..

I could be totally off base...

Ernest

Please consider adding a * if I helped

Nothing drives me crazy - I'm alway close enough to walk....

Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post

Today, 11:11 AM #3

supportservice

supportservice is online now

Registered User

Join Date

08-13-2015

Location

Colorado Springs, CO

MS-Off Ver

2013

Posts

3

Re: Incorrect syntax?

Since I'm not that familiar with working with either, what I did try is the SQL and it produces the results.

But do not see what could be wrong on the Excel side.

All I know is that it's erroring on 'pm' somewhere.

hope someone can see what and where the 'pm' is a problem

Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post

Today, 11:38 AM #4

supportservice

supportservice is online now

Registered User

Join Date

08-13-2015

Location

Colorado Springs, CO

MS-Off Ver

2013

Posts

3

Re: Incorrect syntax?

This is the SQL: