Solved

Need to group results by quarter (not month)

Posted on 2006-11-29
1
421 Views
Last Modified: 2008-02-01
Hi
I have a SQL statement that extracts sales by quarter, problem is I need to use the date field as part of the select query, and since there is an aggregation in the query, my output get grouped by month as opposed to by quarter ie; I get 3 lines for each quarter where  I only want 1 line - any ideas?
thanks
Fergal

Query:

select  'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2) as sales_qtr,
case when geo='EU' then 'EMEA' when geo='AP' then 'APAC' when geo='AM' then 'AMO' end as geo,bgrp_nm, div_dscr, op_id,
SUM(net_blng_amt) from net_gross_sales_new
where op_id = 'BM' and intel_date >='01-01-2006' and geo = 'AP'
group by intel_date, geo,bgrp_nm, div_dscr, op_id
order by sales_qtr

Output:
Q1'06      APAC      Business Client Group      UPSD Division      BM      21720246.9100
Q1'06      APAC      Business Client Group      UPSD Division      BM      13782960.7000
Q1'06      APAC      Business Client Group      UPSD Division      BM      14164670.0600
Q2'06      APAC      Business Client Group      UPSD Division      BM      19134103.3100
Q2'06      APAC      Business Client Group      UPSD Division      BM      23659180.3400
Q2'06      APAC      Business Client Group      UPSD Division      BM      8435456.8200
Q3'06      APAC      Business Client Group      UPSD Division      BM      13244725.7300
Q3'06      APAC      Business Client Group      UPSD Division      BM      5082319.2900
Q3'06      APAC      Business Client Group      UPSD Division      BM      20983776.3700
Q4'06      APAC      Business Client Group      UPSD Division      BM      11132107.9000
Q4'06      APAC      Business Client Group      UPSD Division      BM      11751375.8500

0
Comment
Question by:fjkilken
1 Comment
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
Comment Utility
Hi fjkilken,

Try:

select  'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2) as sales_qtr,
case when geo='EU' then 'EMEA' when geo='AP' then 'APAC' when geo='AM' then 'AMO' end as geo,bgrp_nm, div_dscr, op_id,
SUM(net_blng_amt) from net_gross_sales_new
where op_id = 'BM' and intel_date >='01-01-2006' and geo = 'AP'
group by 'Q' + CAST(datepart(quarter, intel_date)AS VARCHAR) +'''' +  RIGHT(CAST(datepart(year, intel_date) AS VARCHAR),2), geo,bgrp_nm, div_dscr, op_id
order by sales_qtr

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now