Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Total Count for each month based on the Start and End Date Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 11:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
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
Post #1505341
Posted Wednesday, October 16, 2013 11:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,374, Visits: 7,296
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1505349
Posted Wednesday, October 16, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505358
Posted Wednesday, October 16, 2013 12:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,374, Visits: 7,296
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1505359
Posted Wednesday, October 16, 2013 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 7,042, Visits: 12,969
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1505362
Posted Wednesday, October 16, 2013 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:14 PM
Points: 13,126, Visits: 11,964
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505363
Posted Wednesday, October 16, 2013 12:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
Thanks much to all of you for your help and comments.
Post #1505364
Posted Wednesday, October 16, 2013 12:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:08 PM
Points: 1,886, Visits: 18,550

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 !
__________________________________________________________________
Post #1505367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse