Group By Financial Quarter - 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

Group By Financial Quarter

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-04 : 02:14:50

I have a transaction file with Company_code,Gl_code,Amount,Transaction_Date

I want to sum the amount based on a Company_Code,Glcode for every financial quarter(01/04/2007-30/06/2007,01/07/2007-30/09/2007 and so on).

Expected Output

Company_Code,Gl_code,Quarter_1,Amount_1,Quarter_2,Amount_2,Quarter_3,Amount_3,Quarter_4,Amount_4

Regards

Nirene

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-04 : 03:09:08

Is April to June your first quarter?
Don't you need financial year in your output?
What do you want to show under Quarter-1, 2, 3 and 4?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 03:25:57

SELECT DATEDIFF(QUARTER, '19000101', theDateTimeColumnNameHere) AS FirstDateForCurrentQuarter
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-04 : 03:30:31

DECLARE @Y int

SELECT @Y = 2007

SELECT Company_Code, Gl_code, SUM(amt),
IsNull(SUM(CASE WHEN DATEPART(quarter, Transaction_Date) = 2 THEN Amount END), 0),
IsNull(SUM(CASE WHEN DATEPART(quarter, Transaction_Date) = 3 THEN Amount END), 0),
IsNull(SUM(CASE WHEN DATEPART(quarter, Transaction_Date) = 4 THEN Amount END), 0),
IsNull(SUM(CASE WHEN DATEPART(quarter, Transaction_Date) = 1 THEN Amount END), 0)
FROM transactionfile
WHERE DATEADD(day, 0, DATEDIFF(day, 0, Transaction_Date)) BETWEEN DATEADD(month, ((@Y - 1900) * 12) + 3, 0) AND DATEADD(month, ((@Y - 1899) * 12) + 2, 30)
GROUP BY Company_Code, Gl_code
ORDER BY Company_Code, Gl_code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 03:44:38

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-04 : 05:13:40

SQL Server 2000

Nirene

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-12-04 : 05:18:49

Hello all,
This is SP that I have written can this made more simpler.

CREATE PROCEDURE FBT @SDt Varchar(10),@Cocode Varchar(6) AS

Declare @SeQDt Varchar(50),@ThQDt Varchar(50),@FoQDt Varchar(50),@YrEnd Varchar(50)

Select @SeQDt=Dateadd(Month,3,@SDt),@ThQDt=Dateadd(Month,6,@SDt),@FoQDt=Dateadd(Month,9,@SDt),@YrEnd=Dateadd(Month,12,@SDt)

Select Glcode,Description,Round(First_Quarter,2) as Q1,Round(Second_Quarter,2) as Q2,Round(Third_Quarter,2) as Q3,Round(Forth_Quarter,2) as Q4,
Percentage,
Round((First_Quarter*Percentage/100),0) as FiQ,
Round((Second_Quarter*Percentage/100),0) as SeQ,
Round((Third_Quarter*Percentage/100),0) as ThQ,
Round((Forth_Quarter*Percentage/100),0) as FoQ from
(Select a.Glcode,Max(c.Gldesc) as Description,
Sum(Case When Refdt>=@SDt and Refdt<@SeQDt Then Tramt Else 0 End) as First_Quarter,
Sum(Case When Refdt>=@SeQDt and Refdt<@ThQDt Then Tramt Else 0 End) as Second_Quarter,
Sum(Case When Refdt>=@ThQDt and Refdt<@FoQDt Then Tramt Else 0 End) as Third_Quarter,
Sum(Case When Refdt>=@FoQDt and Refdt<@YrEnd Then Tramt Else 0 End) as Forth_Quarter,
Max(b.Percentage) as Percentage
from Trans_Journal a,Fbtgls b,Glmast c
Where (a.Glcode=b.Glcode and a.Cocode=b.Cocode) and (b.Cocode=c.Cocode and b.Glcode=c.Glcode)
and a.Cocode=@Cocode and (a.Cancflg Is Null or a.Cancflg='R' or Len(Rtrim(Ltrim(a.Cancflg)))=0)
Group By a.Cocode,a.Glcode) FBT
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources