Get a list of month & year for particular duration

May 11, 2017

Here i have requirement to display month,year wise record count from table.The condition is starting month,year  would be month & year of Min start date in table and end month,year would be current month & year.

So here a query to fulfill the above requirement.

DECLARE @FromDate DATETIME, @ToDate DATETIME
SELECT @FromDate=MIN(CreatedDate) FROM dbo.U***
SET @ToDate = GETDATE()

DECLARE @MonthsAndYears table (Month INT, Year int)

;WITH dates AS
(
    SELECT @FromDate 'date'
        UNION ALL
    SELECT DATEADD(dd, 1, t.date)
        FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= @ToDate
)
INSERT INTO @MonthsAndYears
SELECT
    DATEPART(MONTH, date),
    DATEPART(YEAR, date)
FROM dates
GROUP BY
DATEPART(MONTH, date),
DATEPART(YEAR, date)
option (maxrecursion 0)

SELECT *,CONCAT(DATENAME(MONTH,DATEADD(MONTH,  [Month] - 1, 0)),',',[Year]) AS Duration FROM @MonthsAndYears

Result is :

Capture

By using outer apply with your table on column created date (month & year part) to month and year column in query result ,we can get the desired result.

Advertisements

Select the inserted/updated value to get affected rows in single query with OUTPUT keyword

April 3, 2013

Select the inserted/Updated Value to get affected rows in single query with OUTPUT keyword

for insert query,

Insert Into tblTest(Name) OutPut Inserted.TestID,Inserted.Name values(‘Juhi’)

Output you will get

Capture

The same thing for update ,

Update tblTest set Name=’Juhi Paunikar’ OutPut inserted.TestID,inserted.Name

The output you will get

bg.jpg Capture1 Enjoy “Sql Queries….!!!”