March 2, 2012 at 12:00 pm
I need to display Sales data by month.
Customer Jan Feb
1 10 20
So I used Month function and hardcoded like
Month(SalesDate) =1 THEN Sales AS Jan
Month(SalesDate) =2 THEN Sales AS Feb
Etc
Month(Salesdate)=12 THEN Sales AS Dec
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.
Customer Jan Nov Dec
1 10 20 30
But I need Nov, Dec and then Jan. So can somebody help me?
Customer Nov Dec Jan
1 20 30 10
March 2, 2012 at 12:13 pm
Best place to start would be the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2012 at 12:35 pm
I attached the code below. In the results you can see jan coming first, but i need it to last as it is from 2012.
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/12/01',2,20)
INSERT INTO #Sales Values ('2012/01/10',3,10)
SELECT
Customer,
SUM(CASE WHEN MONTH(SalesDate)=1 THEN SALES ELSE 0 END) AS Jan, -- Till October
SUM(CASE WHEN MONTH(SalesDate)=11 THEN SALES ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(SalesDate)=12 THEN SALES ELSE 0 END) AS Dec
FROM #Sales
GROUP BY Customer
DROP TABLE #Sales
March 2, 2012 at 12:49 pm
not sure if this is what you are really asking for....maybe something more dynamic perhaps
but this answers your question 🙂
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/12/01',2,20)
INSERT INTO #Sales Values ('2012/01/10',3,10)
SELECT
Customer,
SUM(CASE WHEN MONTH(SalesDate)=11 THEN SALES ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(SalesDate)=12 THEN SALES ELSE 0 END) AS Dec,
SUM(CASE WHEN MONTH(SalesDate)=1 THEN SALES ELSE 0 END) AS Jan
FROM #Sales
GROUP BY Customer
DROP TABLE #Sales
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 2, 2012 at 12:52 pm
I just gave an example. The months might be changing. So i was looking any dynamically arranging it.
March 2, 2012 at 1:04 pm
Shree-903371 (3/2/2012)
I just gave an example. The months might be changing. So i was looking any dynamically arranging it.
Hehe...thought so, sorry.
anyways....please read this post/thread....http://qa.sqlservercentral.com/Forums/FindPost1223418.aspx by Jeff Moden
will give you some great ideas.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 2, 2012 at 1:08 pm
The problem is we are not allowed to use open queries in out campany.
March 2, 2012 at 2:48 pm
Shree-903371 (3/2/2012)
The problem is we are not allowed to use open queries in out campany.
What do you mean by "open queries"??? Do you mean "dynamic SQL"? If so, then you're pretty much stuck because I don't know of a way to achieve your request without the use of dynamic SQL.
--Jeff Moden
March 3, 2012 at 9:11 am
Shree-903371 (3/2/2012)
The problem is we are not allowed to use open queries in out campany.
does the following give any more ideas?
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sales]') AND type in (N'U'))
DROP TABLE [dbo].[Sales]
GO
CREATE TABLE Sales (SalesDate datetime,Customer int,SalesQTY decimal(12,2))
INSERT INTO Sales Values ('2012/02/01',1,10)
INSERT INTO Sales Values ('2012/01/01',2,20)
INSERT INTO Sales Values ('2011/12/10',3,50)
INSERT INTO Sales Values ('2012/02/11',1,100)
INSERT INTO Sales Values ('2012/01/11',6,20)
INSERT INTO Sales Values ('2011/12/15',3,20)
--=== the following "assumes" that we are going to find the earliest month for which we have data
-- and then builds the next months in date order
DECLARE @month1 datetime
DECLARE @month2 datetime
DECLARE @month3 datetime
SET @month1 = (select dateadd(mm, datediff(mm, 0, (SELECT (MIN(SalesDate)) FROM Sales)), 0))
SET @month2 = (select dateadd(mm, datediff(mm, 0, @month1) + 1, 0))
SET @month3 = (select dateadd(mm, datediff(mm, 0, @month2) + 1, 0))
--=== repeat as required
--=== the following is simple SUM/CASE based on the month number...previously defined in variables
-- THIS IS BASED ON MAX NO OF 12 MONTHS DATA ...we are matching on MONTH
SELECT
Customer,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month1) THEN SalesQTY ELSE 0 END) as m1 ,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month2) THEN SalesQTY ELSE 0 END) as m2 ,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month3) THEN SalesQTY ELSE 0 END) as m3
--=== repeat as required ....max 12 mths cos we are matching on MONTH
FROM Sales
GROUP BY Customer
---=== the following can be omitted if descriptive columns are not required
-- inserts into a temp table to allow us to rename the column neaders correctly....are you allowed to use "EXEC sp_rename" ?
DECLARE @m1desc VARCHAR(8)
DECLARE @m2desc VARCHAR(8)
DECLARE @m3desc VARCHAR(8)
SET @m1desc = (SELECT RIGHT(CONVERT(CHAR(11),@month1,106),8))
SET @m2desc = (SELECT RIGHT(CONVERT(CHAR(11),@month2,106),8))
SET @m3desc = (SELECT RIGHT(CONVERT(CHAR(11),@month3,106),8))
SELECT
Customer,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month1) THEN SalesQTY ELSE 0 END) as m1 ,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month2) THEN SalesQTY ELSE 0 END) as m2 ,
SUM(CASE WHEN MONTH(SalesDate)= MONTH(@month3) THEN SalesQTY ELSE 0 END) as m3
INTO #tmpresults
FROM Sales
GROUP BY Customer
EXEC sp_rename '#tmpResults.M1', @m1desc, 'COLUMN'
EXEC sp_rename '#tmpResults.M2', @m2desc, 'COLUMN'
EXEC sp_rename '#tmpResults.M3', @m3desc, 'COLUMN'
SELECT * FROM #tmpresults
DROP TABLE #tmpresults
________________________________________________________________
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 9:38 am
You need to use the PIVOT command. It is bit tough but read up on it and it will work. I have done it
use a query which will get your results in a table month by month
Then Pivot it
To use consecutive cascading Querries you need to understand the WITH comand
if you have look at both these topics you will pick it up
if you need more help post it here and I will answer
March 3, 2012 at 9:49 am
siva 20997 (3/3/2012)
You need to use the PIVOT command. It is bit tough but read up on it and it will work. I have done ituse a query which will get your results in a table month by month
Then Pivot it
To use consecutive cascading Querries you need to understand the WITH comand
if you have look at both these topics you will pick it up
if you need more help post it here and I will answer
That's nice. Since the OP said the company won't allow for dymnamic or "open" SQL, how would you name the columns correctly for the floating window of data they want?
--Jeff Moden
March 3, 2012 at 10:07 am
The reply to the original question will be long and hard to understand once done
hence 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
March 3, 2012 at 10:19 am
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
Hi Siva
would you care to share your code please?
I am always looking out for better ways to improve my exg code....what the OP asked for is a common request for me....I currently use dynamic SQL to deliver such requests, but am keen to understand your method and compare.
many thanks
________________________________________________________________
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 10:59 am
Ok I will do that but even to hash job of it will take some time. So I will do it tomorrow but still the answer would be quite long and might be difficult do it with in here to do it justice. But I will post it first and then we will see
March 3, 2012 at 11:01 am
? 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?
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply