July 15, 2019 at 9:45 am
Hi,
I am creating a dynamic pivot query to produce a simple monthly table showing clients on the left column, calendar dates across the top and simple text in the middle. Table are
CREATE TABLE [dbo].[TblClientActivity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[LocationID] [int] NULL,
[ADate] [date] NULL,
[Outcome] [nvarchar](10) NULL)
Data
ID ClientID ,LocationID, ADate, Outcome
1, 1000 ,1 ,2001-01-03, 'OVRNITE'
2, 1000, 1 ,2001-01-04, 'OVRNITE'
6 ,1000 ,1 ,2001-01-05, 'OVRNITE'
The three columns are 2 tables joined by ClientID and then pivot over the dates. IT works great when I use temporary tables, but I need it to work with without temp tables as the users don't have the create permissions!
So first I get the dates from Calendar tables between 2 dates
Next I get the client Activity for the same dates
Then I pivot.
See two examples below, 1 with temporary tables work great, the second not using the tables but rolling it up into the SQL text for execution.
ALTER PROCEDURE [dbo].[ViewClientActivity2DatesCrosstabV5]
-- Add the parameters for the stored procedure here
@StartDate AS Date = '2001/01/01',
@LocationID as Int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- GET End Date
DECLARE @EndDate Date
SET @EndDate = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
-- DELETE Tmp Table if EXISTS
IF OBJECT_ID(N'TmpCrosstab') IS NOT NULL
DROP TABLE TmpCrosstab
-- CREATE Tmp Table
CREATE TABLE TmpCrosstab (
ClientID int,
ADate Date,
Outcome VARCHAR(10) );
-- Get Clients resident at Location who hae Active Services started before
WITH cteCLients AS (
SELECT CS.CLientID, CA.ADate, CA.Outcome
FROM TblClientIDServices CS
LEFT JOIN TblClientActivity CA
ON CS.ClientID = CA.ClientID
WHERE CS.ClientID = 1000) -- CS.Status =1 AND CS.Location = @LocationID)-- Active Status and
-- Populate New TmpCrosstab
INSERT INTO TmpCrosstab SELECT * FROM cteClients
-- CREATE Dyanmic Crosstab String
DECLARE @PivotColumns AS NVARCHAR(MAX)
-- --SELECT @PivotColumns to get distinct dates
SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(ADate)
--FROM TmpDates
FROM TblCalendar
WHERE ADate BETWEEN @StartDate AND @EndDate;
-- INTO TmpCrosstab --
DECLARE @SqlQuery AS NVARCHAR(MAX)
SET @SqlQuery = N'SELECT ClientID,' + @PivotColumns + ', 0 AS TOTAL
FROM TmpCrosstab
PIVOT (MAX(Outcome) FOR ADate IN (' + @PivotColumns + ')) AS Q
WHERE ClientID IS NOT NULL'
-- SELECT @SqlQuery
EXEC sp_executesql @SqlQuery
--DROP TABLE TmpCrosstab
--DROP TABLE TmpDates
END
Second draft without temp tables...
ALTER PROCEDURE [dbo].[ViewClientActivity2DatesCrosstabV6]
-- Add the parameters for the stored procedure here
@StartDate AS Date = '2001/01/01',
@LocationID as Int = 1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- GET End Date
DECLARE @EndDate Date
SET @EndDate = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
-- --SELECT @PivotColumns to get distinct dates
SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(ADate)
FROM TblCalendar
WHERE ADate BETWEEN @StartDate AND @EndDate;
DECLARE @SqlQuery AS NVARCHAR(MAX)
SET @SqlQuery = N'SELECT ClientID,' + @PivotColumns + ', 0 AS TOTAL
FROM
(SELECT CS.ClientID, CA.ADate, CA.Outcome
FROM TblClientIDServices CS
LEFT JOIN TblClientActivity CA
ON CS.ClientID = CA.ClientID
WHERE CS.ClientID = 1000)
PIVOT (MAX(Outcome) FOR ADate IN (' + @PivotColumns + ')) AS Q
WHERE ClientID IS NOT NULL'
-- SELECT @SqlQuery
EXEC sp_executesql @SqlQuery
END
All help appreciated
July 15, 2019 at 9:52 am
All users have the right to create temp tables, and as far as I know that cannot be revoked.
You are creating permanent tables, and that is never going to work out - lots of problems in case of parallel execution. Just say CREATE TABLE #tmpCrosstab instead.
By the way, in many cases, it is better to perform the pivoting on the client-side. Many reporting tools have support for this built-in. For instance, in SSRS, you can do this with the Tablix report.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 15, 2019 at 10:02 am
Hi Thank you,
I was thinking that temp tables may work alright, what is the diff between #Tmp Table and ##TmpTable?
kind regards
July 15, 2019 at 11:14 am
A #temp is local to the process and disappears when the procedure exits. A global ##temp table can be seen by all processes and goes away when the process that created it exists or drops the table.
You want a local temp table; global temp tables is something which is used only very exceptionally.
Note that two processes can create a table #temp at the the same time without interferring with each other. Not so with global temp tables.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 15, 2019 at 1:26 pm
Good to know, thank you very much...
I tried looking in the tempdb database, but can see the #TmpTable. Can it be veiwed through SSMS ?
Also do I need to create and drop the #tmpTable if its ceases to exist at the end of the stored proc. ?
Thanks again.
July 15, 2019 at 1:29 pm
The actual name of the a #temp is somewhat longer - to ensure that two processes do not clash.
A #temp table is always dropped when the scope in which it was created exits. That is, if you create a temp table in a stored procedure, it is dropped when the procedure exits. Do not drop the temp table explicitly.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply