CODEWITHSUNDEEP
sqlsql-servercalendarformat
kelelehead
kelelehead

Reputation: 7

How to display data in a calendar format with SQL

Good afternoon I need to print some daily info in a format like a calendar, so I searched in some forums including this one and I get some functions and ways to make a calendar but when I try to add a Subquery this crashes telling me "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

This is my code 

;with monthDates
as
(
    select  DATEADD(month, datediff(month, 0, '2013-09-01'),0) as d
            ,DATEPART(week, DATEADD(month, datediff(month, 0, '2013-10-30'),0)) as w
    union all
    select  DATEADD(day, 1, d)
            ,DATEPART(week, DATEADD(day, 1, d))
    from monthDates
    where d < DATEADD(month, datediff(month, 0, '2013-10-30')+1,-1)
)

select  max(case when datepart(dw, d) = 1 then datepart(d,d) else null end) as [Sunday]
        ,max(case when datepart(dw, d) = 2 then (SELECT Cost FROM Freights WHERE ixMov=16788) else null end) as [Monday]
        ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tuesday]
        ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wednesday]
        ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thursday]
        ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Friday]
        ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Saturday]
from monthDates
group by w

In this case I'm only affecting just Mondays but it show to me that error warning from MSSQL

I'm doing it in a wrong way, maybe I don't need to use some code describe of the example that I took, this data is for a report where I use XtraReports of DevExpress, so I'm wanna make a query that it bring to me the info in that format.

I need the info looks this way....

Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|
NULL  |45    |203    |87       |231     |123   |321     |
321   |54    |302    |78       |132     |321   |123     |
154   |44    |283    |47       |131     |128   |NULL    |

Place NULL where there is no data.

Thank you

Upvotes: 0

Views: 662

Answers (1)

Donuts
Donuts

Reputation: 2241

I agree with Roman (kind of), but I don't think this is a good exercise at all... this is not what SQL should be used for. You should write efficient queries and handle the presentation of your query results in your application layer.

Upvotes: 1

Related Questions