December 16, 2015 at 4:56 pm
Hi,
I have a simple table TblAppts
that stores a StarDdate, Appttime, Duration(mins), Frequency, ClientID and EndDate.
All fields are mandatory except EndDate that maybe null.
I want to transforms the data that will show all the data between 2 dates in a crosstab format with each date across the top, times down the left side and client ID in the middle.
I have googled this without much luck so hoping you guys can help.
kind regards
December 16, 2015 at 5:28 pm
This would be easier to pull off in Reporting Services. Is that an option?
December 16, 2015 at 11:39 pm
Hi,
We are running on sql server 2008 r2 express, which I think doesn't have reporting service built in!
But once the output from the pivot table is correct I want to go on then store the results in another table.
The pivot query will be like part of an approval process.
Thanks
December 17, 2015 at 12:28 am
Is there is any limit between two dates? as far as i know cross tab get very slow on reports if the columns list get higher.
you can also check this working on rdlc as well. do perform a POC 1st before finalizing your design.
December 17, 2015 at 1:43 am
twin.devil (12/17/2015)
Is there is any limit between two dates? as far as i know cross tab get very slow on reports if the columns list get higher.you can also check this working on rdlc as well. do perform a POC 1st before finalizing your design.
+1
Good point, TD, I was planning to suggest the same thing. Take a bunch of data and knock up a prototype output, manually, in Excel - shouldn't be too hard.
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
December 17, 2015 at 2:07 am
Hi Guys,
Yes there is a limit of either 1 week, 2 weeks or 4 weeks maximum.
So the user chooses an option 1,2 or 4 week period and 2 dates SDate and EDate, together with the Client ID are passed the stored procedure.
So the sp initially query the table to get just a few rows maybe 5 or 6 maximum for the particular client.
Then each rows Start Date and EndDate, there are checked again the user SDate and Edate. Proved the start date is between these date the each row is then chosen to action. If the Endate is null (ie service still running) then the EDate is used else id EndDate is before EDate then EndDate is used.
I have this working...simple enough so far.
Now with my new set of rows I need turn into a pivot table such that for each day between the two dates I need to show as a column so there may be 7, 14 or 28 columns maximum. For simplicity we could start with 14.
Appointment times down the left side with Client ID showing in the middle.
SO I have to create a temporary table to store a new record such as
ID, ApptDate, ApptTime, ClientID, DUration for each date between my 2 dates.
Once I have the dates stored in my temp table, max about 30 rows, I can then use a pivot command to create a crosstab view of the data.
I may at this stage want to allow the user to store the temp table data into a permanent table in the database
December 17, 2015 at 2:14 am
Tallboy (12/17/2015)
Hi Guys,Yes there is a limit of either 1 week, 2 weeks or 4 weeks maximum.
So the user chooses an option 1,2 or 4 week period and 2 dates SDate and EDate, together with the Client ID are passed the stored procedure.
So the sp initially query the table to get just a few rows maybe 5 or 6 maximum for the particular client.
Then each rows Start Date and EndDate, there are checked again the user SDate and Edate. Proved the start date is between these date the each row is then chosen to action. If the Endate is null (ie service still running) then the EDate is used else id EndDate is before EDate then EndDate is used.
I have this working...simple enough so far.
Now with my new set of rows I need turn into a pivot table such that for each day between the two dates I need to show as a column so there may be 7, 14 or 28 columns maximum. For simplicity we could start with 14.
Appointment times down the left side with Client ID showing in the middle.
SO I have to create a temporary table to store a new record such as
ID, ApptDate, ApptTime, ClientID, DUration for each date between my 2 dates.
Once I have the dates stored in my temp table, max about 30 rows, I can then use a pivot command to create a crosstab view of the data.
I may at this stage want to allow the user to store the temp table data into a permanent table in the database
Jeff Moden's two articles covering Cross Tabs [/url]will help you. If you get stuck, post up sample data in the form of a CREATE TABLE statement with INSERTs to populate the table - this will give us the same starting point as you have.
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
December 17, 2015 at 3:01 am
Hi, Looked at link very good but still unclear how to do it... here is the sql
CREATE TABLE [TblClientPOC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [nchar](10) NULL,
[StartDate] [date] NULL,
[ApptTime] [time](7) NULL,
[Duration] [int] NULL,
[Frequency] [int] NULL,
[EndDate] [date] NULL)
INSERT INTO [TblClientPOC]
([ClientID],[StartDate],[ApptTime],[Duration],[Frequency])
VALUES
(2528,'2015-01-01', '10:00',30,1),
(2528,'2015-01-01', '17:00',30,1)
Then with the following stored procedure....
PROCEDURE ShowClientsPOCCrosstab
@SDate Date = '2015-12-03',
@EDate Date = '2015-12-31',
@ClientID int = 2528
AS
BEGIN
SET NOCOUNT ON;
WITH POC1 AS
(
SELECT CASE WHEN C.StartDate > @SDATE THEN C.StartDate ELSE @SDate END AS NStartDate, C.ApptTime, C.Duration, C.Frequency, C.ClientID, ISNull(C.EndDate,@EDate) AS TmpEndDate
FROM TblClientPOCV2 C
WHERE C.ClientID = @ClientID
),
POC2 AS
(
SELECT C.NStartDate, C.ApptTime, C.Duration, C.Frequency, C.ClientID,
CASE WHEN (TmpEndDate > @EDate) THEN @EDate ELSE C.TmpEndDate END AS NEndDate
FROM POC1 C
)
SELECT * FROM POC2
END
GO
From this you will get a table with 2 rows as follows..
NStartDate ApptTime Duration Frequency ClientID NEndDate
2015-12-03 10:00:00.0000000 30 1 2528 2015-12-31
2015-12-03 17:00:00.0000000 30 1 2528 2015-12-31
So far so good but now I want to create a temp table and store an entry for this client for each date between the 2 dates and the 2 time slots
and then turn into a crosstab!
I was thinking of using a cursor to create a date for each date between the 2 dates, but I have been warned away from cursors, then I was thinking of creating a row number for each date between the 2 dates add the row number to the startdate and insert into tmp table but this sounds like using a cursor again, so at the moment I am unsure how to proceed help? anyone ?
December 17, 2015 at 4:05 am
-- make up some sample data to work with
DROP TABLE #Sample;CREATE TABLE #Sample (NStartDate DATE, ApptTime TIME, Duration SMALLINT, Frequency SMALLINT, ClientID INT, NEndDate DATE)
INSERT INTO #Sample (NStartDate, ApptTime, Duration, Frequency, ClientID, NEndDate)
VALUES
('2015-12-03', '10:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-03', '17:00:00.0000000', 30, 1, 2528, '2015-12-31');
-- use an inline tally table as a row generator to generate 1+DATEDIFF(DAY,NStartDate,NEndDate) rows,
-- i.e. all the days between NStartDate and NEndDate inclusive.
-- This is a STANDARD METHOD.
WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n1.n FROM n1, n1 b),
iTally AS (SELECT n2.n FROM n2, n2 b)
SELECT s.*, NewDate = DATEADD(DAY,x.n,s.NStartDate)
FROM #Sample s
CROSS APPLY (
SELECT TOP(1+DATEDIFF(DAY,NStartDate,NEndDate))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM iTally
) x
ORDER BY ApptTime, x.n
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
December 17, 2015 at 4:57 am
Hi Chris,
Wow, thank you so much... looks the business!
I had been reading about the Tally Tables but can't claim to understand it!
I copied and pasted you code into stored procedure without amendment and it compiled ok, but when I ran it got busy for around 30 seconds but no results or table appeared.
Was I suppose to add/amend it in some way?
December 17, 2015 at 5:08 am
Tallboy (12/17/2015)
Hi Chris,Wow, thank you so much... looks the business!
I had been reading about the Tally Tables but can't claim to understand it!
I copied and pasted you code into stored procedure without amendment and it compiled ok, but when I ran it got busy for around 30 seconds but no results or table appeared.
Was I suppose to add/amend it in some way?
Take a little time out to play with the original code I posted up, to ensure that you fully understand what it's doing. Start with the tally table part. Read Jeff Moden's article [/url]first.
WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n1.n FROM n1, n1 b),
iTally AS (SELECT n2.n FROM n2, n2 b)
SELECT * FROM iTally
Play with it, pull it apart and play with the bits.
then add TOP()
then add ROW_NUMBER()
Finally you need to be entirely comfortable with the idea of switching the sample table in my code with your actual table name.
Any issues, post back.
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
December 17, 2015 at 5:09 am
once you implement Chris query (suggested above) you will be able to get the complete set of date only. in which each date will show you the same information.
i would say what if you have this kind of data
VALUES
('2015-12-03', '10:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-03', '17:00:00.0000000', 30, 1, 2528, '2015-12-31'),
-- Added Few new rows
('2015-12-09', '05:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-12', '05:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-13', '05:00:00.0000000', 30, 1, 2528, '2015-12-31');
in the above case you will generate separate date set for each row. this query will be enormous in no time and you need to implement Case + aggregation to get the values under the same date.
instead of doing this i would recommend something like this
----------------- Parameters
Declare
@SDate Date = '2015-12-03',
@EDate Date = '2015-12-31',
@ClientID int = 2528
;
-- make up some sample data to work with
DROP TABLE #Sample;CREATE TABLE #Sample (NStartDate DATE, ApptTime TIME, Duration SMALLINT, Frequency SMALLINT, ClientID INT, NEndDate DATE)
INSERT INTO #Sample (NStartDate, ApptTime, Duration, Frequency, ClientID, NEndDate)
VALUES
('2015-12-03', '10:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-03', '17:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-11', '05:00:00.0000000', 30, 1, 2528, '2015-12-31');
-- use an inline tally table as a row generator to generate 1+DATEDIFF(DAY,NStartDate,NEndDate) rows,
-- i.e. all the days between NStartDate and NEndDate inclusive.
-- This is a STANDARD METHOD.
WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n1.n FROM n1, n1 b),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM n2, n2 b),
--- Added following
iDate AS
(
select TOP(1+DATEDIFF(DAY,@SDate,@EDate))
NewDate = DATEADD(DAY,t.n,@SDate)
from iTally t
Order by t.n
)
select @ClientID as ClientId, d.NewDate, isnull(sum(p.Duration),0) AS sumofDuration
from iDate d
Left Join #Sample p on d.NewDate = p.NStartDate
Where p.ClientID = @ClientID ----- added the missing where clause.
group by d.NewDate
order by d.NewDate
December 17, 2015 at 6:53 am
This is the approach I would take. I wouldn't store this result in a table as this would be a waste of storage. This should be for display purposes only.
----------------- Parameters
Declare
@SDate Date = '2015-12-03',
@EDate Date = '2015-12-31',
@ClientID int = 2528
;
-- make up some sample data to work with
CREATE TABLE #Sample (NStartDate DATE, ApptTime TIME, Duration SMALLINT, Frequency SMALLINT, ClientID INT, NEndDate DATE)
INSERT INTO #Sample (NStartDate, ApptTime, Duration, Frequency, ClientID, NEndDate)
VALUES
('2015-12-03', '10:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-03', '17:00:00.0000000', 30, 1, 2528, '2015-12-31'),
('2015-12-09', '05:00:00.0000000', 30, 1, 2528, '2015-12-31')
DECLARE @SQL AS NVARCHAR(MAX);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteDates(calDate) AS(
SELECT TOP(DATEDIFF( dd, @SDate, @EDate) + 1) --Create all the dates needed with the use of a tally table
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, @SDate) calDate
FROM E4
)
--Create the hours
SELECT @SQL = N'WITH cteTimes(AppTime) AS(
SELECT TOP(24)
DATEADD( HH, n, CAST( ''00:00'' AS time))
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)
, (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23))x(n)
)
SELECT t.AppTime' +
--Generate the dynamic columns
(SELECT CHAR(10) + ' ,MAX(CASE WHEN ''' + CONVERT( char(8), calDate, 112) + ''' BETWEEN NStartDate AND NEndDate THEN ClientId END) AS [' + CONVERT( char(10), calDate, 121) + ']'
FROM cteDates
FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)')
+ N'
FROM cteTimes t
LEFT JOIN #Sample s ON t.AppTime = s.ApptTime --Add any parameters here to ensure the left join stays like that
AND NStartDate <= @EDate
AND NEndDate >= @SDate
GROUP BY t.AppTime;'
--This is for debug purposes
PRINT @SQL;
--This is the actual execution
EXEC sp_executesql @SQL, N'@SDate date, @EDate date', @SDate, @EDate
GO
DROP TABLE #Sample;
December 17, 2015 at 9:30 am
Hi Luis,
That worked a brilliantly and fast too! Thank you so much
I'll have to spend some time understanding it tho, as I have built a sql statement within a store procedure before and rownumber in pivots but not 'select from (values.... thats alien to me!
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
I think I seen the above before, but I'm sure it was something to do with rocket fuel for the shuttle! 😉
Anyways thank you a lot...up and running.
I will need to shorten the time format shown from '00:00.0000' to just '00:00'
Any may want to add an 2 additional columns such as StaffName and Duration in place of the ClientID column woudl this be possible?
kind regards
December 18, 2015 at 5:14 am
Hi,
I am no sql expert and I see what the select from values is doing but when I reading Jeff Modens article on tally tables.
He suggested using the following script to create a tally table with all date between 2 dates, with no need to create
RIGHT OUTER JOIN
(--==== Create all shipped dates between start and end date
SELECT t.N-1+@DateStart AS ShippedDate
FROM dbo.Tally t
WHERE t.N-1+@DateStart <= @DateEnd
) dates
ON o.ShippedDate = dates.ShippedDate
GROUP BY dates.ShippedDate
The join to my table...
So I'm wondering why do we need to use the select from values ot Table Value Constructors?
Any advice appreicated.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply