Link to home
Start Free TrialLog in
Avatar of asmyatt
asmyatt

asked on

SQL Query to include column for each day of current month

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

Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Do you actually need the column headings to be the actual date? In other words, what if you had columns 1-31. For months with 31 days, it could show results in every column and for other shorter months it could have NULL or 0 in the 29th to 31st columns, respective of month. In any case, you could accomplish this via a PIVOT. To get the actual date values, you will need to do dynamic PIVOT or handle the crosstab on the reporting side of the fence -- have not tried myself.
Avatar of asmyatt
asmyatt

ASKER

No, column headings are not needed.  The 31 columns is what we need.   Can you do a PIVOT in the query?  We are not using SQL reporting...
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of asmyatt

ASKER

Here is the query - It's erroring out right at the begging of the query:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 67
Incorrect syntax near ')'.
;WITH cte([LoanNumber], [Underwriter], [Underwritten]) AS (
    
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 [LoanNumber],
    mwlInstitution.Fullname AS [Underwriter],
	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 as [Underwritten]
		
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
	 ))
)

SELECT [Underwriter]
     , COALESCE([1], 0) AS "1"
     , COALESCE([2], 0) AS "2"
     , COALESCE([3], 0) AS "3"
     , COALESCE([4], 0) AS "4"
     , COALESCE([5], 0) AS "5"
     , COALESCE([6], 0) AS "6"
     , COALESCE([7], 0) AS "7"
     , COALESCE([8], 0) AS "8"
     , COALESCE([9], 0) AS "9"
     , COALESCE([10], 0) AS "10"
     , COALESCE([11], 0) AS "11"
     , COALESCE([12], 0) AS "12"
     , COALESCE([13], 0) AS "13"
     , COALESCE([14], 0) AS "14"
     , COALESCE([15], 0) AS "15"
     , COALESCE([16], 0) AS "16"
     , COALESCE([17], 0) AS "17"
     , COALESCE([18], 0) AS "18"
     , COALESCE([19], 0) AS "19"
     , COALESCE([20], 0) AS "20"
     , COALESCE([21], 0) AS "21"
     , COALESCE([22], 0) AS "22"
     , COALESCE([23], 0) AS "23"
     , COALESCE([24], 0) AS "24"
     , COALESCE([25], 0) AS "25"
     , COALESCE([26], 0) AS "26"
     , COALESCE([27], 0) AS "27"
     , COALESCE([28], 0) AS "28"
     , COALESCE([29], 0) AS "29"
     , COALESCE([30], 0) AS "30"
     , COALESCE([31], 0) AS "31"
FROM (
    SELECT [LoanNumber], [Underwriter], DAY([Underwritten]) AS Dy 
    FROM cte
) d
PIVOT (
    COUNT([LoanNumber]) 
    FOR Dy 
    IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],
        [12],[13],[14],[15],[16],[17],[18],[19],[20],[21],
        [22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) p
;

Open in new window

Avatar of asmyatt

ASKER

Looks like I need to find another way to pull prior month's data, but the code works.  Thanks!
Glad to hear it helped and that you figured out you should not have the declarations and variable selection within the CTE:

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
            )

;WITH cte([Account], [User], [Date]) AS (
    /* Your original query here. */
)
/* Final selection PIVOT'ing by DAY([Date]). */
...

Anyway, best regards and happy coding,

Kevin