Extension of Cross-Tabs, with MAX, MIN

Question

Extension of Cross-Tabs, with MAX, MIN

JackIntheBox

SSCrazy

Points: 2022
More actions
October 6, 2011 at 8:18 am

#249017

Hi,
I was looking for a solution inorder to get the max dates from a cross-tab aka Pivot in the modern 2005,2008. Here is the scenario, I have to return the Max of the modified date and the min of created date to be returned from the crosstab query.
I am pretty sure there must be some easier way than to compare dates individually with use of MAX,MIN function (maybe) to incorporate it in the same query. I basically took the sample code from Jeff Moden's blog about crosstabs and modified it.http://www.sqlservercentral.com/articles/T-SQL/63681/
I can get the MAX(dates) in horizontal fashion, but now i have 4 dates to compare in my real world situation.
CREATE TABLE #SomeTable1
(
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1),DateCreated DATETIME,LastModified DATETIME
)
GO
INSERT INTO #SomeTable1
(Year, Quarter, Amount, DateCreated ,LastModified )
SELECT 2006, 1, 1.1, '1/1/1900','1/1/2011' UNION ALL
SELECT 2006, 2, 1.2,'1/1/1902','3/1/2011' UNION ALL
SELECT 2006, 3, 1.3,'1/1/2003','4/1/2011' UNION ALL
SELECT 2006, 4, 1.4,'1/1/2000','5/1/2011' UNION ALL
SELECT 2007, 1, 2.1,'1/1/2004','6/1/2011' UNION ALL
SELECT 2007, 2, 2.2, '1/1/1902','7/1/2011'UNION ALL
SELECT 2007, 3, 2.3,'1/1/1902','3/1/2011' UNION ALL
SELECT 2007, 4, 2.4, '1/1/1932','5/1/2011'UNION ALL
SELECT 2008, 1, 1.5,'1/1/1942','6/1/2011' UNION ALL
SELECT 2008, 3, 2.3, '2/1/1942','3/1/2011'UNION ALL
SELECT 2008, 4, 1.9,'1/1/1952','3/1/2011'
SELECT * FROM #SomeTable1
SELECT Year,
SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr]
FROM #SomeTable1
GROUP BY Year
--SO FOR year 2006 it should look like Datemodified =2011-05-01(MAX) & created =1900-01-01 (MIN)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Can you give a bit more detail for your expected results. If you're after the min and max per year then this should work.

SELECT Year,

SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],

SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],

SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],

SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],

MAX(LastModified) AS LastModified,

MIN(DateCreated) AS DateCreated

FROM #SomeTable1

GROUP BY Year

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JackIntheBox SSCrazy Points: 2022 More actions · Answer 2

O..o It would have been incredibly simple if i had to return just the min and max on year.:-P

Actually I am behind the values of MAX and MIN per quarter . So here should my result set look like

Year1st Qtr2nd Qtr3rd Qtr4th Qtr Datecreated_MIN Date Modified_MAX

==================================================================

20061.1 1.2 1.3 1.4 1900-01-01 2011-04-01

20072.1 2.2 2.3 2.4 1902-01-01 2011-07-01

20081.5 0.0 2.3 1.9 1942-01-01 2011-06-01

One way which i thought is to return the dates in horizontal fashion per quarter and then use a jumble of case statements to determine the min and max of the dates returned. But i am sure there must be more efficient way to return these values.

HowardW One Orange Chip Points: 29109 More actions · Answer 3

It's a very confusing requirement. So you just need 2 columns which are grouped by year, but you somehow want them to represent 4 different quarters per row??

Do you actually mean you want 8 more columns (one per quarter, per date) rather than 2?

Actually I am behind the values of MAX and MIN per quarter . So here should my result set look like

Which quarter? There are 4 on each row

drew.allen SSC Guru Points: 76985 More actions · Answer 4

ulteriorm (10/6/2011)
O..o It would have been incredibly simple if i had to return just the min and max on year.:-P

I think it IS that simple. Your expected results in this post don't match the original expected results for 2006. Using the the original expected results, it exactly matches the results of the query that Mark posted.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

JackIntheBox SSCrazy Points: 2022 More actions · Answer 5

I think i made a mistake when I posted the resultant set, Also it was because i was trying to reproduce the real world scenario in test data. I think i figured on to get the MAX and MIN dates ( it was showing wrong results because there are other values which are not being pivoted in that table). I am sorry about the confusion.

But i do have an extension question to this crosstabs, which is ; each of the date created and last modified has an associated employee id related to it, I have to return those employees based on the MAX and MIN dates reported.

CREATE TABLE #SomeTable1

(

Year SMALLINT,

Quarter TINYINT,

Amount DECIMAL(2,1),DateCreated DATETIME,LastModified DATETIME,

CreateEmployee INT , LastModifiedEmployee INT

)

GO

INSERT INTO #SomeTable1

(Year, Quarter, Amount, DateCreated ,LastModified, CreateEmployee , LastModifiedEmployee )

SELECT 2006, 1, 1.1, '1/1/1900','1/1/2011' ,26,27 UNION ALL

SELECT 2006, 2, 1.2,'1/1/1902','3/1/2011' ,28,27 UNION ALL

SELECT 2006, 3, 1.3,'1/1/2003','4/1/2011' ,28,27 UNION ALL

SELECT 2006, 4, 1.4,'1/1/2000','5/1/2011' ,28,30 UNION ALL

SELECT 2007, 1, 2.1,'1/1/2004','6/1/2011' ,26,27 UNION ALL

SELECT 2007, 2, 2.2, '1/1/1902','7/1/2011' ,42,31 UNION ALL

SELECT 2007, 3, 2.3,'1/1/1902','3/1/2011' ,42,27 UNION ALL

SELECT 2007, 4, 2.4, '1/1/1932','5/1/2011' ,26,27 UNION ALL

SELECT 2008, 1, 1.5,'1/1/1942','6/1/2011' ,23,32 UNION ALL

SELECT 2008, 3, 2.3, '2/1/1942','3/1/2011' ,26,27 UNION ALL

SELECT 2008, 4, 1.9,'1/1/1952','3/1/2011',26,27

SELECT * FROM #SomeTable1

YearQuarterAmountDateCreatedLastModifiedCreateEmployeeLastModifiedEmployee

============================================================================

200611.11900-01-01 2011-01-01 2627

200621.21902-01-01 2011-03-01 2827

200631.32003-01-01 2011-04-01 2827

200641.42000-01-01 2011-05-01 2830

200712.12004-01-01 2011-06-01 2627

200722.21902-01-01 2011-07-01 4231

200732.31902-01-01 2011-03-01 4227

200742.41932-01-01 2011-05-01 2627

200811.51942-01-01 2011-06-01 2332

200832.31942-02-01 2011-03-01 2627

200841.91952-01-01 2011-03-01 2627

I am trying to get the values for created and modified employeeid. Its a non issue for created, because it remains the same.

The final result should be ;

Year1st Qtr2nd Qtr3rd Qtr4th QtrDateCreatedLastModifiedEmployeeID

============================================================================

20061.11.21.31.41900-01-01 2011-05-01 30

20072.12.22.32.41902-01-01 2011-07-01 31

20081.50.02.31.91942-01-01 2011-06-01 32

I did figure out to retrieve the result set by rejoining to the table with the max values, but somehow the performance is not upto par actual production systems . Is there a different/more efficient way to return the result set ? Below is my query used to return the data. I used cross apply to return data from the original table, and getting the personid based on the MAX values.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

WITH CTE AS (

SELECT Year, Quarter, Amount, DateCreated ,LastModified, CreateEmployee , LastModifiedEmployee,

ROW_NUMBER() OVER(PARTITION BY Year ORDER BY LastModified DESC) AS rn

FROM #SomeTable1)