Julian Dates

Is there a function or quick way to convert a calendar date to a julian
date. For example 1 September 2004 to 4245.
Rus BaileyIs Julian date days from a particular date? If so, you could just use
dateadd()/datediff().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rus Bailey" <Rus.Bailey@.dla.mil> wrote in message
news:OSn9uCFkEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>|||I used a Date Time Picker and a label and it worked for me here is the code I
used
lblJulian.Text = DateTimePicker1.Value.DayOfYear
Hope this helps
"Rus Bailey" wrote:
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>
>

Julian date time Conversion

Can anyone tell me how to convert julian date time to DateTime and Vice Versa?
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||

Try the links below for UDF Julian to DateTime conversion code. Hope this helps.
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm

http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm

Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...

Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:

SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgamesh
there is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>
|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>
|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>
|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

Joining cubes

I have three cubes in AS. How can I have a separate cube which can retrieve
data from these three cubes and then (similar to join function in T-SQL)
can join the results?
Thanks,
Gilgameshthere is no join option for cubes.
but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
when you link 1 cube in another, the user is able to access the measures of
the 2 cubes at the same time.
you have to use shared/common dimensions to be able to analyze the measures
of the 2 cubes at the same time. (like 1 common time dimension)
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...
>I have three cubes in AS. How can I have a separate cube which can retrieve
> data from these three cubes and then (similar to join function in T-SQL)
> can join the results?
> Thanks,
> Gilgamesh
>|||How do you share dimensions? I tried nameing them the same in all thecubes,
but when I drag and drop the dimensions to the browser it creates separate
columns even though they're called with the same name.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...[vbcol=seagreen]
> there is no join option for cubes.
> but you can use virtual cubes (in AS2000) or link the cubes (SSAS2005)
> when you link 1 cube in another, the user is able to access the measures
> of the 2 cubes at the same time.
> you have to use shared/common dimensions to be able to analyze the
> measures of the 2 cubes at the same time. (like 1 common time dimension)
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:ukwtRNGYHHA.1240@.TK2MSFTNGP04.phx.gbl...|||do you use AS2000 or 2005?
is AS2000 shared dimensions are created in the shared dimension tree of the
management console.
use the SAME dimension in both cube to be able to analyze the measures of
the 2 cubes at the same time.
in 2005 all the dimensions are shared by default, so use the same dimension
in both cubes.
having the same name is not enough.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...
> How do you share dimensions? I tried nameing them the same in all
> thecubes, but when I drag and drop the dimensions to the browser it
> creates separate columns even though they're called with the same name.
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:5E0F1674-B392-4739-82CE-2ED8657CF469@.microsoft.com...
>|||Ok. I'm getting very close. How can I combine three fact tables into one? In
other words, how can I JOIN them?
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...[vbcol=seagreen]
> do you use AS2000 or 2005?
> is AS2000 shared dimensions are created in the shared dimension tree of
> the management console.
> use the SAME dimension in both cube to be able to analyze the measures of
> the 2 cubes at the same time.
> in 2005 all the dimensions are shared by default, so use the same
> dimension in both cubes.
> having the same name is not enough.
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:eH3aUcdYHHA.2316@.TK2MSFTNGP04.phx.gbl...|||well... joining 3 fact tables its different.
can you explain what are the 3 tables and the 3 associated cubes you have.
and the version of the tools (SQL2000 or 2005)
take a look at the foodmart sample or the adventure works sample to see how
different fact tables are combined into 1 cube.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...
> Ok. I'm getting very close. How can I combine three fact tables into one?
> In other words, how can I JOIN them?
> -G
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:9234EA65-4AA3-4983-86E1-360F679BA035@.microsoft.com...
>|||I'm using AS2005. I started by creating three differentd data source views
and then created one cube for each view. i.e. one cube for Orders, one for
Invoices, and one for Receipts. That part works fine. Now I need to either
create a fourth cube, or maybe somethings else, whcih allows me to comine
the records of three fact tables into one view. I'll need specifcally six
columns of each fat tables, which have been named the same, to be included
in the new fourth cube.
The issue is not to combine multiple fact tables into one cube. The issue,
maybe, is to combine multiple fact tables into one fact table and then use
that table in a cube.
-G
"Jeje" <willgart@.hotmail.com> wrote in message
news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...[vbcol=seagreen]
> well... joining 3 fact tables its different.
> can you explain what are the 3 tables and the 3 associated cubes you have.
> and the version of the tools (SQL2000 or 2005)
> take a look at the foodmart sample or the adventure works sample to see
> how different fact tables are combined into 1 cube.
>
> "Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
> news:O4gUZDfYHHA.3628@.TK2MSFTNGP02.phx.gbl...|||ok, now I see
first use only 1 DSV and add all the tales used in this unique DSV; add the
correct relationship between fact tables and dimension tables.
after, create only 1 cube and 3 measure groups each one linked to the 3
tables;
now you have 1 cube with the measures from the 3 source tables.
the cube wizard will create everything for you if the DSV contains enough
information (ie good links).
again, take a look at the adv. works sample to see how this works.
if your tables come from 3 different database, and if you are not familiar
with SSAS, try to copy your tables into the same database to simplify your
project.
or use the linked servers + views, but try to have only 1 source database
for your cubes.
"Gilgamesh" <gilgamesh4ever@.aol.com> wrote in message
news:enT0ScoYHHA.4008@.TK2MSFTNGP05.phx.gbl...
> I'm using AS2005. I started by creating three differentd data source views
> and then created one cube for each view. i.e. one cube for Orders, one for
> Invoices, and one for Receipts. That part works fine. Now I need to either
> create a fourth cube, or maybe somethings else, whcih allows me to comine
> the records of three fact tables into one view. I'll need specifcally six
> columns of each fat tables, which have been named the same, to be included
> in the new fourth cube.
> The issue is not to combine multiple fact tables into one cube. The issue,
> maybe, is to combine multiple fact tables into one fact table and then use
> that table in a cube.
> -G
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:AB50D69A-F1BB-455D-88EB-63B8B4EE9A4E@.microsoft.com...
>

joining a table to a user-defined function?

Suppose I have a SQL Server table named 'gadget'. 'gadget' has an integer field named 'gadget_key', which is the primary key of the table.

Now suppose I have a user-defined function named 'udf_gadget_values'. This function takes as its input parameter an integer variable named '@.nGadgetKey'. This function returns a table which will always contain exactly one record. This one record has one field named 'nGadgetKey', which contains the same value that was passed to the function in parameter '@.nGadgetKey'

I would like to join the table 'gadget' with the table that function 'udf_gadget_values' returns kind of like this:

SELECT TOP 10 *
FROM gadget, udf_gadget_values(gadget.gadget_key)

The purpose of this query is to get the top 10 records from 'gadget', as well as the values associated with each record, as returned by the function.

The real issue is this: how do I pass gadget.gadget_key to the function as an input parameter? Or if this will not work, is there an alternative?

Hi,

This syntax is neither supported in Yukon nor Shiloh. I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this. The LHS of CROSS APPLY is a table source and the RHS is a table-valued function. The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset. In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset. In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

It would look like this:

select * from gadget cross apply udf_gadget_values(gadget_key)

Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list. The examples below illustrate:

-- Yukon : works
-- Shiloh: !works
select * from gadget where exists
(select * from udf_gadget_values(gadget.gadget_key))

-- Yukon : works
-- Shiloh: !works
select *
, (select gadget_desc
from dbo.udf_gadget_values(gadget.gadget_key))
as function_value
from gadget

Regards,
Clifford Dibble
Program Manager, SQL Server

JOIN with table valued function very slow

If have a tabled-valued function uCalendar that returns a two-column table
with attributes dayno (number of days past 19000101) and caldate, which is a
formatted date based on dayno.
DDL for uCalendar:
CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
datetime )
RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
AS
BEGIN
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
RETURN
END
Calling the function like so
select * from uCalendar('20050101','20051231')
returns the result set very quickly.
I have another query that reports the number of hits against a website per
day:
SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])
weblog is a view that references a base table with approximately 500,000
rows. The above query finishes in about 2 seconds.
However, if I try
select t1.dayno from uCalendar('20050101','20051231') AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
It seems to hang. The longest I let it run was about 3 minutes. If the
result sets from each "side" of the join are produced rapidly, why doesn't
this query produce its results quickly? Is it because it is constantly
re-evaluating the function over and over?
I'm working on a better solution using a stored procedure to get the entire
result. The point of my question is *why* is it slow, not "this will work
instead".
Thanks,
-Mark WilliamsHi Mark
Why are you not using a calendar table for this
http://www.aspfaq.com/show.asp?id=2519?
John
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:F3466971-ED3D-4026-91B1-0EAFD26DBDB1@.microsoft.com...
> If have a tabled-valued function uCalendar that returns a two-column table
> with attributes dayno (number of days past 19000101) and caldate, which is
> a
> formatted date based on dayno.
> DDL for uCalendar:
> CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
> datetime )
> RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
> AS
> BEGIN
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
> @.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> RETURN
> END
> Calling the function like so
> select * from uCalendar('20050101','20051231')
> returns the result set very quickly.
> I have another query that reports the number of hits against a website per
> day:
> SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])
> weblog is a view that references a base table with approximately 500,000
> rows. The above query finishes in about 2 seconds.
> However, if I try
> select t1.dayno from uCalendar('20050101','20051231') AS t1
> LEFT JOIN
> (SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])) t2
> ON t1.dayno=t2.dayno
> It seems to hang. The longest I let it run was about 3 minutes. If the
> result sets from each "side" of the join are produced rapidly, why
> doesn't
> this query produce its results quickly? Is it because it is constantly
> re-evaluating the function over and over?
> I'm working on a better solution using a stored procedure to get the
> entire
> result. The point of my question is *why* is it slow, not "this will work
> instead".
> Thanks,
> -Mark Williams|||Mark,
Best I can tell, the query optimizer has no information about the
number of rows or distribution of values in
uCalendar('20050101','20051231'),
and so instead of materializing the grouped table, then joining
it with the UDF, it chooses a query plan that runs a count from
the weblog table for each row of the calendar table. When I run
this against the Northwind Orders table, I see that the optimizer thinks
there are only one or two rows in the UDF result set. There is no
way to tell the optimizer to think agian.
The quickest solution is create a permanent (not a UDF) calendar
table indexed on at least the bigint column, and then join against that.
Here I do so, and put a wide range of dates into the calendar table.
I'm writing this against Northwind..Orders so I can test it, but the
improvement in the query plan should translate to your situation
as well.
CREATE TABLE tCalendar (
dayno bigint primary key,
caldate char(20) unique
)
GO
insert into tCalendar
select dayno, caldate
from uCalendar('20010101','20101231')
go
select t1.dayno
from tCalendar AS t1
left outer join (
SELECT
DATEDIFF(dd, 0, [OrderDate]) AS dayno,
COUNT(*) AS "hits" FROM Northwind..Orders
GROUP BY DATEDIFF(dd, 0, [OrderDate])
) T
on t1.dayno = T.dayno
WHERE t1.dayno between
datediff(day,0,'20050101') and datediff(day,0,'20051231')
It's important here to be sure the WHERE clause is a SARG.
Since you don't use any datetime data types here, you can't
compare anything directly against your two datetime strings,
and you don't want to put t1.dayno into an expression.
Ideally, you would use datetime as the type to store dates
with, not bigint and not a string, but perhaps this will help
you out until you can make other improvements to your design.
A permanent calendar table is always a good idea, and if you
use uCalendar widely, you could rewrite it to select from a
permanent table (using the same WHERE clause I show here
outside the join), so you don't have to rewrite as many queries.
I assume you know that your sample query is not too practical,
since because of the outer join with no where clause, your result
will just be all dayno values in the UDF. But the optimizer doesn't
manage to catch that...
Steve Kass
Drew University
Mark Williams wrote:

>If have a tabled-valued function uCalendar that returns a two-column table
>with attributes dayno (number of days past 19000101) and caldate, which is
a
>formatted date based on dayno.
>DDL for uCalendar:
>CREATE FUNCTION uCalendar (@.startdate datetime = '19000101', @.enddate
>datetime )
>RETURNS @.calendar TABLE (dayno bigint, caldate char(20))
>AS
>BEGIN
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO @.calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
>@.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> RETURN
>END
>Calling the function like so
>select * from uCalendar('20050101','20051231')
>returns the result set very quickly.
>I have another query that reports the number of hits against a website per
>day:
>SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
>GROUP BY DATEDIFF(dd, 0, [time])
>weblog is a view that references a base table with approximately 500,000
>rows. The above query finishes in about 2 seconds.
>However, if I try
>select t1.dayno from uCalendar('20050101','20051231') AS t1
>LEFT JOIN
>(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
>GROUP BY DATEDIFF(dd, 0, [time])) t2
>ON t1.dayno=t2.dayno
>It seems to hang. The longest I let it run was about 3 minutes. If the
>result sets from each "side" of the join are produced rapidly, why doesn't
>this query produce its results quickly? Is it because it is constantly
>re-evaluating the function over and over?
>I'm working on a better solution using a stored procedure to get the entire
>result. The point of my question is *why* is it slow, not "this will work
>instead".
>Thanks,
>-Mark Williams
>|||I'm aware of caledar tables, but my question was more oriented toward why it
was slow, and not "what is another solution?" I did come up with another
solution, which produced the results very quickly:
CREATE PROCEDURE hitsByDay
@.startdate datetime = '19000101',
@.enddate datetime
AS
BEGIN
--DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
CREATE TABLE #calendar (dayno bigint, caldate char(20))
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
ORDER BY t1.dayno
DROP TABLE #calendar
END
EXEC dbo.hitsByDay '20050101','20051231'
It should be noted that there are no indexes in the base table or the view
that referenced it. There are no natural candidate keys because of the natur
e
of the data (it's a web site log, lots of duplicates). I tried creating an
index the [time] column in the view, but it complained that is was
non-deterministic. (I don't buy that one).
So, the question is, why, specifically, the join with the table-valued
function is so slow. The join with the temporary #calendar table is pretty
quick, even without an index.
--
"John Bell" wrote:

> Hi Mark
> Why are you not using a calendar table for this
> http://www.aspfaq.com/show.asp?id=2519?
> John
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:F3466971-ED3D-4026-91B1-0EAFD26DBDB1@.microsoft.com...
>
>|||Thank you; very well worded and insightful.
I ended up creating a stored procedure that dynamically creates a temporary
calendar based on the input start and end dates.
CREATE PROCEDURE hitsByDay
@.startdate datetime = '19000101',
@.enddate datetime
AS
BEGIN
--DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
CREATE TABLE #calendar (dayno bigint, caldate char(20))
DECLARE @.firstday bigint
DECLARE @.lastday bigint
SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
WHILE (@.firstday <= @.lastday)
BEGIN
INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
@.firstday, 0), 107))
SET @.firstday = @.firstday + 1
END
SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
LEFT JOIN
(SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
WHERE [time] BETWEEN @.startdate AND @.enddate
GROUP BY DATEDIFF(dd, 0, [time])) t2
ON t1.dayno=t2.dayno
ORDER BY t1.dayno
DROP TABLE #calendar
END
EXEC dbo.hitsByDay '20051201','20051231'
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"Steve Kass" wrote:

> Mark,
> Best I can tell, the query optimizer has no information about the
> number of rows or distribution of values in
> uCalendar('20050101','20051231'),
> and so instead of materializing the grouped table, then joining
> it with the UDF, it chooses a query plan that runs a count from
> the weblog table for each row of the calendar table. When I run
> this against the Northwind Orders table, I see that the optimizer thinks
> there are only one or two rows in the UDF result set. There is no
> way to tell the optimizer to think agian.
> The quickest solution is create a permanent (not a UDF) calendar
> table indexed on at least the bigint column, and then join against that.
> Here I do so, and put a wide range of dates into the calendar table.
> I'm writing this against Northwind..Orders so I can test it, but the
> improvement in the query plan should translate to your situation
> as well.
> CREATE TABLE tCalendar (
> dayno bigint primary key,
> caldate char(20) unique
> )
> GO
> insert into tCalendar
> select dayno, caldate
> from uCalendar('20010101','20101231')
> go
> select t1.dayno
> from tCalendar AS t1
> left outer join (
> SELECT
> DATEDIFF(dd, 0, [OrderDate]) AS dayno,
> COUNT(*) AS "hits" FROM Northwind..Orders
> GROUP BY DATEDIFF(dd, 0, [OrderDate])
> ) T
> on t1.dayno = T.dayno
> WHERE t1.dayno between
> datediff(day,0,'20050101') and datediff(day,0,'20051231')
>
> It's important here to be sure the WHERE clause is a SARG.
> Since you don't use any datetime data types here, you can't
> compare anything directly against your two datetime strings,
> and you don't want to put t1.dayno into an expression.
> Ideally, you would use datetime as the type to store dates
> with, not bigint and not a string, but perhaps this will help
> you out until you can make other improvements to your design.
> A permanent calendar table is always a good idea, and if you
> use uCalendar widely, you could rewrite it to select from a
> permanent table (using the same WHERE clause I show here
> outside the join), so you don't have to rewrite as many queries.
> I assume you know that your sample query is not too practical,
> since because of the outer join with no where clause, your result
> will just be all dayno values in the UDF. But the optimizer doesn't
> manage to catch that...
> Steve Kass
> Drew University
> Mark Williams wrote:
>
>|||Hi Mark
You may find a more permanent calendar table would be useful elsewhere,
Check where you use data functions and comparisons to see if using one would
be more efficient. Searching Google for "UDF AND SLOW" turns up many hits,
although most of these are related to scalar functions e.g.
http://www.sql-server-performance.c...server_udfs.asp
Your function(s) are not set based solutions which included looping which
would be expected to perform worse with a larger number of iterations, this
is where a calendar table would be significantly faster, therefore I would
make sure that you test it with a full date range.
John
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4E2BC61-680E-4B03-9535-4E0015828A5C@.microsoft.com...
> I'm aware of caledar tables, but my question was more oriented toward why
> it
> was slow, and not "what is another solution?" I did come up with another
> solution, which produced the results very quickly:
> CREATE PROCEDURE hitsByDay
> @.startdate datetime = '19000101',
> @.enddate datetime
> AS
> BEGIN
> --DECLARE @.calendar TABLE (dayno bigint, caldate char(20))
> CREATE TABLE #calendar (dayno bigint, caldate char(20))
> DECLARE @.firstday bigint
> DECLARE @.lastday bigint
> SELECT @.firstday = DATEDIFF(dd, 0, @.startdate)
> SELECT @.lastday = DATEDIFF(dd, 0, @.enddate)
> WHILE (@.firstday <= @.lastday)
> BEGIN
> INSERT INTO #calendar VALUES (@.firstday, CONVERT(char(20), DATEADD(dd,
> @.firstday, 0), 107))
> SET @.firstday = @.firstday + 1
> END
> SELECT t1.caldate, ISNULL(t2.hits,0) from #calendar AS t1
> LEFT JOIN
> (SELECT DATEDIFF(dd, 0, [time]) AS dayno, COUNT(*) AS "hits" FROM weblog
> GROUP BY DATEDIFF(dd, 0, [time])) t2
> ON t1.dayno=t2.dayno
> ORDER BY t1.dayno
> DROP TABLE #calendar
> END
> EXEC dbo.hitsByDay '20050101','20051231'
> It should be noted that there are no indexes in the base table or the view
> that referenced it. There are no natural candidate keys because of the
> nature
> of the data (it's a web site log, lots of duplicates). I tried creating an
> index the [time] column in the view, but it complained that is was
> non-deterministic. (I don't buy that one).
> So, the question is, why, specifically, the join with the table-valued
> function is so slow. The join with the temporary #calendar table is pretty
> quick, even without an index.
> --
> "John Bell" wrote:
>

Join Using a substring function

Hi All,

I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.

Thanks

I would do the join in Transact-SQL first, if possible.

|||

I already have a SQL script (SP) that works fine, however what I am trying to achieve is get rid of this SP/view concept and replace with SSIS packages/tasks/transformations. Thanks for helping.

|||

You can use a Derived Column transform to do a substring. I would recommend that you use a Derived Column transform to get the stage data to match with your dimension table before doing the Lookup.

If you absolutely have to do the transformation on the dimension table data, you can override the SQL generated by the Lookup by checking the enable memory restriction option. This will negatively impact performance though. Or you could use a Merge Join (getting your data from the dimension via an OLE DB Source, run it through a Derived Column transform, thenMerge Join it to the stage data.

|||That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this? also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.

|||

db_guy wrote:

That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this? also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.

You can use a derived column to convert the input data type to match that of the column in the lookup table.|||Thanks for ur help Phil

Join table and function

Hi .
I want to join a table function and a table.Is it possible?How?

table (Id,Title)
function (table1.Id) : returns (Id,Describ1,Describ2)

Resault should be: (Table1.Id,Title,Describ1,Describ2)

Use the following query..

Select Table.Id, Table.Title, Fun.Id, Fun.Descib1, Fun.Descib2

From Table

Join function(someid) as Fun on Fun.ID = Table.ID

|||

For SQL Server 2005, use the cross apply operator as below.
For SQL Server 2000, there's no easy way (can be done using a cursor)

create table mytable(Id int,Title varchar(10))

go

create function dbo.myfunction(@.Id int)
returns @.retTab table(Id int ,Describ1 varchar(10),Describ2 varchar(10))
as
begin
insert into @.retTab(Id,Describ1,Describ2)
select @.Id,'Describ1','Describ2'
return
end

go

select mytable.Id, mytable.Title, Fn.Describ1, Fn.Describ2
from mytable
cross apply dbo.myfunction(Id) as Fn

Join on agragate function between tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112

Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359

Where the numbers are way off.

Any suggestions?

When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.

Here is a simple example that shows what's happening.

Drop Table Table1

Create Table Table1(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

Drop Table Table2

Create Table Table2(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

insert Table1 values( '1/1/2006', 'Fred' )

insert Table1 values( '1/2/2006', 'Barney' )

insert Table2 values( '1/1/2006', 'Wilma' )

insert Table2 values( '1/1/2006', 'Pebbles' )

insert Table2 values( '1/2/2006', 'Betty' )

insert Table2 values( '1/2/2006', 'BamBam' )

Select DatePart(dw, dato ), Count(*)

From Table1

Group

By DatePart(dw, dato )

Select DatePart(dw, dato ), Count(*)

From Table2

Group

By DatePart(dw, dato )

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

t1.valCol,

t2.valCol

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

valCol valCol

-- -- - -

1 1 Fred Wilma

1 1 Fred Pebbles

2 2 Barney Betty

2 2 Barney BamBam

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

Count(t1.dato),

Count(t2.dato)

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

Group

By DatePart(dw, t1.dato ),

DatePart(dw, t2.dato)

-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),

t1.howmany,

t2.howmany

From (

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table1

Group by DatePart(dw, dato )

) t1

Full Outer Join

(

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table2

Group by DatePart(dw, dato )

) t2

On t1.dow = t2.dow

howmany howmany
-- -- --
1 1 2
2 1 2

Select dayofweek,

(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),

(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )

From (

Select 1 as dayofweek

Union All

Select 2 as dayofweek

Union All

Select 3 as dayofweek

Union All

Select 4 as dayofweek

Union All

Select 5 as dayofweek

Union All

Select 6 as dayofweek

Union All

Select 7 as dayofweek

)t1

dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.

|||

Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.