Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Set-Based Solution Possible? Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 2:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:50 PM
Points: 156, Visits: 179
I need to be able to summarize the data for a report ordered by StaffName allowing for multiple rows per Staff per Year
Each column needs to be ordered in Ascending date order and can be blank if there was no review performed for this row and year
I can filter out the desired years from actual data by " WHERE Year(ReviewDate) >= @iYear " {@iYear = Year(GetDate())-3}
Note: my actual data is a StaffId Foreign Key - I just wrote this using a varchar field for demonstration purposes
Also note: I'm not concerned with the StaffName showing or not showing per row; the report should handle that


Desired Result Set:
***********************************************************
StaffName 2006_Dates 2007_Dates 2008_Dates
-------------------------------------------------------
Blow, Joe 2006-03-23 2007-03-13 2008-01-24
2006-07-18 2007-06-28
2007-12-21
Doe, Jane 2006-05-16 2007-03-12 2008-01-22
2006-11-19 2007-06-11
2007-12-16
Dunno, I 2006-12-10 2007-07-05
2007-12-18
***********************************************************

I could insert all data into a temp table and loop through each row and add/update a row per Staff based on the date.
However, is there any way I can do this using a SET BASED solution rather than a RBAR TEMP TABLE/CURSOR method?
Thank you for any suggestions (or straight denials that it can actually be done).


Here is a sample data script:
Create Table #tTestData(
StaffName varchar(40),
ReviewDate smalldatetime
)

Insert Into #tTestData(StaffName, ReviewDate)
Select 'Blow, Joe','2008-01-24' Union All
Select 'Doe, Jane','2008-01-22' Union All

Select 'Dunno, I', '2007-07-05' Union All
Select 'Blow, Joe','2007-06-28' Union All
Select 'Doe, Jane','2007-06-11' Union All
Select 'Blow, Joe','2007-03-13' Union All
Select 'Blow, Joe','2007-12-21' Union All
Select 'Doe, Jane','2007-12-16' Union All
Select 'Doe, Jane','2007-03-12' Union All
Select 'Dunno, I', '2007-12-18' Union All

Select 'Blow, Joe','2006-03-23' Union All
Select 'Blow, Joe','2006-07-18' Union All
Select 'Doe, Jane','2006-05-16' Union All
Select 'Doe, Jane','2006-11-19' Union All
Select 'Dunno, I', '2006-12-10'

Select StaffName, ReviewDate
From #tTestData
Order By StaffName, ReviewDate

Drop Table #tTestData




In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)
Post #448580
Posted Monday, January 28, 2008 6:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 2,285, Visits: 4,222
Yes, as single SQL statement can be written but I would be very concerned about resource utilization (e.g. the SQL would run like a pig).
Please be sure to read Jeff Moden's article titled "Hidden RBAR: Triangular Joins" at http://www.sqlservercentral.com/articles/T-SQL/61539/

This SQL ranks each staff member's review for a specific year and is a hidden RBAR. With SQL Server 2005/2008, better performance can be obtained using the rank window function.
select	#tTestData.StaffName
, #tTestData.ReviewDate
, count(*) as ReviewOrder
from #tTestData
join #tTestData as PriorReview
on PriorReview.StaffName = #tTestData.StaffName
and PriorReview.ReviewDate <= #tTestData.ReviewDate
where year(#tTestData.ReviewDate) = @iyear - 2
and year(PriorReview.ReviewDate) = @iyear - 2
group by #tTestData.StaffName
, #tTestData.ReviewDate


This SQL determines the maximum number of reviews for a staff member in any of the past three years:
select 	StaffName
, CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cnt then Year0Cnt
when Year1Cnt > Year0Cnt then Year1Cnt
else Year0Cnt
end as StaffReviewCnt
from (
Select StaffName
, SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt
, SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt
, SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt
From #tTestData
group By StaffName
) as StaffYearlyReviews

This SQL enumerates each of the possible ranks up to four in any given year. A auxiliary numbers table would be a better solution - see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
select 1 as RowNum union all select 2 union all select 3 union all select 4

Finally, combine all the individual SQL statements:
declare	@iYear smallint
set @iyear = 2008
select StaffReviews.StaffName
, Review2.ReviewDate
, Review1.ReviewDate
, Review0.ReviewDate
from (select StaffName
, CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cnt then Year0Cnt
when Year1Cnt > Year0Cnt then Year1Cnt
else Year0Cnt
end as StaffReviewCnt
from (
Select StaffName
, SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt
, SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt
, SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt
From #tTestData
group By StaffName
) as StaffYearlyReviews
) as StaffReviews
join (select 1 as RowNum union all select 2 union all select 3 union all select 4 )
as ReviewRow
on ReviewRow.RowNum between 1 and StaffReviews.StaffReviewCnt
left outer join
(select #tTestData.StaffName
, #tTestData.ReviewDate
, count(*) as ReviewOrder
from #tTestData
join #tTestData as PriorReview
on PriorReview.StaffName = #tTestData.StaffName
and PriorReview.ReviewDate <= #tTestData.ReviewDate
where year(#tTestData.ReviewDate) = @iyear - 2
and year(PriorReview.ReviewDate) = @iyear - 2
group by #tTestData.StaffName
, #tTestData.ReviewDate
) as Review2
on Review2.StaffName = StaffReviews.StaffName
and Review2.ReviewOrder = ReviewRow.RowNum
left outer join
(select #tTestData.StaffName
, #tTestData.ReviewDate
, count(*) as ReviewOrder
from #tTestData
join #tTestData as PriorReview
on PriorReview.StaffName = #tTestData.StaffName
and PriorReview.ReviewDate <= #tTestData.ReviewDate
where year(#tTestData.ReviewDate) = @iyear - 1
and year(PriorReview.ReviewDate) = @iyear - 1
group by #tTestData.StaffName
, #tTestData.ReviewDate
) as Review1
on Review1.StaffName = StaffReviews.StaffName
and Review1.ReviewOrder = ReviewRow.RowNum
left outer join
(select #tTestData.StaffName
, #tTestData.ReviewDate
, count(*) as ReviewOrder
from #tTestData
join #tTestData as PriorReview
on PriorReview.StaffName = #tTestData.StaffName
and PriorReview.ReviewDate <= #tTestData.ReviewDate
where year(#tTestData.ReviewDate) = @iyear - 0
and year(PriorReview.ReviewDate) = @iyear - 0
group by #tTestData.StaffName
, #tTestData.ReviewDate
) as Review0
on Review0.StaffName = StaffReviews.StaffName
and Review0.ReviewOrder = ReviewRow.RowNum

The result set is
Blow, Joe	2006-03-23 00:00:00	2007-03-13 00:00:00	2008-01-24 00:00:00
Blow, Joe 2006-07-18 00:00:00 2007-06-28 00:00:00 NULL
Blow, Joe NULL 2007-12-21 00:00:00 NULL
Doe, Jane 2006-05-16 00:00:00 2007-03-12 00:00:00 2008-01-22 00:00:00
Doe, Jane 2006-11-19 00:00:00 2007-06-11 00:00:00 NULL
Doe, Jane NULL 2007-12-16 00:00:00 NULL
Dunno, I 2006-12-10 00:00:00 2007-07-05 00:00:00 NULL
Dunno, I NULL 2007-12-18 00:00:00 NULL



SQL = Scarcely Qualifies as a Language
Post #448652
Posted Tuesday, January 29, 2008 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:50 PM
Points: 156, Visits: 179
Thank you - I thought there had to be a way but couldn't quite get there and wasn't given the time to work it out.

Checking the execution plan I see why you have concerns about it being resource intensive. There are certainly a lot of table scans going on! Works nicely though and well thought out.

I wrote a stored procedure after I posted using a couple of temp tables and single row processing. It's not Set based, but it does work and while there are a lot of queries involved they are all Clustered Index Scans for the most part returning a single row. Might be more maintainable for my replacement if I get hit by the proverbial I.T. bus.

My actual data requirements are for the last 5 years of data per staff member, but thankfully this is a small company so really only talking between 50-60 staff members to report on so it's not like it's a huge server hit. I will give both methods a shot and the one which crosses the finish line first will get the job.

Thank you for taking the time with this it is very much appreciated.
Mike



In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)
Post #448910
Posted Tuesday, January 29, 2008 9:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 4:07 PM
Points: 458, Visits: 88
I would do it using a user defined function. Some / Many may not consider this a SET based approach.

PLEASE NOTE this example creates a permanat table and user defined function


Create Table tTestData
(
StaffName varchar(40),
ReviewDate smalldatetime
)

Insert Into tTestData(StaffName, ReviewDate)
Select 'Blow, Joe','2008-01-24' Union All
Select 'Doe, Jane','2008-01-22' Union All
Select 'Dunno, I', '2007-07-05' Union All
Select 'Blow, Joe','2007-06-28' Union All
Select 'Doe, Jane','2007-06-11' Union All
Select 'Blow, Joe','2007-03-13' Union All
Select 'Blow, Joe','2007-12-21' Union All
Select 'Doe, Jane','2007-12-16' Union All
Select 'Doe, Jane','2007-03-12' Union All
Select 'Dunno, I', '2007-12-18' Union All
Select 'Blow, Joe','2006-03-23' Union All
Select 'Blow, Joe','2006-07-18' Union All
Select 'Doe, Jane','2006-05-16' Union All
Select 'Doe, Jane','2006-11-19' Union All
Select 'Dunno, I', '2006-12-10'



GO


CREATE FUNCTION dbo.udf_GetDates
(
@pStaffName VARCHAR(40),
@pReviewDate DATETIME
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @RDates VARCHAR(2000)

SELECT @RDates = COALESCE(@RDates + ', ', '') + CONVERT(VARCHAR, ReviewDate, 101)
FROM
tTestData
WHERE
StaffName = @pStaffName
AND DATEDIFF(YEAR, ReviewDate, @pReviewDate) = 0

RETURN @RDates

END

GO



SELECT StaffName,
[2006 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2006'),
[2007 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2007'),
[2008 Reviews] = dbo.udf_GetDates(StaffName, '01/01/2008')
FROM
(
Select DISTINCT StaffName
From
tTestData
) A


GO

DROP TABLE tTestData
DROP FUNCTION udf_GetDates



Regards,
gova
Post #448967
Posted Tuesday, January 29, 2008 10:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:50 PM
Points: 156, Visits: 179
Another interesting approach. Thank you. The data doesn't result in the format I need with this method however. It creates a nice comma-delimited dates per year instead.

Actually though, I wanted to say I like the way you used COALESCE with the @RDates variable for the comma delimiter to handle the first iteration and subsequent appends. I never considered doing it that way. I am going to shamelessly incorporate that into my coding arsenal.

Learn something new every day - right?
Cheers, Mike



In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)
Post #448997
Posted Tuesday, January 29, 2008 3:05 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
This returns exactly what you need.

This script uses table Tally which contains sequential integer numbers.
You may create it yourself or search this forum for one of scripts creating it.

Create Table tTestData (  
StaffName varchar(40),
ReviewDate smalldatetime
)

SET DATEFORMAT YMD

Insert Into tTestData(StaffName, ReviewDate)
Select 'Blow, Joe','2008-01-24' Union All
Select 'Doe, Jane','2008-01-22' Union All
Select 'Dunno, I', '2007-07-05' Union All
Select 'Blow, Joe','2007-06-28' Union All
Select 'Doe, Jane','2007-06-11' Union All
Select 'Blow, Joe','2007-03-13' Union All
Select 'Blow, Joe','2007-12-21' Union All
Select 'Doe, Jane','2007-12-16' Union All
Select 'Doe, Jane','2007-03-12' Union All
Select 'Dunno, I', '2007-12-18' Union All
Select 'Blow, Joe','2006-03-23' Union All
Select 'Blow, Joe','2006-07-18' Union All
Select 'Doe, Jane','2006-05-16' Union All
Select 'Doe, Jane','2006-11-19' Union All
Select 'Dunno, I', '2006-12-10'



GO
CREATE FUNCTION dbo.tReviewsOfYear
(
@ReviewYear SMALLDATETIME
)
RETURNS @Reviews TABLE (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StaffName VARCHAR(40),
ReviewNo int NULL,
ReviewDate SMALLDATETIME NOT NULL
)
AS
BEGIN
-- To make sure we deal with "year only" value
SET @ReviewYear = DATEADD(YY, DATEDIFF(YY, 0, @ReviewYear), 0)

DECLARE @StaffName VARCHAR(200)
DECLARE @Count INT

INSERT INTO @Reviews (StaffName, ReviewDate)
SELECT StaffName, ReviewDate
FROM dbo.tTestData
WHERE ReviewDate >= @ReviewYear
AND ReviewDate < DATEADD(YY, 1, @ReviewYear)
ORDER BY StaffName, ReviewDate

-- Sequential update driven by PRIMARY KEY
UPDATE R
SET @Count = ReviewNo = CASE WHEN @StaffName = StaffName THEN @Count + 1 ELSE 1 END,
@StaffName = StaffName
FROM @Reviews R

RETURN

END

GO

select CASE WHEN T.N = 1 THEN S.StaffName ELSE '' END,
T.N, Y2006.ReviewDate [2006_Dates], Y2007.ReviewDate [2007_Dates], Y2008.ReviewDate [2008_Dates]
from dbo.tTestData S
INNER JOIN dbo.Tally T ON T.N > 0 AND T.N < 5000
-- If you expect more than 5000 reviews per year per customer increase this number
LEFT HASH JOIN dbo.tReviewsOfYear ('2006-01-01') Y2006 ON S.StaffName = Y2006.StaffName AND T.N = Y2006.ReviewNo
LEFT HASH JOIN dbo.tReviewsOfYear ('2007-01-01') Y2007 ON S.StaffName = Y2007.StaffName AND T.N = Y2007.ReviewNo
LEFT HASH JOIN dbo.tReviewsOfYear ('2008-01-01') Y2008 ON S.StaffName = Y2008.StaffName AND T.N = Y2008.ReviewNo
WHERE Y2006.ReviewNo IS NOT NULL OR Y2007.ReviewNo IS NOT NULL OR Y2008.ReviewNo IS NOT NULL
GROUP BY S.StaffName, T.N, Y2006.ReviewDate , Y2007.ReviewDate , Y2008.ReviewDate
ORDER BY S.StaffName, T.N


Post #449107
Posted Wednesday, January 30, 2008 3:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 06, 2008 6:37 AM
Points: 7, Visits: 45
i have a similar problem where setbased solution would speed things up dramatically:
As part of a large report, I have to to calculate various expenses that have incurred.

I achieve this by executing a separate storedproc which updates the temp tables that are created by the Main storedproc.

There are 50 different Expense types with different selection criteria, so Ive created a table to keep all these expenses and there selection criteria.
Ie:
Insert Into fst_Expenses_Template(Descr, Where_SD_GB, Where_TRA)
Select ‘Admin Fees’,’ E3_field = 132 ,’ E1_exp_level1 = 0132’ Union All
Select ‘Broker fees’,’ E3_field = 135 ,’ E1_exp_level1 = 0135’ Union All
Select ‘Legal Fees’,’ E3_field = 139 ,’ E1_exp_level1 = 0139’ Union All

To process each expensetype, I repetitively read (in WHILE loop) from this Expenses "template" table and build up a query-string which will Update a separate temp table with the results. (see code below which ive simplified for readability).
Could this be done set-based ?
DECLARE @REP_count TinyInt ,
@intRow TinyInt,
@commonWhere_SD_GB Varchar (60),
@commonWhere_TRA Varchar (60),
@descr Varchar(50),
@SQL Varchar(5000)

INSERT INTO #EXPENSE_Tbl
SELECT *
FROM fst_Expenses_Template

SET @REP_count = @@ROWCOUNT
SET @intRow = 1

/*-----------------------------------
Loop for each of the 45 expense types
-------------------------------------*/
WHILE @intRow <= @REP_count
BEGIN
SELECT
@descr = Descr,
@commonWhere_SD_GB = Where_SD_GB,
@commonWhere_TRA = Where_TRA
FROM
fst_Expenses_Template --#EXPENSE_Tbl
WHERE
Num = @intRow

SET @SQL='
UPDATE #EXPENSE_Tbl
SET
SD_GB_End =
(
SELECT SUM(gb_field_end)
FROM
fst_tbl_SD
WHERE

' + @commonWhere_SD_GB + '
),
SD_GB_Start =
(
SELECT SUM(gb_field)
FROM
fst_tbl_SD
WHERE
' + @commonWhere_SD_GB + '
),
TRA_G9 =
(
SELECT SUM(g9_income_base)
FROM
fst_tbl_Transactions
WHERE
' + @commonWhere_TRA + '
),
TRA_H5 =
(
SELECT SUM(h5_Income_FX_gl)
FROM
fst_tbl_Transactions
WHERE
' + @commonWhere_TRA + '
),
TRA_H77 =
(
SELECT SUM(h77_gls)
FROM
fst_tbl_Transactions
WHERE
' + @commonWhere_TRA + '
)
WHERE
#EXPENSE_Tbl.Descr = ''' + @descr + '''
'
Exec (@SQL)

SET @intRow= @intRow + 1
END
/*--------------------
Return the results
-------------------- -*/
.
Post #449237
Posted Wednesday, January 30, 2008 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:50 PM
Points: 156, Visits: 179
Ok, Sergiy wins. Lightning fast and easily maintainable if required.

I had to look at that function line a few times
... SET @Count = ReviewNo = CASE ...

to figure out exactly what he did though. And once again I've never considered doing that.

I'm learning just how much I don't know I guess!
Thank you Sergiy



In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move.   Douglas Adams (1952-2001)
Post #449448
Posted Wednesday, January 30, 2008 2:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
You welcome.

This UPDATE has 2 other imprescriptible parts outside of the sattement itself:
ORDER BY StaffName, ReviewDate

which inserts rows in the table in specific order and
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

which holds the rows in the order they've been inserted.

If you want to perform the same trick on a static table you need to have specifically crafted index and use WITH INDEX hint.
Post #449697
Posted Thursday, January 31, 2008 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
Why use UPDATE at all? Or use a function? This is possible to do in a single query.
And still have some options to include or exclude "non-valid" members!

SELECT		CASE WHEN u.theIndex = 0 THEN u.StaffName ELSE '' END AS StaffName,
MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN u.ReviewDate ELSE '' END) AS [2006_Dates],
MAX(CASE WHEN u.ReviewDate LIKE '2007%' THEN u.ReviewDate ELSE '' END) AS [2007_Dates],
MAX(CASE WHEN u.ReviewDate LIKE '2008%' THEN u.ReviewDate ELSE '' END) AS [2008_Dates]
FROM (
SELECT t1.StaffName,
CONVERT(CHAR(10), t1.ReviewDate, 120) AS ReviewDate,
(
SELECT COUNT(*)
FROM #TestData AS t2
WHERE t2.StaffName = t1.StaffName
AND YEAR(t2.ReviewDate) = YEAR(t1.ReviewDate)
AND t2.ReviewDate < t1.ReviewDate
AND t2.ReviewDate >= '20060101'
AND t2.ReviewDate < '20090101'
) AS theIndex
FROM #TestData AS t1
/* Remove this comment to only display staff who has dates in valid range
WHERE t1.ReviewDate >= '20060101'
AND t1.ReviewDate < '20090101'
*/
) AS u
GROUP BY u.StaffName,
u.theIndex
ORDER BY u.StaffName,
u.theIndex

You have also the option to change AND t2.ReviewDate < t1.ReviewDate to AND t2.ReviewDate > t1.ReviewDate if you want the dates sorted ascending instead.



N 56°04'39.16"
E 12°55'05.25"
Post #449955
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse