last updated date

Hi,
I'm trying to retrieve data of previous day from my 'sales' table. Sales
table has:
order#, order date, cust#, sales, qty
I have a metadates table: one colummn
date
my script is
select sum(sales), sum(qty) from sales where order_date=(select top 1 date
from metadates order by date desc)
But I got time out all the time.
I'm considering the Christmas holiday, when I come back on Dec 28, the job
runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
Please help,
Thanks so much,
SarahPerhaps this will run faster:
declare @.prev_workday as datetime
select @.prev_workday = (select top 1 date from metadates order by date desc)
select sum(sales), sum(qty) from sales where order_date = @.prev_workday
So, if Dec 24 - 27 are not business days, then do not include them in the
metadates table.
"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
>|||"SG" <sguo@.coopervision.ca> wrote in message
news:OcxHvbCAGHA.272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to retrieve data of previous day from my 'sales' table. Sales
> table has:
> order#, order date, cust#, sales, qty
> I have a metadates table: one colummn
> date
> my script is
> select sum(sales), sum(qty) from sales where order_date=(select top 1 date
> from metadates order by date desc)
> But I got time out all the time.
> I'm considering the Christmas holiday, when I come back on Dec 28, the job
> runs every day, should pick up the sales on Dec 23rd instead of Dec 27.
> Please help,
> Thanks so much,
> Sarah
This may give you a better idea of what the date table should look like.
http://www.aspfaq.com/show.asp?id=2519sql

Last Three Full Months?

Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
DanBreak down the problem. First, you want the first day of the current month:
convert (char 6), getdate, 112) + '01'
Next, you want from 3 months previous, up to but not including the first of
this month. The 3 months previous are:
dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
Now, put these into your WHERE clause:
WHERE
MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
and MyDate < convert (char 6), getdate, 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
Dan|||declare @.date smalldatetime
declare @.start smalldatetime
declare @.end smalldatetime
set @.date = '2004-02-04'
set @.start = dateadd(d, -(datepart(d, @.date)) + 1, dateadd(m, -3, @.date))
set @.end = dateadd(d, -(datepart(d, @.date)) + 1, @.date)
select ... from invoice where invoiceDate >= @.start and invoiceDate < @.end
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
> Hi,
> I have sales line item detail like
> invoicedate, custid, ordernum
> Is there a way using SQL to only return records for the last three full
> months? For example, if today is 2/4/05, I would want records with
> invoicedate between 11/1/04 and 1/31/05.
> TIA,
> Dan|||On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current mont
h:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first o
f
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||Sorry about that. I'm a little distracted by back pain right now. Try
this:
WHERE
MyDate > = dateadd (mm, -3, convert (char (6), getdate(), 112) + '01')
and MyDate < convert (char (6), getdate(), 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:E1nne.498$Wk4.9251@.news.uswest.net...
On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current
> month:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first
> of
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||The conversion to char (6) gives you the year and month only in YYYMM
format. You still need a day, and you supply that by concatenating '01'.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:1lnne.499$Wk4.9860@.news.uswest.net...
On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||On 6/1/2005 1:51 PM, Tom Moreau wrote:
> The conversion to char (6) gives you the year and month only in YYYMM
> format. You still need a day, and you supply that by concatenating '01'.
>
Thanks a bunch!
Dan|||Another alternative:
SELECT DATEADD( m, -3, @.dt - DAY( @.dt ) + 1 ) AS "start",
@.dt - DAY( @.dt ) AS "end"
-- where @.dt is the date value in question
Anith

Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Large Slowly Changing Dimension

Hello,
I'm somewhat new to data warehousing, and have some basic questions.
Let's say I have a fact table, named sales. The financial analyst
would like to be able to sort the sales information based on several
customer related attributes. Some of these attributes would include
zip code, age, and income. While I can create a customer dimension,
these attributes change over time.
The analysts are really only interested in these customer attributes
at the time of the sale. For example, they only care about where a
customer lived at the time of the sale, but not where they are living
now. Now I know there are different techniques for dealing with
changing dimensions, but I'm left with the impression that it would be
making things more complicated then necessary.
Would it not be best to just simply store this information in the
fact table itself? While I'm aware that fact tables are primarily used
to store numerical information, I don't see the advantage of putting
this information in a separate dimension.
Your input would be much appreciated.
Thanks,
AttilaHi,
You say the analysts are interested in the customer attributes at the time
of the sale. So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques. These must be slowly
changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
exist in the zipcode dimension yet, you'll have to add it to the dimension.
The alternative is to drop and recreate all the facts and dimensions, but
that's no option if the customers move or the incomes change in the source
system.
Good luck...
Jeroen.
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0401290847.23e49471@.posting.google.com...

quote:

---

> Hello,
> I'm somewhat new to data warehousing, and have some basic questions.
> Let's say I have a fact table, named sales. The financial analyst
> would like to be able to sort the sales information based on several
> customer related attributes. Some of these attributes would include
> zip code, age, and income. While I can create a customer dimension,
> these attributes change over time.
> The analysts are really only interested in these customer attributes
> at the time of the sale. For example, they only care about where a
> customer lived at the time of the sale, but not where they are living
> now. Now I know there are different techniques for dealing with
> changing dimensions, but I'm left with the impression that it would be
> making things more complicated then necessary.
> Would it not be best to just simply store this information in the
> fact table itself? While I'm aware that fact tables are primarily used
> to store numerical information, I don't see the advantage of putting
> this information in a separate dimension.
> Your input would be much appreciated.
> Thanks,
> Attila

---

|||> So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques.
Can you elaborate on this part? What specifically will an analyst not
be able to do if I store the customer data in the fact table?
Thanks,
Attila
"Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message news:<bvgf14$sjl$1@.reader08.wxs.nl>...[QUOTE]
> Hi,
> You say the analysts are interested in the customer attributes at the time
> of the sale. So these attributes will have to be stored in dimensions to b
e
> able to analyse them using datawarehouse techniques. These must be slowly
> changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
> exist in the zipcode dimension yet, you'll have to add it to the dimension
.
> The alternative is to drop and recreate all the facts and dimensions, but
> that's no option if the customers move or the incomes change in the source
> system.
> Good luck...
> Jeroen.
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0401290847.23e49471@.posting.google.com...|||I suggest reading articles written by Ralph Kimball. Here's one of his
classic articles on slowly changing dimensions.
http://www.dbmsmag.com/9604d05.html
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0402020643.36d448b7@.posting.google.com...
> able to analyse them using datawarehouse techniques.
> Can you elaborate on this part? What specifically will an analyst not
> be able to do if I store the customer data in the fact table?
> Thanks,
> Attila
>
> "Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message
news:<bvgf14$sjl$1@.reader08.wxs.nl>...
time
be
slowly
doesnt
dimension.
but
source|||There is an entire chapter in Ralph Kimballs book on mini dimensions,
which help to stabilise the pain of rapidly changing dimensions.
Ashish
On Tue, 17 Feb 2004 15:29:51 -0500, Domenico Discepola
<domenico_discepola@.quadrachemicals.com> wrote:

> I suggest reading articles written by Ralph Kimball. Here's one of his
> classic articles on slowly changing dimensions.
> http://www.dbmsmag.com/9604d05.html
>
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0402020643.36d448b7@.posting.google.com...
> news:<bvgf14$sjl$1@.reader08.wxs.nl>...
> time
> be
> slowly
> doesnt
> dimension.
> but
> source
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/