May 5, 2011 at 1:06 am
Here is the condition :
To calculate the average days to pay for each customer by taking the (SUM(DINVPDOF – DOCDATE on each invoice))/number of invoices selected.
I have a table "Table1" with below columns:
Docnumbr-Invoice number
Custnmbr-Customer number
DINVPDOF- Dividend paid of date and time
DOCDATE-Some date and time
How to get the avg no of days and place them as one of column in same table
May 5, 2011 at 1:20 am
I know of a method that could actually get what you want, but i need bit help from your side to do that.. I need the table structure (as CREATE TABLE script) , some sample data (as INSERT INTO scripts) and any indexes/defaults defined on the table. This way , i can work on the query directly without having to assume something and do..
May 5, 2011 at 5:43 am
Here you go RM30101 Tables structure:
USE [TWO]
GO
/****** Object: Table [dbo].[RM30101] Script Date: 05/05/2011 17:12:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[RM30101](
[CUSTNMBR] [char](15) NOT NULL,
[CPRCSTNM] [char](15) NOT NULL,
[DOCNUMBR] [char](21) NOT NULL,
[CHEKNMBR] [char](21) NOT NULL,
[BACHNUMB] [char](15) NOT NULL,
[BCHSOURC] [char](15) NOT NULL,
[TRXSORCE] [char](13) NOT NULL,
[RMDTYPAL] [smallint] NOT NULL,
[DUEDATE] [datetime] NOT NULL,
[DOCDATE] [datetime] NOT NULL,
[POSTDATE] [datetime] NOT NULL,
[PSTUSRID] [char](15) NOT NULL,
[GLPOSTDT] [datetime] NOT NULL,
[LSTEDTDT] [datetime] NOT NULL,
[LSTUSRED] [char](15) NOT NULL,
[ORTRXAMT] [numeric](19, 5) NOT NULL,
[CURTRXAM] [numeric](19, 5) NOT NULL,
[SLSAMNT] [numeric](19, 5) NOT NULL,
[COSTAMNT] [numeric](19, 5) NOT NULL,
[FRTAMNT] [numeric](19, 5) NOT NULL,
[MISCAMNT] [numeric](19, 5) NOT NULL,
[TAXAMNT] [numeric](19, 5) NOT NULL,
[COMDLRAM] [numeric](19, 5) NOT NULL,
[CASHAMNT] [numeric](19, 5) NOT NULL,
[DISTKNAM] [numeric](19, 5) NOT NULL,
[DISAVAMT] [numeric](19, 5) NOT NULL,
[DISCRTND] [numeric](19, 5) NOT NULL,
[DISCDATE] [datetime] NOT NULL,
[DSCDLRAM] [numeric](19, 5) NOT NULL,
[DSCPCTAM] [smallint] NOT NULL,
[WROFAMNT] [numeric](19, 5) NOT NULL,
[TRXDSCRN] [char](31) NOT NULL,
[CSPORNBR] [char](21) NOT NULL,
[SLPRSNID] [char](15) NOT NULL,
[SLSTERCD] [char](15) NOT NULL,
[DINVPDOF] [datetime] NOT NULL,
[PPSAMDED] [numeric](19, 5) NOT NULL,
[GSTDSAMT] [numeric](19, 5) NOT NULL,
[DELETE1] [tinyint] NOT NULL,
[TAXSCHID] [char](15) NOT NULL,
[SLSCHDID] [char](15) NOT NULL,
[FRTSCHID] [char](15) NOT NULL,
[MSCSCHID] [char](15) NOT NULL,
[CURNCYID] [char](15) NOT NULL,
[SHIPMTHD] [char](15) NOT NULL,
[PYMTRMID] [char](21) NOT NULL,
[TRDISAMT] [numeric](19, 5) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[VOIDSTTS] [smallint] NOT NULL,
[VOIDDATE] [datetime] NOT NULL,
[BALFWDNM] [char](21) NOT NULL,
[CSHRCTYP] [smallint] NOT NULL,
[Tax_Date] [datetime] NOT NULL,
[APLYWITH] [tinyint] NOT NULL,
[SALEDATE] [datetime] NOT NULL,
[CORRCTN] [tinyint] NOT NULL,
[SIMPLIFD] [tinyint] NOT NULL,
[Electronic] [tinyint] NOT NULL,
[ECTRX] [tinyint] NOT NULL,
[BKTSLSAM] [numeric](19, 5) NOT NULL,
[BackoutTradeDisc] [numeric](19, 5) NOT NULL,
[BKTFRTAM] [numeric](19, 5) NOT NULL,
[BKTMSCAM] [numeric](19, 5) NOT NULL,
[Factoring] [tinyint] NOT NULL,
[DIRECTDEBIT] [tinyint] NOT NULL,
[ADRSCODE] [char](15) NOT NULL,
[EFTFLAG] [tinyint] NOT NULL,
[DEX_ROW_TS] [datetime] NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PKRM30101] PRIMARY KEY NONCLUSTERED
(
[CUSTNMBR] ASC,
[RMDTYPAL] ASC,
[DOCNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[DINVPDOF])=(0) AND datepart(minute,[DINVPDOF])=(0) AND datepart(second,[DINVPDOF])=(0) AND datepart(millisecond,[DINVPDOF])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[DISCDATE])=(0) AND datepart(minute,[DISCDATE])=(0) AND datepart(second,[DISCDATE])=(0) AND datepart(millisecond,[DISCDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[DOCDATE])=(0) AND datepart(minute,[DOCDATE])=(0) AND datepart(second,[DOCDATE])=(0) AND datepart(millisecond,[DOCDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[DUEDATE])=(0) AND datepart(minute,[DUEDATE])=(0) AND datepart(second,[DUEDATE])=(0) AND datepart(millisecond,[DUEDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[GLPOSTDT])=(0) AND datepart(minute,[GLPOSTDT])=(0) AND datepart(second,[GLPOSTDT])=(0) AND datepart(millisecond,[GLPOSTDT])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[LSTEDTDT])=(0) AND datepart(minute,[LSTEDTDT])=(0) AND datepart(second,[LSTEDTDT])=(0) AND datepart(millisecond,[LSTEDTDT])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[POSTDATE])=(0) AND datepart(minute,[POSTDATE])=(0) AND datepart(second,[POSTDATE])=(0) AND datepart(millisecond,[POSTDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[SALEDATE])=(0) AND datepart(minute,[SALEDATE])=(0) AND datepart(second,[SALEDATE])=(0) AND datepart(millisecond,[SALEDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[Tax_Date])=(0) AND datepart(minute,[Tax_Date])=(0) AND datepart(second,[Tax_Date])=(0) AND datepart(millisecond,[Tax_Date])=(0)))
GO
ALTER TABLE [dbo].[RM30101] WITH CHECK ADD CHECK ((datepart(hour,[VOIDDATE])=(0) AND datepart(minute,[VOIDDATE])=(0) AND datepart(second,[VOIDDATE])=(0) AND datepart(millisecond,[VOIDDATE])=(0)))
GO
ALTER TABLE [dbo].[RM30101] ADD CONSTRAINT [DF__RM30101__DEX_ROW__597119F2] DEFAULT (getutcdate()) FOR [DEX_ROW_TS]
GO
Sample data attached.
May 5, 2011 at 7:59 am
something like
select AVG(datediff(d, DINVPDOF, DOCDATE)) from table
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 6, 2011 at 12:40 am
Thanks for response.....i am getting negative values for some and pretty huge year values for some..my query below :
select AVG(DATEDIFF(D,DINVPDOF, DOCDATE)), CUSTNMBR from
(select docnumbr,custnmbr,dinvpdof,curtrxam,
( RANK() over (partition by custnmbr order by docnumbr desc,[dinvpdof] desc) )as rank ,DOCDATE
from RM30101) two
where rank<=6
GROUP BY CUSTNMBR
May 6, 2011 at 1:18 am
Dhruva25 (5/6/2011)
Thanks for response.....i am getting negative values for some and pretty huge year values for some..my query below :select AVG(DATEDIFF(D,DINVPDOF, DOCDATE)), CUSTNMBR from
(select docnumbr,custnmbr,dinvpdof,curtrxam,
( RANK() over (partition by custnmbr order by docnumbr desc,[dinvpdof] desc) )as rank ,DOCDATE
from RM30101) two
where rank<=6
GROUP BY CUSTNMBR
Are you trying to find the six worst payers? If yes, then perform the RANK() (or TOP n) after calculating the average days to pay.
SELECT TOP 6
CUSTNMBR,
AvgPayLag
FROM (
SELECT
CUSTNMBR,
AvgPayLag = AVG(DATEDIFF(d,DINVPDOF, DOCDATE))
FROM RM30101
GROUP BY CUSTNMBR
) d
ORDER BY AvgPayLag DESC
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 6, 2011 at 4:08 am
1.From the RM30101 (RM Transaction History Table) select the 6 most recent invoices (DOCNUMBR) based off of the date that they have been paid off (DINVPDOF) for each customer (CUSTNMBR).
a.If the customer has not made 6 transactions then select however many there are (5, 4, 3, 2, or 1)
b.Perhaps this could be done with a stored procedure that would select all of these records and place them in a table
2.Then calculate the average days to pay for each customer by taking the (SUM(DINVPDOF – DOCDATE on each invoice))/number of invoices selected
a.Perhaps this could be done with a view that is looking at the table populated from the stored procedure
3.Then take that Average Days to Pay that was calculated for each customer and add it to the DOCDATE for all their unpaid invoices in the RM20101 (RM Open Transaction Table) where DINVPDOF=NULL (or 1900-01-01) to give you the Expected Pay Date for each open invoice along with the Amount Due (CURTRXAM).
a.Perhaps this could be stored in another view
4.Then be able to run a SRS report where the user inputs a date parameter and it returns a report showing the total amount due CURTRXAM for all invoices with an expected pay date prior to the date parameter
This is my actual situation. I have half success till now. Becoz results shows uneven years
ANy help would be appreciated: i have the the below query now.
SELECT DATEADD(DAY, AVGPAYDAYS.AVERAGEDAYS, DOCDATE) AS EXPECTEDPAYDAY, INV.DOCDATE, INV.DUEDATE,inv.CUSTNMBR,CURTRXAM,DOCNUMBR FROM RM20101 AS INV
INNER JOIN (
select AVG(DATEDIFF(DAY,DINVPDOF, DOCDATE)) AVERAGEDAYS, CUSTNMBR from
(select docnumbr,custnmbr,dinvpdof,curtrxam,
( RANK() over (partition by custnmbr order by docnumbr desc,[dinvpdof] desc) )as rank ,DOCDATE
from RM30101) two
where rank<=6
GROUP BY CUSTNMBR) AS AVGPAYDAYS
ON INV.CUSTNMBR = AVGPAYDAYS.CUSTNMBR
WHERE (DINVPDOF IS NOT NULL OR DINVPDOF = '1900-01-01') and CURTRXAM > 0
May 6, 2011 at 11:51 am
Dhruva25 (5/6/2011)
1.From the RM30101 (RM Transaction History Table) select the 6 most recent invoices (DOCNUMBR) based off of the date that they have been paid off (DINVPDOF) for each customer (CUSTNMBR).a.If the customer has not made 6 transactions then select however many there are (5, 4, 3, 2, or 1)
b.Perhaps this could be done with a stored procedure that would select all of these records and place them in a table
2.Then calculate the average days to pay for each customer by taking the (SUM(DINVPDOF – DOCDATE on each invoice))/number of invoices selected
a.Perhaps this could be done with a view that is looking at the table populated from the stored procedure
T-SQL for these 2:
; WITH Base_CTE AS
(
SELECT CUSTNMBR , DINVPDOF , DOCDATE
, RN = ROW_NUMBER() OVER (PARTITION BY CustNMBR ORDER BY DINVPDOF DESC)
FROM RM30101
)
SELECT CUSTNMBR
, AVG(DATEDIFF(d,DINVPDOF, DOCDATE)) [Average Days to Pay]
FROM Base_CTE
WHERE RN <= 6
GROUP BY CUSTNMBR
3.Then take that Average Days to Pay that was calculated for each customer and add it to the DOCDATE for all their unpaid invoices in the RM20101 (RM Open Transaction Table) where DINVPDOF=NULL (or 1900-01-01) to give you the Expected Pay Date for each open invoice along with the Amount Due (CURTRXAM).
a.Perhaps this could be stored in another view
4.Then be able to run a SRS report where the user inputs a date parameter and it returns a report showing the total amount due CURTRXAM for all invoices with an expected pay date prior to the date parameter
T-SQL for these ( derived from your code in the above post )
; WITH Base_CTE AS
(
SELECT CUSTNMBR , DINVPDOF , DOCDATE
, RN = ROW_NUMBER() OVER (PARTITION BY CustNMBR ORDER BY DINVPDOF DESC)
FROM RM30101
),
AVGPAYDAYS AS
(
SELECT CUSTNMBR
, AVG(DATEDIFF(d,DINVPDOF, DOCDATE)) [AverageDays]
FROM Base_CTE
WHERE RN <= 6
GROUP BY CUSTNMBR
)
SELECT DATEADD(DAY, AVGPAYDAYS.AVERAGEDAYS, DOCDATE) AS EXPECTEDPAYDAY,
INV.DOCDATE,
INV.DUEDATE,
inv.CUSTNMBR,
CURTRXAM,
DOCNUMBR
FROM RM20101 AS INV
INNER JOIN AVGPAYDAYS
ON INV.CUSTNMBR = AVGPAYDAYS.CUSTNMBR
WHERE
(
DINVPDOF IS NOT NULL OR
DINVPDOF = '1900-01-01') AND
CURTRXAM > 0
)
Tell us if that works
May 6, 2011 at 1:10 pm
@cold coffee:
Thanks for your response.
If you look at the file I have attached you will notice that In some cases the EXPECTEDPAYDAY is actually prior to the DOCDATE. And in other cases it is many, many years after the DOCDATE and DUEDATE.
I dont get it where i am getting things wrong.....:(
May 6, 2011 at 1:13 pm
That's a GIGO problem. (Garbage In, Garbage Out).
You need to go back to the business and get their business rules on this and for what they want. This isn't a simple computation any more, you're going to need to apply some business rules depending on what the report actually needs to show when your data is this haphazard.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply