[Solved]Generate and sort Month and year wise

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[Solved]Generate and sort Month and year wise

Author

Topic

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-23 : 07:02:56

Thanks @ sunitabeck

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 07:35:29

Seems like your query should give the data for the other months and years also - if there it is not, I am suspecting that it may be the OR condition in your where clause that is the problem. Can you post some sample data that has data April 2007 and some of the following months?

See if the where clause should be this?

SELECT
	DATENAME(YEAR, AAAA) AS YEAR,
	DATENAME(MONTH, AAAA) AS MONTH,
	SUM(NegotiatedSellPrice) AS [Forward Sale Price],
	COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
	tblInvoice
WHERE
	((fk_StatusID = 3) OR (fk_StatusID = 5))
	AND (
	        AAAA BETWEEN
	        CONVERT(DATETIME, '2007-04-01 00:00:00', 102) AND 
	        CONVERT(DATETIME, '2007-04-30 23:59:59', 102)
	    )
	AND (
	        BBBB BETWEEN 
	        CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND 
	        CONVERT(DATETIME, '2015-01-31 23:59:59', 102)
	    )
	AND (
	        AAAA BETWEEN
	        CONVERT(DATETIME, '2007-04-01 00:00:00', 102) AND 
	        CONVERT(DATETIME, '2007-04-30 23:59:59', 102)
	    )
	AND (
	        BBBB BETWEEN 
	        CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND 
	        CONVERT(DATETIME, '2015-01-31 23:59:59', 102)
	    )
GROUP BY
	DATENAME(MONTH, AAAA),
	DATENAME(YEAR, AAAA),
	DATEPART(MM, AAAA),
	DATEPART(YYYY, AAAA)
ORDER BY
	DATEPART(YYYY, AAAA),
	DATEPART(MM, AAAA)

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-23 : 08:16:54

Thanks ..u made my query simple...

My intention was to get the "forward bookings" of every month.
(forward bookings=bookings happening this month but travelling after this month.) with this query i get an output for the "forward bookings "happened in the month of April 2007.My intention was to get a list for april2010,may2010,june2010.....till the last possible date.

here is a sample data:
invoiceid.... bookingdate.... traveldate.... amount
1.... 01-Apr-10.... 11-May-10.... 10....
2.... 02-Apr-10.... 12-Apr-10.... 30....
3.... 13-Apr-10.... 15-Apr-10.... 50....
4.... 04-May-10.... 04-Sep-10.... 10....
5.... 25-May-10.... 25-May-10.... 20....
6.... 26-May-10.... 26-Aug-10.... 40....
7.... 07-Jun-10.... 07-Jun-10.... 10....
8.... 08-Jun-10.... 08-Oct-10.... 20....
9.... 19-Jun-10.... 19-Sep-10.... 50....
10... 30-Jun-10.... 30-Dec-10.... 60....

Now am only able to get output like this:

YEAR....MONTH....Forward Sale Price....Forwardbookings
2007.... April.... 4966.000.... 11

required output(for sample data)
month...forwardbookings
Apr-10.... 1
May-10.... 2
Jun-10.... 3

Thanks for our reply.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 08:39:36

Trying again :) This has a sample table that I am creating, populating and then dropping when I am done. If this works for you, all you would need to use the SELECT query from here and replace the table name #tblInvoice with tblInvoice. (Don't use the DROP TABLE tblInvoice when you do it for real !! That will kill your table!!)

CREATE TABLE #tblInvoice
(AAAA DATETIME, BBBB DATETIME, NegotiatedSellPrice DECIMAL(19,2), InvoiceNumber INT, fk_StatusID INT);

INSERT INTO #tblInvoice VALUES
( '20070405','20070421',1120.70,1,3), -- booked April for April
( '20070406','20070425',1330.70,2,3), -- booked April for April
( '20070405','20070521',3120.40,3,5), -- booked April for May
( '20070406','20070501',30.50,4,5),   -- booked April for May
( '20070405','20070721',420.43,5,5),  -- booked April for July
( '20070406','20070803',310.51,6,5)   -- booked April for August


SELECT
	DATENAME(YEAR, BBBB) AS YEAR,
	DATENAME(MONTH, BBBB) AS MONTH,
	SUM(NegotiatedSellPrice) AS [Forward Sale Price],
	COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
	#tblInvoice
WHERE
	((fk_StatusID = 3) OR (fk_StatusID = 5))
	AND AAAA >= '20070401' AND AAAA < '20070501'
	AND BBBB >= '20070401' AND BBBB < '20150201'
GROUP BY
	DATEPART(MM, AAAA),
	DATEPART(YYYY, AAAA),
	DATENAME(MONTH, BBBB),
	DATENAME(YEAR, BBBB),
	DATEPART(MM, BBBB),
	DATEPART(YYYY, BBBB)
ORDER BY
	DATEPART(YYYY, BBBB),
	DATEPART(MM, BBBB)
	
DROP TABLE #tblInvoice

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-23 : 09:01:25

Thanks a Ton, I don t drop it...it wont kill my table, it ll kill me as-well. LOL

U r awesome..u got me the Month wise breakup..which I thought of asking you after solving my first one...

I guess i confused you with my question. "

so now how can we do the same for MAY 2007, JUNE 2007 BOOKINGS ALSO.....IN THE SAME SINGLE QUERY. U HAVE SOLVED FOR APRIL 2007 BOOKINGS. HOPE AM CLEAR THIS TIME... :)

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 09:09:32

Glad to hear that it is getting there :) See below:

CREATE TABLE #tblInvoice
(AAAA DATETIME, BBBB DATETIME, NegotiatedSellPrice DECIMAL(19,2), InvoiceNumber INT, fk_StatusID INT);

INSERT INTO #tblInvoice VALUES
( '20070405','20070421',1120.70,1,3), -- booked April for April
( '20070406','20070425',1330.70,2,3), -- booked April for April
( '20070405','20070521',3120.40,3,5), -- booked April for May
( '20070406','20070501',30.50,4,5),   -- booked April for May
( '20070405','20070721',420.43,5,5),  -- booked April for July
( '20070406','20070803',310.51,6,5)   -- booked April for August


INSERT INTO #tblInvoice VALUES
( '20070505','20070921',1120.70,1,3), -- booked April for April
( '20070506','20110425',1330.70,2,3), -- booked April for April
( '20070605','20080521',3120.40,3,5), -- booked April for May
( '20070606','20070801',30.50,4,5),   -- booked April for May
( '20070705','20070721',420.43,5,5),  -- booked April for July
( '20070706','20070803',310.51,6,5)   -- booked April for August


SELECT
	DATENAME(YEAR, AAAA) AS BookingYear,
	DATENAME(MONTH, AAAA) AS BookingMonth,
	DATENAME(YEAR, BBBB) AS ForwardYear,
	DATENAME(MONTH, BBBB) AS ForwardMonth,
	SUM(NegotiatedSellPrice) AS [Forward Sale Price],
	COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
	#tblInvoice
WHERE
	((fk_StatusID = 3) OR (fk_StatusID = 5))
	AND AAAA >= '20070401' AND AAAA < '20150201'
	AND BBBB >= '20070401' AND BBBB < '20150201'
GROUP BY
	DATEPART(MM, AAAA),
	DATEPART(YYYY, AAAA),
	DATENAME(MONTH, BBBB),
	DATENAME(YEAR, BBBB),
	DATEPART(MM, BBBB),
	DATEPART(YYYY, BBBB),
	DATENAME(YEAR, AAAA),
	DATENAME(MONTH, AAAA)
ORDER BY
	DATENAME(YEAR, AAAA),
	DATENAME(MONTH, AAAA),
	DATEPART(YYYY, BBBB),
	DATEPART(MM, BBBB)
	
DROP TABLE #tblInvoice

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-23 : 09:23:27

Jahapahan Tussi Great Ho.....

You made my life simple...

I would have taken a day more to do this...and u made my queries v simple...to understand...i have to repeat

Jahapahan Tussi Great Ho.....

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-23 : 10:30:04

I know enough Hindi to know that that is something good, Thank you.

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-24 : 01:29:26

I am from South of India, not too comfortable wit Hindi. That was my Favorite dialogue from "3 idiots" .. Thanks ..hope i ll trouble further with my queries.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources