CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005sql-server-2000
SAQIB ASLAM GANATRA
SAQIB ASLAM GANATRA

Reputation: 11

write a query to fill the above table from 1 Jan, 2008 to 31 Dec 2014

Create a table with the following columns

DateId, Date, Day_Name, Day_of_Week, Day_of_Month, Day_of_Year,
Mon… _Name, Quarter_of_Year, Year_Name,Year

write a query to fill the above table from 1 Jan, 2008 to 31 Dec 2014

DECLARE @DATE smalldatetime
DECLARE @DATESTART smalldatetime
DECLARE @DATEEND smalldatetime

SET @DATESTART = '01-01-2008'
SET @DATEEND = '12-31-2014'

--SET @DATE = @DATESTART

DECLARE @days int 
DECLARE @index int = 1

SET @days= DATEPART(dayofyear, @DATEEND) - DATEPART(dayofyear, @DATESTART)

SELECT @days
WHILE()
INSERT INTO [BI_Database].[dbo].[Date_
Range]
([Date],[Day_Name],[Day_of_Week],[Day_of…
VALUES(<Date, datetime,>,<Day_Name, nvarchar(50),>,<Day_of_Week, smallint,>,<Day_of_Month, smallint,>,<Day_of_Year, smallint,>,<Month_Name, nvarchar(50),>
,<Month_of_Year, smallint,>,<Quarter_Name, nvarchar(50),>,<Quarter_of_Year, nvarchar(50),>,<Year_Name, nvarchar(50),>,<Year, smallint,>)

Upvotes: 1

Views: 332

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try this, unfortunately I had no clue what to populate year_name with. I used a temporary table, this can be replaced by your own table

create table #t
(DateId int identity(1,1), Date date, Day_Name varchar(8), 
Day_of_Week smallint, Day_of_Month smallint, Day_of_Year int,
Mon_Name varchar(12), Quarter_of_Year smallint, Year_Name varchar(30),
Year int)

insert #t
(Date,Day_Name,Day_of_Week,Day_of_Month,Day_of_Year,Mon_Name, 
Quarter_of_Year, Year_Name, Year)
select Date1 Date
,datename(weekday, date1) Day_Name
,datediff(day, 0, date1) %7 + 1 Day_of_Week
,day(date1) Day_of_Month
,datepart(dy, date1) Day_of_Year
,datepart(mm, date1) Mon_Name
,datepart(qq, date1) Quarter,
null Year_Name, -- don't know what you want here
year(date1) year 
from 
master..spt_values v1
cross join
(select 0 mul union all select 1 union all select 2) m
cross apply
(select dateadd(day, v1.number+ 2048 * mul,'2008-01-01') date1) a
where date1 <= '2014-12-31'
and type = 'P'

select * from #t
drop table #t

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56755

This should do it:

DECLARE @DATESTART smalldatetime
DECLARE @DATEEND smalldatetime

SET @DATESTART = '01-01-2008'
SET @DATEEND = '12-31-2014'

;WITH cte4 As (Select 0 As N Union All Select 1 Union All Select 2 Union All Select 4)
, cte64 As (Select 0 As N From cte4 n0, cte4 n1, cte4 n2)
, cte4k As (Select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) As N From cte64 n0, cte64 n1)
, cteDate As (Select N, CAST(@DATESTART+N-1 As smalldatetime) As date_ From cte4k)
INSERT INTO [BI_Database].[dbo].[Date_Range]
([Date],Day_Name,Day_of_Week,Day_of_Week,Day_of_Month,Day_of_Year,Month_Name,Month_of_Year,
 Quarter_Name,Quarter_of_Year,Year_Name,[Year])
SELECT
    N                           As DateId,
    date_                       As [Date],
    DATENAME(WEEKDAY, date_)    As Day_Name,
    DATEPART(WEEKDAY, date_)    As Day_of_Week,
    Day(date_)                  As Day_of_Month,
    DATEPART(DAYOFYEAR, date_)  As Day_of_Year,
    DATENAME(MONTH, date_)      As Month_Name,
    MONTH(date_)                As Month_of_Year,
    DATENAME(QUARTER, date_)    As Quarter_Name,
    DATEPART(QUARTER, date_)    As Quarter_of_Year,
    DATENAME(YEAR, date_)       As Year_Name,
    YEAR(date_)                 As [Year]
FROM cteDate
WHERE date_ BETWEEN @DATESTART AND @DATEEND

Note: Only works for SQL Server 2005+,

Upvotes: 2

Related Questions