Home All Groups Group Topic Archive Search About

INSERT based on a date range

microsoft.public.sqlserver.programming
Author
31 Aug 2006 11:21 PM
Terri
I want to INSERT records into #TEST so that there is a record for every date
within my date range. The date range is all dates between and equal to
@StartDate and @EndDate. The inserted records will be a duplicate of the
record determined by @CurrentDate, except for the TestDate field which is my
key.


Thanks.

DECLARE @CurrentDate datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @CurrentDate = '8/25/2006'
SET @StartDate = '8/20/2006'
SET @EndDate = '8/28/2006'


CREATE TABLE #Test
(
TestDate datetime,
TestValue char(1)
)

INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')

SELECT * FROM #Test
DROP TABLE #Test


Desired results:

8/20/2006,A
8/21/2006,A
8/22/2006,A
8/23/2006,A
8/24/2006,A
8/25/2006,A
8/26/2006,A
8/27/2006,A
8/28/2006,A

Author
31 Aug 2006 11:33 PM
Arnie Rowland
You could use a Calendar table. Check this article:

http://www.aspfaq.com/show.asp?id=2519

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


Show quote
"Terri" <te***@cybernets.com> wrote in message
news:ed7qu3$qaq$1@reader2.nmix.net...
>I want to INSERT records into #TEST so that there is a record for every
>date
> within my date range. The date range is all dates between and equal to
> @StartDate and @EndDate. The inserted records will be a duplicate of the
> record determined by @CurrentDate, except for the TestDate field which is
> my
> key.
>
>
> Thanks.
>
> DECLARE @CurrentDate datetime
> DECLARE @StartDate datetime
> DECLARE @EndDate datetime
>
> SET @CurrentDate = '8/25/2006'
> SET @StartDate = '8/20/2006'
> SET @EndDate = '8/28/2006'
>
>
> CREATE TABLE #Test
> (
> TestDate datetime,
> TestValue char(1)
> )
>
> INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
>
> SELECT * FROM #Test
> DROP TABLE #Test
>
>
> Desired results:
>
> 8/20/2006,A
> 8/21/2006,A
> 8/22/2006,A
> 8/23/2006,A
> 8/24/2006,A
> 8/25/2006,A
> 8/26/2006,A
> 8/27/2006,A
> 8/28/2006,A
>
>
>
>
>
Author
1 Sep 2006 7:31 PM
Terri
OK, I have a calendar table, thanks for reminding me to put it to use. My
INSERT now works using a subquery.

DECLARE @CurrentDate datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @CurrentDate = '8/25/2006'
SET @StartDate = '8/20/2006'
SET @EndDate = '8/28/2006'

CREATE TABLE [dbo].[#Calendar] (
[CalDate] [smalldatetime] NOT NULL ,
[BusinessDay] [char] (1)  NOT NULL
) ON [PRIMARY]

INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')

CREATE TABLE #Test
(
TestDate datetime,
TestValue char(1)
)

INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')

INSERT INTO #Test
SELECT
#Calendar.CalDate,
(SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
FROM #Calendar
LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
@CurrentDate

SELECT * FROM #Test
DROP TABLE #Test
DROP TABLE #Calendar

Show quote
"Arnie Rowland" <ar***@1568.com> wrote in message
news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
> You could use a Calendar table. Check this article:
>
> http://www.aspfaq.com/show.asp?id=2519
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Terri" <te***@cybernets.com> wrote in message
> news:ed7qu3$qaq$1@reader2.nmix.net...
> >I want to INSERT records into #TEST so that there is a record for every
> >date
> > within my date range. The date range is all dates between and equal to
> > @StartDate and @EndDate. The inserted records will be a duplicate of the
> > record determined by @CurrentDate, except for the TestDate field which
is
> > my
> > key.
> >
> >
> > Thanks.
> >
> > DECLARE @CurrentDate datetime
> > DECLARE @StartDate datetime
> > DECLARE @EndDate datetime
> >
> > SET @CurrentDate = '8/25/2006'
> > SET @StartDate = '8/20/2006'
> > SET @EndDate = '8/28/2006'
> >
> >
> > CREATE TABLE #Test
> > (
> > TestDate datetime,
> > TestValue char(1)
> > )
> >
> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> >
> > SELECT * FROM #Test
> > DROP TABLE #Test
> >
> >
> > Desired results:
> >
> > 8/20/2006,A
> > 8/21/2006,A
> > 8/22/2006,A
> > 8/23/2006,A
> > 8/24/2006,A
> > 8/25/2006,A
> > 8/26/2006,A
> > 8/27/2006,A
> > 8/28/2006,A
> >
> >
> >
> >
> >
>
>
Author
1 Sep 2006 7:39 PM
Arnie Rowland
I'm glad that I could help by reminding you...

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


Show quote
"Terri" <te***@cybernets.com> wrote in message
news:eda1r2$jta$1@reader2.nmix.net...
> OK, I have a calendar table, thanks for reminding me to put it to use. My
> INSERT now works using a subquery.
>
> DECLARE @CurrentDate datetime
> DECLARE @StartDate datetime
> DECLARE @EndDate datetime
>
> SET @CurrentDate = '8/25/2006'
> SET @StartDate = '8/20/2006'
> SET @EndDate = '8/28/2006'
>
> CREATE TABLE [dbo].[#Calendar] (
> [CalDate] [smalldatetime] NOT NULL ,
> [BusinessDay] [char] (1)  NOT NULL
> ) ON [PRIMARY]
>
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
> INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')
>
> CREATE TABLE #Test
> (
> TestDate datetime,
> TestValue char(1)
> )
>
> INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
>
> INSERT INTO #Test
> SELECT
> #Calendar.CalDate,
> (SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
> FROM #Calendar
> LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
> WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
> @CurrentDate
>
> SELECT * FROM #Test
> DROP TABLE #Test
> DROP TABLE #Calendar
>
> "Arnie Rowland" <ar***@1568.com> wrote in message
> news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
>> You could use a Calendar table. Check this article:
>>
>> http://www.aspfaq.com/show.asp?id=2519
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>>
>> "Terri" <te***@cybernets.com> wrote in message
>> news:ed7qu3$qaq$1@reader2.nmix.net...
>> >I want to INSERT records into #TEST so that there is a record for every
>> >date
>> > within my date range. The date range is all dates between and equal to
>> > @StartDate and @EndDate. The inserted records will be a duplicate of
>> > the
>> > record determined by @CurrentDate, except for the TestDate field which
> is
>> > my
>> > key.
>> >
>> >
>> > Thanks.
>> >
>> > DECLARE @CurrentDate datetime
>> > DECLARE @StartDate datetime
>> > DECLARE @EndDate datetime
>> >
>> > SET @CurrentDate = '8/25/2006'
>> > SET @StartDate = '8/20/2006'
>> > SET @EndDate = '8/28/2006'
>> >
>> >
>> > CREATE TABLE #Test
>> > (
>> > TestDate datetime,
>> > TestValue char(1)
>> > )
>> >
>> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
>> >
>> > SELECT * FROM #Test
>> > DROP TABLE #Test
>> >
>> >
>> > Desired results:
>> >
>> > 8/20/2006,A
>> > 8/21/2006,A
>> > 8/22/2006,A
>> > 8/23/2006,A
>> > 8/24/2006,A
>> > 8/25/2006,A
>> > 8/26/2006,A
>> > 8/27/2006,A
>> > 8/28/2006,A
>> >
>> >
>> >
>> >
>> >
>>
>>
>
>
Author
5 Sep 2006 5:41 PM
kw_uh97
Arnie or Terri

I was perusing the discussion board and came across this post and thought it
was very helpful. But my questions takes the concept a step farther.

If you had matching rows already in the #test table. How would you go about
not setting its values (i.e. the values will remain as they are in the #test
table) and if there are no matching rows in the #test table, insert the row
into the #test table and set the value to '0'.

Thanks In Advance for your assistance.
kw

Show quote
"Arnie Rowland" wrote:

> I'm glad that I could help by reminding you...
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Terri" <te***@cybernets.com> wrote in message
> news:eda1r2$jta$1@reader2.nmix.net...
> > OK, I have a calendar table, thanks for reminding me to put it to use. My
> > INSERT now works using a subquery.
> >
> > DECLARE @CurrentDate datetime
> > DECLARE @StartDate datetime
> > DECLARE @EndDate datetime
> >
> > SET @CurrentDate = '8/25/2006'
> > SET @StartDate = '8/20/2006'
> > SET @EndDate = '8/28/2006'
> >
> > CREATE TABLE [dbo].[#Calendar] (
> > [CalDate] [smalldatetime] NOT NULL ,
> > [BusinessDay] [char] (1)  NOT NULL
> > ) ON [PRIMARY]
> >
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')
> >
> > CREATE TABLE #Test
> > (
> > TestDate datetime,
> > TestValue char(1)
> > )
> >
> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> >
> > INSERT INTO #Test
> > SELECT
> > #Calendar.CalDate,
> > (SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
> > FROM #Calendar
> > LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
> > WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
> > @CurrentDate
> >
> > SELECT * FROM #Test
> > DROP TABLE #Test
> > DROP TABLE #Calendar
> >
> > "Arnie Rowland" <ar***@1568.com> wrote in message
> > news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
> >> You could use a Calendar table. Check this article:
> >>
> >> http://www.aspfaq.com/show.asp?id=2519
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "Terri" <te***@cybernets.com> wrote in message
> >> news:ed7qu3$qaq$1@reader2.nmix.net...
> >> >I want to INSERT records into #TEST so that there is a record for every
> >> >date
> >> > within my date range. The date range is all dates between and equal to
> >> > @StartDate and @EndDate. The inserted records will be a duplicate of
> >> > the
> >> > record determined by @CurrentDate, except for the TestDate field which
> > is
> >> > my
> >> > key.
> >> >
> >> >
> >> > Thanks.
> >> >
> >> > DECLARE @CurrentDate datetime
> >> > DECLARE @StartDate datetime
> >> > DECLARE @EndDate datetime
> >> >
> >> > SET @CurrentDate = '8/25/2006'
> >> > SET @StartDate = '8/20/2006'
> >> > SET @EndDate = '8/28/2006'
> >> >
> >> >
> >> > CREATE TABLE #Test
> >> > (
> >> > TestDate datetime,
> >> > TestValue char(1)
> >> > )
> >> >
> >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> >> >
> >> > SELECT * FROM #Test
> >> > DROP TABLE #Test
> >> >
> >> >
> >> > Desired results:
> >> >
> >> > 8/20/2006,A
> >> > 8/21/2006,A
> >> > 8/22/2006,A
> >> > 8/23/2006,A
> >> > 8/24/2006,A
> >> > 8/25/2006,A
> >> > 8/26/2006,A
> >> > 8/27/2006,A
> >> > 8/28/2006,A
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>
Author
5 Sep 2006 6:14 PM
Arnie Rowland
Perhaps something like (using Terri's code):

INSERT INTO #Test
   SELECT CalDate, '0'
   FROM #Calendar c
      LEFT JOIN #Test t
         ON c.CalDate = t.TestDate
   WHERE (   c.CalDate >= @StartDate
         AND c.CalDate <= @EndDate
         ) 
         AND c.CalDate <> @CurrentDate

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


Show quote
"kw_uh97" <kwu***@discussions.microsoft.com> wrote in message news:784ABDF3-CCAE-46B1-92B9-A56E82261FF6@microsoft.com...
> Arnie or Terri
>
> I was perusing the discussion board and came across this post and thought it
> was very helpful. But my questions takes the concept a step farther.
>
> If you had matching rows already in the #test table. How would you go about
> not setting its values (i.e. the values will remain as they are in the #test
> table) and if there are no matching rows in the #test table, insert the row
> into the #test table and set the value to '0'.
>
> Thanks In Advance for your assistance.
> kw
>
> "Arnie Rowland" wrote:
>
>> I'm glad that I could help by reminding you...
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>>
>> "Terri" <te***@cybernets.com> wrote in message
>> news:eda1r2$jta$1@reader2.nmix.net...
>> > OK, I have a calendar table, thanks for reminding me to put it to use. My
>> > INSERT now works using a subquery.
>> >
>> > DECLARE @CurrentDate datetime
>> > DECLARE @StartDate datetime
>> > DECLARE @EndDate datetime
>> >
>> > SET @CurrentDate = '8/25/2006'
>> > SET @StartDate = '8/20/2006'
>> > SET @EndDate = '8/28/2006'
>> >
>> > CREATE TABLE [dbo].[#Calendar] (
>> > [CalDate] [smalldatetime] NOT NULL ,
>> > [BusinessDay] [char] (1)  NOT NULL
>> > ) ON [PRIMARY]
>> >
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
>> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')
>> >
>> > CREATE TABLE #Test
>> > (
>> > TestDate datetime,
>> > TestValue char(1)
>> > )
>> >
>> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
>> >
>> > INSERT INTO #Test
>> > SELECT
>> > #Calendar.CalDate,
>> > (SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
>> > FROM #Calendar
>> > LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
>> > WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
>> > @CurrentDate
>> >
>> > SELECT * FROM #Test
>> > DROP TABLE #Test
>> > DROP TABLE #Calendar
>> >
>> > "Arnie Rowland" <ar***@1568.com> wrote in message
>> > news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
>> >> You could use a Calendar table. Check this article:
>> >>
>> >> http://www.aspfaq.com/show.asp?id=2519
>> >>
>> >> --
>> >> Arnie Rowland, Ph.D.
>> >> Westwood Consulting, Inc
>> >>
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "Terri" <te***@cybernets.com> wrote in message
>> >> news:ed7qu3$qaq$1@reader2.nmix.net...
>> >> >I want to INSERT records into #TEST so that there is a record for every
>> >> >date
>> >> > within my date range. The date range is all dates between and equal to
>> >> > @StartDate and @EndDate. The inserted records will be a duplicate of
>> >> > the
>> >> > record determined by @CurrentDate, except for the TestDate field which
>> > is
>> >> > my
>> >> > key.
>> >> >
>> >> >
>> >> > Thanks.
>> >> >
>> >> > DECLARE @CurrentDate datetime
>> >> > DECLARE @StartDate datetime
>> >> > DECLARE @EndDate datetime
>> >> >
>> >> > SET @CurrentDate = '8/25/2006'
>> >> > SET @StartDate = '8/20/2006'
>> >> > SET @EndDate = '8/28/2006'
>> >> >
>> >> >
>> >> > CREATE TABLE #Test
>> >> > (
>> >> > TestDate datetime,
>> >> > TestValue char(1)
>> >> > )
>> >> >
>> >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
>> >> >
>> >> > SELECT * FROM #Test
>> >> > DROP TABLE #Test
>> >> >
>> >> >
>> >> > Desired results:
>> >> >
>> >> > 8/20/2006,A
>> >> > 8/21/2006,A
>> >> > 8/22/2006,A
>> >> > 8/23/2006,A
>> >> > 8/24/2006,A
>> >> > 8/25/2006,A
>> >> > 8/26/2006,A
>> >> > 8/27/2006,A
>> >> > 8/28/2006,A
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>>
Author
5 Sep 2006 6:23 PM
kw_uh97
Arnie thanks for the quick reply. I will try that out and post my results.

kw

Show quote
"Arnie Rowland" wrote:

> Perhaps something like (using Terri's code):
>
> INSERT INTO #Test
>    SELECT CalDate, '0'
>    FROM #Calendar c
>       LEFT JOIN #Test t
>          ON c.CalDate = t.TestDate
>    WHERE (   c.CalDate >= @StartDate
>          AND c.CalDate <= @EndDate
>          ) 
>          AND c.CalDate <> @CurrentDate
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "kw_uh97" <kwu***@discussions.microsoft.com> wrote in message news:784ABDF3-CCAE-46B1-92B9-A56E82261FF6@microsoft.com...
> > Arnie or Terri
> >
> > I was perusing the discussion board and came across this post and thought it
> > was very helpful. But my questions takes the concept a step farther.
> >
> > If you had matching rows already in the #test table. How would you go about
> > not setting its values (i.e. the values will remain as they are in the #test
> > table) and if there are no matching rows in the #test table, insert the row
> > into the #test table and set the value to '0'.
> >
> > Thanks In Advance for your assistance.
> > kw
> >
> > "Arnie Rowland" wrote:
> >
> >> I'm glad that I could help by reminding you...
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "Terri" <te***@cybernets.com> wrote in message
> >> news:eda1r2$jta$1@reader2.nmix.net...
> >> > OK, I have a calendar table, thanks for reminding me to put it to use. My
> >> > INSERT now works using a subquery.
> >> >
> >> > DECLARE @CurrentDate datetime
> >> > DECLARE @StartDate datetime
> >> > DECLARE @EndDate datetime
> >> >
> >> > SET @CurrentDate = '8/25/2006'
> >> > SET @StartDate = '8/20/2006'
> >> > SET @EndDate = '8/28/2006'
> >> >
> >> > CREATE TABLE [dbo].[#Calendar] (
> >> > [CalDate] [smalldatetime] NOT NULL ,
> >> > [BusinessDay] [char] (1)  NOT NULL
> >> > ) ON [PRIMARY]
> >> >
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
> >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')
> >> >
> >> > CREATE TABLE #Test
> >> > (
> >> > TestDate datetime,
> >> > TestValue char(1)
> >> > )
> >> >
> >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> >> >
> >> > INSERT INTO #Test
> >> > SELECT
> >> > #Calendar.CalDate,
> >> > (SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
> >> > FROM #Calendar
> >> > LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
> >> > WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
> >> > @CurrentDate
> >> >
> >> > SELECT * FROM #Test
> >> > DROP TABLE #Test
> >> > DROP TABLE #Calendar
> >> >
> >> > "Arnie Rowland" <ar***@1568.com> wrote in message
> >> > news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
> >> >> You could use a Calendar table. Check this article:
> >> >>
> >> >> http://www.aspfaq.com/show.asp?id=2519
> >> >>
> >> >> --
> >> >> Arnie Rowland, Ph.D.
> >> >> Westwood Consulting, Inc
> >> >>
> >> >> Most good judgment comes from experience.
> >> >> Most experience comes from bad judgment.
> >> >> - Anonymous
> >> >>
> >> >>
> >> >> "Terri" <te***@cybernets.com> wrote in message
> >> >> news:ed7qu3$qaq$1@reader2.nmix.net...
> >> >> >I want to INSERT records into #TEST so that there is a record for every
> >> >> >date
> >> >> > within my date range. The date range is all dates between and equal to
> >> >> > @StartDate and @EndDate. The inserted records will be a duplicate of
> >> >> > the
> >> >> > record determined by @CurrentDate, except for the TestDate field which
> >> > is
> >> >> > my
> >> >> > key.
> >> >> >
> >> >> >
> >> >> > Thanks.
> >> >> >
> >> >> > DECLARE @CurrentDate datetime
> >> >> > DECLARE @StartDate datetime
> >> >> > DECLARE @EndDate datetime
> >> >> >
> >> >> > SET @CurrentDate = '8/25/2006'
> >> >> > SET @StartDate = '8/20/2006'
> >> >> > SET @EndDate = '8/28/2006'
> >> >> >
> >> >> >
> >> >> > CREATE TABLE #Test
> >> >> > (
> >> >> > TestDate datetime,
> >> >> > TestValue char(1)
> >> >> > )
> >> >> >
> >> >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> >> >> >
> >> >> > SELECT * FROM #Test
> >> >> > DROP TABLE #Test
> >> >> >
> >> >> >
> >> >> > Desired results:
> >> >> >
> >> >> > 8/20/2006,A
> >> >> > 8/21/2006,A
> >> >> > 8/22/2006,A
> >> >> > 8/23/2006,A
> >> >> > 8/24/2006,A
> >> >> > 8/25/2006,A
> >> >> > 8/26/2006,A
> >> >> > 8/27/2006,A
> >> >> > 8/28/2006,A
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
Author
7 Sep 2006 1:41 PM
kw_uh97
Thanks Arnie that helped out a lot and I able to set the values to zero

Show quote
"kw_uh97" wrote:

> Arnie thanks for the quick reply. I will try that out and post my results.
>
> kw
>
> "Arnie Rowland" wrote:
>
> > Perhaps something like (using Terri's code):
> >
> > INSERT INTO #Test
> >    SELECT CalDate, '0'
> >    FROM #Calendar c
> >       LEFT JOIN #Test t
> >          ON c.CalDate = t.TestDate
> >    WHERE (   c.CalDate >= @StartDate
> >          AND c.CalDate <= @EndDate
> >          ) 
> >          AND c.CalDate <> @CurrentDate
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "kw_uh97" <kwu***@discussions.microsoft.com> wrote in message news:784ABDF3-CCAE-46B1-92B9-A56E82261FF6@microsoft.com...
> > > Arnie or Terri
> > >
> > > I was perusing the discussion board and came across this post and thought it
> > > was very helpful. But my questions takes the concept a step farther.
> > >
> > > If you had matching rows already in the #test table. How would you go about
> > > not setting its values (i.e. the values will remain as they are in the #test
> > > table) and if there are no matching rows in the #test table, insert the row
> > > into the #test table and set the value to '0'.
> > >
> > > Thanks In Advance for your assistance.
> > > kw
> > >
> > > "Arnie Rowland" wrote:
> > >
> > >> I'm glad that I could help by reminding you...
> > >>
> > >> --
> > >> Arnie Rowland, Ph.D.
> > >> Westwood Consulting, Inc
> > >>
> > >> Most good judgment comes from experience.
> > >> Most experience comes from bad judgment.
> > >> - Anonymous
> > >>
> > >>
> > >> "Terri" <te***@cybernets.com> wrote in message
> > >> news:eda1r2$jta$1@reader2.nmix.net...
> > >> > OK, I have a calendar table, thanks for reminding me to put it to use. My
> > >> > INSERT now works using a subquery.
> > >> >
> > >> > DECLARE @CurrentDate datetime
> > >> > DECLARE @StartDate datetime
> > >> > DECLARE @EndDate datetime
> > >> >
> > >> > SET @CurrentDate = '8/25/2006'
> > >> > SET @StartDate = '8/20/2006'
> > >> > SET @EndDate = '8/28/2006'
> > >> >
> > >> > CREATE TABLE [dbo].[#Calendar] (
> > >> > [CalDate] [smalldatetime] NOT NULL ,
> > >> > [BusinessDay] [char] (1)  NOT NULL
> > >> > ) ON [PRIMARY]
> > >> >
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060818','N')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060819','N')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060820','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060821','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060822','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060823','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060824','N')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060825','N')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060826','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060827','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060828','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060829','Y')
> > >> > INSERT INTO #Calendar (CalDate,BusinessDay) VALUES('20060830','Y')
> > >> >
> > >> > CREATE TABLE #Test
> > >> > (
> > >> > TestDate datetime,
> > >> > TestValue char(1)
> > >> > )
> > >> >
> > >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> > >> >
> > >> > INSERT INTO #Test
> > >> > SELECT
> > >> > #Calendar.CalDate,
> > >> > (SELECT #Test.TestValue FROM #Test WHERE #Test.testDate = @CurrentDate)
> > >> > FROM #Calendar
> > >> > LEFT OUTER JOIN #Test ON #Calendar.CalDate = #Test.testDate
> > >> > WHERE (CalDate >= @StartDate AND CalDate <= @EndDate)  AND CalDate <>
> > >> > @CurrentDate
> > >> >
> > >> > SELECT * FROM #Test
> > >> > DROP TABLE #Test
> > >> > DROP TABLE #Calendar
> > >> >
> > >> > "Arnie Rowland" <ar***@1568.com> wrote in message
> > >> > news:eZI5qXVzGHA.3752@TK2MSFTNGP02.phx.gbl...
> > >> >> You could use a Calendar table. Check this article:
> > >> >>
> > >> >> http://www.aspfaq.com/show.asp?id=2519
> > >> >>
> > >> >> --
> > >> >> Arnie Rowland, Ph.D.
> > >> >> Westwood Consulting, Inc
> > >> >>
> > >> >> Most good judgment comes from experience.
> > >> >> Most experience comes from bad judgment.
> > >> >> - Anonymous
> > >> >>
> > >> >>
> > >> >> "Terri" <te***@cybernets.com> wrote in message
> > >> >> news:ed7qu3$qaq$1@reader2.nmix.net...
> > >> >> >I want to INSERT records into #TEST so that there is a record for every
> > >> >> >date
> > >> >> > within my date range. The date range is all dates between and equal to
> > >> >> > @StartDate and @EndDate. The inserted records will be a duplicate of
> > >> >> > the
> > >> >> > record determined by @CurrentDate, except for the TestDate field which
> > >> > is
> > >> >> > my
> > >> >> > key.
> > >> >> >
> > >> >> >
> > >> >> > Thanks.
> > >> >> >
> > >> >> > DECLARE @CurrentDate datetime
> > >> >> > DECLARE @StartDate datetime
> > >> >> > DECLARE @EndDate datetime
> > >> >> >
> > >> >> > SET @CurrentDate = '8/25/2006'
> > >> >> > SET @StartDate = '8/20/2006'
> > >> >> > SET @EndDate = '8/28/2006'
> > >> >> >
> > >> >> >
> > >> >> > CREATE TABLE #Test
> > >> >> > (
> > >> >> > TestDate datetime,
> > >> >> > TestValue char(1)
> > >> >> > )
> > >> >> >
> > >> >> > INSERT INTO #Test (TestDate,TestValue)VALUES ('8/25/2006','A')
> > >> >> >
> > >> >> > SELECT * FROM #Test
> > >> >> > DROP TABLE #Test
> > >> >> >
> > >> >> >
> > >> >> > Desired results:
> > >> >> >
> > >> >> > 8/20/2006,A
> > >> >> > 8/21/2006,A
> > >> >> > 8/22/2006,A
> > >> >> > 8/23/2006,A
> > >> >> > 8/24/2006,A
> > >> >> > 8/25/2006,A
> > >> >> > 8/26/2006,A
> > >> >> > 8/27/2006,A
> > >> >> > 8/28/2006,A
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >
> > >> >
> > >>
> > >>
> > >>

AddThis Social Bookmark Button

Post Other interesting topics
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..
Iterating through SQL Server data