Hey everybody..
I need a little help getting the correct info out of my cube. I have
a cube that stores the balance for every day over the year.
What I need is to get the balance for every last day of every month
Ill describe my cube a bit. I have a dimention that contains the
various systems used (AH.SP,IG... ), a dimention Time (year, month,
day) and
the Measures. (balance, count)
What I need is to get the balance for jan.31, feb.29, mar 31.. and so
forth. For every system. So it would look something like
jan feb mar apr mai
AH 54 4 43 43 43
SP 43 3 45 56 56
IG 3 5 6 6 6
Thanks for any and all help
Arnar
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/OLAP-child-...pict207887.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=709627http://groups-beta.google.com/group...erver.olap/msg/
50d474b9b11d21ff[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: Deepak Puri <deepak_p...@.progressive.com> Date: Wed, 16 Mar 2005
14:51:42 -0800
Subject: Re: multiple entries with dates
There are well-known techniques for dealing with semi-additive
"snapshot" measures, which are discussed in this MSDN paper. Please note
that Analysis Services 2005 now has built-in balances:
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnsq.
.
Analysis Services: Semiadditive Measures and Inventory Snapshots
Amir Netz
Microsoft Corporation
Updated May 18, 2004
Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services
Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.
.
http://www.microsoft.com/techn_et/p...alua_te/dwsqlsy
.m
spx
.
One of the greatest arguments for using an analytical server such as
Analysis Services is the ability to define complex calculations
centrally. Analysis Services has always delivered rich analytics, but
some complex concepts have been difficult to implement.
One such concept is that of a semi-additive measure. Most common
measures, such as [Sales], aggregate cleanly along all dimensions:
[Total Sales] for all time is the sales for all products, for all
customers, and for all time. A semi-additive measure, by contrast, may
be additive in some dimensions but not in others. The most common
scenario is a balance, such as the number of items in a warehouse. The
aggregate balance for yesterday plus today is not, of course, the sum of
yesterday's balance plus today's balance. Instead it's probably the
ending balance, although in some scenarios it is the beginning balance.
In Analysis Services 2000 you would have to define a complex MDX
calculation to deliver the correct measure. With Analysis Services 2005,
beginning and end balances are native aggregation types.
.[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Showing posts with label havea. Show all posts
Showing posts with label havea. Show all posts
Monday, March 12, 2012
Last 90 Days For a Variable
I triyng to generate a table with the last 90 days for a Part number, I have
a table with data for the last 7 years, and I need to complie the last 90
days of data for all the parts, but if I use the date as a criteria it will
only give the last 90 days, so I need to generate a query that will give all
the parts with only the last 90 days of data for all parts.
If you have any idea or suggestion are more than welcome
Thanks
Can you post DDL (CREATE TABLE statements), some sample data (INSERT
statements), and some sample output?
I'm confused by why using the date to get the last 90 days will not give you
all of the data for all of the parts over the last 90 days.
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||SELECT <columns> FROM <table>
WHERE <datetime_column> >= GETDATE()-90
If you want 90 days from midnight this AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
112))
If you want 90 days from midnight tomorrow AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
GETDATE()+1, 112))
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||Aaron,
Some parts did not ran for the last 90 days, so I need to create a query
that will search the last 90 days for any part, here is an example assuming
the following results
Part Start Date End Date
ABC 1/4/04 7/3/04
CDB 11/1/03 5/4/04
DCB 5/4/04 8/8/04
So as you could see some parts ran in a differnts period of time and I need
to collect information for the latest 90 days for all parts regarding of the
date they ran.
Thanks
"Aaron [SQL Server MVP]" wrote:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= GETDATE()-90
> If you want 90 days from midnight this AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
> 112))
> If you want 90 days from midnight tomorrow AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
> GETDATE()+1, 112))
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Adam,
If I request the last 90 days, it'll only give those parts that ran in that
time interval, and I need the last 90 days for all the parts, regarding the
dates that they ran.
Thaks
"Adam Machanic" wrote:
> Can you post DDL (CREATE TABLE statements), some sample data (INSERT
> statements), and some sample output?
> I'm confused by why using the date to get the last 90 days will not give you
> all of the data for all of the parts over the last 90 days.
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Can you tell us which of these rows satisfy your requirements? Can you
please let us know if those dates are m/d/y or d/m/y?
Please see http://www.aspfaq.com/5006 for information on giving us usable
specifications to help solve your issue...
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:7D125639-DDFA-4836-A755-BC35E7969B78@.microsoft.com...
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example
assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I
need
> to collect information for the latest 90 days for all parts regarding of
the[vbcol=seagreen]
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
GETDATE(),[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Your narrative does not make sense. Please supply real requirements with
CREATE TABLE statements, sample data in the form of INSERT statements, and
desired output.
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:B1666113-0BA6-4893-9C1F-A7ED897FA7A1@.microsoft.com...
> Adam,
> If I request the last 90 days, it'll only give those parts that ran in
that
> time interval, and I need the last 90 days for all the parts, regarding
the[vbcol=seagreen]
> dates that they ran.
> Thaks
> "Adam Machanic" wrote:
you[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Aaron,
Here is
Insert <Table Name>
Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
as downtime
From Tbl_History
Where Pdate >= getdate()-90
by doing this I'll only get those parts that have ran in the last 90 days,
and I need the last 90 days of history for every part that we have in the
table Tbl_History. So in others words I need the last 2160 hours of ran data
for every part in the Tbl_History table and then insert this data into
different table (90*24)
"sanvaces" wrote:
[vbcol=seagreen]
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I need
> to collect information for the latest 90 days for all parts regarding of the
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
|||More narrative doesn't help. I have no idea what "last 2160 hours of ran
data for every part" means. Please see http://www.aspfaq.com/5006 and give
us REAL REQUIREMENTS.
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:79E6CB0F-501C-4643-B3AD-F9DA40E15190@.microsoft.com...
> Aaron,
> Here is
> Insert <Table Name>
> Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
> as downtime
> From Tbl_History
> Where Pdate >= getdate()-90
> by doing this I'll only get those parts that have ran in the last 90 days,
> and I need the last 90 days of history for every part that we have in the
> table Tbl_History. So in others words I need the last 2160 hours of ran
data[vbcol=seagreen]
> for every part in the Tbl_History table and then insert this data into
> different table (90*24)
> "sanvaces" wrote:
query[vbcol=seagreen]
assuming[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
GETDATE(),[vbcol=seagreen]
number, I[vbcol=seagreen]
last 90[vbcol=seagreen]
it[vbcol=seagreen]
give[vbcol=seagreen]
a table with data for the last 7 years, and I need to complie the last 90
days of data for all the parts, but if I use the date as a criteria it will
only give the last 90 days, so I need to generate a query that will give all
the parts with only the last 90 days of data for all parts.
If you have any idea or suggestion are more than welcome
Thanks
Can you post DDL (CREATE TABLE statements), some sample data (INSERT
statements), and some sample output?
I'm confused by why using the date to get the last 90 days will not give you
all of the data for all of the parts over the last 90 days.
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||SELECT <columns> FROM <table>
WHERE <datetime_column> >= GETDATE()-90
If you want 90 days from midnight this AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
112))
If you want 90 days from midnight tomorrow AM:
SELECT <columns> FROM <table>
WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
GETDATE()+1, 112))
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> I triyng to generate a table with the last 90 days for a Part number, I
have
> a table with data for the last 7 years, and I need to complie the last 90
> days of data for all the parts, but if I use the date as a criteria it
will
> only give the last 90 days, so I need to generate a query that will give
all
> the parts with only the last 90 days of data for all parts.
> If you have any idea or suggestion are more than welcome
> Thanks
|||Aaron,
Some parts did not ran for the last 90 days, so I need to create a query
that will search the last 90 days for any part, here is an example assuming
the following results
Part Start Date End Date
ABC 1/4/04 7/3/04
CDB 11/1/03 5/4/04
DCB 5/4/04 8/8/04
So as you could see some parts ran in a differnts period of time and I need
to collect information for the latest 90 days for all parts regarding of the
date they ran.
Thanks
"Aaron [SQL Server MVP]" wrote:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= GETDATE()-90
> If you want 90 days from midnight this AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8), GETDATE(),
> 112))
> If you want 90 days from midnight tomorrow AM:
> SELECT <columns> FROM <table>
> WHERE <datetime_column> >= DATEADD(DAY, -90, CONVERT(CHAR(8),
> GETDATE()+1, 112))
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Adam,
If I request the last 90 days, it'll only give those parts that ran in that
time interval, and I need the last 90 days for all the parts, regarding the
dates that they ran.
Thaks
"Adam Machanic" wrote:
> Can you post DDL (CREATE TABLE statements), some sample data (INSERT
> statements), and some sample output?
> I'm confused by why using the date to get the last 90 days will not give you
> all of the data for all of the parts over the last 90 days.
>
> "sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
> news:3EDCA8EF-D432-47BD-9EE1-CABE20168D20@.microsoft.com...
> have
> will
> all
>
>
|||Can you tell us which of these rows satisfy your requirements? Can you
please let us know if those dates are m/d/y or d/m/y?
Please see http://www.aspfaq.com/5006 for information on giving us usable
specifications to help solve your issue...
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:7D125639-DDFA-4836-A755-BC35E7969B78@.microsoft.com...
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example
assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I
need
> to collect information for the latest 90 days for all parts regarding of
the[vbcol=seagreen]
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
GETDATE(),[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Your narrative does not make sense. Please supply real requirements with
CREATE TABLE statements, sample data in the form of INSERT statements, and
desired output.
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:B1666113-0BA6-4893-9C1F-A7ED897FA7A1@.microsoft.com...
> Adam,
> If I request the last 90 days, it'll only give those parts that ran in
that
> time interval, and I need the last 90 days for all the parts, regarding
the[vbcol=seagreen]
> dates that they ran.
> Thaks
> "Adam Machanic" wrote:
you[vbcol=seagreen]
I[vbcol=seagreen]
90[vbcol=seagreen]
give[vbcol=seagreen]
|||Aaron,
Here is
Insert <Table Name>
Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
as downtime
From Tbl_History
Where Pdate >= getdate()-90
by doing this I'll only get those parts that have ran in the last 90 days,
and I need the last 90 days of history for every part that we have in the
table Tbl_History. So in others words I need the last 2160 hours of ran data
for every part in the Tbl_History table and then insert this data into
different table (90*24)
"sanvaces" wrote:
[vbcol=seagreen]
> Aaron,
> Some parts did not ran for the last 90 days, so I need to create a query
> that will search the last 90 days for any part, here is an example assuming
> the following results
> Part Start Date End Date
> ABC 1/4/04 7/3/04
> CDB 11/1/03 5/4/04
> DCB 5/4/04 8/8/04
> So as you could see some parts ran in a differnts period of time and I need
> to collect information for the latest 90 days for all parts regarding of the
> date they ran.
> Thanks
> "Aaron [SQL Server MVP]" wrote:
|||More narrative doesn't help. I have no idea what "last 2160 hours of ran
data for every part" means. Please see http://www.aspfaq.com/5006 and give
us REAL REQUIREMENTS.
http://www.aspfaq.com/
(Reverse address to reply.)
"sanvaces" <sanvaces@.discussions.microsoft.com> wrote in message
news:79E6CB0F-501C-4643-B3AD-F9DA40E15190@.microsoft.com...
> Aaron,
> Here is
> Insert <Table Name>
> Select actcycle,actcav,actqty,actlab,slot,part,hrsran,hrs ava,hrsava-hrsran
> as downtime
> From Tbl_History
> Where Pdate >= getdate()-90
> by doing this I'll only get those parts that have ran in the last 90 days,
> and I need the last 90 days of history for every part that we have in the
> table Tbl_History. So in others words I need the last 2160 hours of ran
data[vbcol=seagreen]
> for every part in the Tbl_History table and then insert this data into
> different table (90*24)
> "sanvaces" wrote:
query[vbcol=seagreen]
assuming[vbcol=seagreen]
need[vbcol=seagreen]
the[vbcol=seagreen]
GETDATE(),[vbcol=seagreen]
number, I[vbcol=seagreen]
last 90[vbcol=seagreen]
it[vbcol=seagreen]
give[vbcol=seagreen]
Subscribe to:
Posts (Atom)