June 12, 2015 at 9:41 am
I have a View that gives me the results I want, with an exception...
I would like the date range "ChangedDate" to be dynamic, based on user input. I have tried converting it to a Stored Procedure with no success.
Can someone please "walk me through" the EXACT process?
Here is the SQL for the View (the dates are static)
SELECT TOP (100) PERCENT dbo.[!Act32CountyInfo].SubmittersEIN, dbo.[!Act32CountyInfo].LocalAccountNumber, dbo.[!Act32CountyInfo].WorkPSD,
dbo.[!Act32CountyInfo].TaxYear, dbo.[!Act32CountyInfo].Period, HR.EmployeeDemographics.EmployeeSSN AS [Social Security Number],
dbo.[!Act32EmpName].LastName AS [Employee Last Name], dbo.[!Act32EmpName].FirstName AS [Employee First Name],
dbo.[!Act32EmpName].MiddleName AS [Employee Middle Initial], dbo.[!Act32EmpAddInfo].AddressLine1 AS [Location Address],
dbo.[!Act32EmpAddInfo].AddressLine2 AS [Delivery Address], dbo.[!Act32EmpAddInfo].City, dbo.[!Act32EmpAddInfo].EntryValue AS [State Abbreviation],
dbo.[!Act32EmpAddInfo].ZipCode AS [Zip Code], SUM(dbo.[!Act32EITPayrollTaxALL].GrossAmount) AS [Taxable Wages], SUM(dbo.[!Act32EITPayrollTaxALL].TaxAmount)
AS [Tax Withheld], dbo.[!Act32TaxHeader].ACT32number AS [Residence PSD]
FROM dbo.[!Act32TaxHeader] INNER JOIN
dbo.[!Act32CountyInfo] ON dbo.[!Act32TaxHeader].CompanyID = dbo.[!Act32CountyInfo].CompanyID RIGHT OUTER JOIN
dbo.[!Act32EITPayrollTaxALL] ON dbo.[!Act32TaxHeader].TaxHeaderID = dbo.[!Act32EITPayrollTaxALL].TaxHeaderID LEFT OUTER JOIN
HR.EmployeeDemographics ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = HR.EmployeeDemographics.EmployeeId LEFT OUTER JOIN
dbo.[!Act32EmpName] ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpName].EmployeeId LEFT OUTER JOIN
dbo.[!Act32EmpAddInfo] ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpAddInfo].EmployeeId
WHERE (dbo.[!Act32EITPayrollTaxALL].ChangedDate >= CONVERT(DATETIME, '2014-12-28 00:00:00', 102)) AND
(dbo.[!Act32EITPayrollTaxALL].ChangedDate <= CONVERT(DATETIME, '2015-03-27 00:00:00', 102))
GROUP BY HR.EmployeeDemographics.EmployeeSSN, dbo.[!Act32EmpName].LastName, dbo.[!Act32EmpName].FirstName, dbo.[!Act32EmpName].MiddleName,
dbo.[!Act32EmpAddInfo].AddressLine1, dbo.[!Act32EmpAddInfo].AddressLine2, dbo.[!Act32EmpAddInfo].City, dbo.[!Act32EmpAddInfo].EntryValue,
dbo.[!Act32EmpAddInfo].ZipCode, dbo.[!Act32TaxHeader].ACT32number, dbo.[!Act32CountyInfo].SubmittersEIN, dbo.[!Act32CountyInfo].LocalAccountNumber,
dbo.[!Act32CountyInfo].WorkPSD, dbo.[!Act32CountyInfo].TaxYear, dbo.[!Act32CountyInfo].Period
ORDER BY [Social Security Number]
June 12, 2015 at 10:04 am
I would use an inline table valued function instead:
-- create the function
CREATE FUNCTION someFunctioName(@startDate datetime, @endDate datetime)
RETURNS TABLE
AS
RETURN
SELECT dbo.[!Act32CountyInfo].SubmittersEIN
,dbo.[!Act32CountyInfo].LocalAccountNumber
,dbo.[!Act32CountyInfo].WorkPSD
,dbo.[!Act32CountyInfo].TaxYear
,dbo.[!Act32CountyInfo].Period
,HR.EmployeeDemographics.EmployeeSSN AS [Social Security Number]
,dbo.[!Act32EmpName].LastName AS [Employee Last Name]
,dbo.[!Act32EmpName].FirstName AS [Employee First Name]
,dbo.[!Act32EmpName].MiddleName AS [Employee Middle Initial]
,dbo.[!Act32EmpAddInfo].AddressLine1 AS [Location Address]
,dbo.[!Act32EmpAddInfo].AddressLine2 AS [Delivery Address]
,dbo.[!Act32EmpAddInfo].City
,dbo.[!Act32EmpAddInfo].EntryValue AS [State Abbreviation]
,dbo.[!Act32EmpAddInfo].ZipCode AS [Zip Code]
,SUM(dbo.[!Act32EITPayrollTaxALL].GrossAmount) AS [Taxable Wages]
,SUM(dbo.[!Act32EITPayrollTaxALL].TaxAmount) AS [Tax Withheld]
,dbo.[!Act32TaxHeader].ACT32number AS [Residence PSD]
FROM dbo.[!Act32TaxHeader]
INNER JOIN dbo.[!Act32CountyInfo]
ON dbo.[!Act32TaxHeader].CompanyID = dbo.[!Act32CountyInfo].CompanyID
RIGHT OUTER JOIN dbo.[!Act32EITPayrollTaxALL]
ON dbo.[!Act32TaxHeader].TaxHeaderID = dbo.[!Act32EITPayrollTaxALL].TaxHeaderID
LEFT OUTER JOIN HR.EmployeeDemographics
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = HR.EmployeeDemographics.EmployeeId
LEFT OUTER JOIN dbo.[!Act32EmpName]
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpName].EmployeeId
LEFT OUTER JOIN dbo.[!Act32EmpAddInfo]
ON dbo.[!Act32EITPayrollTaxALL].EmployeeID = dbo.[!Act32EmpAddInfo].EmployeeId
WHERE(dbo.[!Act32EITPayrollTaxALL].ChangedDate >= @startDate)
AND (dbo.[!Act32EITPayrollTaxALL].ChangedDate <= @endDate)
GROUP BY HR.EmployeeDemographics.EmployeeSSN
,dbo.[!Act32EmpName].LastName
,dbo.[!Act32EmpName].FirstName
,dbo.[!Act32EmpName].MiddleName
,dbo.[!Act32EmpAddInfo].AddressLine1
,dbo.[!Act32EmpAddInfo].AddressLine2
,dbo.[!Act32EmpAddInfo].City
,dbo.[!Act32EmpAddInfo].EntryValue
,dbo.[!Act32EmpAddInfo].ZipCode
,dbo.[!Act32TaxHeader].ACT32number
,dbo.[!Act32CountyInfo].SubmittersEIN
,dbo.[!Act32CountyInfo].LocalAccountNumber
,dbo.[!Act32CountyInfo].WorkPSD
,dbo.[!Act32CountyInfo].TaxYear
,dbo.[!Act32CountyInfo].Period
GO
-- call the function
SELECT *
FROM someFunctioName(
CONVERT(DATETIME, '2014-12-28 00:00:00', 102),
CONVERT(DATETIME, '2015-03-27 00:00:00', 102)
)
ORDER BY [Social Security Number]
-- Gianluca Sartori
June 12, 2015 at 10:05 am
BTW, TOP 100 PERCENT / ORDER BY is totally ignored in views, so you may also delete it.
-- Gianluca Sartori
June 12, 2015 at 11:44 am
I'm confused, the function executes, but it doesn't prompt me for dates.
It just uses the dates provided in the "call" portion.
Things would be so much easier if I could just use a parameter in a view.
June 12, 2015 at 11:52 am
T-SQL doesn't prompt for input. You need to provide the input.
June 12, 2015 at 11:57 am
tsobiech (6/12/2015)
...Things would be so much easier if I could just use a parameter in a view.
That's what an inline table valued function is (or at least how you can think of it): it's a parameterized view (e.g. a view that accepts parameters). It's a very powerful tool in SQL server.
-- Itzik Ben-Gan 2001
June 12, 2015 at 12:03 pm
I need to run this report quarterly, thus it would be so much easier if I could just use something like Excel to "query" this data and return only a specific date range.
Excel will connect to my SQL database, but it only connects to tables and views (not stored procedures and functions)
Does anyone know how I could set this up so I don't have to go into SQL every time and change the dates "manually?"
June 12, 2015 at 12:10 pm
tsobiech (6/12/2015)
I need to run this report quarterly, thus it would be so much easier if I could just use something like Excel to "query" this data and return only a specific date range.Excel will connect to my SQL database, but it only connects to tables and views (not stored procedures and functions)
Does anyone know how I could set this up so I don't have to go into SQL every time and change the dates "manually?"
Put some parameters in your Excel document. You could even build logic into the VBA if you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2015 at 12:33 pm
Sean Lange (6/12/2015)
tsobiech (6/12/2015)
Put some parameters in your Excel document. You could even build logic into the VBA if you want.
I tried copying the SQL from server manager to Excel, but it doesn't work.
June 12, 2015 at 12:34 pm
If you want the view to always look at last quarter, then you could also dynamically calculate, in the view, what the start and end dates need to be.
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]
June 12, 2015 at 12:38 pm
I think I'm just going to have to resign myself to the fact that I'm going to have to "manually" change the dates, every time, then copy/paste/format the data into Excel for sending.
June 12, 2015 at 12:42 pm
tsobiech (6/12/2015)
I think I'm just going to have to resign myself to the fact that I'm going to have to "manually" change the dates, every time, then copy/paste/format the data into Excel for sending.
No you don't. Create a view in sql server and have excel select from your view and add a where clause with whatever dates you want at run time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2015 at 12:46 pm
Sean Lange (6/12/2015)
tsobiech (6/12/2015)
I think I'm just going to have to resign myself to the fact that I'm going to have to "manually" change the dates, every time, then copy/paste/format the data into Excel for sending.No you don't. Create a view in sql server and have excel select from your view and add a where clause with whatever dates you want at run time.
Or, at least create a stored procedure to generate the data. Add a start and end date to the procedure.
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]
June 12, 2015 at 12:48 pm
Are the dates user selectable or are they determined by when the query is run?
June 12, 2015 at 12:50 pm
Alvin Ramard (6/12/2015)
Sean Lange (6/12/2015)
tsobiech (6/12/2015)
I think I'm just going to have to resign myself to the fact that I'm going to have to "manually" change the dates, every time, then copy/paste/format the data into Excel for sending.No you don't. Create a view in sql server and have excel select from your view and add a where clause with whatever dates you want at run time.
Or, at least create a stored procedure to generate the data. Add a start and end date to the procedure.
That is how I would do it too but it sounded earlier in the thread like the OP is uncertain about stored procedures. Most likely due to a lack of knowing how to build it into Excel.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply