March 20, 2014 at 7:18 am
I have SQL view table to run my report. We want to count how many donations the donor contributed in the past.
The script is below.
(SELECT COUNT(*) FROM DNR_DONTN_MST IDDM WHERE IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM AND IDDM.DNR_DONTN_ID IS NOT NULL AND ((UPPER(RTRIM(IDDM.ACTL_PROC_CD)) = 'PP' OR (RTRIM(IDDM.ACTL_PROC_CD)) = 'P2' OR (RTRIM(IDDM.ACTL_PROC_CD)) = 'RP' OR (RTRIM(IDDM.ACTL_PROC_CD)) = 'DR'))) AS LTD_PHR
This scripts gave us total donations. However, my manager asked me if ACTL_PROC_CD is RP or DR, I should count it each as two donations.
Could anyone help me in this case? Thank you so much for your advice.
March 20, 2014 at 9:33 am
There are many ways to do this, you could use a CTE and UNION ALL the counts between the different ACTL_PROC_CD codes. Also, assuming your SQL Server isn't case sensitive, you don't need to add UPPER() anywhere, and if your data types are varchar(), you don't need RTRIM() either.
This is clearly not the right table structures, but illustrates using the CTE (which is probably overkill anyway)
DECLARE @DNR_DONTN_MST TABLE (DNR_NUM int, INTNL_DNR_NUM INT, DNR_DONTN_ID int, ACTL_PROC_CD varchar(5))
DECLARE @DM TABLE (INTNL_DNR_NUM int)
INSERT INTO @DM
SELECT 1
INSERT INTO @DNR_DONTN_MST
SELECT 1, 1, 1, 'PP'
U-NION ALL SELECT 2,1, 2,'PP'
U-NION ALL SELECT 3,1, 3,'PP'
U-NION ALL SELECT 4,1, 4,'P2'
U-NION ALL SELECT 5,1, 5,'P2'
U-NION ALL SELECT 6,1, 6,'RP'
U-NION ALL SELECT 7,1, 7,'RP'
U-NION ALL SELECT 8,1, 8,'DR'
U-NION ALL SELECT 9,1, 9,'PP'
;WITH CTE AS (
SELECT COUNT(*) Totals
FROM @DNR_DONTN_MST IDDM
INNER JOIN @DM DM ON IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM
AND IDDM.DNR_DONTN_ID IS NOT NULL
WHERE
( (UPPER(RTRIM(IDDM.ACTL_PROC_CD)) = 'PP'
OR ( RTRIM(IDDM.ACTL_PROC_CD) ) = 'P2'))
UNION ALL
SELECT COUNT(*)*2 Totals
FROM @DNR_DONTN_MST IDDM
INNER JOIN @DM DM ON IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM
AND IDDM.DNR_DONTN_ID IS NOT NULL
WHERE
( (UPPER(RTRIM(IDDM.ACTL_PROC_CD)) = 'RP'
OR ( RTRIM(IDDM.ACTL_PROC_CD) ) = 'DR'))
)
SELECT SUM(Totals) FROM CTE
SELECT * FROM @DNR_DONTN_MST
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 20, 2014 at 10:00 am
You're right Jessie there are simpler and more effective ways to do this.
SELECT SUM( CASE WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('PP', 'P2') THEN 1
WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('RP', 'DR') THEN 2 END)
FROM @DNR_DONTN_MST IDDM
Note that I'm adding the collation because I'm not sure if your collation is case sensitive or not. I also removed the RTRIM because it's not necessary.
IF 'PP' = 'PP '
SELECT 'They''re equal'
March 20, 2014 at 12:12 pm
Thank you all so much. I know this structure is not good. I am new in this field. It is designed by vendor. I just need to modify this view table.
I have another question. When I got this count (LTD_PHR), is there a way I can sum this count results with another count (LTD_WB) as total counts for donation? SUM seems sum column in a table. I needs to plus two different count results into total account.
Thanks in advance.
March 20, 2014 at 12:39 pm
Sure, you can do many things, but we need some help from you. Could you post sample data, and expected results in the form of CREATE TABLE and INSERT statements? You can read how to do it in the article linked in my signature.
March 21, 2014 at 9:27 am
Thank you very much, Louis.
Here is the data table.
Query statement for the table.
SELECT DNR_DONTN_MST.DNR_DONTN_ID, DNR_DONTN_MST.ACTL_PROC_CD
FROM DNR_DONTN_MST INNER JOIN
DNR_MST ON DNR_DONTN_MST.INTNL_DNR_NUM = DNR_MST.INTNL_DNR_NUM
Data table
DNR_DONTN_ID ACTL_PROC_CD
025120WB
025119PP
025011WB
025158RP
025159P2
025009RP
025008DR
025006WB
(SELECT COUNT(*) FROM DNR_DONTN_MST IDDM WHERE IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM AND IDDM.DNR_DONTN_ID IS NOT NULL AND IDDM.ACTL_PROC_CD = 'WB') AS LTD_WB,
(SELECT SUM( CASE WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('PP', 'P2') THEN 1
WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('RP', 'DR') THEN 2 ELSE 0 END)
FROM DNR_DONTN_MST IDDM WHERE IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM AND IDDM.DNR_DONTN_ID IS NOT NULL) AS LTD_PHR,
FROM DNR_DONTN_MST, DNR_MST
WHERE DNR_DONTN_MST.INTNL_DNR_NUM = DNR_MST.INTNL_DNR_NUM
I got LTD_WB count 3 and LTD_PHR count 8. I would like to create another Total count (LTD_WB + LTD_PHR) 11.
What is the best way to do it?
Thanks a lot.
March 21, 2014 at 10:46 am
That's not what I asked for. I need table definitions and sample data in a way that I can copy and paste them in SSMS and use them without any additional formatting.
You posted just a part of your query and there are missing references. Where's DM coming from? Are you grouping by something? Why are you joining your tables?
March 24, 2014 at 11:19 am
Louis,
This is a view table for my crystal report. It has 8 joined tables. It is so hard for me to post data. I figured out that I can add the two counts together with the statement which you have provided me. It works but I do not know if it is correct way. Here is the statement. Thank you so much for your help.
(SELECT
(SELECT COUNT(*) FROM DNR_DONTN_MST IDDM WHERE IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM AND IDDM.DNR_DONTN_ID IS NOT NULL AND UPPER(RTRIM(IDDM.ACTL_PROC_CD)) = 'WB')
+
(SELECT SUM( CASE WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('PP') THEN 1
WHEN IDDM.ACTL_PROC_CD COLLATE Latin1_General_CI_AS IN('RP', 'DR', 'P2') THEN 2
ELSE 0 END)
FROM DNR_DONTN_MST IDDM WHERE IDDM.INTNL_DNR_NUM = DM.INTNL_DNR_NUM AND IDDM.DNR_DONTN_ID IS NOT NULL)) AS DONTN_COUNT
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply