March 4, 2013 at 1:17 pm
Hello
I need one help. I need to add missing date in the table so here is one sample on some data so please let me know how is it possible.
create table #x1
(SchoolID int,
CalendarDate datetime
)
insert into #x1 values (101,'2012-08-18')
insert into #x1 values (101,'2012-08-19')
insert into #x1 values (101,'2012-08-20')
insert into #x1 values (101,'2012-08-21')
insert into #x1 values (101,'2012-08-29')
insert into #x1 values (101,'2012-08-30')
insert into #x1 values (101,'2012-08-31')
I got output as below
SchoolIDCalendarDate
1012012-08-18 00:00:00.000
1012012-08-19 00:00:00.000
1012012-08-20 00:00:00.000
1012012-08-21 00:00:00.000
1012012-08-29 00:00:00.000
1012012-08-30 00:00:00.000
1012012-08-31 00:00:00.000
But desired output is
SchoolIDCalendarDate
1012012-08-18 00:00:00.000
1012012-08-19 00:00:00.000
1012012-08-20 00:00:00.000
1012012-08-21 00:00:00.000
1012012-08-22 00:00:00.000
1012012-08-23 00:00:00.000
1012012-08-24 00:00:00.000
1012012-08-25 00:00:00.000
1012012-08-26 00:00:00.000
1012012-08-27 00:00:00.000
1012012-08-28 00:00:00.000
1012012-08-29 00:00:00.000
1012012-08-30 00:00:00.000
1012012-08-31 00:00:00.000
can anyone please tell me is it possible in SQL SERVER?
March 4, 2013 at 1:33 pm
Excellent job setting up the problem. This is certainly possible using a tally table.
;with DateVals as
(
select SchoolID, min(CalendarDate) as StartDate, max(CalendarDate) as EndDate, datediff(day, min(CalendarDate), max(CalendarDate)) as TotalDays
from #x1
group by SchoolID
)
select *, dateadd(day, N - 1, StartDate)
from DateVals d
join tally t on t.N <= datediff(day, StartDate, EndDate) + 1
You can read more about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 1:35 pm
certainly possible, but you need to generate a list of all possible dates;
A Tally Table is one of the easiest ways to do that;
here's just one way to do it:
create table #x1
(SchoolID int,
CalendarDate datetime
)
insert into #x1 values (101,'2012-08-18')
insert into #x1 values (101,'2012-08-19')
insert into #x1 values (101,'2012-08-20')
insert into #x1 values (101,'2012-08-21')
insert into #x1 values (101,'2012-08-29')
insert into #x1 values (101,'2012-08-30')
insert into #x1 values (101,'2012-08-31')
;WITH MyPossibleDates
AS
(
SELECT
DATEADD(day,MiniTally.n - 0,'2012-08-01')As TheDate,
MiniTally.n
FROM
(SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As N FROM sys.columns) MiniTally
)
--INSERT INTO #x1(SchoolID,CalendarDate)
SELECT 101,MyPossibleDates.TheDate
FROM MyPossibleDates
WHERE MyPossibleDates.TheDate NOT IN(SELECT CalendarDate FROM #x1 WHERE SchoolID = 101)
AND MyPossibleDates.TheDate BETWEEN '2012-08-18' AND '2012-08-31'
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply