Creating an Indexed View

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

The first error message is as direct as it can be. You cannot

create a view WITH SCHEMABINDING when its definition mentions

another view that was not itself created WITH SCHEMABINDING.

In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

with schemabinding because it refers to another view,

'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with

schemabinding.

There does seem to be a bit of confusion. The code you pasted

here tries to do two things:

1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

2. Create an index on 'VEW_F_MZT_ORDER_HEADER',

which is a *different* view.

The second error you got (Cannot create index...)

has nothing at all to do with the first error or with

the CREATE VIEW code that generates the first error. It's

also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'

exists, but it was not created with schemabinding, which

is a requirement for creating an index on a view.

If you want to index a view, that view and all the views

(and functions) on which it depends must be created with

schemabinding and meet the requirements for that option.

Steve Kass

Drew University

http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:

> I am trying to create an indexed view, on a date from a date dimension

> table...I am new to SQL, and I am at a loss of ideas on this one. Any

> help would be greatly appreciated!

>

> Here is the Error I am given

>

> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line

> 3

>

> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.

> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

>

> Msg 1939, Level 16, State 1, Line 1

>

> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is

> not schema bound."

>

> Here is my code..

>

> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

>

> AS

>

> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

>

> TEW_D_LOC.LOC_DESC AS LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC AS REGION_DESC,

>

> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

>

> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

>

> COUNT_BIG(*) AS COUNT

>

> FROM

>

> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

>

> ON

>

> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

>

> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =

> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =

> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

>

> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

>

> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

>

> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

>

> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

>

> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

>

> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

>

> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

>

> INNER JOIN

>

> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

>

> ON

>

> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

>

> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

>

> INNER JOIN

>

> JJWHSE.TEW_D_DT TEW_D_DT

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

>

> INNER JOIN

>

> JJWHSE.TEW_D_LOC TEW_D_LOC

>

> ON

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

>

> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

>

> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,

> TEW_D_LOC.LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

>

> GO

>

> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON

> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

>

>

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating a trace file with the date appended to its name

I'm trying to create a trace file with the file as part of it's name.
For example, I'd like to create a file called FailedLogins-20050428. So
far I haven't been able to figure out how to get the name of the file
and the date together (I'm sill very new to SQL Server and tracing).

What I've done is:
declare @.rc int
declare @.traceid int
declare @.maxfilesize bigint
set @.maxfilesize = 50
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins', @.maxfilesize=@.maxfilesize,
@.stoptime=NULL
if @.rc > 0 print 'sp_trace_code failed with error code ' +
rtrim(cast(@.rc as char))
else print 'traceid for the trace is ' + rtrim(cast(@.traceid as char))

I can create a trace file on C drive without difficulty. I've tried
creating a file like this:
exec @.rc=sp_trace_create @.traceid=@.traceid output, @.options=0,
@.tracefile=N'C:\trace\failedlogins + convert (varchar,getdate(),112',
@.maxfilesize=@.maxfilesize, @.stoptime=NULL

But what I end up created is a file on C called
failedlogins + convert(varchar,getdate(),112).trc

I have no doubt what I want to do can be done. I just done know how to
do it.

If anyone could tell me where I'm going wrong, I'd really appreciate
it.

Thanks in advance.Hiya Bill,

Try this in Query Analyzer.. @.tracefile is your variable,
@.tracefile_new is the proposed fix. Take note of the @.tracefile_new
output..

declare @.tracefile varchar(1000),
@.tracefile_new varchar(1000)

set @.tracefile=N'C:\trace\failedlogins + convert
(varchar,getdate(),112'
set @.tracefile_new=N'C:\trace\failedlogins_' + convert
(varchar,getdate(),112)

print @.tracefile

print @.tracefile_new|||Thanks Greg! It works great. Just the way I wanted it.

Creating a string from Date Fields

I have a table with a startdatetime and an enddatetime column such as:

StartDateTime EndDateTime what I want to see returned
is:
01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
1:30PM"
01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
10:30AM"

Maybe someone has accomplished this aready in a stored procedure and
has an example of how to do it?
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?

Looks like you need to use the following T-SQL functions/operators:

convert() - to format the date.
substring() - to extract the portions of the end time you want to display
CASE - to determine whether all of or just part of endtime is to be
included.

Then again, a lot of these display issuses are often best handled client
side.

The above-mentioned functions are all documented in Books Online, see
the T-SQL Reference. convert() may be tricky to find, as it is under
the topic CAST and CONVERT.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401291443.47b9d2c8@.posting.google.c om...
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?
> lq

You could do this with CONVERT() and various string functions, but it would
be better to use your client application to handle this. The dates above are
not correct for most European formats, for example, and it's much easier to
deal with client locale settings in a client-side application.

Simon

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
.. ... ... 4
Howto achieve it?
TIA
Ana
That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>
|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
--
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribió en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Sorry, I didn't explain myself well. The result of a query is a ranking
>> based on customers' sales.
>> The fields from a table are:
>> Cust_ID
>> Sales
>> Date (European format)
>> The query generates the following results:
>> Cust_ID Sales Date
>> 526 12.350 12/5/2007
>> 632 11.520 5/5/2007
>> 123 10.899 6/6/2007
>>
>> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
>> Customer ID 632 generated 11.520 euros so should be 2.
>> And Cust. ID 123 should be 3. and etc.
>> So I was wondering if a column can be generated in a query which would
>> label the ranking from 1 to wherever ends the query. Meaning, if I have
>> 10 rows so will be till 10.
>> Hope I have been a bit clearer.
>> Thank you much for your prompt response.
>> Ana
>>
>> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
>> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>>
>sql

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en e
l
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a Job

I am new to SQL Server and have a few questions:

1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:

Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated. If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action Delete the rows that were queried and updated from the 'Scheduling' table

I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?

2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?

Thanks

I have been trying to answer my second question on my own, but so far have been unable. Like I said earlier, I have a field in my table "Scheduling" called "Date". This is a timestamp of when my clients want their schedule for their job updated. I have been trying to query the database for today's date, but I don't know how. Here is how the table looks right now:

JobID Date DC1235 2006-05-31 00:00:00.0

I can query it by the following and it works fine:

SELECT * FROM Scheduling WHERE Date='May 31, 2006'

SELECT * FROM Scheduling WHERE Date='5/31/2006'

SELECT * FROM Scheduling WHERE Date='2006-05-31'

How can I query it using today's timestamp? The following doesn't work:

SELECT * FROM Scheduling WHERE Date=getdate() //This returns nothing

Thanks,

Scott

|||

If you use datetime, you are not using the timestamp data type which is completly different to datetime and has nothing to do with date/time. It is used for row versioning (thats different to the ANSI standard).

Using datetime means that you are have always the time stored within the data, so comparing this to GETDATE() (which returns a datetime which on its own holds a time part) will return false (except if you query at midnight :-) ). You can either convert the date to a non-timecontaining format or use the datediff function to query thise records:

SELECT * FROM Scheduling WHERE DATEDIFF(dd,Date,GETDATE()) = 0

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi again,

1) is possible, but would that make sense, updating the row in the table and afterwards right deleting it ?

But in common, you can do schedule recurring jobs in SQL Server Agent, thats for sure true.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens! I also tried this:

SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101)

which seemed to work as well. Is this what you where talking about when you said, "...convert the date to a non-time containing format..."? Do you prefer one way over the other?

Also, is it possible to do what I want using a SQL Server Agent Job to perform what I was talking about in my first question on my first post?

Thanks!

|||

I guess we must have posted at the same time :)

As far as question 1 is concerned, 2 tables are affected. When a client schedules a new 'job' action for a future date, it is inserted into the 'Scheduling' table. When that date rolls around, the 'Job' table is updated with the new action and it gets deleted from the 'Scheduling' table as it is no longer needed. Hopefully I explained it better.

Thanks

|||

Also... Is there any good documentation or tutorials on how to get started with Transact-SQL or creating SQL Server Agent Jobs?

Thanks

|||

Hi,

"SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101) which seemed to work as well."

Sure you should always provide a length within VARCHAR otherwise it will be truncated to the length 1. I would rather prefer using 112 which is the ISO format.

"Do you prefer one way over the other?" I would prefer DATEDIFF, because it can take use of indexes.

HTH, jens Suessmeyer.

http://www.sqlserver2005-de

|||

Hi,

sorry I don′t know any good ressource, beside the e-learning classes of microsoft for adminstration, most of them are free and you can use a virtual sql server to test and train you knowledge. Beside this, as of my opinion it is always good to have a pocket book for administration of you are right starting with the sql things like this one here:

SQL 2000
OR
SQL2005

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for all your help Jens! I will look into purchasing the pocket book for SQL 2000. I am sure it can probably answer a lot of questions!

Thanks again,

Scott

creating a function to be used in select query

I have this select query which returns a date. I would like to be able to
call this from a stored procedure and have the result appear in the result
set from the stored procedure.
i.e. SELECT *, CalcDate
FROM Table
WHERE somedate = @.dte
The CalcDate field would be the c.dt in the following select statement.
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
How can I do this - I think the above will be a UDF with a return statement
but I am not sure of the syntax.
THanksHi
I assume this subquery does not return more than 1 value.Yes you can write
an UDF to return the date as well , so please refer to the BOL for more info
See if this hepls , I could not tested it since you have not provided DDL+
sample data
SELECT *, (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM Table
WHERE somedate = @.dte
in message news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>|||I have tried the following but get the error msg:
The column prefix c does not match with a table name . ..
CREATE FUNCTION dbo.AddWorkDays
(
@.dte smalldatetime,
@.NoDays TINYINT
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day,25, @.dte)
AND @.NoDays = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
))
END
GO
"Newbie" <nospam@.noidea.com> wrote in message
news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>

Creating a Dynamic YTD calculation in MDX

I have a YTD calculation that I want to make dynamic based on the real current date.

This is the regular YTD formula:

Sum(YTD([Date].[Calendar Hierarchy].CurrentMember),[Measures].[Planned Orders])

This is the YTD formula hard coded with the current date:

Sum(YTD([Date].[Calendar Hierarchy].[Calendar Year].&[2007].&[2007Q1].&[2007-01].&[2007-01-30T00:00:00]),[Measures].[Planned Orders])

The hard coded date works but I need the date to be dynamic. I’m not sure how to get it to be based from the current date.

I’ve been experimenting with using the NOW() function to return the date but I can’t get the syntax correct.

Thank you.

David

Hmmm i think you can find your answer here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

|||

The article is good but it doesn't address my need to make the date part of the calculation relative and based on the current date (down to the day level).

David

|||

Perhaps this thread can help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996269&SiteID=1

Regards

Thomas Ivarsson

|||

Thomas,

Unfortunately, I have budget like future date information in the cube. As a result, I probably need to develop a dynamic way of capturing the current date.

Would this possible scenario work using your suggestion:

Suppose I created a new fact table with one record where the date changed every day. I have a measure column called "Sales " with a value of 1.

I add this fact table to the cube and rewrite your named set:

Tail(Filter([Time].[Time_Calendar].[Month].Members,(Time.[Time_Calendar].Currentmember,[Measures].[lSales])>0))

This would make the date always based on the current date. However, I'm not sure if would filter out other records that I need to show.

David

|||

If you have a measure that is updated daily, like actual sales, my solution will work even if you have a budget measure that points to future dates.

This link have some other suggestions.

http://support.dspanel.com/help43/Web_Part/Examples/MDX_Examples.htm

HTH

Thomas Ivarsson

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a tabl
e
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO

> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Why don't you let the presentation side of things handle the formatting an
d
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George
> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO

> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}

Create view question

I want to create view to join several tables.
As I want to select the data by the date range , Can I pass the date
condition as parameter to it ?
Please tell me how to do .
I never use View before.
Thanks a lot"UDF's in SQL Server 2000 would give you the capability to do what you want
with a parameterized view... table valued UDF's accept parameters and can be
used anyway a table/view can be used..."
But you can use a parameter from your query to query your view (from your
client or from a procedure)
Select * from YourView
Where Yourconditionscolname = @.Somevar
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23CjkJamTFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I want to create view to join several tables.
> As I want to select the data by the date range , Can I pass the date
> condition as parameter to it ?
> Please tell me how to do .
> I never use View before.
> Thanks a lot
>|||Thank Jens. However, Select * from YourView
Your view is a view also, How Can I set the parameter during create YourView
'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OA62IimTF
HA.2128@.TK2MSFTNGP15.phx.gbl...
> "UDF's in SQL Server 2000 would give you the capability to do what you
> want
> with a parameterized view... table valued UDF's accept parameters and can
> be
> used anyway a table/view can be used..."
> But you can use a parameter from your query to query your view (from your
> client or from a procedure)
> Select * from YourView
> Where Yourconditionscolname = @.Somevar
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:%23CjkJamTFHA.3188@.TK2MSFTNGP09.phx.gbl...
>|||> Your view is a view also, How Can I set the parameter during create YourVi
ew
The short version: You can't short of scripting the entire Create View (and
Drop
View) statement each time you need it (which also means your users will have
to
be granted those rights). The better solution is to use a user-defined funct
ion
instead. This *will* allow you to pass a parameter and add forking condition
s.
HTH
Thomas
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:Ozm1xfsTFHA.2768@.tk2msftngp13.phx.gbl...
> Thank Jens. However, Select * from YourView
> Your view is a view also, How Can I set the parameter during create YourVi
ew
> '
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:OA62IimTFHA.2128@.TK2MSFTNGP15.phx.gbl...
>|||Hi Agnes
You can do as:
CREATE VIEW <view_name>
AS
SELECT <Fiends>
FROM <Tables>
[WHERE <Conditions>]
to see the result, u can use
SELECT * FROM <view_name>
Please let me know if this answered the problem
thanks and regards
Chandra
"Agnes" wrote:

> I want to create view to join several tables.
> As I want to select the data by the date range , Can I pass the date
> condition as parameter to it ?
> Please tell me how to do .
> I never use View before.
> Thanks a lot
>
>|||No you cant do that while setting up the view the example was some client
code to query the view with conditions
Select * from YourView
Where Yourconditionscolname = [Placeyourvariablehere]
You view Would look like some "normal" select:
SELECT...
FROM
JOIN
ORDER
and so on.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:Ozm1xfsTFHA.2768@.tk2msftngp13.phx.gbl...
> Thank Jens. However, Select * from YourView
> Your view is a view also, How Can I set the parameter during create
> YourView '
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:OA62IimTFHA.2128@.TK2MSFTNGP15.phx.gbl...
>

create view

I am trying to create view with select stmt and in the where condition is to dynamically select date from one of the table.

But I am unable to do that.

Also,

I tried to create procedure and declare vairables and tried to use

that variable in the where condition of the Select Clause in the Create View

but got an error

Incorrect syntax near the keyword 'VIEW'.

Can I create procedure to create view?

Can I use dynamic variable in the Select ...where..clause of the View?

How do I do?

A repro script would have helped. Take a look at table-valued functions. This might help in your case.