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.
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]
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ')'.
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
;
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_fnGetReportDate s]
(
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
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_fnGetReportDate
(
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