6.5 need efficient query to group information 1

[jenlion]

I have base tables that look like this:

table tglAcctHist
glacctkey fiscyear fiscper beginningbal  creditamt  debitamt

table tglAcctHistAcctRef
glacctkey acctrefkey fiscyear fiscper creditamt debitamt

In table tglAcctHist, beginningbal is only given for fiscal period one in each fiscal year. For subsequent periods it is calculated based on activity (credits-debits) after that beginning balance in period 1.

in table tglAcctHistAcctRef, I have activity for "sub-accounts" of the main account. If an account in table tglAcctHist had total activty (credits-debits) of, say, 100.00, then the tglAcctHistAcctRef might show that $20 went to reference 2 and $50 went to reference 3. (the remaining $30 was here not assigned a reference code).

Here's what I need to do:
I need to create a report that displays, in one row, the following:

glacctkey  acctrefkey  fiscyear  EndBalPeriod1  EndBalPeriod2  EndBalPeriod3 EndBalPeriod[4...12]

I also need to make sure that every possible account, reference, and fiscalperiod show up, regardless of activity.

My problem is speed. I've created a couple of data views: some with cross joins to get every possible account and fiscal period combo, some to do the "end balance" calculation at the 'base account' level (subtracting activity for that account at reference codes, etc.

The closest I have been able to get to the end goal is a view that gives me this:

glacctkey  acctrefkey  fiscyear  fiscper  endbal

That's great, but I actually need to do a sort of pivot that groups by glacctkey, acctrefkey, fiscyear, and then lists the endbal for each fiscper. Not a sum (though it is actually a crazy sum of credits and debits of previous periods, and I worked through this in my little views that feed into this main view).

I can't find the proper command that will do this. I'm finding some about summarizing and grouping by into a cube, but I don't need to sum anything.

I know it can be done -- Crystal does a Crosstab report relatively quickly that lays it out exactly the right way, except it insists on totalling up the results. Totals don't make any sense here, so the "with cube" option doesn't seem to do it.

I tried joining the view back to itself for each period, but that wound up being too clunky -- takes too long to run.

The current business requirement is to have this output to crystal (and then save it easily to csv). But I prefer to get this exactly as desired in sql so it can eventually be run directly, easily to csv.

The db is 6.5-compat so no ctes, nothing new or fancy. I know there's got TO be a way to do this.... but what is it?

 

[RyanEK]

Hi, can you provide sample data and desired output?

 

[jenlion]

Here is some sample data:

AcctKey  RefKey  Year   Per      EndBal
639	    1	 2002 	1	98116.810
639	    1	 2002 	2	217203.200
639	    1	 2002 	3	345858.440
639	    1	 2002 	4	461707.220
639	    2	 2002 	1	26341.340
639	    2	 2002 	2	49290.230
639	    2	 2002 	3	62367.320
639	    2	 2002 	4	98582.500

Here is desired output:

AcctKey RefKey  Year  Per1      Per2       Per3       Per4   
639       1     2002  98116.81  217203.20  345858.44  461707.22
639       2     2002  26341.34  49290.23   62367.32   98582.50

Thanks!

 

[gmmastros]

Do you know the maximum number of Per's for each refkey? I mean... your sample data shows 4. Is that a constant that you can rely on?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

The max is 12 -- one for each month in the year.

I tried doing a bunch of joining-back-to-itself,but with 12 subqueries, it took a really, really, really long time. There are too many records.

I'm hoping for a speedier cube type of thing? Crystal reports does it quite quickly. Query Analyzer does not. (Stuck in the 6.5 world for this one).

Thanks!

 

[gmmastros]

Try this...

Select AcctKey, RefKey, Year, 
       Min(Case When Per = 1 Then EndBal End) As Per1,
       Min(Case When Per = 2 Then EndBal End) As Per2,
       Min(Case When Per = 3 Then EndBal End) As Per3,
       Min(Case When Per = 4 Then EndBal End) As Per4
From   [!]YourTableName[/!]
Group By AcctKey, RefKey, Year

I know this is a very efficient method that would work well in sql 2000 (and up). I think it will work with sql 6.5.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

Wow! This works quite well!

WHY does it work? Do you know?

It now takes only 3 1/2 minutes to run. I can probably tweak some things to get it to run even faster. We're now into the realm of possibility.

THANK YOU!

 

[gmmastros]

WHY does it work? Do you know?

In your sample data, all the AcctKey's and Year are the same, so I will omit that for the purposes of this discussion.

So, let's start with this data:

[tt][blue]
RefKey Per EndBal
----------- ----------- ---------------------------------------
1 1 98116.810
1 2 217203.200
1 3 345858.440
1 4 461707.220
2 1 26341.340
2 2 49290.230
2 3 62367.320
2 4 98582.500
[/blue][/tt]

If we run this query:

Select RefKey, 
       Case When Per = 1 Then EndBal End As Per1, 
       Case When Per = 2 Then EndBal End As Per2, 
       Case When Per = 3 Then EndBal End As Per3, 
       Case When Per = 4 Then EndBal End As Per4
From   YourTableName

(notice, no group by and no aggregates), we get this:

[tt]
RefKey Per1 Per2 Per3 Per4
------ --------- --------- --------- ---------
1 [blue]98116.810[/blue] [green]NULL[/green] NULL NULL
1 [blue]NULL[/blue] [green]217203.200[/green] NULL NULL
1 [blue]NULL[/blue] [green]NULL[/green] 345858.440 NULL
1 [blue]NULL[/blue] [green]NULL[/green] NULL 461707.220

2 26341.340 NULL NULL NULL
2 NULL 49290.230 NULL NULL
2 NULL NULL 62367.320 NULL
2 NULL NULL NULL 98582.500
[/tt]

Notice how I displayed the data with a blank line separating the RefKey's (where 1 changes to 2). When you group by RefKey, SQL Server essentially does this for you, so that each grouping of rows will be considered separately.

Now, think for a moment, about the MIN function. Min (and other aggregate functions) ignore nulls. So, look at the data again. See where I highlighted the blue data. By taking the min of that data, SQL Server will return the value in the row that is not null. Similarly, for the green highlighted data. SQL Server will consider that data as a group and return the min value that is not null.

The query could have been written using other aggregate functions too (like SUM, MAX, AVG). This works because those functions ignore NULL values during the calculations.

For completeness sake, the query based on this sample data would be:

Select RefKey, 
       Min(Case When Per = 1 Then EndBal End) As Per1, 
       Min(Case When Per = 2 Then EndBal End) As Per2, 
       Min(Case When Per = 3 Then EndBal End) As Per3, 
       Min(Case When Per = 4 Then EndBal End) As Per4
From   YourTableName
Group By RefKey

Does this make sense? Did I explain it well enough?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hwkranger]

I'll give my 2 cents.

George has the right solution (as almost always) -- I work in a world of dynamic columns -- so I can't always hard code my columns.

I think i got this idea originally from TeamSQL about 5 years ago or so. I've made modifications to it for sorting and what not for my purposes.

Should you ever find yourself in this postion you can use the following to pivot your data:

/****** Object:  StoredProcedure [dbo].[uspCrossTab_Get]    Script Date: 11/13/2008 10:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[uspCrossTab_Get] 
(
	@Select varchar(5000), 
    @PivotCol varchar(500),
    @Summaries varchar(500), 
    @GroupBy varchar(500),
    @OtherCols varchar(500) = Null,
	@EndCols varchar(500) = null,
	@OrderByCol varchar(500) = null
)

AS

set nocount on
set ansi_warnings off

declare @Vals varchar(8000);
set @Vals = '';
set @OtherCols= isNull(', ' + @OtherCols,'')

DECLARE @ORDERBY varchar(100)

SET @OrderBy = (Case when @OrderByCol is not null then ' Order by ' + @OrderByCol + ' ASC ' else '' end)

DECLARE @temp table ([myPivot] varchar(100), [Orderby] int)
--create table #temp  ([myPivot] varchar(100), [Orderby] int)
--
insert into @temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as [myPivot], ' + @OrderByCol + ' as OrderBy ' + ' FROM (' + @Select + ') A' +  @OrderBy)

select @Vals = @Vals + ', ' + 
    replace(
       replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + myPivot +  ''' THEN ')
      ,')', ' END) as [' + myPivot +']')
from @temp 
order by CASE WHEN @OrderByCol is null then 1 else 2 end -- , [myPivot]
--drop table #temp


SET @EndCols =  (case when @EndCols is null then null else ', ' + @EndCols end)

exec ( 'select ' + @GroupBy + @OtherCols + @Vals + @EndCols +
       ' from (' + @Select + ') A GROUP BY ' + @GroupBy + @EndCols)

set nocount off

set ansi_warnings on

Example Usage:

-- =====================================================
-- uspSystemComponentTestMode_CrossTab
-- Author: Randall Vollen
-- Create Date: 8/16/2008
-- Description: SystemComponent TestMode CrossTab
--
-- =====================================================
ALTER PROCEDURE [dbo].[uspSystemComponentTestMode_GetCrossTab]
(
 @LANGUAGEID as int,
 @ProposalID as int = null,
 @AgreementID as int = null,
 @CategoryID as int,
 @SolutionYear int

)
AS

IF @AgreementID is not null
	SELECT @ProposalID = AGR_ProposalID from Agreement where AGR_AgreementID = @AgreementID

DECLARE @Select varchar(2000)
SET @Select = '
SELECT TOP 100 PERCENT
 TM_SortOrder,
 ET_EquipmentTemplateCode EquipmentTemplateCode,
 LET_EquipmentTemplateName as EquipmentTemplateName,
 ET_ParentTemplateID ParentTemplateID,
 ET_DefaultColor DefaultColor,
 ET_EquipmentCategoryID EquipmentCategoryID,
 LTM_TestModeID TestModeID,
 /*TM_TestModeID, */
 LTM_Description Description,
 /*Equip_Test_Value */
 NumTestComponent = 
	COALESCE(
			CASE WHEN (SELECT 1 WHERE EXISTS(SELECT * FROM Equipment_Template_Test_Mode WHERE ETTM_TestModeID = TM.TM_TestModeID AND ETTM_EquipmentTemplateCode = ET.ET_EquipmentTemplateCode)) = 1 
			THEN 
			(SELECT SCTM_NumTestComponent 
			FROM System_Component_Test_Mode SCTM1 INNER JOIN System_Component SC1 ON SCTM1.SCTM_SystemComponentID = SC1.SC_SystemComponentID 
			WHERE 
				SCTM_SolutionYear = ' + Convert(varchar(3),@SolutionYear) + '
				AND SCTM_TestModeID = TM.TM_TestModeID 
				AND  SCTM_SystemComponentID = (Select SC_SystemComponentID from System_Component Where SC_ProposalID = ' + Convert(varchar(3), @ProposalID) + '  and SC_EquipmentTemplateCode = ET.ET_EquipmentTemplateCode) 
				AND SC_ProposalID = ' + Convert(varchar(3), @ProposalID) + ')
			ELSE
				-1
			END, 
		 0),
 NumComponents = 
       COALESCE((Select SC_NumComponents FROM dbo.System_Component Where SC_ProposalID = ' + Convert(varchar(3), @ProposalID) + '  and SC_EquipmentTemplateCode = ET.ET_EquipmentTemplateCode), 0),
0.0 as [TotalHours], 
0.0 as [TotalHoursEner],
0.0 as [TotalHoursDeEner]
FROM
 Equipment_Template ET
	INNER JOIN dbo.Language_Equipment_Template LET
		ON ET_EquipmentTemplateCode = LET.LET_EquipmentTemplateCode
				AND
			LET_LANGUAGEID = ' + Convert(varchar(3), @LanguageID) + '
,Test_Mode TM
     INNER JOIN dbo.Language_Test_Mode LTM
		ON TM.TM_TestModeID = LTM.LTM_TestModeID
				AND
			LTM_LANGUAGEID = ' + Convert(varchar(3), @LanguageID) + '
WHERE
 ET_EquipmentCategoryID =  ' + Convert(varchar(3), @CategoryID) + '
 AND
 0 < (SELECT COUNT(*) FROM Equipment_Template ET1 INNER JOIN dbo.Equipment_Template_Test_Mode ETTM1 ON ET_EquipmentTemplateCode = ETTM_EquipmentTemplateCode WHERE ET1.ET_EquipmentCategoryID = ET.ET_EquipmentCategoryID AND ETTM1.ETTM_TestModeID = TM.TM_TestModeID)

 ORDER BY ET_DisplayOrderID
' 

-- Equip_Test_Value test returns -1 if it's not a valid test
--0 < ....  Is for returning only tests (columns) that should show

DECLARE @PivotCol varchar(100)
SET @PivotCol = 'TestModeID'--'LTM_Description'
DECLARE @Summaries varchar(100)
SET @Summaries = 'MAX(NumTestComponent)'
DECLARE @GroupBy varchar(100)
SET @GroupBy = 'EquipmentTemplateName, EquipmentTemplateCode, DefaultColor, NumComponents'
DECLARE @OtherCols varchar(100)
SET @OtherCols = null
DECLARE @EndCols varchar(100)
SET @EndCols = '[TotalHours], [TotalHoursEner], [TotalHoursDeEner]'


EXECUTE [dbo].uspCrossTab_Get 
   @Select = @Select
  ,@PivotCol = @PivotCol
  ,@Summaries = @Summaries
  ,@GroupBy = @GroupBy
  ,@OtherCols = @OtherCols
  ,@EndCols = @EndCols
  ,@OrderByCol = 'TM_SortOrder'

I know the implimentation example is ugly, it was sort of rushed, but the crosstab sp does work and it's pretty solid. The only issue I have with it, is if there are NO columns to pivot on (which happens sometimes) so in those cases, I have to do data checks prior to attempting the pivot.

 

[jenlion]

That is awesome. I had to hesitate to implement something if I didn't know why it worked, but this makes sense.

Thanks!!

 

[gmmastros]

You should hesitate to implement code you don't understand. Please don't hesitate to ask for clarification on code you see here. It's how we all learn.

I'm glad I was able to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom