July 9, 2012 at 4:39 pm
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?
July 10, 2012 at 1:50 am
phoeneous (7/9/2012)
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?
You refer to 'rows 5 and 6', yet the data appears to be denormalised (ie, all the dates and amounts coming from a single row), so there is only one row - or am I mistaken?
It would be useful if you would confirm the structure of the source table & also put into words the selection logic.
July 10, 2012 at 8:36 am
Sorry, I meant to say "columns 5 and 6", not rows.
I'll get a few screenshots soon.
July 10, 2012 at 8:55 am
OK. Your problem is possibly down to the fact that the WHERE clause is acting on the whole row at a time (and not just to date1, date2 etc individually).
You may find that you have more success if you use several UNIONs to get your recordset along the lines of
Id, Date, Amount
and filter that.
July 10, 2012 at 10:27 am
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.
July 10, 2012 at 10:42 am
phoeneous (7/10/2012)
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.
If you want some real help read the first link in my signature about best practices. We need ddl, sample data and desired output to help you write queries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2012 at 11:55 am
phoeneous (7/10/2012)
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.
The fact that you're calling them dollar1....dollar5 probably means that you probably have the table denormalized. A Phil previously mentioned: you have a normalization issue. You REALLY need to help us confirm that by posting the actual table structure behind the report.
As of now - unless you use Phil's trick to re-normalize the data, you'll be stuck be a very ugly kind of equation to add the "dollar" columns up. Something along the lines of (T-SQL version):
select [other columns],
case when date1 between @startdate and @enddate then dollar1 else 0 end +
case when date2 between @startdate and @enddate then dollar2 else 0 end +
case when date3 between @startdate and @enddate then dollar3 else 0 end +
case when date4 between @startdate and @enddate then dollar4 else 0 end +
case when date5 between @startdate and @enddate then dollar5 else 0 end
as TotalFilteredAmount
from myTable
This will KILL your performance on anything other than a tiny table. You do not want to leave your table this way - it's a timebomb.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2012 at 4:10 pm
It looks like the best way for me to accomplish this is with an ssrs expression.
This one appears to be working correctly:
=(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))
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy