November 15, 2013 at 2:11 pm
Hi,
I need some help on making the right SQL-query to get the desired results. I have a table, named tblCalls with the following tables:
ID | Room | date | time | CallType
This tables has some data like this:
1 | 11040 | 13-11-2013 | 10:00:22 | 4
2 | 11040 | 13-11-2013 | 11:30:55 | 4
3 | 11055 | 13-11-2013 | 09:22:41 | 4
4 | 11040 | 14-11-2013 | 15:25:16 | 4
..
this data needs to be presented in the following format:
Room | 13-11-2013 | 14-11-2013
11040 | 2 | 1
11055 | 1 | 0 (or null)
there are lots of rooms (growing number) and I need to display only the count of the last 5 days.
Please help me
KInd regards,
November 15, 2013 at 2:32 pm
Since this is a dynamic query ("last 5 days" is causing a change in the column names) you'd need a dynamic CrossTab approach as described in the related article in my signature.
If you don't want to use dynamic SQL there are three alternatives I can think of:
a) don't name the columns with the reslated dates but rather with today, today_mi2, today_mi3, today_mi4 and let the app format the column names (facing the risk that the column names are off by one day under certain conditions) or
b) add the column name as the very first row to your query and let the app sort it out
c) change the output to an xml format.
Option (a) is more dirty than quick and not really reliable and option (b) violates any rule of normalization.
November 18, 2013 at 8:44 am
To expand on LutzM's post, you need to use the PIVOT function. The issue with this is that the PIVOT function requires you to specify the column names/values, but because they will change each day you can't do this directly.
Instead you have to create a dynamic SQL statement and feed the column names into it. To do this you must first build the string of column names to be used. There are lots of ways to do this; in your case it is probably just as easy to do
@cols = '''' + select cast(cast(cast(floor(cast(getdate() as numeric(18,6))) as datetime) as date) as nvarchar(10)) + ''''
@cols = @cols + ',' + '''' + select cast(cast(cast(floor(cast(dateadd(days,-1,getdate()) as numeric(18,6))) as datetime) as date) as nvarchar(10))+ ''''
etc.. changing the days offset each time
You will find loads of posts on the web about dynamic SQL and Pivot
November 18, 2013 at 8:53 am
Aaron,
I just wanted to point out that PIVOT is not needed (is just one option). A pre-aggregated CROSS TAB might perform better. Check the articles recommended by Lutz.
By the way, it seems that you overcomplicate to convert your dates into strings. CONVERT will do it a lot easier.
CONVERT( char(10), DATEADD( day, -1, GETDATE()), 120)
November 18, 2013 at 11:23 am
The "simple" CrossTab version won't work since the column names need to be dynamic.
Hence my recommendation to use the Dynamic CrossTab version.
November 18, 2013 at 11:55 am
I agree with you, I never suggested the non-dynamic approach. However, it's important to understand it before jumping into the dynamic cross tabs. Pre-aggregation can be used as well on dynamic cross tabs.
November 18, 2013 at 12:39 pm
I agree with Lutz. You can name the columns as ([4], [3], [2], [1], [0]) and use that number together with the report date to figure out the date value in the client app.
Here you have two of the options, static and dynamic pivot.
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE #T (
ID int NOT NULL,
Room varchar(10) NOT NULL,
dt date NOT NULL,
tm time NOT NULL,
CallType char(1) NOT NULL
);
INSERT INTO #T
(ID, Room, dt, tm, CallType)
VALUES
(1,'11040','20131113','10:00:22','4'),
(2,'11040','20131113','11:30:55','4'),
(3,'11055','20131113','09:22:41','4'),
(4,'11040','20131114','15:25:16','4');
DECLARE
@dt date = '20131117',
@columns nvarchar(MAX),
@sql nvarchar(MAX);
WITH C1 AS (
SELECT
Room,
CASE
WHEN dt = DATEADD([day], -4, @dt) THEN 4
WHEN dt = DATEADD([day], -3, @dt) THEN 3
WHEN dt = DATEADD([day], -2, @dt) THEN 2
WHEN dt = DATEADD([day], -1, @dt) THEN 1
WHEN dt = DATEADD([day], -0, @dt) THEN 0
END AS rn,
COUNT(*) AS cnt
FROM
#T
WHERE
dt >= DATEADD([day], -4, @dt)
GROUP BY
Room,
dt
)
SELECT
P.*
FROM
C1
PIVOT
(
MAX(cnt)
FOR rn IN ([4], [3], [2], [1], [0])
) AS P
ORDER BY
P.Room;
-- dynamic pivot
SET @columns = STUFF(
(
SELECT
',' + QUOTENAME(dt)
FROM
(
VALUES
(DATEADD([day], -4, @dt)),
(DATEADD([day], -3, @dt)),
(DATEADD([day], -2, @dt)),
(DATEADD([day], -1, @dt)),
(@dt)
) AS T(dt)
ORDER BY
dt
FOR XML PATH('')
), 1, 1, '');
SET @sql = N'
WITH C1 AS (
SELECT
Room,
dt,
COUNT(*) AS cnt
FROM
#T
WHERE
dt >= DATEADD([day], -4, @dt)
GROUP BY
Room,
dt
)
SELECT
P.*
FROM
C1
PIVOT
(
MAX(cnt)
FOR dt IN (' + @columns + N')
) AS P
ORDER BY
P.Room;';
EXEC sp_Executesql @sql, N'@dt date', @dt;
GO
DROP TABLE #T;
GO
November 18, 2013 at 12:46 pm
Hi all,
thanks for the replies; I tried to create a dynamic query, it's almost working. Here's the code:
DECLARE @cols as varchar(max)
SET @cols = '' + QUOTENAME( Convert ( nvarchar,dateadd("d",-1,getdate()),105 ) )
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-2,getdate()) ,105))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-3,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-4,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-5,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-6,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-7,getdate()) ,105))
set @sSql = '
SELECT Kamernummer,' + @cols + '
FROM
(
SELECT Kamernummer, DATUM, TypeOproep
FROM tmpKameroproepen
WHERE typeoproep = ''4'' and
kamernummer <> ''19999'' and
SUBSTRING (kamernummer,1,2) <> ''10'' AND
SUBSTRING(kamernummer,1,1) <> ''0''
) up
PIVOT (COUNT(datum)
FOR DATUM IN ( '+ @cols+ ' )) AS pvt
ORDER BY Kamernummer DESC'
select @ssql
It passes me the following result, which I can't explain. The where-clause isn't complete
SELECT Kamernummer,[17-11-2013],[16-11-2013],[15-11-2013],[14-11-2013],[13-11-2013],[12-11-2013],[11-11-2013]
FROM
(
SELECT Kamernummer, DATUM, TypeOproep
FROM tmpKameroproepen
WHERE typeoproep = '4' and
kamernummer <>
(1 row(s) affected)
Executing the query shows me an error:
Msg 203, Level 16, State 2, Line 31
The name '
SELECT Kamernummer,[17-11-2013],[16-11-2013],[15-11-2013],[14-11-2013],[13-11-2013],[12-11-2013],[11-11-2013]
FROM
...
FOR DATUM IN ( [17-11-2013],[16-11-2013],[15-11-2013],[14-11-2013],[13-11-2013],[12-11-2013],[11-11-2013] )) AS pvt
ORDER BY Kamernummer DESC' is not a valid identifier.
November 18, 2013 at 1:08 pm
Can you post the whole script?
I wonder if you declared @sSql as NVARCHAR(MAX) or a length big enough to hold the whole query.
November 18, 2013 at 1:43 pm
Here's the whole script
DECLARE @sSql AS varchar(max)
DECLARE @cols as varchar(max)
SET @cols = '' + QUOTENAME( Convert ( nvarchar,dateadd("d",-1,getdate()),105 ) )
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-2,getdate()) ,105))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-3,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-4,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-5,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-6,getdate()),105 ))
SET @cols = @cols +',' + QUOTENAME( Convert ( nvarchar,dateadd("d",-7,getdate()) ,105))
set @sSql = '
SELECT Kamernummer,' + @cols + '
FROM
(
SELECT Kamernummer, DATUM, TypeOproep
FROM tblOproepen
WHERE typeoproep = ''4'' AND
kamernummer <> ''19999'' and
SUBSTRING (kamernummer,1,2) <> ''10'' AND
SUBSTRING(kamernummer,1,1) <> ''0''
) up
PIVOT ( COUNT(DATUM)
FOR CAST (DATUM as NVARCHAR) IN '+ @cols+ '
) AS pvt
ORDER BY Kamernummer DESC
'
execute ( @ssql )
I made some changes. the only error I receive is : Incorrect syntax near '('. but I can't figure out where it should go wrong
November 18, 2013 at 3:55 pm
The List of columns in the IN subclause should be inside parenthesies.
... IN (' + @columns + N') ...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply