How do i group by month?
-
Hi All,
I need a query that should group by month of one date field. For eg.
I need like this.
I've table which has the fields Part#, Replacement Date, Replace Qty etc
Part# Jan'04 Feb'04 Mar'04
5002 23 12 10
5003 22 10 12
--------------------------------
45 22 22 -This is Sum
thx
Rgds
K.Senthil Kumar
-
Your Question is a bit vague. Please post the source table structure and what the results should look like.
-
SELECT [Part#],
SUM(CASE WHEN MONTH([Replacement Date]) = 1 THEN [Replace Qty] ELSE 0 END) AS [Jan],
SUM(CASE WHEN MONTH([Replacement Date]) = 2 THEN [Replace Qty] ELSE 0 END) AS [Feb],
SUM(CASE WHEN MONTH([Replacement Date]) = 3 THEN [Replace Qty] ELSE 0 END) AS [Mar],
SUM(CASE WHEN MONTH([Replacement Date]) = 4 THEN [Replace Qty] ELSE 0 END) AS [Apr],
SUM(CASE WHEN MONTH([Replacement Date]) = 5 THEN [Replace Qty] ELSE 0 END) AS [May],
SUM(CASE WHEN MONTH([Replacement Date]) = 6 THEN [Replace Qty] ELSE 0 END) AS [Jun],
SUM(CASE WHEN MONTH([Replacement Date]) = 7 THEN [Replace Qty] ELSE 0 END) AS [Jul],
SUM(CASE WHEN MONTH([Replacement Date]) = 8 THEN [Replace Qty] ELSE 0 END) AS [Aug],
SUM(CASE WHEN MONTH([Replacement Date]) = 9 THEN [Replace Qty] ELSE 0 END) AS [Sep],
SUM(CASE WHEN MONTH([Replacement Date]) = 10 THEN [Replace Qty] ELSE 0 END) AS [Oct],
SUM(CASE WHEN MONTH([Replacement Date]) = 11 THEN [Replace Qty] ELSE 0 END) AS [Nov],
SUM(CASE WHEN MONTH([Replacement Date]) = 12 THEN [Replace Qty] ELSE 0 END) AS [Dec]
FROM
WHERE YEAR([Replacement Date]) = 2004
GROUP BY [Part#] WITH ROLLUP
ORDER BY[Part#]
Far away is close at hand in the images of elsewhere.
Anon. -
Ok. My Table Structure is :
Product Code
Part#
Replacement Date
Replacement Qty
I Result should be
Product Code Part# Month(Replacement Date) Qty
CL001 5001 Jan'04 10
CL001 5002 Jan'04 12
CL001 5001 Feb'04 25
CL001 5002 Feb'04 26
Note: In the table the 'Replacement Date' has 'dd/mm/yyyy' format
thx
Senthil
-
In That Case You Are Looking for:
Select
[Product Code], [Part#]
,Month = Convert( Varchar(7), [Replacement Date], 120 )
,Qty = Sum( [Replacement Qty] )
From SourceTable
-
...followed by a group by:
group by [Product Code], [Part#],
Convert( Varchar(7), [Replacement Date], 120 )
JM
-
Actually, there are two functions you can use. select Month(getdate()), and select datename(mm, getdate()).
As far as doing a pivot table...in that each month becomes a column...is a lot more complicated. Here's a sample that may help you:
declare @ColName sysname,
@MonthID int,
@vcMin varchar(25),
@vcmax varchar(25),
@Select varchar(8000),
@Group varchar(8000)
select @ColName = '',
@MonthID = 0,
@Select = ''
While 1 = 1
Begin
select top 1
@ColName = ColName,
@MonthID = MonthID,
@vcMin = minBILLDateID,
@vcmax = maxBILLDateID,
@Select = @Select + ', sum([' + ColName + ']) as [' + ColName + ']'
--@Select = @Select + ', [' + ColName + ']'
From #Month
Where MonthID > @MonthID
order by MonthID
if @@RowCount = 0 Break
exec( 'Alter Table ##Event add [' + @ColName + '] int')
exec( 'Update e
Set [' + @ColName + '] = isnull(Total, 0)
From ##Event e
JOIN
(
Select BillEventID, sum(Total) Total
From Operator.dbo.BillDetail bd (nolock)
Where bd.BillDateID between ''' + @vcMin + ''' and ''' + @vcMax + '''
group by BillEventID
 
e1 on e.BillEventID = e1.BillEventID')exec( 'Update ##Event
Set [' + @ColName + '] = 0 Where [' + @ColName + '] is null')
END
set nocount off
exec('Select Case isBillable When 0 then ''Non Billable-'' When 1 then ''Billable-'' Else '''' END + bt.Name [Bill Type] ,
Case
When e.URL is not null then ''<A HREF="Report.jsp?ReportID=3'' + e.URL + ''">'' + e.Name + ''</A>''
Else e.Name
END as [' + @ColHeader + ']' + @Select + '
From ##Event e (nolock)
JOIN BillType bt (nolock) on e.BillTypeID = bt.BillTypeID
group by Case isBillable When 0 then ''Non Billable-'' When 1 then ''Billable-'' Else '''' END + bt.Name,
Case
When e.URL is not null then ''<A HREF="Report.jsp?ReportID=3'' + e.URL + ''">'' + e.Name + ''</A>''
Else e.Name
END
order by 1, 2
')
cl
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply