April 3, 2013 at 10:28 am
Hello
I need one help
I have list of dates from those date I need to display all dates with schoolIDs
The list of dates is
create table #temp
(date datetime,
IsWeekDay int,
SchoolDay int)
insert into #temp values('2011-08-06 00:00:00.000',0,0)
insert into #temp values('2011-08-07 00:00:00.000',0,0)
insert into #temp values('2011-08-08 00:00:00.000',0,0)
insert into #temp values('2011-08-09 00:00:00.000',0,0)
insert into #temp values('2011-08-10 00:00:00.000',0,0)
insert into #temp values('2011-08-11 00:00:00.000',0,0)
insert into #temp values('2011-08-12 00:00:00.000',0,0)
insert into #temp values('2011-08-13 00:00:00.000',0,0)
insert into #temp values('2011-08-14 00:00:00.000',0,0)
insert into #temp values('2011-08-15 00:00:00.000',0,0)
insert into #temp values('2011-08-16 00:00:00.000',0,0)
insert into #temp values('2011-08-17 00:00:00.000',0,0)
insert into #temp values('2011-08-18 00:00:00.000',0,0)
insert into #temp values('2011-08-19 00:00:00.000',0,0)
insert into #temp values('2011-08-20 00:00:00.000',0,0)
insert into #temp values('2011-08-21 00:00:00.000',0,0)
create table #temp2
(SchoolID int)
insert into #temp2 values (101)
insert into #temp2 values (102)
the desired output as below
dateSchoolID
2011-08-06 00:00:00.000101
2011-08-07 00:00:00.000101
2011-08-08 00:00:00.000101
2011-08-09 00:00:00.000101
2011-08-10 00:00:00.000101
2011-08-11 00:00:00.000101
2011-08-12 00:00:00.000101
2011-08-13 00:00:00.000101
2011-08-14 00:00:00.000101
2011-08-15 00:00:00.000101
2011-08-16 00:00:00.000101
2011-08-17 00:00:00.000101
2011-08-18 00:00:00.000101
2011-08-19 00:00:00.000101
2011-08-20 00:00:00.000101
2011-08-21 00:00:00.000101
2011-08-06 00:00:00.000102
2011-08-07 00:00:00.000102
2011-08-08 00:00:00.000102
2011-08-09 00:00:00.000102
2011-08-10 00:00:00.000102
2011-08-11 00:00:00.000102
2011-08-12 00:00:00.000102
2011-08-13 00:00:00.000102
2011-08-14 00:00:00.000102
2011-08-15 00:00:00.000102
2011-08-16 00:00:00.000102
2011-08-17 00:00:00.000102
2011-08-18 00:00:00.000102
2011-08-19 00:00:00.000102
2011-08-20 00:00:00.000102
2011-08-21 00:00:00.000102
please help me to do this
April 3, 2013 at 10:33 am
Awesome job posting ddl and sample data along with desired output.
This produces the output according to your sample data.
select date, SchoolID from #temp
cross join #temp2
order by #temp2.SchoolID, #temp.date
_______________________________________________________________
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/
April 3, 2013 at 10:34 am
This:
create table #temp
(CalendarDate datetime,
IsWeekDay int,
SchoolDay int)
insert into #temp values('2011-08-06 00:00:00.000',0,0)
insert into #temp values('2011-08-07 00:00:00.000',0,0)
insert into #temp values('2011-08-08 00:00:00.000',0,0)
insert into #temp values('2011-08-09 00:00:00.000',0,0)
insert into #temp values('2011-08-10 00:00:00.000',0,0)
insert into #temp values('2011-08-11 00:00:00.000',0,0)
insert into #temp values('2011-08-12 00:00:00.000',0,0)
insert into #temp values('2011-08-13 00:00:00.000',0,0)
insert into #temp values('2011-08-14 00:00:00.000',0,0)
insert into #temp values('2011-08-15 00:00:00.000',0,0)
insert into #temp values('2011-08-16 00:00:00.000',0,0)
insert into #temp values('2011-08-17 00:00:00.000',0,0)
insert into #temp values('2011-08-18 00:00:00.000',0,0)
insert into #temp values('2011-08-19 00:00:00.000',0,0)
insert into #temp values('2011-08-20 00:00:00.000',0,0)
insert into #temp values('2011-08-21 00:00:00.000',0,0)
create table #temp2
(SchoolID int)
insert into #temp2 values (101)
insert into #temp2 values (102)
select
CalendarDate,
SchoolID
from
#Temp
cross join #Temp2;
drop table #Temp;
drop table #Temp2;
April 3, 2013 at 10:35 am
Biggest suggestion I have, don't use date as a column name. It is also a datatype starting with SQL Server 2008.
April 3, 2013 at 10:36 am
Thanks Sean Lange and Lynn Pettis
really Appreciated
April 3, 2013 at 10:42 am
Lynn Pettis (4/3/2013)
Biggest suggestion I have, don't use date as a column name. It is also a datatype starting with SQL Server 2008.
+1
Not only is it a reserved word in 2008+ but also it gives you no idea what the date is. The other columns give an indication of what they are based on the name. Date is sufficiently vague that it could be the date it was added, the calendar date, the date it was modified, etc etc etc...
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply