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 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

produce SQL statement for a database

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)(for SQL Server 2000...and maybe 7.0)

In the SQL Query Analyzer Object Browser, drill down to your table, right-click on the table name and select "Script Object...As" and it will generate the SQL for you.

Additionally, in Enterprise Manager, drill down to your "Tables" and select the table you want to query. Right-click on the table, Select Open Table --> Query. You SQL statement is generated dynamically as you select fields. A Select statement is the default. In the gray area where you see the table and all of it's fields, Right-click and there is a selection for "Change Type." In there you can change the query to an Insert, Create, etc...|||I prefer to build my own..

USE NorthWind

DECLARE @.TBName sysname, @.SQL varchar(8000)

SELECT @.TBName = 'Cust', @.SQL = ''

SELECT @.SQL = @.SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @.SQL

--EXEC(@.SQL)

Produce data -> Store in table?

Hi!

I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..

Date
Year
Quarter
Month
WeekDay
...

With some precalculated values for each date.

I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.

My first thought is to use some kind of Insert Cursor in a While loop...

Pseudo:

declare cursor ex for 'Insert table ... '

while
begin

(produce data)
insert data

end

close cursor

While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...

Have a nice day!

You could use WHILE loop without cursor:

Code Snippet

create table dates

(

Year int,

Quarter int,

Month int,

WeekDay int,

SomeData decimal

)

go

declare @.start_date datetime

declare @.end_date datetime

set @.start_date = '2007-01-01'

set @.end_date = getdate()

while @.start_date<@.end_date

begin

set @.start_date = dateadd(day, 1, @.start_date)

insert into dates values(

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

rand() --Just for demo

)

end

go

select * from Dates

|||

Best have a calendar table (you can add all the nessasary columns here), fill the table with required date range, join with your table & use the required calculations,

Code Snippet

create table calendar

(

year int,

quarter int,

month int,

weekday int,

date datetime primary key

)

Go

create proc fill_calendar(@.start_date datetime,@.end_date datetime)

as

begin

set nocount on;

while @.start_date <= @.end_date

begin

insert into calendar

select

datepart(year,@.start_date),

datepart(quarter,@.start_date),

datepart(month,@.start_date),

datepart(weekday,@.start_date),

@.start_date

where

not exists(select 1 from calendar where date=@.start_date);

set @.start_date = dateadd(day, 1, @.start_date)

end

end

go

--Fill your calendar when required

exec fill_calendar '1/1/2000','12/31/2007'

select * from calendar

go

select

year --other columns

,sum(yourtable.calculation)

from

calendar

inner join yourtable on yourtable.datefield = calendar.date

group by

year --other columns

Process a Mining Model Programmatically

I have tried to process a data mining model that I have produce programmatically. I use the following code for processing

Private Sub BtProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtProcess.Click

' Now some different styles of processing

'Process mining structure first - don't process the models though

' ms.Process(ProcessType.ProcessStructure)

ms.Process(ProcessType.ProcessStructure)

DeclareBox.Text = "Process Data Mining Structure"

' Process the mining model only (because the structure has been processed above

' We could have said ProcessDefault without processing the structure first, though

mm.Process(ProcessType.ProcessDefault)

DeclareBox.Text = "Process Data Mining Model"

End Sub

I use the following code for data mining model and is correctly designed

Private Sub CreateMiningModel()

Dim mc As MiningModelColumn

mm = ms.MiningModels.Add(CbDatabase.Text & " miningmodelName") ', Utils.GetSyntacticallyValidID(miningmodelName, Type.GetType(MiningModel)))

mm.Algorithm = "Microsoft_Decision_Trees"

mm.AlgorithmParameters.Add("COMPLEXITY_PENALTY", 0.3)

mc = New MiningModelColumn("SUMCODE", "SUMCODE")

mc.SourceColumnID = ms.Columns("SUMCODE").ID

mc.Usage = "Key"

mm.Columns.Add(mc)

mc = New MiningModelColumn("STAFF_YES", "STAFF_YES")

mc.SourceColumnID = ms.Columns("STAFF_YES").ID

mc.Usage = "Input"

mm.Columns.Add(mc)

mc = New MiningModelColumn("D_G_OZODIS_IPER", "D_G_OZODIS_IPER")

mc.SourceColumnID = ms.Columns("D_G_OZODIS_IPER").ID

mc.Usage = "PredictOnly"

mm.Columns.Add(mc)

and I received the following error message. Any Ideas? What is wrong with this? Thank you in advance

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>test27</DatabaseID>
<MiningStructureID>test27 MiningStructureName</MiningStructureID>
<MiningModelID>test27 miningmodelName</MiningModelID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Mining Structure 'test27 MiningStructureName' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension 'test27 MiningStructureName ~MC-SUMCODE' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute 'OZOS NUM MANY' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[VISIT].[OZOS_NUM_MANY] AS [VISITOZOS_NUM_MANY0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Processing Dimension Attribute 'STAFF_YES' failed. 1 rows have been read.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT [VISIT].[STAFF_YES] AS [VISITSTAFF_YES0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Processing Dimension Attribute 'D_G_OZODIS_IPER' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Errors and Warnings from Response
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported..
Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OZOS NUM MANY' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'D_G_OZODIS_IPER' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.

Manolis

There seems to be an error converting the STAFF_YES values to the mining structure column type. What is the data type (in the database) for this column and what is the Mining Structure column type for this column?

|||

I thing that you are correct

In the database STAFF_YES is of type bit and in the mining structure is a text. I tried to convert the type in the mining structure to bit but I did not find such a type. How can I do that programmatically? Do I have to change anything to the database? I hope not

Thank you in advance

|||

I tried this code

mc = New MiningModelColumn("STAFF_YES", STAFF_YES, Type.GetType(MiningModelColumn)))

I received an error in MiningModelColumn, what is the problem with MiningModelColumn. Is there any other code?

Thank you in advance

Manolis

|||

Bit should work fine when represented (in the mining structure) as Boolean.

Can you try to change the structure column type to boolean and then retrain your model? The data type is specific to the mining structure column (not the mining model column)

|||

Thank you very much your advice worked perfectly.

My problem now is to get the type programmatically and not type OleDbType.Boolean by me

Can I try the following code?

Seems that does not work?

What do I have to use for the ScalarMiningStructureColumn

smsc = New ScalarMiningStructureColumn("D_G_OZODIS_IPER") , Utils.GetSyntacticallyValidID("VISIT"), Type.GetType(ScalarMiningStructureColumn)))

smsc.IsKey = False

smsc.Content = "Discrete"

smsc.KeyColumns.Add("VISIT", "D_G_OZODIS_IPER", OleDbType.Boolean)

ms.Columns.Add(smsc)

Thank you in advance

Manolis