Payment for Each Month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Payment for Each Month

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2014-03-27 : 15:33:06

Hi

I'm working on a query where I need to verify whether customer made at least 1 payment per month over the last year. In my example, I am using the DatePart function to look at the date of each payment which evaluates to Yes/No for each payment. This kind of works but what I really need is one record for each Borrower Number where all of the Yes/No values are on a single row.


SELECT     
	cnsmr.cnsmr_idntfr_agncy_id AS BorrowerNumber, 
	(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 12 Then 'Yes' Else 'No' END) As DecemberPayment,
	(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 1 Then 'Yes' Else 'No' END) As JanPayment,
	(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 2 Then 'Yes' Else 'No' END) As FebPayment,
	(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 3 Then 'Yes' Else 'No' END) As MarchPayment
FROM         
	cnsmr INNER JOIN
    wrkgrp ON cnsmr.wkgrp_id = wrkgrp.wkgrp_id INNER JOIN
    cnsmr_pymnt_jrnl ON cnsmr.cnsmr_id = cnsmr_pymnt_jrnl.cnsmr_id INNER JOIN
    cnsmr_pymnt_tag ON cnsmr_pymnt_jrnl.cnsmr_pymnt_jrnl_id = cnsmr_pymnt_tag.cnsmr_pymnt_jrnl_id INNER JOIN
    tag ON cnsmr_pymnt_tag.tag_id = tag.tag_id
GROUP BY 
	cnsmr.cnsmr_idntfr_agncy_id,
	cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt

My current results

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-27 : 15:58:33

[code]

DECLARE @start_date datetime
SET @start_date = DATEADD(MONTH, -12, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

SELECT
cnsmr.cnsmr_idntfr_agncy_id AS BorrowerNumber,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 12 Then 'Yes' Else 'No' END) As DecPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 01 Then 'Yes' Else 'No' END) As JanPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 02 Then 'Yes' Else 'No' END) As FebPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 03 Then 'Yes' Else 'No' END) As MarPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 04 Then 'Yes' Else 'No' END) As AprPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 05 Then 'Yes' Else 'No' END) As MayPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 06 Then 'Yes' Else 'No' END) As JunPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 07 Then 'Yes' Else 'No' END) As JulPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 08 Then 'Yes' Else 'No' END) As AugPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 09 Then 'Yes' Else 'No' END) As SepPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 10 Then 'Yes' Else 'No' END) As OctPayment,
MAX(CASE WHEN DatePart(month, cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt) = 11 Then 'Yes' Else 'No' END) As NovPayment
FROM
cnsmr INNER JOIN
wrkgrp ON cnsmr.wkgrp_id = wrkgrp.wkgrp_id INNER JOIN
cnsmr_pymnt_jrnl ON cnsmr.cnsmr_id = cnsmr_pymnt_jrnl.cnsmr_id INNER JOIN
cnsmr_pymnt_tag ON cnsmr_pymnt_jrnl.cnsmr_pymnt_jrnl_id = cnsmr_pymnt_tag.cnsmr_pymnt_jrnl_id INNER JOIN
tag ON cnsmr_pymnt_tag.tag_id = tag.tag_id
WHERE
cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt >= @start_date AND
cnsmr_pymnt_jrnl.cnsmr_pymnt_entrd_dt < DATEADD(YEAR, 1, DATEADD(DAY, 1, @start_date))
GROUP BY
cnsmr.cnsmr_idntfr_agncy_id

[/code]

Edit: Corrected typos.

evanburen
Posting Yak Master

167 Posts

Posted - 2014-03-27 : 16:23:42

Thank you, that's awesome. How do I get this proficient at SQL? I've been practicing a long time and just can't seem to get this good at it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources