Getting Total Count for each month based on the Start and End Date

Question

Post reply

Getting Total Count for each month based on the Start and End Date

sql1411

Ten Centuries

Points: 1390
More actions
October 16, 2013 at 11:00 am

#284715

I've a simple TSQL query as follows
SELECT COUNT (PaxNum) as TotalPassengersCount
FROM dbo.FlightDetails
WHERE FlightDate BETWEEN '1/1/2013' and '1/31/2013';
Here, I'm getting the TotalPassengercount for the month of Jan 2013.
Now, if I change my end date to '10/15/2013', I want to get the passenger count for each month up to Oct 15.
For eg, my desired result should be
Jan - 2000
Feb - 2500
-
-
Oct (month to date up to 15) - 1000
Instead of changing the date and running it for every month manually, I wanted to know if we can do this in one single query without having to run it manually for each month.
Thanks for your help in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You could add the MONTH and YEAR functions or a CONVERT to group by.

SELECT RIGHT( CONVERT(char(11), FlightDate, 113), 8) Month,

COUNT (PaxNum) as TotalPassengersCount

FROM dbo.FlightDetails

WHERE FlightDate BETWEEN '1/1/2013' and '1/31/2013'

GROUP BY RIGHT( CONVERT(char(11), FlightDate, 113), 8);

PS. You can change the Month format

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Assuming FlightDate is a datetime data type you could use this.

SELECT Month(FlightDate) as Month,

COUNT (PaxNum) as TotalPassengersCount

FROM dbo.FlightDetails

WHERE FlightDate BETWEEN '1/1/2013' and '10/15/2013'

GROUP BY Month(FlightDate);

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Assuming also that both dates will be on the same year and months won't overlap. Otherwise a year column is also necessary.;-)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LutzM SSC Guru Points: 107049 More actions · Answer 4

Just as a side note:

Whenever dealing with a date type in character format, I'd prefer the ISO format 'YYYYMMDD', so '20131015' instead of '10/15/2013'

Just use SET DATEFORMAT DMY in a query window and try Sean's query again....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

LutzM (10/16/2013)
Just as a side note:
Whenever dealing with a date type in character format, I'd prefer the ISO format 'YYYYMMDD', so '20131015' instead of '10/15/2013'

+10000 to that.

I should have reformatted the query and stated the same but apparently I was being lazy this time. 😀

_______________________________________________________________

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/

sql1411 Ten Centuries Points: 1390 More actions · Answer 6

Thanks much to all of you for your help and comments.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

SELECT CONVERT(VARCHAR(10), DATEADD(mm, DATEDIFF(mm, 0, FlightDate), 0), 102) AS FlightMonth,

COUNT(PaxNum) AS TotalPassengersCount

FROM FlightDetails

GROUP BY DATEADD(mm, DATEDIFF(mm, 0, FlightDate), 0)

ORDER BY DATEADD(mm, DATEDIFF(mm, 0, FlightDate), 0)

another idea 🙂

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day