October 22, 2010 at 3:30 am
Hello
I have to create a sp that returns results between an interval of dates, those dates are passed by parameters.
The columns names will be the dates inside the interval, how can i set the names of the columns?
Just an example:
DECLARE @data AS DATETIME
SET @data = '10/10/2010'
SELECT DISTINCT UtilizadorNome,
SUM(CASE WHEN CONVERT(NCHAR(10),DataRegisto,103) = CONVERT(NCHAR(10),DATEADD(day,0,GETDATE()),103) THEN Horas ELSE 0 END) AS '10/22/2010'
SUM(CASE WHEN CONVERT(NCHAR(10),DataRegisto,103) = CONVERT(NCHAR(10),DATEADD(day,-1,GETDATE()),103) THEN Horas ELSE 0 END) AS '10/21/2010'
...
FROM dbo.PontoRegistos
WHERE CONVERT(NCHAR(10),DataRegisto,103) >= CONVERT(NCHAR(10),@DATA,103) AND CONVERT(NCHAR(10),DataRegisto,103) <= CONVERT(NCHAR(10),GETDATE(),103)
GROUP BY UtilizadorNome
October 22, 2010 at 3:53 am
Only with dynamic SQL. Why? Because this is something SQL is not designed to do. Why? Because it shouldn't do this.
A stored procedure has a contract with the consumers of that procedure. The structure of the output should be fixed, the input parameters should be fixed. I would strongly recommend that you review your requirements and do whatever you can to have the procedure return fixed column names and have the consumer(s) of the procedure (Excel, SSRS, Business Objects, ASP.NET etc.) do the required column renaming.
October 22, 2010 at 4:01 am
-- Create temporary table
IF OBJECT_ID('Tempdb..#PontoRegistos') IS NOT NULL
DROP TABLE #PontoRegistos
CREATE TABLE #PontoRegistos (
UtilizadorNome varchar(50),
Horas int,
DataRegisto datetime
)
-- Insert some sample data
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 2, '20101018')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 6, '20101019')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101020')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101021')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 3, '20101012')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 5, '20101018')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 8, '20101021')
-- Declare some variables
DECLARE @data AS DATETIME
SET @data = '20101010'
DECLARE @sql nvarchar(4000)
DECLARE @dates nvarchar(4000)
-- Determine the date list to work with
-- and save it to a concatenated string
SET @dates = STUFF((
SELECT DISTINCT ',' + QUOTENAME(CONVERT(char(10),DataRegisto,112)) AS [text()]
FROM #PontoRegistos
WHERE DataRegisto BETWEEN @data AND CONVERT(datetime, CONVERT(char(8),GETDATE(),112), 112)
FOR XML PATH('')
), 1, 1, SPACE(0))
-- Set up the PIVOT query
SET @sql = '
SELECT UtilizadorNome, ' + @dates + '
FROM (
SELECT UtilizadorNome, CONVERT(char(8),DataRegisto,112) AS DataRegisto, Horas
FROM #PontoRegistos AS PR
WHERE DataRegisto BETWEEN @data AND CONVERT(datetime, CONVERT(char(8),GETDATE(),112), 112)
) AS SRC
PIVOT( SUM(Horas) FOR DataRegisto IN ('+ @dates +')) AS PVT
'
-- Execute PIVOT query
EXECUTE sp_executesql @sql, N'@data datetime', @data
Hope this helps
Gianluca
-- Gianluca Sartori
October 22, 2010 at 4:26 am
Blimey Gianluca, first-rate stuff and quick too. I was going to start by offering some improvements to the original query, then finish up with a cross-tab.
DROP TABLE #PontoRegistos
CREATE TABLE #PontoRegistos (UtilizadorNome VARCHAR(25), DataRegisto DATETIME, Horas INT)
INSERT INTO #PontoRegistos (UtilizadorNome, DataRegisto, Horas)
SELECT 'Company 1', GETDATE()+2, 8 UNION ALL
SELECT 'Company 1', GETDATE()+1, 8 UNION ALL
SELECT 'Company 1', GETDATE()-0, 8 UNION ALL
SELECT 'Company 1', GETDATE()-1, 8 UNION ALL
SELECT 'Company 1', GETDATE()-2, 8 UNION ALL
SELECT 'Company 1', GETDATE()-3.1, 8 UNION ALL
SELECT 'Company 1', GETDATE()-3.2, 8 UNION ALL
SELECT 'Company 2', GETDATE()-0, 6 UNION ALL
SELECT 'Company 2', GETDATE()-2.1, 6 UNION ALL
SELECT 'Company 2', GETDATE()-2.2, 6 UNION ALL
SELECT 'Company 2', GETDATE()-4, 6 UNION ALL
SELECT 'Company 2', GETDATE()-5, 6
SET DATEFORMAT MDY
DECLARE @data AS DATETIME, @Today AS DATETIME
SET @data = '10/10/2010'
SET @Today = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) -- strip the time component from the date
SELECT UtilizadorNome, -- DISTINCT not required because of GROUP BY
SUM(CASE WHEN DataRegisto = DATEADD(dd, 0, @Today) THEN Horas ELSE 0 END) AS '10/22/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -1, @Today) THEN Horas ELSE 0 END) AS '10/21/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -2, @Today) THEN Horas ELSE 0 END) AS '10/20/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -3, @Today) THEN Horas ELSE 0 END) AS '10/19/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -4, @Today) THEN Horas ELSE 0 END) AS '10/18/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -5, @Today) THEN Horas ELSE 0 END) AS '10/17/2010',
SUM(CASE WHEN DataRegisto = DATEADD(dd, -6, @Today) THEN Horas ELSE 0 END) AS '10/16/2010'
FROM (
SELECT
UtilizadorNome,
DataRegisto = DATEADD(dd,DATEDIFF(dd,0,DataRegisto),0), -- strip the time component from the date
Horas = SUM(Horas)
FROM #PontoRegistos
WHERE DataRegisto >= @data
AND DataRegisto < DATEADD(dd,1,@Today)
GROUP BY UtilizadorNome, DataRegisto
) d
GROUP BY UtilizadorNome
ORDER BY UtilizadorNome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 22, 2010 at 4:26 am
Thanks for the replys.
I need this, because it's necessary...
This is a kind of a working schedule, where the users put the hours they work every day.
For example, the User wants to see what hours he/she had register in the past month. And yes i know that i can set the name of the columns in the application that will use it, but if i set everything in the sp i just need to run the sp.
@Gianluca Sartori
That doens't i have this error:
Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'XML'.
I never used the XML Path so i don't know how to solve it..
October 22, 2010 at 4:32 am
rootfixxxer (10/22/2010)
@Gianluca SartoriThat doens't i have this error:
Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'XML'.
I never used the XML Path so i don't know how to solve it..
Aw, crap! I just realized this is the SQL 7,2000 forum!
My code won't work in versions below 2005.
Give me a couple of minutes to tweak it for SQL 2000.
-- Gianluca Sartori
October 22, 2010 at 4:32 am
rootfixxxer (10/22/2010)
@hallidaydI need this, because it's necessary...
<snip>i can set the name of the columns in the application that will use it, but if i set everything in the sp i just need to run the sp.
😉
I know you will disregard this, however I'll reiterate in different terms: presentation issues belong in the presentation layer, data issues belong in the data layer. Working this way couples your database and your application.
October 22, 2010 at 4:33 am
Rootfixxxxxxer, can you please confirm which SQL Server version you are using?
The code I posted is stage 1 - optimise the query.
The new columns names would be generated in stage 2, crosstab.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 22, 2010 at 4:39 am
There you go:
-- Create temporary table
IF OBJECT_ID('Tempdb..#PontoRegistos') IS NOT NULL
DROP TABLE #PontoRegistos
CREATE TABLE #PontoRegistos (
UtilizadorNome varchar(50),
Horas int,
DataRegisto datetime
)
-- Insert some sample data
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 2, '20101018')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 6, '20101019')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101020')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101021')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 3, '20101012')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 5, '20101018')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 8, '20101021')
-- Declare some variables
DECLARE @data AS DATETIME
SET @data = '20101010'
DECLARE @sql nvarchar(4000)
DECLARE @dates nvarchar(4000)
-- Determine the date list to work with
-- and save it to a concatenated string
SET @dates = ''
SELECT @dates = @dates + SelectList
FROM (
SELECT DISTINCT ', SUM(CASE WHEN DataRegisto = ''' + CONVERT(char(10),DataRegisto,112) + ''' THEN Horas ELSE NULL END) AS ''' + CONVERT(char(10),DataRegisto,112) + '''' AS SelectList
FROM #PontoRegistos
WHERE DataRegisto BETWEEN @data AND CONVERT(datetime, CONVERT(char(8),GETDATE(),112), 112)
) AS SRC
SET @dates = STUFF(@dates, 1, 1, SPACE(0))
-- Set up the PIVOT query
SET @sql = '
SELECT UtilizadorNome, ' + @dates + '
FROM (
SELECT UtilizadorNome, CONVERT(char(8),DataRegisto,112) AS DataRegisto, Horas
FROM #PontoRegistos AS PR
WHERE DataRegisto BETWEEN @data AND CONVERT(datetime, CONVERT(char(8),GETDATE(),112), 112)
) AS SRC
GROUP BY UtilizadorNome
'
-- Execute PIVOT query
EXECUTE sp_executesql @sql, N'@data datetime', @data
-- Gianluca Sartori
October 22, 2010 at 4:42 am
hallidayd (10/22/2010)
rootfixxxer (10/22/2010)
@hallidaydI need this, because it's necessary...
<snip>i can set the name of the columns in the application that will use it, but if i set everything in the sp i just need to run the sp.
😉
I know you will disregard this, however I'll reiterate in different terms: presentation issues belong in the presentation layer, data issues belong in the data layer.
Agreed.
Working this way couples your database and your application.
Every time you write a stored procedure you're doing this.
I banged my head lots of times to find a way to abstract completely from the database on the app side and from the app on the db side and I could not find a resonable way.
-- Gianluca Sartori
October 22, 2010 at 4:49 am
Yes i am aware of that, but i like to put a meaning name in the columns, and i don't think that's really a problem with presentation issues, the other columns have names so what's the problem to define names for this columns!?
I can leave them without names, and then interact with each one in the consumer app, but once again if i can do the most of the job in the sp i don't see the point to define the name of them in the client.
@chris-2 Morris-439714
The version is the 2000
October 22, 2010 at 5:19 am
@Gianluca Sartori
Thanks that works... 🙂
October 22, 2010 at 5:25 am
I hope you understand I am attempting to guide you towards better practice and am not trolling. After that you can take what I say on board or disregard.
The fact it has taken several attempts by a couple of very experienced programmers to produce this answer, contorting SQL to do something it is not intended to do (i.e. treating a statement as an expression) is evidence enough in my mind. I am also at a loss why you see it as a good thing that you have not written the simple couple of lines of client side code. Once that was written you would still only need to "run the procedure" - there would be no difference, except now you have had to enlist some expert help.
I can't remember who I am replying too (I wish the forum retained the rest of the thread to refer back to as you reply!) but there are degrees of couplement\ decouplement: this is unnecessarily tightly coupled.
EDIT: ah - @Gianluca Sartori 😀
EDIT2: Also, I wasn't suggesting have no names, simply that the names should be fixed in the sproc.
October 22, 2010 at 5:37 am
hallidayd (10/22/2010)
I hope you understand I am attempting to guide you towards better practice and am not trolling. After that you can take what I say on board or disregard.The fact it has taken several attempts by a couple of very experienced programmers to produce this answer, contorting SQL to do something it is not intended to do (i.e. treating a statement as an expression) is evidence enough in my mind. I am also at a loss why you see it as a good thing that you have not written the simple couple of lines of client side code. Once that was written you would still only need to "run the procedure" - there would be no difference, except now you have had to enlist some expert help.
I can't remember who I am replying too (I wish the forum retained the rest of the thread to refer back to as you reply!) but there are degrees of couplement\ decouplement: this is unnecessarily tightly coupled.
EDIT: ah - @Gianluca Sartori 😀
EDIT2: Also, I wasn't suggesting have no names, simply that the names should be fixed in the sproc.
It's not the first time I've come across this requirement. This type of query lends itself really well to KPI reports. I'm sure Gianluca's had to code it in anger before too. I agree in principal with what you're saying, that formatting should be performed in the presentation layer, but as with almost everything else, there are exceptions. This is one of them. Look at the PIVOT operator, it does pretty much the same thing.
BTW while you are writing a reply, scroll down to the bar which reads "last 10 posts..." and click on the plus-sign on the RHS - which will then expand.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply