Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Lumber
Starting Member
3 Posts |
Posted - 2013-09-16 : 12:07:01
|
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. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-09-16 : 14:52:06
|
1) Write the code as a stored procedure2) Write the code as a inline table valued functionCREATE FUNCTION dbo.fnMyFunction( @FromDate DATE, @ToDate DATE)RETURNS TABLEASRETURN ( SELECT ... FROM ... WHERE invc_hdr.ivh_dt BETWEEN @FromDate AND @ToDate )GOSELECT *FROM dbo.fnMyFunction('20130801', '20130814'); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Lumber
Starting Member
3 Posts |
Posted - 2013-09-16 : 17:05:34
|
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. |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-09-19 : 23:34:22
|
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. |
|
|
Lumber
Starting Member
3 Posts |
Posted - 2013-09-20 : 16:06:59
|
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? |
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-09-21 : 00:52:12
|
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. |
|
|
|
|
|
|
|