Need to get SubTotals - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need to get SubTotals

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2011-03-29 : 15:47:11

I have the Query below and the field [Lag_Bucket]
contains the following data:
0-10
11-15
16-20
21-25
26-30
>30

What I would like to be able to do is sum ([COUNT_INVOICES])
0-20 and 0-25


SELECT 
       [LAG_BUCKET]
      ,[COUNT_INVOICES]
      ,[GRP_INVOICE_COUNT]
      ,[GRP]
      ,[CLOSE_DATE]
      ,CASE
  FROM [AdHoc].[dbo].[LAG_DAYS_2010]

Brian

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-29 : 15:54:08

1. what is the final result you would like to see.
2. What datatype is Lag_Bucket
3. what do you mean by
What I would like to be able to do is sum ([COUNT_INVOICES])
0-20 and 0-25
4. it would help i fyou provided more sample data
5. You need to change your table design. what is that lag_bucket? a date range? if so why not create lag_start_date and lag_end_date. As is you will have major problems

If you don't have the passion to help people, you have no passion

bconner
Starting Member

48 Posts

Posted - 2011-03-29 : 16:13:20

Lag Bucket is a string used to categorize the number of days between date of service and the date the claim was billed. There is a query before this one that calculates the number of days.

This is what I have Right now when I pivot the data in excel

LAG_BUCKET 11-Jan 11-Feb 11-Mar Grand Total
0-10 34% 33% 36% 34%
11-15 24% 24% 23% 24%
16-20 11% 11% 11% 11%
21-25 8% 7% 8% 8%
26-30 7% 8% 6% 7%
>30 15% 17% 15% 16%

What I would like to have is this

LAG_BUCKET 11-Jan 11-Feb 11-Mar Grand Total
0-10 34% 33% 36% 34%
11-15 24% 24% 23% 24%
16-20 11% 11% 11% 11%
21-25 8% 7% 8% 8%
26-30 7% 8% 6% 7%
>30 15% 17% 15% 16%
0-20 69% 68% 70% 69%
0-25 77% 75% 78% 77%

Brian

bconner
Starting Member

48 Posts

Posted - 2011-03-29 : 16:14:59

Basically I am showing the Percentage of Invoice billed within 10 days, 15 days, 20 days and so on

Brian

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-29 : 16:20:37

so you want to use the lag_bucket as your range? you need to redesign your table. trying to use a text field for ranges is possible but not good design. can you redesign your table? table name says

[AdHoc].[dbo].[LAG_DAYS_2010]

So are you going to have one for every year? why not include a year field? dude your design will cause you nightmares and headache to those that wish to help you

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-29 : 16:33:44

your table needs to be designed as follows imho. in fact this might be overkill since you can calculate the lag on the fly


DECLARE @lag_days_2010 TABLE(date_of_service datetime, 
                             date_claim_billed datetime, 
                             lag INT,
                             count_invoices INT,
                             grp_invoice_count INT,
                             grp NVARCHAR(25),
                             close_date DATETIME,
                             intCASE int)
                             
                             
INSERT INTO  @lag_days_2010
        ( date_of_service ,
          date_claim_billed,
          lag,
          count_invoices ,
          grp_invoice_count ,
          grp ,
          close_date,
          intCASE
        )
        
SELECT GETDATE() -12 date_of_service,
       GETDATE() date_claim_billed,
        DATEDIFF(day, GETDATE(), GETDATE()+12 ) lag,
        50,
        56,
        'group 1',
        GETDATE(),
        20
UNION   
SELECT GETDATE() - 24 date_of_service,
       GETDATE() date_claim_billed,
        DATEDIFF(day, GETDATE(), GETDATE()+ 24 ) lag,
        45,
        56,
        'group 2',
        GETDATE(),
        20
UNION 
SELECT GETDATE() - 48 date_of_service,
       GETDATE() date_claim_billed,
        DATEDIFF(day, GETDATE(), GETDATE()+ 48 ) lag,
        33,
        56,
        'group 3',
        GETDATE(),
        20
     
SELECT SUM(count_invoices) AS sumcount_invoices
  FROM @lag_days_2010        
WHERE lag BETWEEN 0 AND 12

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources