• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

SQL Query with Date Range

I need a sql statement that queries based on the variables that are as follows:

>=:startD and <=:endD (based on user input)

Then I need it to also query on the following inside this date range:

MATUSETRANS.GLDEBITACCT between '918-00-00' and '920-01-00' or MATUSETRANS.GLDEBITACCT = '928-00-00'

The second query is working but I don't know how to add the date range query.

Help!
0
reldadenise
Asked:
reldadenise
  • 4
  • 3
1 Solution
 
jkwassonCommented:
select field1,field2 from table1 where (field1 >= @startid and field1 <= @endid) and (matusetrans.gldebitacct between '918-00-00' and '920-01-00' or matusetrans.gldebitacct='928-00-00')

it would help to know some more information about your DB, like what DB you are using, but generally when you want to check for something between two dates you can do the following:

select field1 from table1 where datefield between ('date1' and 'date2'). You can also do it the way I have it in the query above.

some things to keep in mind...

1. some databases require dates to be specially identified. For example Access requires dates to be surrounded by # signs, like so #04-14-2005#

2.Most databases required stored procedure type queries to identify paramters. I used the @ sign above to identify the parameters - dyanmic values that you can pass to the query.

hope this helps,
jk
0
 
jrb1Commented:
What type or of DB?  The SQL is similar:

and yourdatefield between startDate and endDate
0
 
reldadeniseAuthor Commented:
I am using a report writer called Actuate which is pulling the data from an Oracle DB.  In this report writer, I do not normally have to write sql statements (it creates itself based on the information you put in).  However with this type of query, I have to write it in sql which I am not very proficient (sp) at.  Here is the full sql for this report I have created:

SELECT   MATUSETRANS.ORGID, MATUSETRANS.SITEID, MATUSETRANS.ITEMNUM, MATUSETRANS.STORELOC, MATUSETRANS.TRANSDATE, MATUSETRANS.QUANTITY, MATUSETRANS.CURBAL, MATUSETRANS.UNITCOST, MATUSETRANS.ISSUETYPE, MATUSETRANS.GLDEBITACCT, MATUSETRANS.DESCRIPTION, MATUSETRANS.IT1
FROM     MATUSETRANS
WHERE    MATUSETRANS.ORGID = :mroOrg AND MATUSETRANS.SITEID = :mroSite AND MATUSETRANS.ISSUETYPE = 'ISSUE' AND MATUSETRANS.STORELOC = :storeLoc AND (MATUSETRANS.TRANSDATE >= @startD and MATUSETRANS.TRANSDATE <= @endD) and (matusetrans.gldebitacct between '918-00-00' and '920-01-00' or matusetrans.gldebitacct='928-00-00')  
ORDER BY MATUSETRANS.IT1 DESC , MATUSETRANS.GLDEBITACCT, MATUSETRANS.ITEMNUM
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jrb1Commented:
Well, you could still use the transdate between @startD and @endD.

What kind of error are you receiving?  I've used Actuate in the past, but don't have an environment available at the moment.
0
 
reldadeniseAuthor Commented:
I don't know how to end this question.  I have been told to go a different direction with this report - therefore I do not need the sql statement.  I appreciate your reponses!

jrb1     How well do you know Actuate?  Can I use Experts Exchange to ask you questions?
0
 
jrb1Commented:
I don't know it well enough to be a lot of help here.  We had implemented the fully web enabled version and I did a bit of work on some reports, but not enough and not recent enough to be much help.
0
 
reldadeniseAuthor Commented:
Ok, they've been talking about sending me to the advanced Actuate class - heard it's real hard.

Anyway, thanks!

Who do I award this question to????

I'll just leave it open and the moderator can decide.

THANKS!!!!
0
 
jrb1Commented:
You have two choices.  Give it to someone....split it between multiple people...or go to Support at the top of the screen and post a message asking for the question to be closed and for a refund.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now