It contains a list of items (ITEM1 - ITEM5) that is assigned to a user (USERID) once every day if that day < than ENDS of item (= this is discontinuation date for item and user) or if ENDS is NULL.For a given date range filter I need to group the quantities of items by date. e.g. for filter 01-06-2013 - 03-06-2013 the desired outcome would be

If I understood you correctly, your problem isn't really how to group by a date: GROUP BY [ASSIGNED] takes care of that. I'm sure you also have no problem creating a WHERE clause to select only rows with [ASSIGNED] dates in the specified range.

Your problem is you do not want to count quantities of assigned items where they are beyond the end date. For that you need a CASE expression that only returns the value of quantities for dates prior to [ENDS].

Kingston's solution is exactly what I am looking for - I did not know how to group by date within specified date range, but where records do not have a timestamp to group by. Apologies to Dixie Flatline if my request might not have been clear enough, really appreciate your suggestion with CASE for discontinued items.

Kingston's note about non-discontinuation for one instance of item5 is absolutely correct - I overlooked this one when posting the expected results for 01-07-2013 - 03-07-2013.

Kingston's solution is exactly what I am looking for - I did not know how to group by date within specified date range, but where records do not have a timestamp to group by. Apologies to Dixie Flatline if my request might not have been clear enough, really appreciate your suggestion with CASE for discontinued items.

Kingston's note about non-discontinuation for one instance of item5 is absolutely correct - I overlooked this one when posting the expected results for 01-07-2013 - 03-07-2013.

Many thanks again,

Marin

Glad that I could assist youPlease check the link that I provided about the Tally Tables and if possible read more articles on SSC about Tally tables

I would also like to appreciate the way you posted sample data, DDL and the expected results, great job It really helped me in creating the code and testing the solution before posting it here