October 30, 2016 at 8:52 pm
I'm getting variable (read bad!) results for what should be something simple.
I have a table that has 2 columns that are relevant to this query.
Amount and PaymentDate.
I want to be able to show the sum(amount) and count(amount) by month/year of PaymentDate
Something like:
$2,000 - 25 - 01/2016
$5,000 - 42 - 02/2016
...
Anybody able to shed some light on the required t-sql please?
October 31, 2016 at 12:40 am
Jay@Work (10/30/2016)
I'm getting variable (read bad!) results for what should be something simple.I have a table that has 2 columns that are relevant to this query.
Amount and PaymentDate.
I want to be able to show the sum(amount) and count(amount) by month/year of PaymentDate
Something like:
$2,000 - 25 - 01/2016
$5,000 - 42 - 02/2016
...
Anybody able to shed some light on the required t-sql please?
What you are asking for is a really simple SQL statement.
Can you provide us with some sample data, and the SQL that you are using that returns the bad results.
HINT: COUNT(Amount) may return unexpected results if any of the amount fields are NULL
October 31, 2016 at 1:04 am
I sense you are testing whether I am posting a "homework question"... at 49 years old in 2 weeks, I should be so lucky 😉
OK...
Here is my query
SELECT SUM(Amount) AS 'Total Payments'
, count(amount) as 'Number of Payments'
FROM ConfirmingPayment
GROUP BY Year(PaymentComfirmDate), Month(PaymentComfirmDate), PaymentComfirmDate
Here is a portion of the resultset....
55.0000001
55.0000001
55.0000001
55.0000001
80.0000001
55.0000001
55.0000001
55.0000001
55.0000001
55.0000001
55.0000001
....
Obviously come criteria is being met multiple times.
Here is a sample of the raw data.....
AmountPaymentComfirmDate
55.0000002014-06-30 15:38:33.167
55.0000002014-07-01 10:49:25.203
55.0000002014-07-01 14:42:58.453
55.0000002014-07-01 15:43:18.460
80.0000002014-07-01 16:39:28.877
55.0000002014-07-02 08:14:58.117
55.0000002014-07-02 12:22:51.653
55.0000002014-07-02 12:29:04.043
55.0000002014-07-02 13:11:01.900
55.0000002014-07-02 13:22:48.247
55.0000002014-07-02 13:22:48.420
55.0000002014-07-02 14:32:23.450
80.0000002014-07-02 14:38:19.863
55.0000002014-07-02 16:20:37.723
I forgot to add that I'd also like to group on PaymentType.
So the new desired result set would be (Type, Sum(amount), count(amount), MM/YYYY of PaymentComfirmDate
Apples - $25,000 -25 - 06/20014
Oranges - $42,000 - 42 - 07/2014
October 31, 2016 at 1:46 am
Here is a quick example that should get you passed this hurdle
😎
USE TEEST;
GO
SET NOCOUNT ON;
/* Sample data set */
;WITH SAMPLE_DATA(Amount,PaymentComfirmDate,PaymentType) AS
(
SELECT
CONVERT(NUMERIC(18,2),Amount,0)
,CONVERT(DATETIME,PaymentComfirmDate,121)
,PaymentType
FROM (VALUES (55.000000,'2014-06-30 15:38:33.167',1)
,(55.000000,'2014-07-01 10:49:25.203',1)
,(55.000000,'2014-07-01 14:42:58.453',2)
,(55.000000,'2014-07-01 15:43:18.460',2)
,(80.000000,'2014-07-01 16:39:28.877',1)
,(55.000000,'2014-07-02 08:14:58.117',1)
,(55.000000,'2014-07-02 12:22:51.653',2)
,(55.000000,'2014-07-02 12:29:04.043',2)
,(55.000000,'2014-07-02 13:11:01.900',1)
,(55.000000,'2014-07-02 13:22:48.247',1)
,(55.000000,'2014-07-02 13:22:48.420',2)
,(55.000000,'2014-07-02 14:32:23.450',2)
,(80.000000,'2014-07-02 14:38:19.863',1)
,(55.000000,'2014-07-02 16:20:37.723',1)
,(77.000000,'2014-07-03 12:22:37.043',2)
,( NULL,'2014-07-03 15:20:37.877',2)
)X(Amount,PaymentComfirmDate,PaymentType)
)
SELECT
CONVERT(DATE,SD.PaymentComfirmDate,0) AS PaymentComfirmDate
,SD.PaymentType
,SUM(SD.Amount) AS SUM_Amount
,COUNT(*) AS COUNT_Payment
,COUNT(sd.Amount) AS COUNT_Amount
FROM SAMPLE_DATA SD
GROUP BY CONVERT(DATE,SD.PaymentComfirmDate,0)
,SD.PaymentType;
Output
PaymentComfirmDate PaymentType SUM_Amount COUNT_Payment COUNT_Amount
------------------ ----------- ----------- ------------- ------------
2014-06-30 1 55.00 1 1
2014-07-01 1 135.00 2 2
2014-07-02 1 300.00 5 5
2014-07-01 2 110.00 2 2
2014-07-02 2 220.00 4 4
2014-07-03 2 77.00 2 1
October 31, 2016 at 2:06 am
Eirikur's solution groups by PaymentComfirmDate.
In order to group by Year/Month, you will need to use something like this, which groups by the 1st of every month.
-- ...
MonthYear = DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
-- ...
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
October 31, 2016 at 3:22 am
DesNorton (10/31/2016)
Eirikur's solution groups by PaymentComfirmDate.In order to group by Year/Month, you will need to use something like this, which groups by the 1st of every month.
-- ...
MonthYear = DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
-- ...
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
Alternatively, one can add a YYYYMM number for the grouping
😎
USE TEEST;
GO
SET NOCOUNT ON;
/* Sample data set */
;WITH SAMPLE_DATA(Amount,PaymentComfirmDate,PaymentType) AS
(
SELECT
CONVERT(NUMERIC(18,2),Amount,0)
,CONVERT(DATETIME,PaymentComfirmDate,121)
,PaymentType
FROM (VALUES (55.000000,'2014-06-30 15:38:33.167',1)
,(55.000000,'2014-07-01 10:49:25.203',1)
,(55.000000,'2014-07-01 14:42:58.453',2)
,(55.000000,'2014-07-01 15:43:18.460',2)
,(80.000000,'2014-07-01 16:39:28.877',1)
,(55.000000,'2014-07-02 08:14:58.117',1)
,(55.000000,'2014-07-02 12:22:51.653',2)
,(55.000000,'2014-07-02 12:29:04.043',2)
,(55.000000,'2014-07-02 13:11:01.900',1)
,(55.000000,'2014-07-02 13:22:48.247',1)
,(55.000000,'2014-07-02 13:22:48.420',2)
,(55.000000,'2014-07-02 14:32:23.450',2)
,(80.000000,'2014-07-02 14:38:19.863',1)
,(55.000000,'2014-07-02 16:20:37.723',1)
,(77.000000,'2014-07-03 12:22:37.043',2)
,( NULL,'2014-07-03 15:20:37.877',2)
)X(Amount,PaymentComfirmDate,PaymentType)
)
SELECT
(YEAR(SD.PaymentComfirmDate) * 100) + MONTH(SD.PaymentComfirmDate) AS YYYYMM
,SD.PaymentType
,SUM(SD.Amount) AS SUM_Amount
,COUNT(*) AS COUNT_Payment
,COUNT(sd.Amount) AS COUNT_Amount
FROM SAMPLE_DATA SD
GROUP BY (YEAR(SD.PaymentComfirmDate) * 100) + MONTH(SD.PaymentComfirmDate)
,SD.PaymentType;
Output
YYYYMM PaymentType SUM_Amount COUNT_Payment COUNT_Amount
----------- ----------- ------------ ------------- ------------
201406 1 55.00 1 1
201407 1 435.00 7 7
201407 2 407.00 8 7
October 31, 2016 at 8:57 am
DesNorton (10/31/2016)
Eirikur's solution groups by PaymentComfirmDate.In order to group by Year/Month, you will need to use something like this, which groups by the 1st of every month.
-- ...
MonthYear = DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
-- ...
GROUP BY DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
You don't need the DATEADD in the GROUP BY clause.
-- ...
MonthYear = DATEADD(mm, DATEDIFF(mm, 0, d.PaymentComfirmDate), 0)
-- ...
GROUP BY DATEDIFF(mm, 0, d.PaymentComfirmDate)
This way, the DATEADD is only calculated for each group rather than for each row. The savings will depend on the average number of rows per group.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 31, 2016 at 1:39 pm
Thanks guys, this gives me what I need.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply