Return all known dates given parameters

phoeneous

Can't wrap my head around this one. Not sure which is the best way to handle the logic, in t-sql or ssrs.

12 columns total, six money columns and six smalldatetime columns that correspond to their respective money column.

Example:

sale.Dollar1 - sale.Date1
sale.Dollar2 - sale.Date2
sale.Dollar3 - sale.Date3
sale.Dollar4 - sale.Date4
sale.Dollar5 - sale.Date5
sale.Dollar6 - sale.Date6

---

Given this example:

$1 - 01/01/12
null - null
$3 - 03/03/12
null - null
$5 - 05/05/12
$5 - 05/10/12

I'd like my user to be able to choose the sale.DateX range based on ssrs parameters @begindate and @enddate. So in the above example, if the user searched for all dates from 5/1/12 - 5/30/12 then only rows 5 and 6 would be returned.

---

But when I attempt to use the following WHERE clause I don't get any results if any of the date columns are null which is 50% of the data:

WHERE
((saleDate1 IS NULL OR saleDate1 >= @begindate)
AND (saleDate2 IS NULL OR saleDate2 >= @begindate)
AND (saleDate3 IS NULL OR saleDate3 >= @begindate)
AND (saleDate4 IS NULL OR saleDate4 >= @begindate)
AND (saleDate5 IS NULL OR saleDate5 >= @begindate)
AND (saleDate6 IS NULL OR saleDate6 >= @begindate))
AND
((saleDate1 IS NULL OR saleDate1 <= @enddate)
AND (saleDate2 IS NULL OR saleDate2 <= @enddate)
AND (saleDate3 IS NULL OR saleDate3 <= @enddate)
AND (saleDate4 IS NULL OR saleDate4 <= @enddate)
AND (saleDate5 IS NULL OR saleDate5 <= @enddate)
AND (saleDate6 IS NULL OR saleDate6 <= @enddate))

Suggestions?

NotHackingYou

phoeneous wrote: »

Can't wrap my head around this one. Not sure which is the best way to handle the logic, in t-sql or ssrs.

12 columns total, six money columns and six smalldatetime columns that correspond to their respective money column.

Example:

sale.Dollar1 - sale.Date1
sale.Dollar2 - sale.Date2
sale.Dollar3 - sale.Date3
sale.Dollar4 - sale.Date4
sale.Dollar5 - sale.Date5
sale.Dollar6 - sale.Date6

---

Given this example:

$1 - 01/01/12
null - null
$3 - 03/03/12
null - null
$5 - 05/05/12
$5 - 05/10/12

I'd like my user to be able to choose the sale.DateX range based on ssrs parameters @begindate and @enddate. So in the above example, if the user searched for all dates from 5/1/12 - 5/30/12 then only rows 5 and 6 would be returned.

---

But when I attempt to use the following WHERE clause I don't get any results if any of the date columns are null which is 50% of the data:

WHERE
((saleDate1 IS NULL OR saleDate1 >= @begindate)
AND (saleDate2 IS NULL OR saleDate2 >= @begindate)
AND (saleDate3 IS NULL OR saleDate3 >= @begindate)
AND (saleDate4 IS NULL OR saleDate4 >= @begindate)
AND (saleDate5 IS NULL OR saleDate5 >= @begindate)
AND (saleDate6 IS NULL OR saleDate6 >= @begindate))
AND
((saleDate1 IS NULL OR saleDate1 <= @enddate)
AND (saleDate2 IS NULL OR saleDate2 <= @enddate)
AND (saleDate3 IS NULL OR saleDate3 <= @enddate)
AND (saleDate4 IS NULL OR saleDate4 <= @enddate)
AND (saleDate5 IS NULL OR saleDate5 <= @enddate)
AND (saleDate6 IS NULL OR saleDate6 <= @enddate))

Suggestions?

If I am reading your query properly, Each saleDate must be either null OR newer than @beginDate. So if @beginDate is 5/1/2012 but saleDate1 is 3/1/2012, SQL server will not return that row because it does not meet the condition of being null or newer than/equal to 5/1/2012.

Aside from that, try wrapping each statement in parents like this: ( (saleDate5 IS NULL) OR (saleDate5 <= @enddate) )

phoeneous

CarlSaiyed wrote: »

If I am reading your query properly, Each saleDate must be either null OR newer than @beginDate. So if @beginDate is 5/1/2012 but saleDate1 is 3/1/2012, SQL server will not return that row because it does not meet the condition of being null or newer than/equal to 5/1/2012.

Aside from that, try wrapping each statement in parents like this: ( (saleDate5 IS NULL) OR (saleDate5 <= @enddate) )

Correct. Given your parameter, only rows 5 and 6 should be returned.

I have to take into consideration @begindate too.

What about this?

((saleDate1 IS NULL) OR (saleDate1 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate2 IS NULL) OR (saleDate2 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate3 IS NULL) OR (saleDate3 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate4 IS NULL) OR (saleDate4 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate5 IS NULL) OR (saleDate5 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate6 IS NULL) OR (saleDate6 BETWEEN @begindate AND @enddate))

NotHackingYou

phoeneous wrote: »

Correct. Given your parameter, only rows 5 and 6 should be returned.

I have to take into consideration @begindate too.

What about this?

((saleDate1 IS NULL) OR (saleDate1 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate2 IS NULL) OR (saleDate2 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate3 IS NULL) OR (saleDate3 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate4 IS NULL) OR (saleDate4 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate5 IS NULL) OR (saleDate5 BETWEEN @begindate AND @enddate))
AND/OR? <

---

HERE
((saleDate6 IS NULL) OR (saleDate6 BETWEEN @begindate AND @enddate))

the AND/OR depends on the logic you want to use. In order to display a record does it need to have every saledate be either null or between start and end? (IE all saledates must be null or between start/end)

Or does it need to only have one date which is null or is between beginning and end dates? (IE show if any date is null or between the saledates).

I suspect you need the OR.

phoeneous

I can retrieve the results just fine, it's when I have to do the sale.DollarX summation that is a problem.

See attached screenshots.

Notice that sale.Dollar5 is $50 and the date is 5/5/2012.

When the user runs the report in ssrs and chooses @begindate of 5/1/2012 and @enddate as 5/31/2012, I need the grand total field to reflect only sale.Dollar5 since it is the only one in the date range.

I think the filter that I have to do is going to be in ssrs and not in t-sql but I'm not sure how to do the summation AND the filter if multiple sale.DateX values are in the date range chosen.

NotHackingYou

The way you have the data organized makes it tough. This data really lends itself to having each saleDate as a separate row like this:

RecordID (NEWGUID())), SaleID ( if applicable), SaleDate, Price - then any other pertinent information that would be mostly unique to this row. If it's only unique to the sale, put it in the table for the sales and not saledates.
What does the rest of your script look like? How are you uniquely identifying rows? If there were several rows with saledates between 5/1 and 5/31, should those be included in the summation? How many rows do you expect in this table?

Do you have the ability to run multiple scripts or instead execute a stored procedure? I can think of some ways to help you with this but I need a little more information.

phoeneous

The ssrs expression below appears to be what I need. Thank you.

=(IIF((Fields!Date1.Value >= Parameters!BeginDate.Value
AND Fields!Date1.Value <= Parameters!EndDate.Value),Fields!Dollar1.Value,0)) +
(IIF((Fields!Date2.Value >= Parameters!BeginDate.Value
AND Fields!Date2.Value <= Parameters!EndDate.Value),Fields!Dollar2.Value,0)) +
(IIF((Fields!Date3.Value >= Parameters!BeginDate.Value
AND Fields!Date3.Value <= Parameters!EndDate.Value),Fields!Dollar3.Value,0)) +
(IIF((Fields!Date4.Value >= Parameters!BeginDate.Value
AND Fields!Date4.Value <= Parameters!EndDate.Value),Fields!Dollar4.Value,0)) +
(IIF((Fields!Date5.Value >= Parameters!BeginDate.Value
AND Fields!Date5.Value <= Parameters!EndDate.Value),Fields!Dollar5.Value,0))

NotHackingYou

Glad you got it worked out!

phoeneous

CarlSaiyed wrote: »

Glad you got it worked out!

Yeah, and now my head hurts

Thanks for your help!