SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to insert a date range parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumber
Starting Member

3 Posts

Posted - 09/16/2013 :  12:07:01  Show Profile  Reply with Quote
I am new to SQL. I am pulling information from my POS system which took some time to figure out. I am trying to pull MTD and YTD sales for this year and last year.

I have this line to pull all invoices between two dates:
WHERE (invc_hdr.ivh_dt Between '08/01/2013' And '08/31/13')
But I want to be able to put the dates in without having to change the code each time. I want a window to pop up so I can input the date range. Is there a way to do this.

Any help would be great.

Edited by - Lumber on 09/16/2013 14:06:41

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/16/2013 :  14:52:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1) Write the code as a stored procedure
2) Write the code as a inline table valued function

CREATE FUNCTION dbo.fnMyFunction
(
	@FromDate DATE,
	@ToDate DATE
)
RETURNS TABLE
AS
RETURN	(
		SELECT	...
		FROM	...
		WHERE	invc_hdr.ivh_dt BETWEEN @FromDate AND @ToDate
	)
GO

SELECT	*
FROM	dbo.fnMyFunction('20130801', '20130814');



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Lumber
Starting Member

3 Posts

Posted - 09/16/2013 :  17:05:34  Show Profile  Reply with Quote
I am pulling the data using ODBC. So I am not sure if this will work. I think I am a little over my head.
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 09/19/2013 :  23:34:22  Show Profile  Reply with Quote
This is really easy from a technical perspective.

Look up "form processing" and your language: PHP/ASP/whatever. There will be tons of tutorials on how to handle to do this. You'll be able to copy them almost verbatim except for the SQL. Dates can get a little bit messy, depending on formatting, so you may need to find a validation script of some sort, usually JavaScript.

If this is for mass consumption then you'll probably want to read up on SQL Injection as well.

That sounds like a lot, but seriously, someone's done it all for you, you've just got to find it.

Note: If you are using PHP the process for this has changed over the years so make sure you are looking at a newer tutorial.
Go to Top of Page

Lumber
Starting Member

3 Posts

Posted - 09/20/2013 :  16:06:59  Show Profile  Reply with Quote
I am pulling info from ECS Pro (our POS system) by writing a SQL code and it is putting the info in excel. My boss wants a spreadsheet that will show MTD and YTD accounting totals for this year and last year. I found the tables I needed. When I use "WHERE (invc_hdr.ivh_dt=?). A window will pop up every time I refresh the spreadsheet and I can put the date in. But I don't want just one date. So I rewrote my code to look like this WHERE (invc_hdr.ivh_dt Between 'mm/dd/yyyy' And mm/dd/yyyy). This will get me the rage I want but I will have to change the date in the code every time I want a different date rage.

Is there just a way to change my WHERE code to have a window pop up but still put in a date range?
Go to Top of Page

allan8964
Posting Yak Master

247 Posts

Posted - 09/21/2013 :  00:52:12  Show Profile  Reply with Quote
I guess you want where clause input as Between '08/01/2013' And '08/31/13' and then Between '09/01/2013' And '09/30/13') so forth till December for you MTD. If this is the case then you can use CURSOR but inside the cursor you still need use SwePeso's way to solve this.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000