September 27, 2006 at 9:42 pm
I've these following table structure,
tbltransaction
TID | Date_occur
----------------------
1 | 3/21/2006
2 | 3/24 /2006
3 | 3/31/2006
4 | 4/18/2006
5 | 4/21/2006
6 | 4/29/2006
7 | 4/30/2006
8 | 4/30/2006
9 | 5/2/2006
10 | 5/10/2006
11 | 5/12/2006
12 | 5/17/2006
How do i write SQL, to know 'How many transaction happen in Mar, April and May?'. The expected result shown below
Month | NumberOfTransaction
------------------------------
Mar | 3
April | 5
May | 4
Plz...
September 27, 2006 at 11:07 pm
SELECT DATEPART(mm, Date_occur) AS Month, COUNT(*) as NumberOfTransaction
FROM tblTransaction
GROUP BY DATEPART(mm, Date_occur)
ORDER BY DATEPART(mm, Date_occur) ASC
That should do it...
September 29, 2006 at 2:39 am
If you want the months converted from numbers into names, try the following:
select
CASE DatePart(month,Date_occur)
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
ELSE cast(DatePart(month,Date_occur) as varchar)
END as [Month]
, count(*) as [NumberOfTransactions]
from tblTransaction
group by DatePart(month, Date_occur)
having DatePart(month,Date_occur) in (3,4,5) -- Only need this line if you're only interested in those particular months
order by DatePart(month,Date_occur)
September 29, 2006 at 7:14 am
Depending on whether your data will eventually span multiple years you may want to either add DATEPART(year, Date_occur) or add a WHERE condition if it is important to determine which events happened THIS September versus in ALL Septembers, etc.
If you want the month name rather than the month number, you can either use Ian's CASE statement or DATENAME(month, Date_occur).
/* Group by year-month */
SELECT DATEPART(year, Date_occur) AS [Year], DATEPART(month, Date_occur) AS [Month],
COUNT(*) AS [NumberOfTransactions]
FROM tblTransaction
GROUP BY DATEPART(year, Date_occur), DATEPART(month, Date_occur)
ORDER BY DATEPART(year, Date_occur), DATEPART(month, Date_occur)
GO
/* Group by month for the selected year only */
SELECT DATEPART(month, Date_occur) AS [Month],
COUNT(*) AS [NumberOfTransactions]
FROM tblTransaction
WHERE Date_occur >= CONVERT(DATETIME, '2006-01-01')
AND Date_occur < CONVERT(DATETIME, '2007-01-01')
GROUP BY DATEPART(month, Date_occur)
ORDER BY DATEPART(month, Date_occur)
GO
--Andrew
September 29, 2006 at 8:47 am
>>
select CASE DatePart(month,Date_occur)
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
ELSE cast(DatePart(month,Date_occur) as varchar)
END as [Month]
<<
Anything wrong with
select
Left(DateName(month,Date_occur),3) AS [Month]
?
September 29, 2006 at 11:52 am
This should for a selected year
SELECT DATENAME(month, Date_Occur) AS [Month],
COUNT(*) AS [NumberOfTransactions]
FROM
tblTransaction
WHERE
Date_Occur >= CONVERT(DATETIME, '2006-01-01')
AND
Date_Occur < CONVERT(DATETIME, '2007-01-01')
GROUP BY
DATENAME(month, Date_Occur),
DATEPART(month,Date_Occur)
ORDER BY
DATEPART(month,Date_Occur)
Thx
Prasad Bhogadi
www.inforaise.com
October 1, 2006 at 11:01 am
Ian,
Nice job at producing the return that the original poster actually asked for... there is one little problem... you cannot include the ELSE clause that you have or you get this...
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblTransaction.Date_Occur' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Lisa,
Yep... there is something wrong with doing it that way... you would also have to include the formula as the object of both the GROUP BY and the ORDER BY... that would cause the output to be generated in alpha order for the month instead or ordinal order...
select Left(DateName(month,Date_occur),3) AS [Month] , count(*) as [NumberOfTransactions] from tblTransaction group by Left(DateName(month,Date_occur),3) order by Left(DateName(month,Date_occur),3)
Month NumberOfTransactions Apr 5 Mar 3 May 4
(3 row(s) affected)
My rule of thumb is divide-and-conquer... make it work, make it fast, then make it pretty. In other words, do the math part first and then format the results...
I use a derived table to do the simple math (thereby keeping the math/code simple) and format the results of that derived table using a very simple outer select (thereby keeping the formatting/code simple). If you don't want the YEAR in the output, just remove it from the outer select. Makes for a much simpler GROUP BY and ORDER BY and is much easier to mod the output for whatever reason including the construction of cross-tabs and all.
SELECT [Year] = YEAR(d.MonthStart), [Month] = LEFT(DATENAME(mm,d.MonthStart),3), d.NumberOfTransaction FROM (--Derived table "d" does the math, the outer select does the formatting SELECT MonthStart = DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0), NumberOfTransaction = COUNT(*) FROM dbo.tblTransaction GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0) ) d ORDER BY d.MonthStart
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2006 at 11:15 am
Thank you for explaining your reasoning, Jeff. I respectfully disagree.
1. The order will almost always be determined externally to this expression anyway, since unless you can guarantee < 12 months and all in the same calendar year you might as well do your ordering by the real/original date.
2. *My* rule of thumb is "never embed localizable strings into code" <shrug>.
As it happens, I do agree with you about doing the formatting and making things pretty only *after* making it work/getting the SQL results. Since my "final formatting" (as well as final display order) will not be determined by the server in many cases, prettification is not generally the concern or the major responsibility of my sproc. Efficiency, however, is.
>L<
October 1, 2006 at 11:28 am
1. The order will almost always be determined externally to this expression anyway, since unless you can guarantee < 12 months and all in the same calendar year you might as well do your ordering by the real/original date.
Don't need to guarantee that they'll all be in the same calendar year with the way I wrote it... it will work for all months of all years because I am, in fact, doing the ordering by the "real/original" date. Read the code...
2. *My* rule of thumb is "never embed localizable strings into code" <shrug>.
I'm not sure what you mean by "never embed localizable strings into code"... What localizable strings have I embedded?
Also, in case anyone's interested, here's the basis for making this into a proc with a parameter for the year...
DECLARE @pYear INT SET @pYear = 2006
SELECT [Year] = YEAR(d.MonthStart), [Month] = LEFT(DATENAME(mm,d.MonthStart),3), d.NumberOfTransaction FROM (--Derived table "d" does the math, the outer select does the formatting SELECT MonthStart = DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0), NumberOfTransaction = COUNT(*) FROM dbo.tblTransaction WHERE Date_Occur >= DATEADD(yy,@pYear-1900,0) AND Date_Occur < DATEADD(yy,@pYear-1900+1,0) GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0) ) d ORDER BY d.MonthStart
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2006 at 11:51 am
Jeff, I did read your code. I know that you are handling the order by adding the year. I think you missed my point; you shouldn't need to compensate, because you have the original date in the first place.
The localizable strings... you are kidding, right?... were in the code I quoted when I wrote my original message:
>>
select CASE DatePart(month,Date_occur)
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
ELSE cast(DatePart(month,Date_occur) as varchar)
END as [Month]
<<
When I wrote to you, I was explaining why I bothered contributing to the thread and why this matters to me.
Although I do a huge amount of date math, and although code efficiency matters, a simple efficiency issue (for example, an unnecessary CASE statement or even somebody who prefers to use YEAR() and MONTH() functions to order records rather than the date) would not have been enough for me to write.
What prompted me to volunteer a different solution was the code you see above.
Everybody has their rules of thumb and their priorities. Smoothly-localizable code is one of mine. (Rooting out hard-coded strings is a related one.)
As I said before, ORDER is not one of my priorities, since my date-related presentations are mostly going to end up in SSML or XLSX anyway. The sproc can handle calculating columns efficiently, anything beyond that is left to mid-tier ordering (by the component that's going to extrude the SSML). And generally the user is going to be able to re-sort in the end-level interface.
But I have no problem with ORDER being a priority for you -- however you choose to handle it.
>L<
October 1, 2006 at 1:04 pm
Ok, Lisa... you just assumed that I knew you were talking about the "other" code in your rule of thumb and I thought you were talking about the code I submitted which didn't come near a localized string. I agree that localized strings like in the "other" should not be included in code.
I didn't add the year to the mathematical process... it was already there because I handle the data as datetimes (as they should be) instead of just months. Remove the year from the outer select and it will still work correctly provided that you add the where clause (as I did in the last post) to isolate a single year as you would for any of the posted code. Like this...
DECLARE @pYear INT SET @pYear = 2006
SELECT [Month] = LEFT(DATENAME(mm,d.MonthStart),3), d.NumberOfTransaction FROM (--Derived table "d" does the math, the outer select does the formatting SELECT MonthStart = DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0), NumberOfTransaction = COUNT(*) FROM dbo.tblTransaction WHERE Date_Occur >= DATEADD(yy,@pYear-1900,0) AND Date_Occur < DATEADD(yy,@pYear-1900+1,0) GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date_Occur),0) ) d ORDER BY d.MonthStart
So far as the Order By being done somewhere else, you could not correctly sort the 3 character months externally without including an additional column in the output. You'd always end up with "Apr" as the first month on a straight sort. Or, you'd have to do some wierdo conversion back to the month ordinals (could be a built in function but why bother doing it there?) and then sort on those to get it right. You've also made a bad assumption that original poster will be doing any external processing at all. Frequently, especially with ETL, SQL IS the entire app... there is no middle tier to make things pretty in those cases. No... getting the month order right on this problem should be one of the priorites of the SQL code especially since it's so very easy and efficient there.... it is just another simple form of data manipulation and that shouldn't be done in the middle tier.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply