Creating matrix via TSQL

Question

Creating matrix via TSQL

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
March 20, 2008 at 7:08 am

#204642

I have attached a xls that shows what I need for output.
This is the original code that I started with:
DECLARE @consultantID VARCHAR(20)
DECLARE @StartDt DateTime
DECLARE @EndDt DateTime
--SET @ConsultantID = @COnsultantID
--SET @StartDt = @StartDt
--SET @EndDt = @EndDt
--
SET @ConsultantID = '0000344'
SET @StartDt = '11/01/2007'
SET @EndDt = '10/31/2008'
----
Set @EndDt = DATEADD(month, DATEDIFF(month, 0, @EndDt)+1, 0)
Select endtime into #Temp1 From appperioddate
Where EndTime >= @StartDt
AND EndTime <@EndDt
select v.ConsultantID
,(Select c.firstname + ' ' + Lastname) AS ConsultantName
,v.SaleAmountLevelOne AS Mthly1stLinePORSales
,v.SaleAmountLevelTwo AS Mthly2ndLinePORSales
,v.SaleAmountLevelThree AS Mthly3rdLinePORSales
,v.PurchaseAmount AS MthlyPORSalesLessCredits
--,SUM(v.PurchaseAmount) AS MthlyPORSalesLessCreditsTotal
,(v.SaleAmountLevelOne+v.SaleAmountLevelTwo+v.SaleAmountLevelThree) AS YTDTeamSalesTotal
,CASE
WHEN v.PurchaseAmount/15000 <=1 THEN 0
ELSE v.PurchaseAmount/15000
END as 'TotalPersonalSalesCalculatedPoints'
,v.PeriodEndDate
From Volume v
LEFT OUTER JOIN Consultant c ON v.ConsultantID = c.ConsultantID
WHERE v.ConsultantID = @ConsultantID
AND v.PeriodEndDate >= @StartDt
AND v.PeriodEndDate <@EndDt
GROUP BY v.ConsultantID
,FirstName
,LastName
,v.SaleAMountLevelONe
,v.SaleAmountLevelTwo
,v.SaleAmountLevelThree
,v.PurchaseAmount
,v.PeriodEndDate
--ORDER BY v.PeriodENdDate Asc
DROP TABLE #Temp1
Which does not give me the matrix I need. Please review the attachment and let me know you thoughts.
What I need to happen is in a column we will call June I would need rows that will reflect Personal Sales, First Line Sales, etc for the month of june. Then the next column would be July and it's rows would reflect July data. Please let me know what I can do.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 15 posts - 1 through 15 (of 21 total)

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

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 1

With a some research into a solution what I think i need is a crosstab query. I started on some code but need some assistance:

DECLARE @consultantID VARCHAR(20)

DECLARE @StartDt DateTime

DECLARE @EndDt DateTime

SET @ConsultantID = '0000344'

SET @StartDt = '11/01/2007'

SET @EndDt = '10/31/2008'

Create TABLE #ActivitySummary (

ActivitySort INT,

ActivityDesc CHAR(20)

)

INSERT INTO #ActivitySummary VALUES(1, 'Qualified Recruits')

INSERT INTO #ActivitySummary VALUES(2, 'Personal Sales')

INSERT INTO #ActivitySummary VALUES(3, 'First Line Sales')

INSERT INTO #ActivitySummary VALUES(4, 'Second Line Sales')

INSERT INTO #ActivitySummary VALUES(5, 'Third Line Sales')

SELECT * FROM #ActivitySummary

This is the Output:

Sort ActivityDesc

1Qualified Recruits

2Personal Sales

3First Line Sales

4Second Line Sales

5Third Line Sales

As in the attachment these would be the row headers. But I need some idea of how to generated the month columns and then put the two together.

I put together the following to get the date headers:

Select DIstinct Monthname + ' ' + CONVERT(Varchar(4),[Year]) AS DateHeader from SharedDimension..DimDate

WHEREActualDate BETWEEN '2007-11-01' AND '2008-10-31'

Which gave me the following output:

date Header

April 2008

August 2008

December 2007

February 2008

January 2008

July 2008

June 2008

March 2008

May 2008

November 2007

October 2008

September 2008

No I just need help putting it together.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 2

Well, your basic crosstab in SQL looks like:

use AdventureWorks

GO

Declare @MonthsOut int, @StartDate datetime, @EndDate datetime

Set @MonthsOut = 6

Set @StartDate = '2/1/2002'

Set @EndDate = dateadd(mm,@MonthsOut, @StartDate)

Select SalesPersonID,

Jan = sum(case when month(OrderDate) = 1 then SubTotal else 0 end),

Feb = sum(case when month(OrderDate) = 2 then SubTotal else 0 end),

Mar = sum(case when month(OrderDate) = 3 then SubTotal else 0 end),

Apr = sum(case when month(OrderDate) = 4 then SubTotal else 0 end),

May = sum(case when month(OrderDate) = 5 then SubTotal else 0 end)

from Sales.SalesOrderHeader SOH

Where OrderDate >= @StartDate and OrderDate < @EndDate

Group By SalesPersonID

You havent stated whether or not your date range is dynamic, or if spans years in which case this will need to be modified.

If you need a variable amount of columns, then you'll have to do go the dynamic SQL route, I think.

There are also the obvious efficiency issues of executing a function on your data, but this is just something basic to get the wheels turning.

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

Thanks for that It gave me an idea on how to pull the totals I am looking for but now I need to join in my temp table #ActivitySummary:

DECLARE @consultantID VARCHAR(20)

DECLARE @StartDt DateTime

DECLARE @EndDt DateTime

SET @ConsultantID = '0000344'

SET @StartDt = '11/01/2007'

SET @EndDt = '10/31/2008'

Create TABLE #ActivitySummary (

ActivitySort INT,

ActivityDesc CHAR(20)

)

INSERT INTO #ActivitySummary VALUES(1, 'Qualified Recruits')

INSERT INTO #ActivitySummary VALUES(2, 'Personal Sales')

INSERT INTO #ActivitySummary VALUES(3, 'First Line Sales')

INSERT INTO #ActivitySummary VALUES(4, 'Second Line Sales')

INSERT INTO #ActivitySummary VALUES(5, 'Third Line Sales')

--SELECT * FROM #ActivitySummary

Select DIstinct SUBSTRING(Monthname,1,3) + ' ' + CONVERT(Varchar(4),[Year]) AS DateHeader,

'DateSort' =

CASE

WHEN MONTHNAME = 'January' THEN 1

WHEN MONTHNAME = 'February' THEN 2

WHEN MONTHNAME = 'March' THEN 3

WHEN MONTHNAME = 'April'Then 4

WHEN MONTHNAME = 'May'Then 5

WHEN MONTHNAME = 'June'Then 6

WHEN MONTHNAME = 'July'Then 7

WHEN MONTHNAME = 'August'Then 8

WHEN MONTHNAME = 'September'Then 9

WHEN MONTHNAME = 'October'Then 10

WHEN MONTHNAME = 'November'Then 11

WHEN MONTHNAME = 'December'Then 12

END

from SharedDimension..DimDate

WHEREActualDate BETWEEN '2007-11-01' AND '2008-10-31'

There should be a set of four rows for each month containing the activity descriptions if that makes sense.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

...but now I need to join in my temp table #ActivitySummary...

I suppose this should be painfully obvious, but you need to relate the ActivitySort column to some other column in your other table. It's kinda hard to join two tables that have no relationship to one another, and based on what you have posted so far, I don't see one.

chris-650182 SSC Veteran Points: 240 More actions · Answer 5

If it makes you feel any better, I have to so something similar to this. On the first of each month, the query pulls data going back the previous 12 months. I had to use a combination of Dynamic SQL and the new 2k5 Pivot function.

Actually, there was an article on this written a while back that helped.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=94268&DisplayTab=Article

See if this helps.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

You can use cross apply to join the tables together. See below.

declare @t TABLE(

id int identity(1,1),

col1 varchar(10)

)

insert into @t (col1)

select 'test1' union all

select 'test2' union all

select 'test3'

select *

from @t a

cross apply(

SELECT 'Cross Apply 1' AS [SomeText] union

SELECT 'Cross Apply 2'

) as b

order by a.id

My blog: http://jahaines.blogspot.com

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 7

I am not sure that gets me to where I want to be.

This almost gets me to where I want to be:

GO

Declare @MonthsOut int, @StartDate datetime, @EndDate datetime, @COnsultantID char(20)

Set @ConsultantID = '0000344'

Set @MonthsOut = 12

Set @StartDate = '11/1/2007'

Set @EndDate = dateadd(mm,@MonthsOut, @StartDate)

Create TABLE #ActivitySummary (

ActivitySort INT,

ActivityDesc CHAR(20)

)

INSERT INTO #ActivitySummary VALUES(1, 'Qualified Recruits')

INSERT INTO #ActivitySummary VALUES(2, 'Personal Sales')

INSERT INTO #ActivitySummary VALUES(3, 'First Line Sales')

INSERT INTO #ActivitySummary VALUES(4, 'Second Line Sales')

INSERT INTO #ActivitySummary VALUES(5, 'Third Line Sales')

Select

Jan = sum(case when month(v.PeriodEndDate) = 1 AND V.consultantID=@consultantID then PurchaseAmount else 0 end),

Feb = sum(case when month(v.PeriodEndDate) = 2 AND V.consultantID=@consultantID then PurchaseAmount else 0 end),

Mar = sum(case when month(v.PeriodEndDate) = 3 AND V.consultantID=@consultantID then PurchaseAmount else 0 end),

Apr = sum(case when month(v.PeriodEndDate) = 4 AND V.consultantID=@consultantID then PurchaseAmount else 0 end),

May = sum(case when month(v.PeriodEndDate) = 5 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Jun = sum(case when month(v.PeriodEndDate) = 6 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Jul = sum(case when month(v.PeriodEndDate) = 7 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Aug = sum(case when month(v.PeriodEndDate) = 8 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Sep = sum(case when month(v.PeriodEndDate) = 9 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Oct = sum(case when month(v.PeriodEndDate) = 10 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Nov = sum(case when month(v.PeriodEndDate) = 11 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end),

Dec = sum(case when month(v.PeriodEndDate) = 12 AND V.consultantID=@consultantID then v.PurchaseAmount else 0 end)

from Volume V

Where v.PeriodEndDate >= @StartDate and v.PeriodEndDate < @EndDate

--Group By SalesPersonID

See attached:

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 8

So the question becomes...how do you know whether a sale is Personal, first, second or third? Once you know that, group by that column, and it should break it out for you appropriately.

Also, one other minor problem that I see is that if your date range overlaps years, the same month in different years will get put into the same bucket.

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 9

Oh, I see. In your original post, you show those values as columns in a table. What you are trying to do in one query is UNPIVOT the columns into rows, then PIVOT those results into buckets by month.

I can think of all sorts of horrible ways to do this.

My first stab would be to do something like use a query in a temp table/derived table/CTE like:

Select ConsultantID, PersonalSales, Date

From Table

union all

Select ConsultantID, FirstLevelSales, Date

From Table

Union all

Select ConsultantID, SecondLevelSales, Date

From Table

Then crosstab and group those results into what you want.

I don't unpivot very often, so there may be more optimal strategies than this one out there...

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 10

In the #ActivitySummary there is a sort column ActivitySort which puts them in the appropriate Order so but how do I get the #Activity table joined sinced there is not a common ID linking the two?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 11

If you're in the business of making IDs, then just make some IDs...

Select ConsultantID, 2 as ActivityID, PersonalSales, Date

From Table

union all

Select ConsultantID, 3 ,FirstLevelSales, Date

From Table

Union all

Select ConsultantID, 4, SecondLevelSales, Date

From Table

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 12

OK, I did this:

Select (Select ACtivityDesc FROM #ActivitySummary WHERE ActivitySort = 2) AS ActivtySummary,