March 3, 2012 at 11:06 am
R.P.Rozema (3/3/2012)
? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?
i had this in my code...albeit m1.m2.m3
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 3, 2012 at 11:10 am
Jeff
sorry I didnt notice that you were part of the SSC Moderators etc
Can I eMail you the answer and then you can cut and paste whatever you think best at the appropriate place after you got it working
Siva
March 3, 2012 at 12:13 pm
I just noticed noticed your article on cross tabs etc.
I am new to your Forum and didnt notice who is who etc
There are few additional things i am doing on top of what you are doing, Like Calandar Table, CTE Commands
since you undertand Pivot better than me I can just send you my solution tomorrow
March 3, 2012 at 11:10 pm
siva 20997 (3/3/2012)
Jeffsorry I didnt notice that you were part of the SSC Moderators etc
Can I eMail you the answer and then you can cut and paste whatever you think best at the appropriate place after you got it working
Siva
I'm not an SSC moderator. Just a concerned fellow programmer.
You can post your own code. Heh... "after you got it working".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 11:17 pm
siva 20997 (3/3/2012)
I just noticed noticed your article on cross tabs etc.I am new to your Forum and didnt notice who is who etc
There are few additional things i am doing on top of what you are doing, Like Calandar Table, CTE Commands
since you undertand Pivot better than me I can just send you my solution tomorrow
It would be better if you just posted it here. All code ideas are welcome and we all learn from other's code.
I believe that just about anyone can meet the OPS output requirement of listing varying short month names in the output using dynamic SQL . I don't believe there's a way to do the same thing without the use of dynamic SQL which is another of the OPS requirements. If you have a way to do that, then I know I'd certainly like to see it because I'm at a loss for how to do create varying short month names in the output based on the data without the use of dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 11:21 pm
J Livingston SQL (3/3/2012)
R.P.Rozema (3/3/2012)
? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?i had this in my code...albeit m1.m2.m3
I agree. But the OP specifically listed short month names as part of the desired output. I don't believe such a thing can be done without the use of dynamic SQL but would be very happy to see someone pull it off without the use of dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 11:29 pm
siva 20997 (3/3/2012)
The reply to the original question will be long and hard to understand once donehence I just touched upon the subjects which is needed to be understood to undertand the solution
Since it is quite a big complicated query I will put the whole query in a Strored procedure and return a result set
The parameter to Sp will specify the starting Date and ending Date. I usually do 12 or 24 months
I also have Calendar Table. This is so usefull in all aplications I have this as a standard. There is discussion thread on that in this forum
So the steps involved in the Stored procedure are as follows
1) select all the rows grouped by Customer and month and the value. you can pick up the month name from the Calendar table after joing it on Date instead of Month Function. easier and faster for grouping etc
2) Then Pivot the results from 1 above
to get the results from 1 in to 2 need to use Common Table Expression, which start with the reserved word WITH
I didnt want to cut and paste my code because it will be difficult to explain but once these are understood maybe
I agree with all those tools and methods. But none of them will allow for varying months as column headers in the output without a bit of dynamic SQL which the OP isn't allowed to use by the company he works for. If you can think of a way, I, for one, would absolutely love to see it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 11:33 pm
J Livingston SQL (3/3/2012)
R.P.Rozema (3/3/2012)
? I think I don't get it? But isn't this simply answered by no longer specifying the columns names as 'Jan', 'Feb', etc, but simply label the columns something like 'last month', 'the month before that', '2 months back', '3 months back', etc? Then use a cross tab to get the values for those periods?i had this in my code...albeit m1.m2.m3
Ya know, Graham, even though we can't change the column names because dynamic SQL isn't allowed for this problem, using your good code, we could use the first row of the return as a secondary column header. It would require turning the rest of the output to VARCHAR, but it should work.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2012 at 11:38 pm
Heh... {FacePalm}... {Self Head Slap}... {pound head against hard spot on desk}. I've really got to learn to scroll further down in code windows. I didn't see the column renaming you did using sp_Rename. Absolutely brilliant. Like I told Siva, I'd love to see how it could be done and now I have. I've definitely learned something new today. Thanks.
The only stumbling block is that ALTER permissions are required to use sp_Rename to rename a column. If they don't allow dynamic SQL, they may also seriously restrict such permissions.
I'm going to go suck my thumb now. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2012 at 12:01 am
Yes PIVOT is the best
CREATE TABLE #Sales (SalesDate smalldatetime,Customer int,Sales decimal(20,4))
INSERT INTO #Sales Values ('2011/11/01',1,10)
INSERT INTO #Sales Values ('2011/11/02',1,10)
INSERT INTO #Sales Values ('2011/05/02',2,25)
INSERT INTO #Sales Values ('2011/12/01',2,20)
INSERT INTO #Sales Values ('2012/01/10',3,10)
INSERT INTO #Sales Values ('2012/02/20',3,10)
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;
DROP TABLE #Sales
March 4, 2012 at 12:45 am
abhilasht (3/4/2012)
Yes PIVOT is the best
CREATE TABLE #Sales (SalesDate smalldatetime,Customer int,Sales decimal(20,4))INSERT INTO #Sales Values ('2011/11/01',1,10)
INSERT INTO #Sales Values ('2011/11/02',1,10)
INSERT INTO #Sales Values ('2011/05/02',2,25)
INSERT INTO #Sales Values ('2011/12/01',2,20)
INSERT INTO #Sales Values ('2012/01/10',3,10)
INSERT INTO #Sales Values ('2012/02/20',3,10)
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;
DROP TABLE #Sales
How are you proposing to meet the OP's request...?
they require Nov/Dec to display before Jan
Shree-903371 (3/2/2012)
But when years overlap like if I run for 2011/11/01 to 2012/01/31 then it displays Jan first and then Nov and Dec.CustomerJanNovDec
1102030
But I need Nov, Dec and then Jan. So can somebody help me?
CustomerNovDecJan
1203010
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 4, 2012 at 3:27 am
The Pivot Table solution given by Livingstone works
we actually have a table of dates for say from yesr 2000 to 2020 with bank holidays, period number, week number etc etc
we would have joined the data created by Livingstone to dates table and known every period the summed quantity falls in to
we would Group it by Account and Period and feed it into Pivot Table method shown by Livinsstone wuing CTE
So instaead of Jan feb Mar we will have period 111,112,113
Now in the application dispaly we would have changed 111 to jan 2012 @var1, 112 to Feb 2012 @var2 etc
I think we do that in the application. we are able to format the titles before display
With this method the periods can be floating , go over a year boundary, go backwards etc
However putting the correct title in the TSQL itself with in the Pivot Command I am not if that can be done
where in that example we have
FOR mn IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
could we have something like
FOR mn IN (period1 as @var1,Period2 as var@2 etc
we have not managed to do that
So our solution would look like
Ac/No Period1 Period 2 Period3
A001 100 222 33
A002 3 44 55
The application then puts the correct title as it knows what dates belong to period1
March 4, 2012 at 5:40 am
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
CREATE TABLE #PERIOD (ID INT,DFROM DATE, DTO DATE,PERIODNAME VARCHAR(10))
DECLARE @STARTDATE DATE,@ENDDATE DATE;
SELECT @STARTDATE = '2011-01-01',@ENDDATE = '2013-12-31' ;
WITH CTEDATES AS(
SELECT TOP (DATEDIFF(MM,@STARTDATE,@ENDDATE) + 1)
MONTHDATE = DATEADD(MM,
DATEDIFF(MM,0,@STARTDATE)
+ (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)
FROM SYS.ALL_COLUMNS AC1
CROSS JOIN SYS.ALL_COLUMNS AC2
)
INSERT INTO #PERIOD
SELECT ROW_NUMBER()OVER(ORDER BY MONTHDATE), MONTHDATE,
DATEADD(D,-1,DATEADD(MM,1 ,MONTHDATE)),
SUBSTRING( CONVERT(VARCHAR(100),MONTHDATE,106),4,100)FROM CTEDATES;
CREATE TABLE #SALES (SALESDATE SMALLDATETIME,CUSTOMER INT,SALES DECIMAL(20,4))
INSERT INTO #SALES VALUES ('2011/11/01',1,10)
INSERT INTO #SALES VALUES ('2011/9/01',1,1)
INSERT INTO #SALES VALUES ('2011/9/01',3,25)
INSERT INTO #SALES VALUES ('2011/11/02',1,10)
INSERT INTO #SALES VALUES ('2011/05/02',2,25)
INSERT INTO #SALES VALUES ('2011/12/01',2,20)
INSERT INTO #SALES VALUES ('2012/01/10',3,10)
INSERT INTO #SALES VALUES ('2012/02/20',3,10)
DECLARE @PERIODS_ORDER VARCHAR(MAX)
SELECT @PERIODS_ORDER=ISNULL(@PERIODS_ORDER+',','')+'['+PERIODNAME+']' FROM
#SALES S, #PERIOD P WHERE S.SALESDATE BETWEEN P.DFROM AND P.DTO
GROUP BY P.ID,PERIODNAME
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
SELECT CUSTOMER ,
'+@PERIODS_ORDER+'
FROM
(
SELECT
PERIODNAME AS MN
,SALES,CUSTOMER
FROM
#SALES S
JOIN #PERIOD P ON S.SALESDATE BETWEEN P.DFROM AND P.DTO
) AS SOURCETABLE
PIVOT
(
SUM(SALES)
FOR MN IN ('+@PERIODS_ORDER+')
) AS PIVOTTABLE;'
EXEC (@SQL)
DROP TABLE #SALES
DROP TABLE #PERIOD
March 4, 2012 at 5:47 am
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
we are assuming that
Shree-903371 (3/2/2012)
The problem is we are not allowed to use open queries in out campany.
means OP cannot use dynamic SQL
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 4, 2012 at 7:36 am
abhilasht (3/4/2012)
Yes PIVOT is the best
Actually, it's not. See the article at the following link for why not. Pay particular attention to the performance chart at the end of the article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply