create view vendasG aswith Vendas as( select codcad, case when month(datped) = 01 then sum(totped) else 0 end as M01, case when month(datped) = 02 then sum(totped) else 0 end as M02, case when month(datped) = 03 then sum(totped) else 0 end as M03, case when month(datped) = 04 then sum(totped) else 0 end as M04, case when month(datped) = 05 then sum(totped) else 0 end as M05, case when month(datped) = 06 then sum(totped) else 0 end as M06, case when month(datped) = 07 then sum(totped) else 0 end as M07, case when month(datped) = 08 then sum(totped) else 0 end as M08, case when month(datped) = 09 then sum(totped) else 0 end as M09, case when month(datped) = 10 then sum(totped) else 0 end as M10, case when month(datped) = 11 then sum(totped) else 0 end as M11, case when month(datped) = 12 then sum(totped) else 0 end as M12 from arfat a where a.datped between '20200101' and '20201231' and a.flagcan = 0 and a.FLAGENF = 1 group by codcad, datped),vendaT as(select codcad, sum(M01) as jan, sum(M02) as Fev, Sum(M03) as Mar, Sum(M04) as Abr, Sum(M05) as Maio, Sum(M06) as Jun from vendas group by codcad)select a.*, b.nomcad from vendaT ainner join arcad b on (b.codcad=a.codcad)
Today I have the month fix creating for each month a Mxx - As you can see, I have a two dates '20200101' and '20201231' that is one year.
Now, Imagine that I have two dates '20190601' and '20200631'
How could I create a dinamic columns with Month and Year? Is it possible?
thanks
* Microsoft sql 2017Microsoft SQL Server
Last Comment
Khan Consultoria
8/22/2022 - Mon
Jim Horn
Not entirely following what you mean by 'dinamic columns with Month and Year? ', but if you're trying to create custom calendars I have two articles out there that speaks to that with downloadable code: SQL Server Calendar Table and SQL Server Calendar Table: Fiscal Years.
(this might be it) If you're building a feed, and absolutely have to have column names like M##, then ignor this comment.
If you're building a report, a trick I often used when creating PIVOT queries like what ste5an has provided, and name them something like cm (for current month), cm_minus_one, cm_minus_two, etc. Then in your reporting layer you can get all fancy and pass whatever the current month is, and re-label them 'July', 'August', 'September', etc.
That way, both your query and the report doesn't have to change as the months march on.
Imagine please that you have [datep] that includes the time of day e.g. 2020-12-31 09:10:36.123456 If that is true then this following where clause will NOT include that data
Between over DATE is absolutely fine. The real caveat is in the design and semantics of the data model:
A column named date must contain a DATE, no DATETIME or anything different. Only exception it may by a dimension key as lexical sortable INT, but then it should be named somethingDateKey.
Just my 2 cent.
PortletPaul
It is true, if the column is the date data type then between is workable. Not using between ( using >= and < ) is workable for all date/datetime/datetime2/timestamp data types and hence (in my view) preferable in all cases.
Good luck.