Link to home
Start Free TrialLog in
Avatar of eseinc
eseincFlag for United States of America

asked on

SQL Reporting Parameter Help

I am new to SQL and trying to create a report that shows some basic information about jobs. Currently the report displays information from a column (TRXHRUNIT) about jobs but only when they had hours billed to them because of the way the where clause is setup.

Where Transaction_Date>=@begin date and Transaction_Date<=@end date

I want to include all jobs in this report so I need to remove the where clause but still show the billed hours from the date range parameters, so several jobs would just have 0 but how do I change the select statement to show the TRXHRUNIT for jobs when the transaction date is between the two parameters?
Avatar of netozai
netozai

How do you get the billed hours for each row, whether it's 0 or something else? Is it stored in a column by itself? Or, do you need to compute it? If so, what's the criteria? What are the inputs to compute the billing hours?

Once you take care of that, then you may solve the whole thing.

One solution approach I'm imagining is a 3-step process...
Step 1. Drop the current WHERE clause in your main SQL query so it does not eliminate the rows that you want to show in your results/report.
Step 2. Establish and implement the criteria to calculate the billing hours for each row as a separate SQL query.
Step 3. Now, incorporate the SQL query from Step 2 into your main query from Step 1... simply as one more column showing the billed hours. This column will be just one more column next to your existing TRXHRUNIT column in your main query (a SELECT query I suppose).

If I am correct, this can basically be solved using a SELECT query. Here are examples of using the SELECT statement (Transact-SQL):

http://msdn.microsoft.com/en-us/library/ms187731%28v=sql.100%29.aspx
ASKER CERTIFIED SOLUTION
Avatar of TempDBA
TempDBA
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of eseinc

ASKER

I put the following case statement in to try and capture the hours billed between the date ranges selected but it doesn't seem to be capturing the hours correctly. I only want the TRXHRUNT for when the WS_Transaction_Date falls between the begining and end date parameters. Did I mess something up?

CASE

WHEN WS_Transaction_Date >= @Begin_Date
THEN TRXHRUNT

WHEN WS_Transaction_Date <= @End_Date
THEN TRXHRUNT
ELSE 0

END AS Billed_Hours
Avatar of eseinc

ASKER

Also in response to the first post, all the billed hours are stored in the system in the TRXHRUNT column, however I only want to pull out ones that fall between a certain date range. If there were no billed hours during the date range I want the report to just display 0.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of eseinc

ASKER

i tried adding the following IIF into the report

=IIF(Fields!WS_Transaction_Date >= Parameters!Begin_Date.Value AND Fields!WS_Transaction_Date <= Parameters!End_Date.Value,Fields!TRXHRUNT,"0")

I'm getting an error when trying to preview the report

The value expression for the textrun TRXQTY.Paragraphs.[0].TextRuns[0] Contains an error: [BC30452] Operator '>=' is not defined for types.
Avatar of eseinc

ASKER

I got it figured out with a CASE statement, thanks for the help.