We help IT Professionals succeed at work.
Get Started

SQL Query to include column for each day of current month

asmyatt
asmyatt asked
on
388 Views
Last Modified: 2012-05-12
Hi Experts,

The query below returns all loans for the current month sorted by user. Results:

Account    User             Date
111111      John Doe      1/3/2012
122222      John Doe      1/3/2012
133333      John Doe      1/6/2012
144444      Betty Doe      1/3/2012
155555      Betty Doe      1/6/2012
166666      Betty Doe      1/6/2012

How can I get it to have a row for each day in the current month and sum the records on each day?  Needed results:

User     1/1/2012    1/2/2012  1/3/2012    1/4/2012   1/5/2012   1/6/2012
John Doe     0              0                  2               0              0                   1  
Betty Doe     0              0                 1                0              0                   2

Thanks.
DECLARE 
	@StartDate DATETIME, --start
	@EndDate DATETIME, --end
	@Now DATETIME  --now

SELECT 
	@Now = GETDATE() --get now

SELECT 
	@StartDate = StartDate, --start return
	@EndDate = EndDate  --end return
FROM 
	[dbo].[CRG_fnGetReportDates]
		(
		1,	--interval
		'MONTH',	--interval type
		'CUR',	--enddatetype
		@Now	--now
		)

 SELECT 	mwlLoanApp.LoanNumber AS [Record],
    mwlInstitution.Fullname AS [User],
	case when (mwlApprovalStatus10.StatusDateTime <> '1899-12-30 00:00:00.000') then mwlApprovalStatus10.StatusDateTime 
         when (mwlApprovalStatus4.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlApprovalStatus4.StatusDateTime 
         when (mwlApprovalStatus5.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlApprovalStatus5.StatusDateTime
         when (mwlAppStatus9.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlAppStatus9.StatusDateTime
         when (mwlAppStatus7.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlAppStatus7.StatusDateTime
         when (mwlApprovalStatus8.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlApprovalStatus8.StatusDateTime
         when (mwlApprovalStatus6.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlApprovalStatus6.StatusDateTime    
         when (mwlAppStatus3.StatusDateTime <> '1899-12-30 00:00:00.000') THEN mwlAppStatus3.StatusDateTime end
		
FROM
	mwlLoanApp (NOLOCK)
	LEFT JOIN mwlAppStatus AS mwlAppStatus3 (NOLOCK) ON (mwlAppStatus3.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus3.StatusDateTime Is Null OR (mwlAppStatus3.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus3.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Pending USDA Commitment'))))))
	LEFT JOIN mwlApprovalStatus AS mwlApprovalStatus4 (NOLOCK) ON (mwlApprovalStatus4.LoanApp_ID=mwlLoanApp.ID AND (mwlApprovalStatus4.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlApprovalStatus AS Stat2 WHERE (Stat2.LoanApp_ID=mwlApprovalStatus4.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Approved')))))
	LEFT JOIN mwlApprovalStatus AS mwlApprovalStatus5 (NOLOCK) ON (mwlApprovalStatus5.LoanApp_ID=mwlLoanApp.ID AND (mwlApprovalStatus5.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlApprovalStatus AS Stat2 WHERE (Stat2.LoanApp_ID=mwlApprovalStatus5.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Approved with PTC(s)')))))
	LEFT JOIN mwlApprovalStatus AS mwlApprovalStatus6 (NOLOCK) ON (mwlApprovalStatus6.LoanApp_ID=mwlLoanApp.ID AND (mwlApprovalStatus6.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlApprovalStatus AS Stat2 WHERE (Stat2.LoanApp_ID=mwlApprovalStatus6.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Loan Committe Approved')))))
	LEFT JOIN mwlAppStatus AS mwlAppStatus7 (NOLOCK) ON (mwlAppStatus7.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus7.StatusDateTime Is Null OR (mwlAppStatus7.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus7.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Underwriter Suspended Loan'))))))
	LEFT JOIN mwlApprovalStatus AS mwlApprovalStatus8 (NOLOCK) ON (mwlApprovalStatus8.LoanApp_ID=mwlLoanApp.ID AND (mwlApprovalStatus8.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlApprovalStatus AS Stat2 WHERE (Stat2.LoanApp_ID=mwlApprovalStatus8.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Limited Approved')))))
	LEFT JOIN mwlAppStatus AS mwlAppStatus9 (NOLOCK) ON (mwlAppStatus9.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus9.StatusDateTime Is Null OR (mwlAppStatus9.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus9.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Adverse Pending'))))))
	LEFT JOIN mwlApprovalStatus AS mwlApprovalStatus10 (NOLOCK) ON (mwlApprovalStatus10.LoanApp_ID=mwlLoanApp.ID AND (mwlApprovalStatus10.StatusDateTime=(SELECT  Max(Stat2.StatusDateTime) FROM mwlApprovalStatus AS Stat2 WHERE (Stat2.LoanApp_ID=mwlApprovalStatus10.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Approved Twice')))))
	LEFT JOIN mwlInstitution (NOLOCK) ON (mwlLoanApp.ID=mwlInstitution.ObjOwner_ID AND mwlInstitution.InstitutionType='UNDERWR' AND mwlInstitution.ObjOwnerName='CONTACTS')

WHERE   mwlInstitution.Fullname is not null and (
		 (		
		 mwlAppStatus3.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlApprovalStatus4.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlApprovalStatus5.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlApprovalStatus6.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlAppStatus7.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlApprovalStatus8.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlAppStatus9.StatusDateTime BETWEEN @StartDate AND @EndDate
		OR
		 mwlApprovalStatus10.StatusDateTime BETWEEN @StartDate AND @EndDate
	 ))

ORDER BY 
	mwlInstitution.[Fullname]

Open in new window

Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE