Hi ! I'm new in SQL and I need to create a Store Procedure which can count the number of type of invoice and the days in the system (aka less than 30 days , 30-60 90 or more invoice aging ) Like this :

                 less than 30_   30-60 days    60-90 days

NewLoans |            5    |      12          |       4          |

In transit    |            3    |       8           |       2         |

Finish         |            4    |        2          |       7         |    

So far I determined th number of days with a DATEDIFF, and that ok but, how I can count them inside the SP?

Can anyone help?

DECLARE @invoice TABLE
  (invoice_ID INT IDENTITY(1,1),
   invoice_Date DATETIME,
     invoice_TYpe VARCHAR(10))

INSERT INTO @Invoice VALUES('Jan 01, 2008','NewLoans')
INSERT INTO @Invoice VALUES('Jan 01, 2008','In transit')
INSERT INTO @Invoice VALUES('Jan 05, 2008','In transit')
INSERT INTO @Invoice VALUES('Jan 20, 2008','In transit')
INSERT INTO @Invoice VALUES('Feb 01, 2008','In transit')
INSERT INTO @Invoice VALUES('Feb 02, 2008','NewLoans')
INSERT INTO @Invoice VALUES('Mar 01, 2008','Finish ')
INSERT INTO @Invoice VALUES('Mar 20, 2008','Finish ')
INSERT INTO @Invoice VALUES('Mar 25, 2008','In transit')
INSERT INTO @Invoice VALUES('Mar 29, 2008','Finish ')
INSERT INTO @Invoice VALUES('Apr 01, 2008','Finish ')
INSERT INTO @Invoice VALUES('Apr 20, 2008','In transit')
INSERT INTO @Invoice VALUES('Apr 25, 2008','NewLoans')
INSERT INTO @Invoice VALUES('May 19, 2008','NewLoans')
INSERT INTO @Invoice VALUES('May 20, 2008','Finish ')
INSERT INTO @Invoice VALUES('May 24, 2008','NewLoans')
INSERT INTO @Invoice VALUES('May 28, 2008','NewLoans')
INSERT INTO @Invoice VALUES('May 28, 2008','Finish ')
INSERT INTO @Invoice VALUES('May 29, 2008','Finish ')
INSERT INTO @Invoice VALUES('Jun 05, 2008','NewLoans')
INSERT INTO @Invoice VALUES('Jun 20, 2008','In transit')
INSERT INTO @Invoice VALUES('Jul 02, 2008','In transit')

SELECT 
'Invoice_type'=COALESCE(invoice_Type,'TOTAL'),
'less than 30'=
  SUM(CASE WHEN DATEDIFF(DAY,invoice_Date,GETDATE())<30 THEN 1 ELSE 0 END),
'30-60 days'=
  SUM(CASE WHEN DATEDIFF(DAY,invoice_Date,GETDATE())BETWEEN 30 AND 59 THEN 1 ELSE 0 END),    
'60-90 days'=
  SUM(CASE WHEN DATEDIFF(DAY,invoice_Date,GETDATE())BETWEEN 60 AND 89  THEN 1 ELSE 0 END),
'greater than 90 days'=
  SUM(CASE WHEN DATEDIFF(DAY,invoice_Date,GETDATE())>89  THEN 1 ELSE 0 END),
'sum'=COUNT(*)
 FROM @invoice
GROUP BY invoice_Type
WITH ROLLUP

/*
Invoice_type less than 30 30-60 days  60-90 days  greater than 90 days sum
------------ ------------ ----------- ----------- -------------------- -----------
Finish       3            0           3           1                    7
In transit   2            1           1           4                    8
NewLoans     4            1           0           2                    7
TOTAL        9            2           4           7                    22
*/

Hi! I finally finish the stored procedure . The final result is as I expected.

My Many Thanks!!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[p_Rpt_aging]

-- =============================================

-- Author:        <Himilce Jackson Vazquez>

-- Create date: <2008-jun-10>

-- Description:   <Informe de Produccion Mensual - Aging>

-- =============================================

-- Add the Parameters for stored procedure here

as

select  MAX(A.i_status)AS STATUS,C.s_Status_Name AS Estatus ,DATEDIFF(day, d_fecha_entrada, GETDATE()) + 1 AS NumberOfDays INTO #AGINGTBL

from dbo.t_Master_Trans_Member A

INNER JOIN t_Dias_Proceso B ON B.i_status = A.i_status AND A.i_transac_id =B.i_transac_id and B.i_status < 10

inner join t_Transac_Status C ON C.I_STATUS = A.i_Status

group by A.i_status,B.d_fecha_entrada,B.d_fecha_salida,C.s_Status_Name

order by C.s_Status_Name

--    Select Estatus,NumberOfDays

--          from #AGINGTBL

      SELECT

    'Estatus'=COALESCE(Estatus,'TOTAL'),

                        'less than 30'=

                        Sum (CASE WHEN NumberOfDays < 30 THEN 1 ELSE 0 END),

                        '30-60 days'=

                        Sum(CASE WHEN NumberOfDays BETWEEN 30 AND 59 THEN 1 ELSE 0 END),

                        '60-90 days'=

                        Sum(CASE WHEN NumberOfDays BETWEEN 60 AND 89 THEN 1 ELSE 0 END),

                        '90-120 days' =

                        Sum(CASE WHEN NumberOfDays BETWEEN 90 AND 119 THEN 1 ELSE 0 END),

                        'greater than 120 days'=

                        Sum(CASE WHEN NumberOfDays >119 THEN 1 ELSE 0 END),

                        'sum'=COUNT(*)

                        FROM #AGINGTBL

                      GROUP BY Estatus

                WITH ROLLUP

drop table #AGINGTBL