StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would do this with dynamic SQL, since you probably don't know all of the store (TranSID) values in advance. This stored procedure takes start/end date as parameters, and generates a cross-tab. You can probably parameterize some of the other things as well, such as TranStatus and ItemClassID, but it wasn't clear if those are fixed or variable elements in your query.</p> <pre><code>CREATE PROCEDURE dbo.CrossTabByItem @StartDate DATETIME, @EndDate DATETIME = NULL AS BEGIN SET NOCOUNT ON; SET @EndDate = COALESCE(DATEADD(DAY, 1, @EndDate), GETDATE()); DECLARE @sql NVARCHAR(4000); SET @sql = N''; SELECT @sql = @sql + ', [' + TranSID + '] = SUM(CASE WHEN t.TranSID = ''' + TranSID + ''' THEN td.Qty ELSE 0 END)' FROM ( SELECT DISTINCT TranSID FROM dbo.Trans WHERE TranDate >= @StartDate AND TranDate < @EndDate ) AS x ORDER BY TranSID; SET @sql = N'SELECT ItemID = td.TranItemID' + @sql + ' FROM dbo.Trans AS t INNER JOIN dbo.TransDetail AS td ON t.TranID = td.TranID INNER JOIN dbo.Item AS i ON td.TranItemID = i.ItemID WHERE t.TranDate >= @StartDate AND t.TranDate < @EndDate AND t.TranTypeID IN (''so'',''ca'') AND t.TranStatus <> ''v'' AND i.ItemClassID = ''RHM'' GROUP BY td.TranItemID;'; EXEC sp_executesql @sql, N'@StartDate DATETIME, @EndDate DATETIME', @StartDate, @EndDate; END GO </code></pre> <p>So you can call it like this, to get everything from January 1st, 2005 until right now:</p> <pre><code>EXEC dbo.CrossTabByItem @StartDate = '20050101'; </code></pre> <p>Or like this, to get just January of 2005:</p> <pre><code>EXEC dbo.CrossTabByItem @StartDate = '20050101', @EndDate = '20050131'; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload