Producing week ending dates for each week of the year

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

Quote:

Originally Posted by rcr69er

Hi

I am trying to produce a script that will show date for the last day of the week(Sunday). The script will have to show dates for all weeks of the year. Does anyone know how this can be done?

Thanks

hai try this-----------
declare
v_date date;
begin
select next_day('01-JAN-07','sunday') into v_date from dual;
for i in 0..51 loop
dbms_output.put_line(to_char(to_date((v_date)+7*i) ));
end loop;
end;sql

Producing Text File

Hello all,
My sql skills are pretty poor, so please bear with me!

I need to take info from two tables (in Access db) and produce two txt files.

The data in the first table is listed as follows: OrderID, OrderDate, CustomerID, Total
The data in the second table is listed as follows: OrderID, Quantity, Discount, UnitPrice

The user has to be able to specify a time frame (start date and end date).

The major problem I'm having is with setting up the txt files. The header has to have the OrderID listed as "!!OrderID" (this is the way the fake company has their db set up apparently) Also, in the actual data in the txt files the OrderDate has to start w/ "#" and the CustomerID has to start w/ "&".

here's what I mean:

!!OrderID OrderDate CustomerID Total
--- --- ---- --
123456 #456789 &abcdef 987654

If you can help me at all, I'd much appreciate it!!! Thank you!Well, in MS Access you can't use "!" in column name, so you'll have to handle this using VBA coding and replace column name when writing into the text file. You didn't describe tables relationship.

SELECT
[OrderID],
"#" + [OrderDate] AS [OrderDate],
"&" + [CustomerID] AS [CustomerID],
[Total]
from
your_table;

store the result into some VBA object (I don't remember which one to use, it's long time since I used Access for last time). Open text file and read row by row from VBA object and write it into the file. Replace column name OrderID with !!OrderID|||Thanks for the help. The tables are linked through OrderID. Also, I found out that I don't have to use Access to do the project. MySQL is allowed as well. If that makes a difference w/ the !! problem, let me know.

Thanks again!

Producing several letters based on the results of a query

I have created a single page letter using a number of text boxes and one
table, which is populated with static text along with some data from a single
row results set. This works perfectly when the results set only contains one
row, but when there are multiple data rows, the report still only contains
one page, whereas I expected there to be a page for every row.
A simple way to reproduce this is to create a report with a single text box
containing the value of one column from a query that returns multiple rows.
This results in a one page report, rather than a page for each row returned.
I'd be grateful if someone can help with this - I'm hoping there's a simple
solution, like setting a 'repeating region' property.
By the way, I've tried creating a single column table in the hope I could
put the entire contents of the letter into a cell of the table and that the
table row (and therefore the letter) would then repeat for each row of data,
but it doesn't appear to be possible to fix the size of a table row and have
multiple text boxes (and a table) inside it.
Thanks in advance,
David.Hey David,
If I understand you correctly, your trying to make, basically, a form
letter, that populates the variables from a query. For example
This is my test letter to "DAVID".
Where "DAVID" is generated from column/row in your Dataset. Why can't you
make a single cell table with all the text as you want it, except insert the
Field references you need (instead of textboxes)? For Example, the cell
would look like:
= "This is my test letter to " & Fields!ContactName.Value & ". " &
Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
etc...
Once this is done, you choose the "Group" properties and place a page break
after each group (or row in this case). This creates a new letter, each on
its own pages, for each row returned.
Michael C
"David C" wrote:
> I have created a single page letter using a number of text boxes and one
> table, which is populated with static text along with some data from a single
> row results set. This works perfectly when the results set only contains one
> row, but when there are multiple data rows, the report still only contains
> one page, whereas I expected there to be a page for every row.
> A simple way to reproduce this is to create a report with a single text box
> containing the value of one column from a query that returns multiple rows.
> This results in a one page report, rather than a page for each row returned.
> I'd be grateful if someone can help with this - I'm hoping there's a simple
> solution, like setting a 'repeating region' property.
> By the way, I've tried creating a single column table in the hope I could
> put the entire contents of the letter into a cell of the table and that the
> table row (and therefore the letter) would then repeat for each row of data,
> but it doesn't appear to be possible to fix the size of a table row and have
> multiple text boxes (and a table) inside it.
> Thanks in advance,
> David.|||Thanks Michael - it was the grouping that I needed. I actually stumbled
across the same answer last night in a TechNet article
(http://technet.microsoft.com/en-us/library/ms155816.aspx).
I put a List in first and then added a single column Table to it. I then
created a group expression on the list in Properties - General - Edit Details
Group - General, as follows: -
=Ceiling(RowNumber(Nothing)/1)
I also set 'Page break at end'.
Is this what you meant?
My problem now is that I want to make some of the text bold and I thought
I'd be able to do this using the Format command - something like Format("some
text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
that?
Thanks again.
David.
"Michael C" wrote:
> Hey David,
> If I understand you correctly, your trying to make, basically, a form
> letter, that populates the variables from a query. For example
> This is my test letter to "DAVID".
> Where "DAVID" is generated from column/row in your Dataset. Why can't you
> make a single cell table with all the text as you want it, except insert the
> Field references you need (instead of textboxes)? For Example, the cell
> would look like:
> = "This is my test letter to " & Fields!ContactName.Value & ". " &
> Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> etc...
> Once this is done, you choose the "Group" properties and place a page break
> after each group (or row in this case). This creates a new letter, each on
> its own pages, for each row returned.
> Michael C
> "David C" wrote:
> > I have created a single page letter using a number of text boxes and one
> > table, which is populated with static text along with some data from a single
> > row results set. This works perfectly when the results set only contains one
> > row, but when there are multiple data rows, the report still only contains
> > one page, whereas I expected there to be a page for every row.
> >
> > A simple way to reproduce this is to create a report with a single text box
> > containing the value of one column from a query that returns multiple rows.
> > This results in a one page report, rather than a page for each row returned.
> >
> > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > solution, like setting a 'repeating region' property.
> >
> > By the way, I've tried creating a single column table in the hope I could
> > put the entire contents of the letter into a cell of the table and that the
> > table row (and therefore the letter) would then repeat for each row of data,
> > but it doesn't appear to be possible to fix the size of a table row and have
> > multiple text boxes (and a table) inside it.
> >
> > Thanks in advance,
> >
> > David.|||Hey David,
Unfortunately I do not think there is a way, and if there i do not know
it. I had asked the forum recently about 2 fonts, 1 control but got no
responses.
You have done basically what I was describing. Glad it (sort of) worked for
you.
Michael
"David C" wrote:
> Thanks Michael - it was the grouping that I needed. I actually stumbled
> across the same answer last night in a TechNet article
> (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> I put a List in first and then added a single column Table to it. I then
> created a group expression on the list in Properties - General - Edit Details
> Group - General, as follows: -
> =Ceiling(RowNumber(Nothing)/1)
> I also set 'Page break at end'.
> Is this what you meant?
> My problem now is that I want to make some of the text bold and I thought
> I'd be able to do this using the Format command - something like Format("some
> text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> that?
> Thanks again.
> David.
>
> "Michael C" wrote:
> > Hey David,
> > If I understand you correctly, your trying to make, basically, a form
> > letter, that populates the variables from a query. For example
> >
> > This is my test letter to "DAVID".
> >
> > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > make a single cell table with all the text as you want it, except insert the
> > Field references you need (instead of textboxes)? For Example, the cell
> > would look like:
> >
> > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > etc...
> >
> > Once this is done, you choose the "Group" properties and place a page break
> > after each group (or row in this case). This creates a new letter, each on
> > its own pages, for each row returned.
> >
> > Michael C
> >
> > "David C" wrote:
> >
> > > I have created a single page letter using a number of text boxes and one
> > > table, which is populated with static text along with some data from a single
> > > row results set. This works perfectly when the results set only contains one
> > > row, but when there are multiple data rows, the report still only contains
> > > one page, whereas I expected there to be a page for every row.
> > >
> > > A simple way to reproduce this is to create a report with a single text box
> > > containing the value of one column from a query that returns multiple rows.
> > > This results in a one page report, rather than a page for each row returned.
> > >
> > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > solution, like setting a 'repeating region' property.
> > >
> > > By the way, I've tried creating a single column table in the hope I could
> > > put the entire contents of the letter into a cell of the table and that the
> > > table row (and therefore the letter) would then repeat for each row of data,
> > > but it doesn't appear to be possible to fix the size of a table row and have
> > > multiple text boxes (and a table) inside it.
> > >
> > > Thanks in advance,
> > >
> > > David.|||Hi Michael,
The text I wanted to be in bold was on a separate line, so in the end I just
put in a separate row in the table and set the row to bold. This worked fine,
presumably because the List control was handling the grouping.
Thanks again for your help.
David.
"Michael C" wrote:
> Hey David,
> Unfortunately I do not think there is a way, and if there i do not know
> it. I had asked the forum recently about 2 fonts, 1 control but got no
> responses.
> You have done basically what I was describing. Glad it (sort of) worked for
> you.
> Michael
> "David C" wrote:
> > Thanks Michael - it was the grouping that I needed. I actually stumbled
> > across the same answer last night in a TechNet article
> > (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> >
> > I put a List in first and then added a single column Table to it. I then
> > created a group expression on the list in Properties - General - Edit Details
> > Group - General, as follows: -
> >
> > =Ceiling(RowNumber(Nothing)/1)
> >
> > I also set 'Page break at end'.
> >
> > Is this what you meant?
> >
> > My problem now is that I want to make some of the text bold and I thought
> > I'd be able to do this using the Format command - something like Format("some
> > text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> > that?
> >
> > Thanks again.
> >
> > David.
> >
> >
> >
> > "Michael C" wrote:
> >
> > > Hey David,
> > > If I understand you correctly, your trying to make, basically, a form
> > > letter, that populates the variables from a query. For example
> > >
> > > This is my test letter to "DAVID".
> > >
> > > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > > make a single cell table with all the text as you want it, except insert the
> > > Field references you need (instead of textboxes)? For Example, the cell
> > > would look like:
> > >
> > > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > > etc...
> > >
> > > Once this is done, you choose the "Group" properties and place a page break
> > > after each group (or row in this case). This creates a new letter, each on
> > > its own pages, for each row returned.
> > >
> > > Michael C
> > >
> > > "David C" wrote:
> > >
> > > > I have created a single page letter using a number of text boxes and one
> > > > table, which is populated with static text along with some data from a single
> > > > row results set. This works perfectly when the results set only contains one
> > > > row, but when there are multiple data rows, the report still only contains
> > > > one page, whereas I expected there to be a page for every row.
> > > >
> > > > A simple way to reproduce this is to create a report with a single text box
> > > > containing the value of one column from a query that returns multiple rows.
> > > > This results in a one page report, rather than a page for each row returned.
> > > >
> > > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > > solution, like setting a 'repeating region' property.
> > > >
> > > > By the way, I've tried creating a single column table in the hope I could
> > > > put the entire contents of the letter into a cell of the table and that the
> > > > table row (and therefore the letter) would then repeat for each row of data,
> > > > but it doesn't appear to be possible to fix the size of a table row and have
> > > > multiple text boxes (and a table) inside it.
> > > >
> > > > Thanks in advance,
> > > >
> > > > David.

Producing reports on a web page

Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
reading up on MS SQL Reporting Services and from what I can see they offer a
report repository as well as a a web based portal for downloading the reports.
The company I work for is currently using the following:
â?¢ Webserver running IIS
â?¢ SQL Server 2000
â?¢ Coldfusion MX 7.0 Server
â?¢ Crystal Enterprise Server 10.0
The current set-up we have is to produce the report in a web page (not
necessarily through a proprietary portal or anything like that).
Crystal Reports no longer supports this functionality in version 11. Does
anyone here know if it's possible for me to be able to create and produce a
report and then display it on a web page using Cold Fusion? This would
include being able to create a subreport and passing it parameters with
stored procedures.
Thanks!
ToddNot sure how Cold Fusion works but there are two ways you can integrate with
Report Services (assuming you are not using the Portal it ships with). One
is to use URL integration and the other is web services. With VS 2005 there
are two new controls (webform and winform) that integrate with RS 2005 using
webservices. Very good and very tight integration. They require 2.0
framework.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> reading up on MS SQL Reporting Services and from what I can see they offer
> a
> report repository as well as a a web based portal for downloading the
> reports.
> The company I work for is currently using the following:
> . Webserver running IIS
> . SQL Server 2000
> . Coldfusion MX 7.0 Server
> . Crystal Enterprise Server 10.0
> The current set-up we have is to produce the report in a web page (not
> necessarily through a proprietary portal or anything like that).
> Crystal Reports no longer supports this functionality in version 11. Does
> anyone here know if it's possible for me to be able to create and produce
> a
> report and then display it on a web page using Cold Fusion? This would
> include being able to create a subreport and passing it parameters with
> stored procedures.
>
> Thanks!
> Todd|||Thanks Bruce, I really appreciate the response.
This is how they currently do it:
Can you tell me if it is at all possible to do this with Microsoft Reporting
services? They are asking me to do the research and find out if MS SQL is a
viable solution.
-----
This document has been created demonstrating how we use Crystal Reports in
real life for reporting. It would be great if Cold Fusion reports could
produce the same result in the near future. Not only would it relive us of
many yearly fees from Business Objects, but it would rededicate our future to
ColdFusion and Macromedia/Adobe.
We currently use Crystal Reports Developer 10 and XI to develop reports that
get generated and displayed in our software on the fly. This software is
driven by ColdFusion MX 7.0. The reports are built upon stored procedures
that accept variables and produce different results.
Crystal Developer lets you browse the available stored procedures and select
the one you would like to use:
After you select which stored procedure you would like to use, the next
screen automatically asks you for some sample data to pass to the SP
variables:
Once the report has been attached to the stored procedure, the rest is just
creating the report. The field explorer on the shows which fields are
available from the stored procedure:
Fields are dragged on and the report is saved. Displaying the report can be
done by using some simple javascript. So we have the flexibility to have
whatever we want on the screen, and the report to be â'embeddedâ' in our
application.
We created a header and a footer to wrap around the parameters because those
donâ't change.
In our attempts to move over to the early version of ColdFusion reports,
this process has been real clumsy. The stored procedures donâ't show up on
the database browser and the only way we can get them to execute is using the
exec <stored procedure> in the query window. You can forget about passing
variables to it. It would be great if a similar process could be duplicated
with ColdFusion reports.
Thanks!!!
Todd
"Bruce L-C [MVP]" wrote:
> Not sure how Cold Fusion works but there are two ways you can integrate with
> Report Services (assuming you are not using the Portal it ships with). One
> is to use URL integration and the other is web services. With VS 2005 there
> are two new controls (webform and winform) that integrate with RS 2005 using
> webservices. Very good and very tight integration. They require 2.0
> framework.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > reading up on MS SQL Reporting Services and from what I can see they offer
> > a
> > report repository as well as a a web based portal for downloading the
> > reports.
> >
> > The company I work for is currently using the following:
> >
> > . Webserver running IIS
> > . SQL Server 2000
> > . Coldfusion MX 7.0 Server
> > . Crystal Enterprise Server 10.0
> >
> > The current set-up we have is to produce the report in a web page (not
> > necessarily through a proprietary portal or anything like that).
> >
> > Crystal Reports no longer supports this functionality in version 11. Does
> > anyone here know if it's possible for me to be able to create and produce
> > a
> > report and then display it on a web page using Cold Fusion? This would
> > include being able to create a subreport and passing it parameters with
> > stored procedures.
> >
> >
> > Thanks!
> >
> > Todd
>
>|||The process here is just how the report is created by the developer? I.e.
this is not happening by an end user is it?
What you would do with RS is to create the report using your stored
procedures. SP are fully supported as long as the SP returns a single
resultset. Multiple resultsets are not supported. Once you have the report
working then you use either URL integration or webservices and integrate it
with your web pages.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <ToddJaspers@.discussions.microsoft.com> wrote in message
news:8E94C9CC-0C74-4F60-AA08-C02CA08C71DB@.microsoft.com...
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft
> Reporting
> services? They are asking me to do the research and find out if MS SQL is
> a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us
> of
> many yearly fees from Business Objects, but it would rededicate our future
> to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports
> that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and
> select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is
> just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can
> be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be "embedded" in our
> application.
> We created a header and a footer to wrap around the parameters because
> those
> don't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures don't show up on
> the database browser and the only way we can get them to execute is using
> the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be
> duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
>> Not sure how Cold Fusion works but there are two ways you can integrate
>> with
>> Report Services (assuming you are not using the Portal it ships with).
>> One
>> is to use URL integration and the other is web services. With VS 2005
>> there
>> are two new controls (webform and winform) that integrate with RS 2005
>> using
>> webservices. Very good and very tight integration. They require 2.0
>> framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
>> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
>> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've
>> > been
>> > reading up on MS SQL Reporting Services and from what I can see they
>> > offer
>> > a
>> > report repository as well as a a web based portal for downloading the
>> > reports.
>> >
>> > The company I work for is currently using the following:
>> >
>> > . Webserver running IIS
>> > . SQL Server 2000
>> > . Coldfusion MX 7.0 Server
>> > . Crystal Enterprise Server 10.0
>> >
>> > The current set-up we have is to produce the report in a web page (not
>> > necessarily through a proprietary portal or anything like that).
>> >
>> > Crystal Reports no longer supports this functionality in version 11.
>> > Does
>> > anyone here know if it's possible for me to be able to create and
>> > produce
>> > a
>> > report and then display it on a web page using Cold Fusion? This would
>> > include being able to create a subreport and passing it parameters with
>> > stored procedures.
>> >
>> >
>> > Thanks!
>> >
>> > Todd
>>|||Hi Todd,
I assume you know Cold fussion ? Does cold fusion, uses web services,
if yes, you can easily..integrate the way of reporting services or crystal
report
to be see in ur portal.
Why crystal report is not supported in the web in Version 11'who said this
regards
"Todd Jaspers" wrote:
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft Reporting
> services? They are asking me to do the research and find out if MS SQL is a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us of
> many yearly fees from Business Objects, but it would rededicate our future to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be â'embeddedâ' in our
> application.
> We created a header and a footer to wrap around the parameters because those
> donâ't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures donâ't show up on
> the database browser and the only way we can get them to execute is using the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
> > Not sure how Cold Fusion works but there are two ways you can integrate with
> > Report Services (assuming you are not using the Portal it ships with). One
> > is to use URL integration and the other is web services. With VS 2005 there
> > are two new controls (webform and winform) that integrate with RS 2005 using
> > webservices. Very good and very tight integration. They require 2.0
> > framework.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> > news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > > reading up on MS SQL Reporting Services and from what I can see they offer
> > > a
> > > report repository as well as a a web based portal for downloading the
> > > reports.
> > >
> > > The company I work for is currently using the following:
> > >
> > > . Webserver running IIS
> > > . SQL Server 2000
> > > . Coldfusion MX 7.0 Server
> > > . Crystal Enterprise Server 10.0
> > >
> > > The current set-up we have is to produce the report in a web page (not
> > > necessarily through a proprietary portal or anything like that).
> > >
> > > Crystal Reports no longer supports this functionality in version 11. Does
> > > anyone here know if it's possible for me to be able to create and produce
> > > a
> > > report and then display it on a web page using Cold Fusion? This would
> > > include being able to create a subreport and passing it parameters with
> > > stored procedures.
> > >
> > >
> > > Thanks!
> > >
> > > Todd
> >
> >
> >

producing a date time report in SQL/DTS

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every w.
What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.
I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.
Any ideas?
Thanks,
BC"Blasting Cap" schrieb:
> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BC
Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday|||Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:eiUP1w9CGHA.4080@.TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BCsql