Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson

Another thorny summing problem

I'm trying to generate a "rollup" of stock positions based on the trades to
date. I want to generate a row for every month where there was any activity.
Some of the trades have null shares, however. So I did this (and before I get
snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
cut and paste it exactly)...
SELECT stockId,
CASE
WHEN ABS(SUM(f.units)) > 0.01
THEN SUM(f.units)
ELSE 0
END AS openingposition
FROM tblTrans
WHERE tdate < [a date provided by a dialog box in code]
GROUP BY stockId
The problem I'm having is that "opening" months, when you first buy a stock,
don't generate a row. That's because I was generating the opening position by
looking for all trades before the start of the month (the sum of which is
your current position). The obvious solution was to do two rollups, for
opening and closing positions, something like...
SELECT stockId,
CASE when tdate < [startofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
openingposition,
CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
closingposition
FROM tblTrans
WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
GROUP BY stockId
But this doesn't work, it generates multiple rows per stock. I don't
understand exactly why it does this, nor do the number seem to have any
bearing on reality. I suspect the outer CASE on the dates has something to do
with it, and I have to put a similar case in the GROUP perhaps?
Perhaps this is just the wrong way to do this? Two subqueries perhaps?
MauryI tried my own advice and some fiddling later and I have a solution.
The working idea is to use two subqueries, one summing everything to the
start of the period, another to the end, and then grouping the results on
stock. The only real trick in there was that the opening position could be
NULL, which resulted in odd outcomes. I added CASEs on the outside to force
them to zero, and presto!
Maury|||If you are still looking for a solution, can you provide the table
schema and some sample data? It would be helpful to arrive at the
solution.
Thanks
Manisha Gandhi
Maury Markowitz wrote:
> I'm trying to generate a "rollup" of stock positions based on the trades to
> date. I want to generate a row for every month where there was any activity.
> Some of the trades have null shares, however. So I did this (and before I get
> snarky e-mails about it, this is paraphrased from VBA code so, no, I can't
> cut and paste it exactly)...
> SELECT stockId,
> CASE
> WHEN ABS(SUM(f.units)) > 0.01
> THEN SUM(f.units)
> ELSE 0
> END AS openingposition
> FROM tblTrans
> WHERE tdate < [a date provided by a dialog box in code]
> GROUP BY stockId
> The problem I'm having is that "opening" months, when you first buy a stock,
> don't generate a row. That's because I was generating the opening position by
> looking for all trades before the start of the month (the sum of which is
> your current position). The obvious solution was to do two rollups, for
> opening and closing positions, something like...
> SELECT stockId,
> CASE when tdate < [startofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END END AS
> openingposition,
> CASE when tdate >= [startofmonth] AND tdate <= [endofmonth] THEN
> CASE WHEN ABS(SUM(f.units)) > 0.01 THEN SUM(f.units) ELSE 0 END AS
> closingposition
> FROM tblTrans
> WHERE tdate < [endOfMonth[a date provided by a dialog box in code])]
> GROUP BY stockId
> But this doesn't work, it generates multiple rows per stock. I don't
> understand exactly why it does this, nor do the number seem to have any
> bearing on reality. I suspect the outer CASE on the dates has something to do
> with it, and I have to put a similar case in the GROUP perhaps?
> Perhaps this is just the wrong way to do this? Two subqueries perhaps?
> Maury

another question

Thanks for the note Vishal,
What if each had a date. So that
create table #cartype(manufacturer varchar(500), itemnumber int, datemade date)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
So that
insert into #cartype values('Toyota',1, 4/6/2004)
insert into #cartype values('Honda',2, 4/6/2004)
Would get deleted because there the exact same records (same number) of records are duplicated for that date.
But the records:
insert into #cartype values('Toyota',1, 4/7/2004)
insert into #cartype values('Honda',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
insert into #cartype values('GE',3, 4/7/2004)
The GE records would stay because all of the records are not duplicated, just the GE records are so I want to keep all the records.
Thanks for any ideas!
Try query as follows:
delete a
from #cartype a join
(select manufacturer, datemade, count(*) cnt
from #cartype
group by manufacturer, datemade
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.datemade = b.datemade and b.cnt <>
(select count(*)
from #cartype x
group by manufacturer
having x.manufacturer = b.manufacturer)
Vishal Parkar
vgparkar@.yahoo.co.in
|||Here is what I have done to make it fit my query. Here are my records:
store, deliverydate, itemnumber, qty
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
006SS,04/15/2004,070100,018
006SS,04/15/2004,090096,018
(this should get deleted, exact same as 2 lines above)
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,030498,020
007SS,04/15/2004,090495,020
007SS,04/15/2004,090495,020
(all lines should stay because it is not exact same.)
selext a.*, cnt
from tblItemOrder a join
(select itemnumber, quantity, store, deliverydate, count(*) cnt
from tblItemOrder
group by itemnumber, quantity, store, deliverydate
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.quantity = b.quantity and a.store = b.store and a.deliverydate = b.deliverydate and b.cnt <>
(select count(*)
from tblItemOrder x
group by store, deliverydate
having x.store = b.store and x.deliverydate = b.deliverydate)
I get all records that have duplicate lines, not just the ones with same count of duplicate records (storeno, deliverydate).
Any ideas? I have thought about it many different ways and have not come up with a solution yet. Thanks again,
|||hi ashley,
Remember SELECT and DELETE are different statements. DELETE will delete
the data from the table while with the help of SELECT statement you can
filterout the rows from the table.
See following example:
create table tt
(store varchar(50),
deliverydate datetime,
itemnumber varchar(50),
qty int)
--insert some data
insert into tt
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '006SS','04/15/2004','070100','018' union all
select '006SS','04/15/2004','090096','018' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','030498','020' union all
select '007SS','04/15/2004','090495','020' union all
select '007SS','04/15/2004','090495','020'
--Try this query:
select a.*
from tt a join
(select store, deliverydate, itemnumber,count(*) cnt
from tt
group by store, deliverydate, itemnumber
having count(*) > 1) b on a.itemnumber = b.itemnumber and
a.store = b.store and a.deliverydate = b.deliverydate and 1 not in
(select 1
from tt x
group by store, deliverydate, itemnumber
having x.store = b.store and x.deliverydate = b.deliverydate and
x.itemnumber <> b.itemnumber and count(*) = b.cnt)
Vishal Parkar
vgparkar@.yahoo.co.in

Another query problem

I am using Dreamweaver for the following:
Hi,
I have a problem with a search/results page, one of the fields is a date, I
want to display all results if the date is not entered in the search page
and display the records that match the date entered if any.
My problem is that if in the results page recordset I enter:
Lastworked = 'MMColParam7'
and I enter MMColParam7 01/01/1950 Request("Date")
Then the result is that if the date is entered I DO get the correct results,
but if the date is not entered I don't get any results at all becasue it
takes 01/01/1950 as default date.
Then I try to use
Lastworked LIKE 'MMColParam7'
and I enter MMColParam7 % Request("Date")
Then If i enter the date I get NO results, even where there are records that
match.
Any ideas ?
ACould you enter more info... Like the table structures of the two SQL tables
you are referring to, and the exact SQL Query you are sending to the databas
e
that is producing "incorrect" results? Some parts of Your post, specificall
y
Lastworked = 'MMColParam7'
are confusing. Lastworked sounds like a name for a column with dates in it,
but 'MMColParam7' is a string, not a date, and IT sopunds like the name for
another column...
"Aleks" wrote:

> I am using Dreamweaver for the following:
> Hi,
> I have a problem with a search/results page, one of the fields is a date,
I
> want to display all results if the date is not entered in the search page
> and display the records that match the date entered if any.
> My problem is that if in the results page recordset I enter:
> Lastworked = 'MMColParam7'
> and I enter MMColParam7 01/01/1950 Request("Date")
> Then the result is that if the date is entered I DO get the correct result
s,
> but if the date is not entered I don't get any results at all becasue it
> takes 01/01/1950 as default date.
>
> Then I try to use
> Lastworked LIKE 'MMColParam7'
> and I enter MMColParam7 % Request("Date")
> Then If i enter the date I get NO results, even where there are records th
at
> match.
> Any ideas ?
>
> A
>
>

Another Date time question

I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie" wrote in =message news:446c01c37baa$88=07dfa0$a601280a@.phx.gbl...I have a datetime column. This column has an index (Primary key). I =need to insert only the date part(not the time) so that when I run my DTS =package it only inserts one date (TODAY's DATE) without the time. How =can I insert today's date with only date part ?Thanks.

---

--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this ?. Is there any code out there to get this ?
Thanks.
Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com
|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Another Date Picker question

I created a report with two parameters, StartDate and EndDate.

They are specified as datetime type.

When I went to the Preview tab, there were date pickers for both values.

After a week or so of using the report, I thought it would be better if I gave the parameters default values. I spent some time reading what others had done in this forum and used the following as the expressions for the parameters:

Code Snippet

=IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2)))))))

=IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2)))))))

and now, there are no date pickers. I am curious. Why are they gone? Can I get them back and still have my default values?

Thanks,

IanO

Try removing the FormatDateTime function.|||

Thanks for your reply. Allow me to explain why the Format is there.

When I had not specified default values for the report parameters, the date pickers provided values that were dates only - no time values. After I put in the IIFs for the default values, the defaults included 12:00:00 AM with the date.

I hunted for quite a while and the solution that I chose was the Format. Perhaps you have another idea of how I can have a default Sunday and a default Saturday for the current week, without the time?

Thanks,

IanO

|||It's odd that the default values gave you a time. I used it here and had only date values. Hmmmm.|||If you want to keep the format text you should be able to add the cdate function.|||

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today(),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(-1),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(-2),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(-3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(-4),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(-5),2), FormatDateTime(Today.AddDays(-6),2))))))))

Code Snippet

=CDATE(IIF(Weekday(Today()) =1, FormatDateTime(Today.AddDays(6),2),
IIF(Weekday(Today()) =2, FormatDateTime(Today.AddDays(5),2),
IIF(Weekday(Today()) =3, FormatDateTime(Today.AddDays(4),2),
IIF(Weekday(Today()) =4, FormatDateTime(Today.AddDays(3),2),
IIF(Weekday(Today()) =5, FormatDateTime(Today.AddDays(2),2),
IIF(Weekday(Today()) =6, FormatDateTime(Today.AddDays(1),2), FormatDateTime(Today(),2))))))))

Then make sure your datatype is date because using just your original expression you get an error if the datatype is not a string.

Simone

Another Check Constraint Error.

I have created a check constraint on a field called Date in a table called
StudentInfo. In the constraint expression I put Date = GETDATE() and
unchecked the "check existing date on creation" and the "enforce constraint
for replication" check boxes. I left the "Enforce constraint for INSERTs and
UPDATEs" checked. However, when I teachers tried to update this field this
morning, and enter today's date, they all got check constraint errors. Does
anybody have any idea what might be wrong? All I want is for the Date field
to only accept the current date when being updated.
Any help would be appreciated, I only have 9 teachers but, everyday at least
1 will get the date wrong.
Thanks.
KevinKevin,
GETDATE returns the current date and time accurate to 1/300 of a
second. It's very unlikely that anyone entering data by hand would
happen to enter precisely this value. If you want to get a date-only
for today into the table, instead of having the teacher enter it, why
not use a default on that column of
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
column list and values the teacher enters?
SK
Kevin Sinclair wrote:
>I have created a check constraint on a field called Date in a table called
>StudentInfo. In the constraint expression I put Date = GETDATE() and
>unchecked the "check existing date on creation" and the "enforce constraint
>for replication" check boxes. I left the "Enforce constraint for INSERTs and
>UPDATEs" checked. However, when I teachers tried to update this field this
>morning, and enter today's date, they all got check constraint errors. Does
>anybody have any idea what might be wrong? All I want is for the Date field
>to only accept the current date when being updated.
>Any help would be appreciated, I only have 9 teachers but, everyday at least
>1 will get the date wrong.
>Thanks.
>Kevin
>
>|||I can't use a default because there is already a date in the field. What is
supposed to happen is the teachers update this field when they do their
daily attendance. However, at least 1 teacher every day, will input the
wrong date. When this database was in access I used a validation rule on
this field, I need a way to do the same thing in SQL Server 2000.
Thanks.
Kevin
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
> Kevin,
> GETDATE returns the current date and time accurate to 1/300 of a
> second. It's very unlikely that anyone entering data by hand would
> happen to enter precisely this value. If you want to get a date-only
> for today into the table, instead of having the teacher enter it, why
> not use a default on that column of
> DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
> column list and values the teacher enters?
> SK
> Kevin Sinclair wrote:
> >I have created a check constraint on a field called Date in a table
called
> >StudentInfo. In the constraint expression I put Date = GETDATE() and
> >unchecked the "check existing date on creation" and the "enforce
constraint
> >for replication" check boxes. I left the "Enforce constraint for INSERTs
and
> >UPDATEs" checked. However, when I teachers tried to update this field
this
> >morning, and enter today's date, they all got check constraint errors.
Does
> >anybody have any idea what might be wrong? All I want is for the Date
field
> >to only accept the current date when being updated.
> >
> >Any help would be appreciated, I only have 9 teachers but, everyday at
least
> >1 will get the date wrong.
> >
> >Thanks.
> >
> >Kevin
> >
> >
> >
> >
>|||Kevin,
You can check for the same value I was suggesting as a default:
create table Kevin (
...
dateCol datetime CHECK (dateCol = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
...
Steve
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>|||Oops. Ignore my previous message. The check constraint will fail
tomorrow if you update the row! You may need to use a trigger. What
you could do is put in the default and execute the update with:
UPDATE Attendance SET
this = whatever,
that = whatever,
dateCol = DEFAULT
WHERE
keyCol = theRowBeingEdited
SK
Kevin Sinclair wrote:
>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>>Kevin,
>> GETDATE returns the current date and time accurate to 1/300 of a
>>second. It's very unlikely that anyone entering data by hand would
>>happen to enter precisely this value. If you want to get a date-only
>>for today into the table, instead of having the teacher enter it, why
>>not use a default on that column of
>>DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
>>column list and values the teacher enters?
>>SK
>>Kevin Sinclair wrote:
>>
>>I have created a check constraint on a field called Date in a table
>>
>called
>
>>StudentInfo. In the constraint expression I put Date = GETDATE() and
>>unchecked the "check existing date on creation" and the "enforce
>>
>constraint
>
>>for replication" check boxes. I left the "Enforce constraint for INSERTs
>>
>and
>
>>UPDATEs" checked. However, when I teachers tried to update this field
>>
>this
>
>>morning, and enter today's date, they all got check constraint errors.
>>
>Does
>
>>anybody have any idea what might be wrong? All I want is for the Date
>>
>field
>
>>to only accept the current date when being updated.
>>Any help would be appreciated, I only have 9 teachers but, everyday at
>>
>least
>
>>1 will get the date wrong.
>>Thanks.
>>Kevin
>>
>>
>>
>
>

Another Check Constraint Error.

I have created a check constraint on a field called Date in a table called
StudentInfo. In the constraint expression I put Date = GETDATE() and
unchecked the "check existing date on creation" and the "enforce constraint
for replication" check boxes. I left the "Enforce constraint for INSERTs and
UPDATEs" checked. However, when I teachers tried to update this field this
morning, and enter today's date, they all got check constraint errors. Does
anybody have any idea what might be wrong? All I want is for the Date field
to only accept the current date when being updated.
Any help would be appreciated, I only have 9 teachers but, everyday at least
1 will get the date wrong.
Thanks.
KevinKevin,
GETDATE returns the current date and time accurate to 1/300 of a
second. It's very unlikely that anyone entering data by hand would
happen to enter precisely this value. If you want to get a date-only
for today into the table, instead of having the teacher enter it, why
not use a default on that column of
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
column list and values the teacher enters?
SK
Kevin Sinclair wrote:

>I have created a check constraint on a field called Date in a table called
>StudentInfo. In the constraint expression I put Date = GETDATE() and
>unchecked the "check existing date on creation" and the "enforce constraint
>for replication" check boxes. I left the "Enforce constraint for INSERTs an
d
>UPDATEs" checked. However, when I teachers tried to update this field this
>morning, and enter today's date, they all got check constraint errors. Doe
s
>anybody have any idea what might be wrong? All I want is for the Date field
>to only accept the current date when being updated.
>Any help would be appreciated, I only have 9 teachers but, everyday at leas
t
>1 will get the date wrong.
>Thanks.
>Kevin
>
>|||I can't use a default because there is already a date in the field. What is
supposed to happen is the teachers update this field when they do their
daily attendance. However, at least 1 teacher every day, will input the
wrong date. When this database was in access I used a validation rule on
this field, I need a way to do the same thing in SQL Server 2000.
Thanks.
Kevin
"Steve Kass" <skass@.drew.edu> wrote in message
news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
> Kevin,
> GETDATE returns the current date and time accurate to 1/300 of a
> second. It's very unlikely that anyone entering data by hand would
> happen to enter precisely this value. If you want to get a date-only
> for today into the table, instead of having the teacher enter it, why
> not use a default on that column of
> DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) and leave that column out of the
> column list and values the teacher enters?
> SK
> Kevin Sinclair wrote:
>
called
constraint
and
this
Does
field
least
>|||Kevin,
You can check for the same value I was suggesting as a default:
create table Kevin (
..
dateCol datetime CHECK (dateCol = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
...
Steve
Kevin Sinclair wrote:

>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>called
>
>constraint
>
>and
>
>this
>
>Does
>
>field
>
>least
>
>
>|||Oops. Ignore my previous message. The check constraint will fail
tomorrow if you update the row! You may need to use a trigger. What
you could do is put in the default and execute the update with:
UPDATE Attendance SET
this = whatever,
that = whatever,
dateCol = DEFAULT
WHERE
keyCol = theRowBeingEdited
SK
Kevin Sinclair wrote:

>I can't use a default because there is already a date in the field. What is
>supposed to happen is the teachers update this field when they do their
>daily attendance. However, at least 1 teacher every day, will input the
>wrong date. When this database was in access I used a validation rule on
>this field, I need a way to do the same thing in SQL Server 2000.
>Thanks.
>Kevin
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uAltir$7DHA.3112@.tk2msftngp13.phx.gbl...
>
>called
>
>constraint
>
>and
>
>this
>
>Does
>
>field
>
>least
>
>
>

Analysis Services Rolling Date reports

I have a Time and Billing OLAP cube which I'm running RS against. I'm
stumped on a date issue.
How can I run a report which selects the last 7 days worth of data and rolls
forward each week? I've tried using an MDX query with the Tail funtion.
This pulls the last date which has data in it. (Effectively yesterday's
date) When I run it in MDX builder it gives me the desired results. However
when I use it in VS.Net to build my report, it pulls the last 7 days over,
but it's static and doesn't roll forward.
I can make a report with a date drop down parameter which is described in
the AS and RS article on Technet, but I'd really like the parameter to be
automatically selected based off of the date when the report is run.
My date formats in my cube are [Year].[Quarter].[Month].[Day]
Can anybody help me with this?
Thanks, MattTry setting defaults for your start and end date parameters. For
example, you can set the end date to
DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
parameters in subscription creation, it may look like they are
hard-coded, but they aren't as long as the default checkbox is selected.|||Thanks for the suggestion... I think I'm close...
The problem is that my date needs to be in a [Time].[FY Calendar].[All
Time].[Year].[Quarter].[Month].[Day] format.
As you probably can tell, I'm a newbie to VB Scripting. How can I convert
your suggestion "DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")" to my cube
format? I think if I can do that, I can get it to work.
Thanks,
Matt
"Kenny" wrote:
> Try setting defaults for your start and end date parameters. For
> example, you can set the end date to
> DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
> DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
> parameters in subscription creation, it may look like they are
> hard-coded, but they aren't as long as the default checkbox is selected.
>