SQL Grouping Output - Creating Ageing Data

Question

SQL Grouping Output - Creating Ageing Data

sam.cool.just4u

SSC Veteran

Points: 211
More actions
December 29, 2018 at 12:35 am

#373449

Greeting!
Input SQL Table Sample
Output Expected.
Trying to get above output, would appreciate some guidance from the experts out here. looked out few ideas leading no where :unsure:

Structure ex.; instead of 0 it should come the actual value between that no. of days,
SELECT [custSOAReference],0 AS 'r0_30' ,0 AS 'r31_60' ,0 AS 'r61_90' ,0 AS 'r91_120' ,0 AS 'r121_150' , 0 AS 'r151_180' ,0 AS 'r181_210' ,0 AS 'r211_240' ,0 AS 'r241_360' ,0 AS 'r361_AndMore', SUM([custSOAAmountInLocalCurrency]) AS totalAmountReceivable FROM [custSOAInlineData] GROUP BY [custSOAReference]

Thanks in advance,
Sam

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

sam.cool.just4u - Saturday, December 29, 2018 12:35 AM
Greeting!
Input SQL Table Sample
Output Expected.
Trying to get above output, would appreciate some guidance from the experts out here. looked out few ideas leading no where :unsure:

Structure ex.; instead of 0 it should come the actual value between that no. of days,
SELECT [custSOAReference],0 AS 'r0_30' ,0 AS 'r31_60' ,0 AS 'r61_90' ,0 AS 'r91_120' ,0 AS 'r121_150' , 0 AS 'r151_180' ,0 AS 'r181_210' ,0 AS 'r211_240' ,0 AS 'r241_360' ,0 AS 'r361_AndMore', SUM([custSOAAmountInLocalCurrency]) AS totalAmountReceivable FROM [custSOAInlineData] GROUP BY [custSOAReference]

Thanks in advance,
Sam

Can you please post the DDL (create table) script, sample data and the desired output from the sample data!
😎

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 2

Thanks for the response Sir, will do it nxt 30 minute.

Best Regards,
Sam

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 3

As requested have Attached the DDL.
Contains Script for :

1. custAmtReceivable : Output Table, Data gets inserted from the Output into this Table.
2. custSOAInlineData : Input Table, will be used to get the Grouped Data.
3. Script for the Sample Data Insertion of Input Table.

Hope I am clear enough to explain.

Best Regards,
Sam

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 4

Eirikur Eiriksson Sir, By any chance you got time to look at the problem posted?

pietlinden SSC Guru Points: 63428 More actions · Answer 5

What's the big deal? Use DATEDIFF() to figure out how old a bill (or whatever) is, and then use CASE to group into bins.
If you do this in SSRS, you don't even have to use a crosstab, but if you do, Jeff Moden has some really handy examples on how to do it.

If you post your create table and INSERT scripts in this nice handy box here and use the SQL Code tag, we can read it just fine.

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 6

Thanks for the response.
I have already uploaded the DDL & Data Insert Script in Last message above.
Create Table Scripts:
For your ready reference please find below;

Input Table:
CREATE TABLE [dbo].[custSOAInlineData]( [custSOAInlineDataID] [int] IDENTITY(1,1) NOT NULL, [custSOAcompanyCode] [nvarchar](30) NULL, [custSOAReference] [nvarchar](30) NULL, [custSOAInvoiceNo] [nvarchar](30) NULL, [custDocNo] [nvarchar](30) NULL, [custSOAEntryType] [nvarchar](10) NULL, [custSOAInvoiceDate] [date] NULL, [custSOAAmountInLocalCurrency] [decimal](18, 4) NULL, [custSOALocalCurrency] [nvarchar](10) NULL, [custSOAPODetails] [nvarchar](800) NULL, [custSOAAmountInInvoicedCurrency] [decimal](18, 4) NULL, [custSOAInvoicedCurrency] [nvarchar](10) NULL, [custSOAUpdatedBy] [nvarchar](10) NULL, [custSOATimeStamp] [datetime] NULL CONSTRAINT [DF_custSOAInlineData_custSOATimeStamp] DEFAULT (getdate()) ) ON [PRIMARY]

GO

Out Put Table:
CREATE TABLE [dbo].[custAmtReceivable]( [custReference] [varchar](50) NOT NULL, [custCompanyName] [nvarchar](300) NOT NULL, [r0_30] [decimal](18, 0) NULL, [r31_60] [decimal](18, 0) NULL, [r61_90] [decimal](18, 0) NULL, [r91_120] [decimal](18, 0) NULL, [r121_150] [decimal](18, 0) NULL, [r151_180] [decimal](18, 0) NULL, [r181_210] [decimal](18, 0) NULL, [r211_240] [decimal](18, 0) NULL, [r241_360] [decimal](18, 0) NULL, [r361_AndMore] [decimal](18, 0) NULL, [totalAmountReceivable] [decimal](18, 0) NULL, [lastRecordUpdatedOn] [date] NULL, [lastDatabaseUpdatedOn] [date] NULL, [debtCollectorRating] [int] NULL CONSTRAINT [DF_custAmtReceivable_debtCollectorRating] DEFAULT ((0)) ) ON [PRIMARY]

GO
If needed Input table Insert Data also available in attached text file.
Best Regards,

Sam

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

This should get you started
😎

USE TEEST; GO SET NOCOUNT ON;

;WITH BASE_DATA AS ( SELECT CID.custSOAcompanyCode ,CID.custSOAInvoiceDate ,CID.custSOAAmountInLocalCurrency ,SUM(CID.custSOAAmountInLocalCurrency) OVER ( PARTITION BY CID.custSOAcompanyCode ) AS INV_TOTAL -- AGE GROUP BY 30 DAY INTERVAL, 1 = 0-30 ETC. ,CEILING(DATEDIFF(DAY,CID.custSOAInvoiceDate,CONVERT(DATE,GETDATE(),0)) / 30.0) AS AGRP FROM dbo.custSOAInlineData CID ) SELECT BD.custSOAcompanyCode ,SUM(CASE WHEN BD.AGRP = 1 THEN BD.custSOAAmountInLocalCurrency END) AS r0_30 ,SUM(CASE WHEN BD.AGRP = 2 THEN BD.custSOAAmountInLocalCurrency END) AS r31_60 ,SUM(CASE WHEN BD.AGRP = 3 THEN BD.custSOAAmountInLocalCurrency END) AS r61_90 ,SUM(CASE WHEN BD.AGRP = 4 THEN BD.custSOAAmountInLocalCurrency END) AS r91_120 ,SUM(CASE WHEN BD.AGRP = 5 THEN BD.custSOAAmountInLocalCurrency END) AS r121_150 ,SUM(CASE WHEN BD.AGRP = 6 THEN BD.custSOAAmountInLocalCurrency END) AS r151_180 ,SUM(CASE WHEN BD.AGRP = 7 THEN BD.custSOAAmountInLocalCurrency END) AS r181_210 ,SUM(CASE WHEN BD.AGRP = 8 THEN BD.custSOAAmountInLocalCurrency END) AS r211_240 ,SUM(CASE WHEN BD.AGRP BETWEEN 9 AND 11 THEN BD.custSOAAmountInLocalCurrency END) AS r241_360 ,SUM(CASE WHEN BD.AGRP > 11 THEN BD.custSOAAmountInLocalCurrency END) AS r361_AndMore ,BD.INV_TOTAL FROM BASE_DATA BD GROUP BY BD.custSOAcompanyCode ,BD.INV_TOTAL ORDER BY BD.custSOAcompanyCode ASC;

Output
custSOAcompanyCode r0_30 r31_60 r61_90 r91_120 r121_150 r151_180 r181_210 r211_240 r241_360 r361_AndMore INV_TOTAL ------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------- ------------- 0091 878154.2800 517100.2700 427799.9200 217562.4200 206889.7500 101407.3500 113513.7900 109625.6600 320447.1400 275991.9700 3168492.5500 0231 48625.0000 139427.0000 144960.0000 84624.0000 26925.0000 26630.0000 8100.0000 22450.0000 16560.0000 144003.8000 662304.8000 0248 1335.0000 585.0000 595.0000 1735.0000 515.0000 2160.0000 3234.5760 1000.0000 2250.0000 5110.3140 18519.8900

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 8

Thanks a Lot Sir, Will check out, appreciated and greatly helpful.

Best Regards,

Sam

sam.cool.just4u SSC Veteran Points: 211 More actions · Answer 9

Thanks a Lot again Eirikur Eiriksson Sir, did some changes as needed and Its working beautifully 😀
Best Regards,
Sam

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

sam.cool.just4u - Monday, December 31, 2018 2:08 AM
Thanks a Lot again Eirikur Eiriksson Sir, did some changes as needed and Its working beautifully 😀
Best Regards,
Sam

You are very welcome.
😎