Adding a sysdate to a tablename

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksUse dynamic Sql within pl/sql:

l_statement varchar2(100);
begin
l_statement:=' create table name_'||to_char(sysdate,'DDMMRRRR');
l_statement:=l_statement||define colums here

execute immediate l_statement;
end;

eventually you must append an ; at the end of the varchar2 because it is an DDL-Statement

Adding a sysdate to a table

Hi basically Im creating a package and I need to back up the table and put the date in the name of the table whenever the package is run, however I get an error when I try and do it, I think it should be possible but im not sure:

SELECT *
INTO tablename + CONVERT(char(10), GETDATE(), 112)
FROM tablename

So the tablename should looksomething like this

tablename20031210

The error message is: incorrect syntax near +

Can anybody help.

ThanksMaybe...

Declare @.Query nVarchar(1000)

SET @.Query=N'Select * From ' + 'tablename' + CONVERT(char(10), GETDATE(), 112)

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35|||Thanks a lot this has been driving me crazy. I had to modify the code slightly but it works fine.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'Select * INTO ' + 'tablename' + CONVERT(char(10), GETDATE(), 112) + 'FROM tablename' EXECUTE sp_executesql @.Query, N'@.level tinyint',
@.level = 35

Adding a day to a date.

Pls help if you can. I have two dates, date1 and date2.
Basically I want to set date2 = date1 + 1 day for a range of dates
that I can select out.

Is there any way to do this?"Chachu" <cpatel1@.gmail.com> wrote in message
news:d7c4c47b.0410050757.6d4bc3a1@.posting.google.c om...
> Pls help if you can. I have two dates, date1 and date2.
> Basically I want to set date2 = date1 + 1 day for a range of dates
> that I can select out.
> Is there any way to do this?

See DATEADD in Books Online:

update dbo.MyTable
set date2 = dateadd(dd, 1, date1)
where ...

Simon

add another report to the existing report

Hi All,

I have one report with 3 graphs on it. Now I want to add another report to the existing report. So basically I have seperate reports and I want to add all together that way when I do the preview it will show may be 3 of 1 page, 3 of 2 and 3 of 3 pages. Please let me know the process how to add the reports.

Thanks

Rozarm02

You would use the sub-report control in the toolbox. Right click on it and open up the properties window. There are several tab on that dialog that allow you to select the report, set parameters, etc.

BobP

Actuarial sql statement Need Help.

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods. Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer
>
does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.

Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have
>
a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed
>
12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.

Here is a query from Northwind to illustrate:

SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this

Bill Table
- billNumber
- fileDate

Transaction Table
- billNumber
- payDate
- amount

Currently I am trying this sql statement

SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

>
The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.
>
Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.
>

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

>
Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.
>
Here is a query from Northwind to illustrate:
>
SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12 and I
simply do a order by....my problem now is when a payment is made
before a 12 month marker. This causes the datediff to return a 0/12 *
12 causing my report to produce a "0" column. I will be working on it
some more ...i will keep to up on any progress that i make.

Twobridge wrote:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this
>
Bill Table
- billNumber
- fileDate
>
Transaction Table
- billNumber
- payDate
- amount
>
Currently I am trying this sql statement
>
SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer
>
does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.

The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.

Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.

Quote:

Originally Posted by

Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have
>
a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed
>
12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500

Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.

Here is a query from Northwind to illustrate:

SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
my problem....3/ 12 = 0 * 12...so now my column heading has a value
of 0 when in reality it should be grouped as a payment made in the
first 12 months. I hope this makes since.

Twobridge wrote:

Quote:

Originally Posted by

I have found a way to generate the number for the columns by using
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12 and I
simply do a order by....my problem now is when a payment is made
before a 12 month marker. This causes the datediff to return a 0/12 *
12 causing my report to produce a "0" column. I will be working on it
some more ...i will keep to up on any progress that i make.
>
Twobridge wrote:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this

Bill Table
- billNumber
- fileDate

Transaction Table
- billNumber
- payDate
- amount

Currently I am trying this sql statement

SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)

You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:
I hope someone can help me out with my problem.
I have found a sql statement that basically pulls all bills filed
within a
certain time period and the payments made on those bills with in the
same time period. I group the payments by payment year and filed year
which gives me a matrix with the filed year as the row and the pay
year
as the column...and this appears fine. My problem is that my employer

does not want to see the payment year as the column header. He would
like to see the number of months from the bill year in sequence of 12
months periods.
>
The column names in a query are fixed, so you would just name the columns
12, 24, 36 etc.
>
Sorry, that is not a very good answer, but not knowing your query or
anything it's the best I can say.
>
Also I have run into a problem that if I narrow down
my search to display only bills from a certain department. We may have

a instance where that department will not have any payments for that
time period.
My employer would like to see a zero in that spot on the matrix. My
sql statement
would simply skip that year and display the next years value. Any
suggestions? Someone else suggested the ISNULL but since there are no
payments for that year there would be no null to replace. This is a
sample of how they want it displayed

12 24 36
1985 $50 $100 $200
1986 $0 $120 $40
1987 $0 $0 $500
>
Set up a one-column table with the years (or just get them from the
base table as (SELECT DISTINCT year FROM tbl) in a derived table.
The left join that table with the rest.
>
Here is a query from Northwind to illustrate:
>
SELECT m.month, nooforders = coalesce(o.cnt, 0)
FROM (SELECT DISTINCT month = convert(char(6), OrderDate, 112)
FROM Orders) AS m
LEFT JOIN (SELECT month = convert(char(6), OrderDate, 112),
cnt = COUNT(*)
FROM Orders
WHERE CustomerID = 'BERGS'
GROUP BY convert(char(6), OrderDate, 112)) AS o
ON m.month = o.month
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

I apologize for not getting back to you sooner, I do appreciate your
help. My database tables look like this
>
Bill Table
- billNumber
- fileDate
>
Transaction Table
- billNumber
- payDate
- amount
>
Currently I am trying this sql statement
>
SELECT SUM(Transaction.amount) AS [Amount Paid],
YEAR(Bill.fileDate) AS [Date Filed], YEAR(Transaction.payDate) AS
[Year Paid]
FROM Bill LEFT OUTER JOIN
Transaction ON Bill.billNumber =
Transaction.billNumber
GROUP BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
HAVING (YEAR(Bill.fileDate) BETWEEN 1985 AND 1990) AND
(YEAR(Transaction.payDate) BETWEEN 1985 AND 1990)
ORDER BY YEAR(Bill.fileDate), YEAR(Transaction.payDate)
>
You mentioned just simply naming the columns, the problem with that
solution is that the number of columns are different each
time...depending on the user query. My user can query by year. In the
statement I currently just hard coded in a year to test.

If the number of columns or the column names are different depending
on user input, the query must be constructed dynamically. A query in
SQL returns a fixed number of columns with fixed names. This because
SELECT returns a table, and a table is supposed to describe an entity
with a fixed set of attributues.

Since it appears that you want to run a crosstab, you should have a look
at RAC, a third-party which is good for that sort of things. See
http://www.rac4sql.com/.

Quote:

Originally Posted by

Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
my problem....3/ 12 = 0 * 12...so now my column heading has a value
of 0 when in reality it should be grouped as a payment made in the
first 12 months. I hope this makes since.

I'm afraid that I did not understand much. It seems that you have not
decided what output your query should give in this case, and this is
nothing we can assist you with in a newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Twobridge (Twobridge@.gmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Sorry let me clarify...if a bill's file date is 1/1/1985 and is paid
>on 3/3/1985 this causes the datediff function to produce a 3/12 * 12 in
>my problem....3/ 12 = 0 * 12...so now my column heading has a value
>of 0 when in reality it should be grouped as a payment made in the
>first 12 months. I hope this makes since.

Quote:

Originally Posted by

I'm afraid that I did not understand much. It seems that you have not
decided what output your query should give in this case, and this is
nothing we can assist you with in a newsgroup.

Surely "first 12 months" ought to be followed by "second 12 months"
etc., and so
datediff(month, bill.fileDate, transaction.payDate)/ 12 * 12
should be changed to
(datediff(month, bill.fileDate, transaction.payDate)+11)/ 12 * 12