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
 General SQL Server Forums
 New to SQL Server Programming
 Varience in Sql query(between dates)

Author  Topic 

anuraag205
Yak Posting Veteran

58 Posts
Posted - 2012-08-24 : 03:43:38
Hi all,

I just wanted to find variance of sales and bookings between month and quarterly.

please help :

my query
SELECT
DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
SUM(NegotiatedSellPrice) AS [Forward Sale Price],
COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
tblInvoice
WHERE
((fk_StatusID = 3) OR (fk_StatusID = 5))
AND BOOKINGDATE >= '20070401' AND BOOKINGDATE < '20200201'
AND COLLECTIONDATE >= '20070501' AND COLLECTIONDATE < '20200201'
GROUP BY
DATEPART(MM, BOOKINGDATE),
DATEPART(YYYY, BOOKINGDATE),
DATENAME(MONTH, COLLECTIONDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATEPART(MM, COLLECTIONDATE),
DATEPART(YYYY, COLLECTIONDATE),
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE)
ORDER BY
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YYYY, BOOKINGDATE),
DATEPART(MM, BOOKINGDATE)

my query gives me the forward bookings from -2007- 2020...
How can i add a new coloum to find variance from last month/quarter/year???

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-08-24 : 04:13:55
[code]SELECT DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
SUM(NegotiatedSellPrice) AS [Forward Sale Price],
COUNT(InvoiceNumber) AS [Forward Bookings],
SUM(CASE WHEN BOOKINGDATE >= '20120401' AND BOOKINGDATE < '20120701' THEN NegotiatedSellPrice ELSE 0 END) AS LastQuarter
FROM dbo.tblInvoice
WHERE fk_StatusID IN (3, 5)
AND BOOKINGDATE >= '20070401'
AND BOOKINGDATE < '20200201'
AND COLLECTIONDATE >= '20070501'
AND COLLECTIONDATE < '20200201'
GROUP BY DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATENAME(MONTH, COLLECTIONDATE)
ORDER BY DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE);[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

anuraag205
Yak Posting Veteran

58 Posts
Posted - 2012-08-24 : 05:22:44
Dear SwePeso,Thanks for your reply.
I could not get the variance from this query . I could only get the same value what i have @ fowardsaleprice in Last quarter column.

thanks ... varience what i mean is
=((june2007 Booking value-july2007Booking value)/july2007 Booking value)*100

Hope i made you clear this time.. . thanks for your responce

Thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2012-08-24 : 06:41:43
[code]SELECT DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
SUM(NegotiatedSellPrice) AS [Forward Sale Price],
COUNT(InvoiceNumber) AS [Forward Bookings],
100E * (
SUM(CASE WHEN BOOKINGDATE >= '20120601' AND BOOKINGDATE < '20120701' THEN NegotiatedSellPrice ELSE 0 END)
- SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END)
) / SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END)
FROM dbo.tblInvoice
WHERE fk_StatusID IN (3, 5)
AND BOOKINGDATE >= '20070401'
AND BOOKINGDATE < '20200201'
AND COLLECTIONDATE >= '20070501'
AND COLLECTIONDATE < '20200201'
GROUP BY DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATENAME(MONTH, COLLECTIONDATE)
ORDER BY DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE);[/code]

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

anuraag205
Yak Posting Veteran

58 Posts
Posted - 2012-08-24 : 07:18:32
tHANKS FOR YOUR REPLY ...

I ENCOUNTERED WITH AN ERROR USING THIS QUERY::::



"Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

(1 row(s) affected)"

What should be done....as there is else 0 in the query, i think that is the reason for this error.???


Thanks
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-24 : 07:37:57
If you make the change shown in red to Peso's query, you can get past the divide by zero error. However, you will end up with a few nulls in the result set. Whether that is the correct behavior or not... I know not!
SELECT		DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
		DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
		DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
		DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
		SUM(NegotiatedSellPrice) AS [Forward Sale Price],
		COUNT(InvoiceNumber) AS [Forward Bookings],
		100E * (
			SUM(CASE WHEN BOOKINGDATE >= '20120601' AND BOOKINGDATE < '20120701' THEN NegotiatedSellPrice ELSE 0 END) 
			- SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END)
		) / NULLIF(SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END))
FROM		dbo.tblInvoice
WHERE		fk_StatusID IN (3, 5)
		AND BOOKINGDATE >= '20070401'
		AND BOOKINGDATE < '20200201'
		AND COLLECTIONDATE >= '20070501'
		AND COLLECTIONDATE < '20200201'
GROUP BY	DATENAME(YEAR, BOOKINGDATE),
		DATENAME(MONTH, BOOKINGDATE),
		DATEPART(YEAR, BOOKINGDATE),
		DATEPART(MONTH, BOOKINGDATE),
		DATENAME(YEAR, COLLECTIONDATE),
		DATENAME(MONTH, COLLECTIONDATE)
ORDER BY	DATEPART(YEAR, BOOKINGDATE),
		DATEPART(MONTH, BOOKINGDATE);
Go to Top of Page

anuraag205
Yak Posting Veteran

58 Posts
Posted - 2012-08-24 : 09:22:36
Hi all, I tried different types and could not get the output...this is the Msg error am getting....


Incorrect syntax near the keyword 'FROM'.



SELECT DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
SUM(NegotiatedSellPrice) AS [Forward Sale Price],
COUNT(InvoiceNumber) AS [Forward Bookings],
100E * (
SUM(CASE WHEN BOOKINGDATE >= '20120601' AND BOOKINGDATE < '20120701' THEN NegotiatedSellPrice ELSE 0 END)
- SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END)
) / NULLIF(SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END) )
FROM dbo.tblInvoice
WHERE fk_StatusID IN (3, 5)
AND BOOKINGDATE >= '20070401'
AND BOOKINGDATE < '20200201'
AND COLLECTIONDATE >= '20070501'
AND COLLECTIONDATE < '20200201'
GROUP BY DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATENAME(MONTH, COLLECTIONDATE)
ORDER BY DATEPART(YEAR, BOOKINGDATE),
DATEPART(MONTH, BOOKINGDATE);

Thanks
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-08-24 : 09:31:41
Ugh! Sorry!! - see change in red below.
SELECT		DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
		DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
		DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
		DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
		SUM(NegotiatedSellPrice) AS [Forward Sale Price],
		COUNT(InvoiceNumber) AS [Forward Bookings],
		100E * (
			SUM(CASE WHEN BOOKINGDATE >= '20120601' AND BOOKINGDATE < '20120701' THEN NegotiatedSellPrice ELSE 0 END) 
			- SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END)
		) / NULLIF(SUM(CASE WHEN BOOKINGDATE >= '20120701' AND BOOKINGDATE < '20120801' THEN NegotiatedSellPrice ELSE 0 END),0)
FROM		dbo.tblInvoice
WHERE		fk_StatusID IN (3, 5)
		AND BOOKINGDATE >= '20070401'
		AND BOOKINGDATE < '20200201'
		AND COLLECTIONDATE >= '20070501'
		AND COLLECTIONDATE < '20200201'
GROUP BY	DATENAME(YEAR, BOOKINGDATE),
		DATENAME(MONTH, BOOKINGDATE),
		DATEPART(YEAR, BOOKINGDATE),
		DATEPART(MONTH, BOOKINGDATE),
		DATENAME(YEAR, COLLECTIONDATE),
		DATENAME(MONTH, COLLECTIONDATE)
ORDER BY	DATEPART(YEAR, BOOKINGDATE),
		DATEPART(MONTH, BOOKINGDATE);
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site