SQL Query to get Last and First Day of Previous and Upcoming Period

----Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) LastDay_PreviousMonth

----Last Day of Current Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) LastDay_CurrentMonth

----Last Day of Next Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) LastDay_NextMonth

--First Day of Previous Month

SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0) FirstDay_PreviousMonth

--First Day of Current Month

SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) FirstDay_CurrentMonth

--First Day of Next Month

SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) FirstDay_NextMonth

--First Day of Prev Week

SELECT DATEADD(DAY,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0)) FirstDay_PreviousWeek

--First Day of Current Week

SELECT DATEADD(DAY,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),0)) FirstDay_CurrentWeek

--First Day of Next Week

SELECT DATEADD(DAY,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()+7),0)) FirstDay_NextWeek

--Last Day of Prev Week

SELECT DATEADD(DAY,5,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0)) LastDay_PreviousWeek

--Last Day of Current Week

SELECT DATEADD(DAY,5,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),0)) LastDay_CurrentWeek

--Last Day of Next Week

SELECT DATEADD(DAY,5,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()+7),0)) LastDay_NextWeek

Sending Mail Alert to respective Supervisor using SQL

The below query helps you to trigger mail to respective supervisors of the empoyees containing office In-time violation details

DECLARE @LastDay_PreviousWeek DATETIME

DECLARE @FirstDay_PreviousWeek DATETIME

SELECT @LastDay_PreviousWeek = DATEADD(DAY,5,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0))

SELECT @FirstDay_PreviousWeek = DATEADD(DAY,-1,DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE())-1,0))

DECLARE @COUNTER INT

DECLARE @Max INT

SET @Counter = 1

DECLARE  @TEMP1 TABLE

(SNO INT, Department Varchar(255), Employee NVARCHAR(255), SupervisorName NVARCHAR(255), SupervisorKey INT, SupervisorMail NVARCHAR(255)

,[Date] DATETIME, ExpectedTime VARCHAR(20), ActualEntryTime NVARCHAR(20), GraceTime NVARCHAR(20), Status1 VARCHAR(50), Status2 VARCHAR(50))

INSERT INTO @Temp1

SELECT  DENSE_RANK() OVER (ORDER BY SupervisorEmail) AS SNO

            ,DDE.DepartmentName

            ,DE.Firstname

            ,DE.SupervisorName

            ,DE.SupervisorKey

            ,DE.SupervisorEMail

            ,DD.[Date]

            ,DE.ExpectedTime

            ,MIN(FH.EntryTime) AS ActualEntryTime

            ,CONVERT(VARCHAR(8),DATEADD(n, 10,  DE.ExpectedTime), 114) AS GraceTime

            ,CASE WHEN DE.ExpectedTime < MIN(FH.EntryTime) THEN 'Violated' ELSE 'Normal' END AS [STATUS1]

            ,CASE WHEN CONVERT(VARCHAR(8),DATEADD(n, 10,  DE.ExpectedTime), 114) <  MIN(FH.EntryTime) THEN 'Violated' ELSE 'Normal' END AS [STATUS2]

FROM [devsql2000\CITBI].[CITGlobal Warehouse].dbo.[DimEmployee] DE

JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.FactHolder FH ON FH.Employeekey = DE.EmployeeKey

JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.DimDepartment DDE ON DDE.DepartmentKey = DE.DepartmentKey

JOIN [devsql2000\CITBI].[CITGlobal Warehouse].dbo.DimDate DD ON DD.DateKey = FH.ActualInDateKey

WHERE DATEPART(Week, GETDATE())-1 = DD.[WeekOfYear]

AND DD.[year] = YEAR(GETDATE()) AND DE.DepartmentKey in (105)

GROUP BY DD.[Date]

            ,DDE.DepartmentName

            ,DE.Firstname

            ,DE.SupervisorName

            ,DE.SupervisorKey

            ,DE.ExpectedTime

            ,SupervisorEMail

SELECT @Max = (SELECT MAX(SNO) FROM @TEMP1)

--SELECT * from @Temp1

WHILE (@COUNTER <= @Max)

    BEGIN

            DECLARE @tableHTML NVARCHAR(MAX)

            DECLARE @LeadMailID NVARCHAR(MAX)

            SELECT @LeadMailID = SupervisorMail FROM @Temp1

            WHERE SNO = @COUNTER

           

            SET @tableHTML =

                  N'<span color=Red style="font-weight:bold;font-size:1.5em;font-family:Calibri;color:Red">

                  In-Time Violation Alert for Last Week </span>' +

                  '<span color=Red style="font-weight:bold;font-size:1em;font-family:Calibri;color:Red"> (From ' + CONVERT(VARCHAR(50), @FirstDay_PreviousWeek,106 ) + ' to ' + CONVERT(VARCHAR(50), @LastDay_PreviousWeek, 106 ) + ')</span>' +

                  N'<table cellspacing="0" cellpadding="5" border="1" style="font-family:Calibri">' +

                  N'<tr bgcolor="#87CEFA" style="font-weight:bold;"><td>SNo</td>' +

                  N'<td>Department</td>' +

                  N'<td>Employee</td>' +

                  N'<td>Date</td>' +

                  N'<td>Expected Time</td>' +

                  N'<td>Actual Time</td>' +

                  N'<td>Time Difference (min)</td></tr>' +

                  CAST ( ( SELECT  td = ROW_NUMBER() OVER (ORDER BY [Department], Employee,[Date]), ''

                                          ,td = [Department], ''

                                          ,td = [Employee], ''

                                          ,td = CONVERT(VARCHAR(10),[Date],101),''

                                          ,td = [ExpectedTime], ''

                                          ,td = [ActualEntryTime], ''

                                          ,td = DATEDiff(n,[ExpectedTime], [ActualEntryTime]), ''

                                FROM      (SELECT  Department

                                                       ,Employee

                                                       ,[Date]

                                                       ,ExpectedTime

                                                       ,ActualEntryTime

                                          FROM @Temp1

                                          WHERE Status2 ='Violated' AND SNO = @Counter) TT

                                          ORDER BY Department

                                                      ,Employee

                                                      ,[Date]

                                FOR XML PATH('tr'), TYPE

                  ) AS NVARCHAR(MAX) ) +

                  N'</table>' ;

                 

            --    print @tableHTML

            EXEC msdb.dbo.sp_send_dbmail

               @subject = 'Employee In-Time Violation Alert for Last Week',

               @profile_name = 'InTimeViolationAlert',

               @recipients = @LeadMailID,

               @copy_recipients = 'mahadevan.v@congruentindia.com',

                 @body = @tableHTML,

                 @body_format = 'HTML' ;

      SET @Counter = @Counter + 1

      END

Use following code to allign values in cell:
          ,"td/@align" = 'right', td =  TotalHours, ''
          ,"td/@align" = 'right', td =  TotalRevenue,''
          ,"td/@align" = 'right', td =  TotalCost,''
          ,"td/@align" = 'right', td =  GrossMargin,''

Use following code to format values in cell:

       ,"td/@align" = 'right', "td/@bgcolor" = CASE WHEN CurrMonth_IdleCost > PrevMonth_IdleCost
           THEN 'RED' END, td = CurrMonth_IdleCost, ''
         ,"td/@align" = 'right', td = PrevWeek_IdleHrs, ''
         ,"td/@align" = 'right', td = PrevWeek_IdleCost, ''
On executing the above stored procs a mail will be sent to the respective supervisors. The format will be as like below:

Named Sets for Time Dimensions

--Time examples

--How to define a default value for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")

--How to create a named set for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
{StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")}

--How to create a named set referring to a separate column in the table used for the time dimension as a member property
{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--Referring to a named set called Current Month
[Current Month].Item(0).Item(0)

--Referring to last 6 months (up to current month)
LastPeriods(6, [Current Month].Item(0).Item(0))

--How to use the named set Current Month for positioning on the quarter level
LastPeriods(6, Ancestor([Current Month].Item(0).Item(0), Time.Quarter))

--Last 6 members on month
{Tail([Time].[Month].members,6)}

--Last 6 members on month where actual is larger then 0
{Tail(Filter({[Time].[Month].members},[Measures].[Actual] > 0),6)}

--All months where actual is larger then 0
{Filter({[Time].[Month].members},[Measures].[Actual] >0)}

--Order months depending on actual
{Order([Time].[Month].members, [Measures].[Actual], BDESC)}

MDX Named Set using Filter and Except functions

--Return members filtered on a custom property. We need to exclude the All level from the set to filter on since it does not have any custom properties.

Filter(Except([Warehouse].Members,{ [Warehouse].[All Warehouse]}), [Warehouse].CurrentMember.Properties("PROPERTYNAME") = "PROPERTYVALUE")


--How to create a named set referring to a separate column in the table used for the time dimension as a member property

{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--To get product names with sales amount greater than 0
([Product].[Product Name].[Product Name] ,NOT ISEMPTY([Measures].[Sales Amt]OR (NOT ISEMPTY([Measures].[Sales Amt] > 0))
FILTER
(
) )
)

MDX query for Top and Bottom Count

Top 10 Campaigns based on Opportunity Count

WITH SET [TopOpportunity] AS {TopCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT [TopOpportunity] ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

Bottom10 Campaigns based on Opportunity Count

WITH SET [BottomOpportunity] AS {BottomCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT BottomOpportunity ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

Setting No. of Rows Per page in SSRS

This can be achieved at the group level. If you do not have group, then add one group but uncheck the 'Include Group Header' and 'Include Group Footer'. Also, check 'Page Break at Start'. 

But when ask to group by what, use:

=Ceiling(RowNumber(Nothing)/20)

SQL Query to display First and Last day of a week

The below query displays the firstday and Lastday of a Current Week:

DECLARE
DECLARE

SELECT @StartDate DATETIME @ENDDATE DATETIME @STARTDATE = (SELECT min(d) AS WeekBeginFROM (SELECT [DATE] AS D FROMDIMDATE WHERE YEAR([DATE]) = YEAR(GETDATE())
AND Datepart(WEEK, GETDATE()) = DIMDATE.WEEKOFYEAR)t)SELECT @ENDDATE = (SELECT MAX(d) AS WeekBeginFROM (SELECT [DATE] AS D FROMDIMDATE WHERE YEAR([DATE]) = YEAR(GETDATE())

AND Datepart(WEEK, GETDATE()) = DIMDATE.WEEKOFYEAR)t)SELECT @startdate AS FirstDay, @Enddate AS LastDay