sql server group by different criteria

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

sql server group by different criteria

Author

Topic

anu33sri
Starting Member

1 Post

Posted - 2015-02-02 : 22:24:22

I have a table in the following format

reporting_date interest_payment balance
200401 10 10
200402 20 15
200403 30 20
200404 40 30
200405 50 40
200406 60 50
200407 70 60

i wanted to generate an OUTPUT in the following format :

The output of the query should look like this :

reporting_date interest_payment balance
Q1 -2004 60 10
Q2 -2004 170 30
Q3 -2004 70 60
Q4 -2004 0 0
i.e i wanted to represent data by quarter and year and group by quarter and year for interest_payment column but for balance i need to pick up the value from the first reporting date in that quarter ,so as you can see q1-2004 has 10,15 and 20 but only 10 is accounted as that was the first reporting date in that quarter

I have my query working for interest payment but i am not sure how do i pickup the first reporting value for balance in a quarter

SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment"
FROM (SELECT *,
(reporting_date%100 - 1)/3 as report_quarter,
reporting_date/100 as report_year
FROM employee) a
GROUP by report_year, report_quarter
order by report_year, report_quarter

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 09:51:13

if SQL SERVER 2012 (you didn't post your version):


SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment",
	FIRST_VALUE(balance) 
		over(partition by report_quarter order by reporting_date)
FROM (
	SELECT *,
	(reporting_date%100 - 1)/3 as report_quarter,
	reporting_date/100 as report_year
	FROM employee
) a
GROUP by report_year, report_quarter
order by report_year, report_quarter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources