YearWise Report Doubt - SQL Server Forums

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

YearWise Report Doubt

Author

Topic

Sidharthgs6
Starting Member

2 Posts

Posted - 2012-08-24 : 06:45:05

Hi,

I have one table name crime with thre column name id, Category and Date

ex:Crime table

id Category Date
---------------------
1 Murder 2012-08-11 18:45:55.780

2 Murder 2010-07-11 17:45:55.780

3 Robery 2012-08-11 18:45:55.780

4 Robery 2010-07-11 17:45:55.780

I want to display

Category 2010 2011 2012
----------------------------------
Murder 1 0 or null 1
Robery 1 0 or null 1

What is the query for this Please help me

Sidharthan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 06:48:02

select category, [2010] = sum(case when year(date) = 2010 then 1 else 0 end),
[2011] = sum(case when year(date) = 2011 then 1 else 0 end),
[2012] = sum(case when year(date) = 2012 then 1 else 0 end)
from tbl
group by category

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-24 : 08:45:19

OK,

if i wanted to calculate the difference between last years and this years? I mean to find variance
formula =((June Murder value-July Murder value)/July Murder value)*100

Please Help

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-24 : 09:10:29

I didn't quite follow the June/July example you indicated. The data that you had in the original post is for different years for July and August. Assuming you want year-over-year, extending Nigel's query, it would be something like this:

SELECT
	category,
	[2010] = SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END),
	[2011] = SUM(CASE WHEN YEAR(date) = 2011 THEN 1 ELSE 0 END),
	[2012] = SUM(CASE WHEN YEAR(date) = 2012 THEN 1 ELSE 0 END),
	(
		SUM(CASE WHEN YEAR(date) = 2011 THEN 1 ELSE 0 END)
		-
		SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END)
	)*100.0E/NULLIF(SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END),0)
FROM
	tbl
GROUP BY
	category

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-24 : 09:17:10

I would be tempted to use a cte

;with cte as
(
select category, [2010] = sum(case when year(date) = 2010 then 1 else 0 end),
[2011] = sum(case when year(date) = 2011 then 1 else 0 end),
[2012] = sum(case when year(date) = 2012 then 1 else 0 end)
from tbl
group by category
)
select category, [2010], [2011], [2012],[2012-2011] = [2012]-[2011]
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-24 : 09:40:52

My intention was to ask you about generating monthly/quarterly/annually reports???

The formula for Variance ={(old value-new value)/(old value)}*100...

i wanted to fit this in my query of total bookings and sales.
Here is my actual Query:

output table for this query looks like::::

Year>>>>month>>>>totalBookings>>>>totalsales
2007>>>>june>>>>>1111>>>>>>>>>>>>>1212121212
2007>>>>july>>>>>2222>>>>>>>>>>>>>2323232323
2007>>>>August>>>3333>>>>>>>>>>>>>1010101010
till 2010 moth wise.... how can i get the varience or comparision for sales/bookings with last year???

SELECT
DATENAME(Year,BookingDate) as Year, DATENAME(Month,BookingDate) as Month,
COUNT(InvoiceNumber) AS [Total Bookings],
Sum(NegotiatedSellPrice) AS [Total Sale Price]

FROM tblInvoice
WHERE (fk_StatusID = 3) AND
(BookingDate BETWEEN
CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 23:59:59', 102))
OR
(fk_StatusID = 5) AND
(BookingDate BETWEEN
CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 23:59:59', 102))
GROUP BY DATENAME(Month,BookingDate) , DATENAME(Year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(MM,BookingDate)

Thanksssssssssss

You r back...i got to see your Blog...only few posts right

I didn't quite follow the June/July example you indicated. The data that you had in the original post is for different years for July and August. Assuming you want year-over-year, extending Nigel's query, it would be something like this:

SELECT
	category,
	[2010] = SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END),
	[2011] = SUM(CASE WHEN YEAR(date) = 2011 THEN 1 ELSE 0 END),
	[2012] = SUM(CASE WHEN YEAR(date) = 2012 THEN 1 ELSE 0 END),
	(
		SUM(CASE WHEN YEAR(date) = 2011 THEN 1 ELSE 0 END)
		-
		SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END)
	)*100.0E/NULLIF(SUM(CASE WHEN YEAR(date) = 2010 THEN 1 ELSE 0 END),0)
FROM
	tbl
GROUP BY
	category

[/quote]

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources