September 22, 2010 at 2:46 pm
Hi,
I have the follo specification
Table --
ITEM, MONTH, QTY1, QTY2
A , 1 , 10 , 5
B , 2 , 20 , 15
A , 1 , 10 , 15
B , 2 , 5 , 10
Now, I need to display something like this..
ITEM QTY1 JAN QTY2 JAN QTY1 FEB QTY2 FEB ...........for 12 months.
A 20 (10+10) 20(5+15) 0 0
B 0 0 15 15
NOTE: Braces given for explanation. Actual report does'nt have brackets and the values inside it.i.e., under qty1 jan there is just 20 and 0 for A and B resp..
So, I have done it using case and group by.
Now, I need this columns to be changed dynamically, meaning, the user is given an option of selecting the month. Say, if the user selected FEB 2009 , then the report should have follo columns..
ITEM QTY1FEB2009 QTY2FEB2009 QTY1MAR2009 QTY2MAR2009 QTY1APR2009 QTY2APR2009........till.. QTY1JAN2010 QTY2JAN2010
Also, right now, the column headers just show a static value. Is there any way where we can change the column headers with the month and year value.
Thanks.
September 22, 2010 at 7:43 pm
sugsam
To get a tested answer please post table definition, sample data, and what you have already done, following the methods listed in the article whose link is the first link in my signature block. ("Please help us, help you -before posting a question please read" )
That said, have you considered using pivot ? Here is a good starting point to determine if that will satisfy your requirements
September 27, 2010 at 9:53 am
Thanks for the guidance Ron.
Here is a better one.
create table order_rep (itemno varchar(10), mnth int, year int, ordermade int)
insert into order_rep values('ABC',1,2009, 5)
insert into order_rep values('ABC',2,2009, 6)
insert into order_rep values('ABC',1,2009, 10)
insert into order_rep values('ABC',4,2009, 5)
insert into order_rep values('ABC',5,2009, 7)
insert into order_rep values('ABC',2,2009, 5)
insert into order_rep values('ABC',1,2009, 4)
insert into order_rep values('ABC',6,2009, 15)
insert into order_rep values('ABC',8,2009, 8)
insert into order_rep values('ABC',3,2010, 5)
insert into order_rep values('ABC',5,2010, 7)
insert into order_rep values('ABC',12,2010, 5)
insert into order_rep values('ABC',11,2010, 4)
insert into order_rep values('ABC',1,2010, 7)
insert into order_rep values('ABC',5,2010, 5)
insert into order_rep values('XYZ',2,2009, 6)
insert into order_rep values('XYZ',4,2009, 7)
insert into order_rep values('XYZ',2,2009, 4)
insert into order_rep values('XYZ',3,2009, 5)
insert into order_rep values('XYZ',5,2009, 7)
insert into order_rep values('XYZ',12,2009, 12)
insert into order_rep values('XYZ',11,2009, 4)
insert into order_rep values('XYZ',1,2009, 9)
insert into order_rep values('XYZ',5,2009, 4)
insert into order_rep values('XYZ',3,2009, 5)
insert into order_rep values('XYZ',4,2010, 7)
insert into order_rep values('XYZ',1,2010, 1)
insert into order_rep values('XYZ',4,2010, 4)
insert into order_rep values('XYZ',2,2010, 9)
insert into order_rep values('XYZ',5,2010, 4)
insert into order_rep values('MNO',1,2009, 5)
insert into order_rep values('MNO',3,2009, 6)
insert into order_rep values('MNO',5,2009, 8)
insert into order_rep values('MNO',12,2009, 23)
insert into order_rep values('MNO',9,2009, 45)
insert into order_rep values('MNO',5,2009, 3)
insert into order_rep values('MNO',1,2009, 5)
insert into order_rep values('MNO',4,2009, 3)
insert into order_rep values('MNO',3,2009, 9)
insert into order_rep values('MNO',3,2010, 5)
insert into order_rep values('MNO',5,2010, 7)
insert into order_rep values('MNO',12,2010, 12)
insert into order_rep values('MNO',11,2010, 4)
insert into order_rep values('MNO',1,2010, 9)
insert into order_rep values('MNO',5,2010, 4)
--> Query used in the report
select itemno,
sum(case when (mnth = 1 and year=2009) then ordermade end) as jan,
sum(case when (mnth = 2 and year=2009) then ordermade end) as feb,
sum(case when (mnth = 3 and year=2009) then ordermade end) as mar,
sum(case when (mnth = 4 and year=2009) then ordermade end) as apr,
sum(case when (mnth = 5 and year=2009) then ordermade end) as may,
sum(case when (mnth = 6 and year=2009) then ordermade end) as jun,
sum(case when (mnth = 7 and year=2009) then ordermade end) as jul,
sum(case when (mnth = 8 and year=2009) then ordermade end) as aug,
sum(case when (mnth = 9 and year=2009) then ordermade end) as sep,
sum(case when (mnth = 10 and year=2009) then ordermade end) as oct,
sum(case when (mnth = 11 and year=2009) then ordermade end) as nov,
sum(case when (mnth = 12 and year=2009) then ordermade end) as dec
from order_rep
group by itemno
RESULT: THIS IS MY RESULT
ITEMNOJAN2009FEB2009MAR2009APR2009MAY2009JUN2009JUL2009AUG2009SEP2009OCT2009NOV2009DEC2009
ABC1911NULL5715NULL8NULLNULLNULLNULL
XYZ9101711NULLNULLNULLNULLNULL412
MNO10NULL15311NULLNULLNULL45NULLNULL23
NEEDED RESULT:In the report, the user can select the Month, Year & Interval.
Eg, Feb, 2009, yearly. In this case, it should print the sum of orders from Feb2009 to Jan2010. Other user options may be,
FEB, 2009, Half-Yearly->FEB2009MAR2009APR2009MAY2009JUN2009JUL2009
FEB, 2009, Quarterly-->FEB2009MAR2009APR2009
FEB, 2009, Monthly -->FEB2009
REPORT DATA includes data for the years, 2000-2015. SO IT CAN BE ANY YEAR IN BETWEEN.
September 28, 2010 at 2:15 am
Hello,
your query can be rewritten using PIVOT as
SELECT *
FROM (
SELECT ItemNo
, cast(Year as varchar(4)) + ' ' + CONVERT(varchar(3), dateadd(m, mnth, -1), 107) as MnthName
, OrderMade
FROM Order_rep
) P
PIVOT (
SUM(OrderMade)
FOR MnthName IN ([2009 Apr], [2009 May], [2009 Jun], [2009 Jul], [2009 Aug])
) AS PVT
If the end user can fix start date and interval then you should construct the FOR clause dynamically.
Regards,
Francesc
September 29, 2010 at 3:06 am
It is good practice to use an appropriate type to represent a date, ie DATETIME or DATE (in 2008), rather than split it into it's component parts.
In your case when you want to represent just a month then the column should have a constraint to ensure it has a zero time component and a 1 for the day component.
CREATE TABLE SomeTable(
[TheMonth] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE SomeTable
WITH CHECK
ADD CONSTRAINT
[CK_TheMonth_IsFirstOfMonth]
CHECK
((datepart(day,[TheMonth])=(1) AND
datepart(hour,[TheMonth])=(0) AND
datepart(minute,[TheMonth])=(0) AND
datepart(second,[TheMonth])=(0) AND
datepart(millisecond,[TheMonth])=(0)))
GO
October 19, 2010 at 10:09 am
I have come up with a solution for this scenario. Its working perfectly. Also, it does'nt take too much of time unlike pivot table.
Since, I need the user to select the month and year. I need to generate a dynamic set of columns. So what I did is, instead of one set of casing, I have two set of casing.
1st set - 1st Year
2nd set - 2nd year.
Also, user has the ability to select the duration. Say 3/6/9/12 months.
So, when user selected May 2009 and 12 months.
1st set holds records from May - Dec and 2nd set holds records from Jan - Apr. I've made the rest of the columns as invisible if it holds no records. So now I can see the records from May 2009 - Apr 2010.
Duration calculation is done with a little bit of logical calculations. So everything is working fine.
Thanks for all the suggestions. It definitely contributed in some way or the other. 😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply