May 12, 2014 at 3:19 pm
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
May 12, 2014 at 3:35 pm
Quick solution
😎
Use tempdb;
GO
DECLARE @CUST_PAY TABLE
(
CustNO INT NOT NULL
,InvNO INT NOT NULL
,ChkNO INT NOT NULL
);
INSERT INTO @CUST_PAY(CustNO,InvNO,ChkNO)
VALUES
(1,109,101 )
,(1,110,101 )
,(1,111,102 )
,(3,112,10003 )
,(2,113,799 )
,(2,114,800 )
,(1,115,103 )
,(3,116,10009 )
,(2,117,799 )
,(1,118,103 );
;WITH CUST_NUM_PAY AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY X.CustNO
ORDER BY (SELECT NULL)
) AS CUST_RID
,CustNO
,COUNT(X.ChkNO) OVER
(
PARTITION BY X.CustNO
) AS PAY_COUNT
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY CP.CustNO,CP.ChkNO
ORDER BY (SELECT NULL)
) AS CP_RID
,CP.CustNO
,CP.ChkNO
FROM @CUST_PAY CP
) AS X WHERE X.CP_RID = 1
)
SELECT
CNP.CustNO
,CNP.PAY_COUNT
FROM CUST_NUM_PAY CNP
WHERE CNP.CUST_RID = 1
Results
CustNO PAY_COUNT
----------- -----------
1 3
2 2
3 2
May 12, 2014 at 3:43 pm
SELECT CustNO,count(DISTINCT ChkNo) AS ChkCount
FROM CUST_PAY
GROUP BY CustNO
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 12, 2014 at 3:52 pm
:-DNice MM
😎
May 12, 2014 at 7:22 pm
Thank you MM, I was able to get the counts, but I wanted to update the results table with the counts. This is where I am having the issues.
May 12, 2014 at 9:37 pm
Give this a try.
WITH BaseData as (
SELECT CustNO, count(DISTINCT ChkNo) AS ChkCount
FROM CUST_PAY
GROUP BY CustNO
)
update cust set
CustPayments = bd.ChkCount
from
dbo.CustTab cust
inner join BaseData bd
on (cust.CustNO = bd.CustNO);
May 12, 2014 at 9:38 pm
Eirikur Eiriksson (5/12/2014)
Quick solution😎
Use tempdb;GO
DECLARE @CUST_PAY TABLE
(
CustNO INT NOT NULL
,InvNO INT NOT NULL
,ChkNO INT NOT NULL
);
INSERT INTO @CUST_PAY(CustNO,InvNO,ChkNO)
VALUES
(1,109,101 )
,(1,110,101 )
,(1,111,102 )
,(3,112,10003 )
,(2,113,799 )
,(2,114,800 )
,(1,115,103 )
,(3,116,10009 )
,(2,117,799 )
,(1,118,103 );
;WITH CUST_NUM_PAY AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY X.CustNO
ORDER BY (SELECT NULL)
) AS CUST_RID
,CustNO
,COUNT(X.ChkNO) OVER
(
PARTITION BY X.CustNO
) AS PAY_COUNT
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY CP.CustNO,CP.ChkNO
ORDER BY (SELECT NULL)
) AS CP_RID
,CP.CustNO
,CP.ChkNO
FROM @CUST_PAY CP
) AS X WHERE X.CP_RID = 1
)
SELECT
CNP.CustNO
,CNP.PAY_COUNT
FROM CUST_NUM_PAY CNP
WHERE CNP.CUST_RID = 1
Results
CustNO PAY_COUNT----------- -----------
1 3
2 2
3 2
I really would like to know why every solution I see you post seems to use the windowing functions.
May 12, 2014 at 9:53 pm
Here is the statistics of the Windowing function and the group by select queries.
********** Windowing function *********
(3 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CUST_PAY'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 31 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
********** Windowing function *********
(3 row(s) affected)
Table 'CUST_PAY'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Edit: Added execution plans
May 13, 2014 at 9:08 am
When I ran this code it update ALL the records to the same value.
(These are the real table, field names)
WITH BaseData as
(SELECT vendorno, count(DISTINCT checkno) AS ChkCount
FROM checks GROUP BY vendorno)
update results set PaymentCount=bd.chkcount
from dbo.checks cust
inner join BaseData bd on (cust.vendorno=bd.vendorno)
May 13, 2014 at 9:24 am
Hyabusact (5/13/2014)
When I ran this code it update ALL the records to the same value.(These are the real table, field names)
WITH BaseData as
(SELECT vendorno, count(DISTINCT checkno) AS ChkCount
FROM checks GROUP BY vendorno)
update results set PaymentCount=bd.chkcount
from dbo.checks cust
inner join BaseData bd on (cust.vendorno=bd.vendorno)
You need to take a close look at your code. In the code above you are reading checks in both the BaseData CTE and the UPDATE statement, but in the UPDATE statement you are attempting to update a table or alias name results which is not if used in the update query. Please take a close look at the code I posted (and report below) where the two tables you identified in your original post are simply identified as customer invoices (CUST_PY in my code) and payment records (CUSTTAB in my code).
WITH BaseData as (
SELECT CustNO, count(DISTINCT ChkNo) AS ChkCount
FROM CUST_PAY
GROUP BY CustNO
)
update cust set
CustPayments = bd.ChkCount
from
dbo.CustTab cust
inner join BaseData bd
on (cust.CustNO = bd.CustNO);
May 13, 2014 at 9:47 am
DOH!
Thank you all and thanks Lynn!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply