How to display months across like crosstab?

Question

How to display months across like crosstab?

Viewing 10 posts - 31 through 39 (of 39 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

abhilasht (3/4/2012)
So we must have the details on the Periods.
Here i created one sample periods #period table. and come one more issue to find the available periods. So it puts as exec statements.See the sample

Go back and read this full thread. The OP has stated that he cannot use dynamic SQL because his company doesn't allow it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

siva 20997 (3/4/2012)
However putting the correct title in the TSQL itself with in the Pivot Command I am not if that can be done

Actually, I missed what Jonathan Livingston SQL did. His method works just fine. It's a simple and brilliant method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

siva 20997 Ten Centuries Points: 1161 More actions · Answer 3

I agree livingstones method works.

However you have write the script everytime you want to Execute it becaus the 12 months will change from

jan,feb,mar, etc

feb,Mar,Apr after a month etc

What I was planaing to do his put his method into a Store procedure called lets Say PeriodicSales with the start date as the parameter

so to execute it you would do

Exec PeriodicSales '01/01/2012' --- This for the first case

Exec PeriodicSales '02/01/2012' --- This for the seccond case

For that the titles must change within the script

That is where the Calendar tables come into play

If Livingstone Script does what the person wanted lets leave it at that.

We are just wrapping it up in SP so that it is flexible but it requires 2 supporting files

whcih can be used by manay queries like this

In accounting you have that requirment. Hence we have the supporting files as standard

1) is a period table setting up all the periods by Month or week or user defined like 13Month

2) is a dates table which has every date and the period it belongs to

For me to put how we do it we will have to put all these scripts on the forum and Sps whicg generates the Dates etc

after that only the SP we devlop to output as required will work

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

siva 20997 (3/4/2012)
I agree livingstones method works.
However you have write the script everytime you want to Execute it becaus...

Nope, you don't. If you make the minor tweak of using GETDATE() for the @monthx variables, the code will be self maintaining.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

siva 20997 Ten Centuries Points: 1161 More actions · Answer 5

I am not sure where you can use variables in the Pivot Statment so as to move the12 month period forward month by month

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

siva 20997 (3/4/2012)
I am not sure where you can use variables in the Pivot Statment so as to move the12 month period forward month by month

Livingston's code didn't use a PIVOT nor is one needed. He used the variables to control what was returned in each column of a Cross-Tab to populate a Temp table and then renamed the columns of the Temp Table. None of it requires Dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

siva 20997 Ten Centuries Points: 1161 More actions · Answer 7

SELECT Customer,

Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

FROM

(

Select

left(datename(month,SalesDate ),3) as mn

,Sales,Customer

from

#Sales

) AS SourceTable

PIVOT

(

sum(Sales)

FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

) AS PivotTable;

1) The Data mn,Sales,Cusomer is being Pivoted here. Is there a some other command called PIVOT if this is not PIVOT

2) How would you move the columns forward a Month without writing new code

3) How would you do it if it is needed week by week.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

siva 20997 (3/4/2012)
SELECT Customer,
Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
FROM
(
Select
left(datename(month,SalesDate ),3) as mn
,Sales,Customer
from
#Sales
) AS SourceTable
PIVOT
(
sum(Sales)
FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
) AS PivotTable;
1) The Data mn,Sales,Cusomer is being Pivoted here. Is there a some other command called PIVOT if this is not PIVOT
2) How would you move the columns forward a Month without writing new code
3) How would you do it if it is needed week by week.

Heh... for goodness sake, Siva... go look at the code that Livingston posted! You'll see how!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

siva 20997 Ten Centuries Points: 1161 More actions · Answer 9

Sorry. I have been looking at the wrong code

I should have been looking at Abishalt code

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

siva 20997 (3/4/2012)
Sorry. I have been looking at the wrong code
I should have been looking at Abishalt code

Ah, got it. Your questions were conerning Abishalt's code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions