Dwain,

cool code! I'll have to dig in and figure out how it works. One thing is missing though... the Symptom name disappeared. The final output should look...

Dwain,

cool code! I'll have to dig in and figure out how it works. One thing is missing though... the Symptom name disappeared. The final output should look...

"There are multiple categories for each product."

That's unusual. Could you give an example? Normally, you have a category hierarchy (Category, Subcategory...)

http://www.manning.com/nielsen/SampleChapter5.pdf

From SQL Server Deep Dives. I think I need to buy this book... <g>

There is also this article...

http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions

using LAG/LEAD. definitely worth a read!

I would probably look for an article by Itzik Ben-Gan on it... being that he's a lot smarter than I am.

Looks like a running total... if you're using 2012, this works:

sample data setup:

-- create table in tempdb (yes, it probably should have a PK)

CREATE TABLE #pMovement(

pDate DATE,

pallet int,

qty int

);

GO

--...

Glad to help!

Have fun!

Add a tablix to your layout, then put a rectangle in the cell you want to put these two values in. Then put the two textboxes (for the two...

Sounds like a "gaps and islands" question. You're looking for the maximum size of an island.

Is the same true if you use a common table expression to calculate the running total?

Dwain, I think you get it. The problem was that everything was in Access, and so I ended up writing some really crazy VBA to union the stuff together...

Can you just create it as a table-valued function?

CREATE FUNCTION [dbo].[GenerateCalendar]

(

@FromDate DATETIME,...

Great! Glad to help.

When in doubt, test it out in TEMPDB.

Either use

USE TEMPDB;

GO

... and then paste your code here

OR prefix the tablename with a # sign....

CREATE TABLE #TableInTempDB...

This pretty much explains it -- just run the queries and see what happens:

USE tempdb;

GO

CREATE TABLE SomeDates(TheDate DATE);

INSERT INTO SomeDates VALUES('1/11/2014');

INSERT INTO SomeDates VALUES('1/12/2014');

INSERT INTO SomeDates VALUES('1/13/2014');

INSERT INTO SomeDates VALUES('1/14/2014');

INSERT...

Do you mean 'Does it matter the order in which I declare variables and assign them values?' No. Not unless one is dependent upon another.