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 6 (of 6 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