dimension question

I'm creating a datawarehouse and I have a question on a date dimension. My
users want to see the date formatted as such yyyy-mm-dd so they can sort on
it. How can something like this be possible? I never did this so I'm new to
the datawarehouse - cubes - dimension things.On 15.03.2007 14:00, John wrote:
> I'm creating a datawarehouse and I have a question on a date dimension. My
> users want to see the date formatted as such yyyy-mm-dd so they can sort o
n
> it. How can something like this be possible? I never did this so I'm new t
o
> the datawarehouse - cubes - dimension things.
This is not really DWH specific. Normally output formatting is client
work. You could add a calculated column or create a view that does the
conversion of a timestamp to a particular time format.
robert

Difficult SQL-Problem

Hello !

This is my table:

Ordernr Date Article
O1 1.1.05 22
O2 2.2.05 33
O3 5.5.05 22
O4 2.2.05 33
O7 8.8.05 55

I need one result-row for each article with the newest Order
(max(date)):

article lastDate lastOrdernumber
22 5.5.05 O3
33 2.2.05 O4
55 8.8.05 O7

How can I get this ?

I tried this:

SELECT distinct article, max(date), max(ordernr)
FROM table
GROUP BY article

article and max(date) is ok, but I am not sure that max(ordernr) and
max(date) comes from the same row.

I think, I will need complex subqueries.

Many thanks
aaapaulPerhaps something like this?

select
t.article,
t.ordernr,
t.orderdate
from
dbo.MyTable t
join
(
select
article,
max(ordernr) as 'ordernr'
from
dbo.MyTable
group by
article
) dt
on t.article = dt.article
and t.ordernr = dt.ordernr

If this doesn't give the results you expect, I suggest you post CREATE
TABLE and INSERT statements to set up a test case - you will probably
get a better response if people can copy and paste something into QA
for testing.

Simon|||Thanks Simon !

But the Problem is, that I need the order with the highest date not the
order with the highest ordernumber.

Perhaps you can modify the statement...

aaapaul|||Or perhaps you can :-) Just replace max(ordernr) with max(orderdate)
and change the join to be on that column.

Simon|||SELECT article, date, MAX(ordernr)
FROM Table AS T
WHERE date =
(SELECT MAX(date)
FROM Table
WHERE article = T.article)
GROUP BY article, date

--
David Portas
SQL Server MVP
--|||Thanks David !

This is what I need.

paul|||Hi Simon !

Thanks, but this doesn t work, too.

I think I need something like this:

(SELECT a.article,a.odate,max(a.ordernr) as maxordernr
FROM TEST a
GROUP BY a.article,a.odate) t1
JOIN
(
SELECT article,max(odate) as maxdat
FROM TEST
GROUP By article
) t2
on t1.article = t2.article and
t1.odate = t2.maxdat

But why cant I join this 2 tables.

Any suggestion ?

Thanks
aaapaul|||in SQL 2000

create table orders(Ordernr int, orderdt Datetime, Article int)

insert into orders values(1,'11/11/2005',1)
insert into orders values(3,'11/12/2005',1)
insert into orders values(5,'11/13/2005',1)

insert into orders values(2,'1/11/2005',2)
insert into orders values(4,'1/12/2005',2)
insert into orders values(6,'1/13/2005',2)

SELECT article, lastdate,
(select MAX(ordernr) from orders AS T
where t.article = latest.article
and t.orderdt = latest.lastdate)
from
(SELECT article, MAX(orderdt) lastdate
FROM orders
GROUP BY article) latest

article lastdate

---- ----------------
----
1 2005-11-13 00:00:00.000 5
2 2005-01-13 00:00:00.000 6

(2 row(s) affected)

drop table orders

in 2005,
use an OLAP function
(row_number() over(partition by article order by orderdt desc) = 1|||Hi David,
Excellent !!
But we can trim the query further If the lastOrderNumber is the
OrderNumber with Max(Orderdate) for a given article .

SELECT *
FROM orders AS T
WHERE orderdt =
(SELECT MAX(orderdt)
FROM orders
WHERE article = T.article)

With warm regards
Jatinder Singh

David Portas wrote:
> SELECT article, date, MAX(ordernr)
> FROM Table AS T
> WHERE date =
> (SELECT MAX(date)
> FROM Table
> WHERE article = T.article)
> GROUP BY article, date
> --
> David Portas
> SQL Server MVP
> --

DIFFERENTIAL backup

Just want to clarify the date from which a DB backup with DIFFERENTIAL
option includes its changes.
I have a db which I do a full backup on Saturday, and a differential the
other days (this is plenty in our case)
If someone performs an ad-hoc backup for whatever reason during the
week, say prior to a roll-out, I assume the DIFFERENTIAL backup that
night will only include changes back to the ad-hoc backup? Can I force
it to go back to the Saturday one at all?> If someone performs an ad-hoc backup for whatever reason during the week, say prior to a roll-out,
> I assume the DIFFERENTIAL backup that night will only include changes back to the ad-hoc backup?
Yes, assuming that the ad-hoc backup was a full database backup.
> Can I force it to go back to the Saturday one at all?
No. However the ad-hoc backup should have been taken using the COPY_ONLY clause (new in 2005). That
will not break the backup sequence for diff backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Rum" <bmwbase-newsgroup@.yahoo.com> wrote in message news:eul5oi$h51$1@.news-02.connect.com.au...
> Just want to clarify the date from which a DB backup with DIFFERENTIAL option includes its
> changes.
> I have a db which I do a full backup on Saturday, and a differential the other days (this is
> plenty in our case)
> If someone performs an ad-hoc backup for whatever reason during the week, say prior to a roll-out,
> I assume the DIFFERENTIAL backup that night will only include changes back to the ad-hoc backup?
> Can I force it to go back to the Saturday one at all?

Different Execution Plans -> Same Query, Same Database, Different SQL Server Install

Background: Same database, restored from development to production.

Indexes up to date. Service Pack 3 on Production. Originally RTM on Development, upgraded to Sp4, Execution Plan remained the same.

The query itself is not my concern, but that there is such a wide difference in performance between 2 boxes. Is there a setting on the Production that might be slowing it down? My system seems to have selected a worktable, is there a way I can turn that off to mimic the server?

I want to optimize the queries to perform best on the Production server, which is hard to do if my server's chosing different execution plans.

Is there anyway to determine why the Production machine would choose one plan vs. the development machine?
Development:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Index Spool(SEEK:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
| |--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

Production:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]), WHERE:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

The following is the output of the statistics on my Local Development
Server:
Table 'CONSUMER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3187 ms, elapsed time = 1877 ms.
This is the output of the statistics on the Production environment:
Table 'CONSUMER'. Scan count 88857, logical reads 274907, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 41708, logical reads 48294390, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 690218 ms, elapsed time = 225583 ms.

Have you checked that the statistics are equal for the indexes on both servers?

Look for DBCC SHOW_STATISTICS in the BOL.
|||

Try and rebuild the Indexes on Production Database

DBCC DBREINDEX

|||

The databases were identical, both restored from the same backup resources on the same day.

Thank you for your responses, and I will attempt both, but I don't believe that the statistics or indexes would be different if drawn from the same resource?

|||This is not due to statistics or indexes. It should be identical when you restore from a backup of a database. There are other factors that can affect the query plan - number of processors, memory, maxdop settings etc. Additionally you should try to keep the service pack level the same between the machines. The main reason is that you could get different plans due to changes in service pack and some could be bugs also. So it is best to compare servers that are on the same service pack too. If you are seeing worse performance than SP3 for the same query/machine configuration then it is most probably a bug. I would encourage you to create a bug using the MSDN Product Feedback Center with the repro steps. Also, please use SET STATISTICS PROFILE to check the actual query plan/execution times.|||

It should indeed be identical when you restore but a colleague here has experienced some strange behavior after a restore several times. He claims that after an update of the statistics everything functioned as expected. Now I can't say I fully support this statement as I have no personal experience with this.

We tend to keep our service packs identical to production, I think this should be a rule for every development environment.

You should update your weblog Uma, you still have to answer my NOLOCK question :-)

|||

Thanks for your reply,

The production is running SP3a. I tested the query on development environment at both RTM and SP4 (not realizing the production was still only at SP3) and I did get the same plans between RTM and SP4.

The production machine is Windows 2003 server dual processor,Intel Xeon 3.6ghz, with 3.5 GB Memory, it is part of a cluster server.
The development machine is Windows XP dual processor, Intel Pentium 4 2.6ghz, with 1 GB Memory.

I will compare the profiles in the am.

Different Default Date format for 2 SQL Server Instances

Hi,
We recently had a new environment created. The servers were all installed
as separate instances on the same physical machine.
For my first instance INST1
When I execute the following query exec getMyData '1964-11-19'
in query analyser everything is fine
from my (ASP) website everything is fine
For my second instance INST2
in query analyser everything is fine
from my asp website I get varchar cannot be converted to datetime.
I can only believe that the default settings for the server were different
when each of the SQL Server Installs were performed.
I cannot change the way we pass dates in our website because it is a massive
re-write of everytihg if I do.
Is there some why of changing the default settings of the server after
installing?
I have tried using
sp_configure
SET language
sp_defaultlanguage
and all these methods did not fix my problem.
Any ideas?
JayneK wrote:
> Hi,
> We recently had a new environment created. The servers were all
> installed as separate instances on the same physical machine.
> For my first instance INST1
> When I execute the following query exec getMyData '1964-11-19'
> in query analyser everything is fine
> from my (ASP) website everything is fine
> For my second instance INST2
> in query analyser everything is fine
> from my asp website I get varchar cannot be converted to datetime.
> I can only believe that the default settings for the server were
> different when each of the SQL Server Installs were performed.
> I cannot change the way we pass dates in our website because it is a
> massive re-write of everytihg if I do.
> Is there some why of changing the default settings of the server after
> installing?
> I have tried using
> sp_configure
> SET language
> sp_defaultlanguage
> and all these methods did not fix my problem.
> Any ideas?
When working with dates in character format, you should only ever use a
portable format. Two formats are supported that will never cause
problems related to the server's regional settings:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
yyyymmdd
What is probably occurring is that one server is using MDY format and
the other is using DMY.
For example:
SET NOCOUNT ON
SET DATEFORMAT MDY
SELECT CAST('1964-11-19' as DATETIME)
SET DATEFORMAT DMY
SELECT CAST('1964-11-19' as DATETIME)
-- Results
1964-11-19 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
David Gugick - SQL Server MVP
Quest Software
|||David,
Unfortunately they all have all their language setting exactly the same. So
they are all set to us_english as default language, yet one server acts
differently to the other.
How can we fix this? do we have to uninstall and re-install, can't we hack
a file or something?
"David Gugick" wrote:

> JayneK wrote:
> When working with dates in character format, you should only ever use a
> portable format. Two formats are supported that will never cause
> problems related to the server's regional settings:
> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
> yyyymmdd
>
> What is probably occurring is that one server is using MDY format and
> the other is using DMY.
> For example:
> SET NOCOUNT ON
> SET DATEFORMAT MDY
> SELECT CAST('1964-11-19' as DATETIME)
> SET DATEFORMAT DMY
> SELECT CAST('1964-11-19' as DATETIME)
> -- Results
> 1964-11-19 00:00:00.000
> Server: Msg 242, Level 16, State 3, Line 9
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||JayneK wrote:[vbcol=seagreen]
> David,
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
> "David Gugick" wrote:
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @.MyDate =
REPLACE(@.MyDate, '-', '')
David Gugick - SQL Server MVP
Quest Software

Different Date Formats when using xp_cmdshell

Hi all,
If I use the TSQL command:-
EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
What determines the date format from of the output? I have 2 PC's (XP and
W2K) both with the same regional settings (DMY), both giving the same date
format output (DMY) for the dir command in a command window, but giving
different date formats with the above command.
I've tried changing the regional settings in the CP and also using the
DATEFORMAT command but the nothing changes the date format of the output.
I want to make sure the command returns the same format regardless of the
database, country or anyother SQL option/setting.
Thanks in advance.
Greg
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replyingWho is logged into the machine in each case? What account is SQL Server
running under? What windows user is issuing the command?
The only way you can expect the output to be consistent is to be sure that
regional settings on all machines are identical, as are the regional
settings of all system accounts (e.g. the one SQL Server runs under) and all
accounts that may be logged in at the time the command is run.
A
"Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
> Hi all,
> If I use the TSQL command:-
> EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
> What determines the date format from of the output? I have 2 PC's (XP and
> W2K) both with the same regional settings (DMY), both giving the same date
> format output (DMY) for the dir command in a command window, but giving
> different date formats with the above command.
> I've tried changing the regional settings in the CP and also using the
> DATEFORMAT command but the nothing changes the date format of the output.
> I want to make sure the command returns the same format regardless of the
> database, country or anyother SQL option/setting.
> Thanks in advance.
> Greg
>
> --
> greghines@.bigfoot.com.NOSPAM
> Remove NOSPAM when replying
>|||The only logged in user in both cases is sa.
Account for both is dbo
It's not a windows user it's sa. Both use only SQL Authentication.
As I said the regional settings are the same. Proven by the fact that the
dir command in a command window gives the same date format on both PCs.
Greg
--
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replying
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23Zf2Ay3FHA.696@.TK2MSFTNGP09.phx.gbl...
> Who is logged into the machine in each case? What account is SQL Server
> running under? What windows user is issuing the command?
> The only way you can expect the output to be consistent is to be sure that
> regional settings on all machines are identical, as are the regional
> settings of all system accounts (e.g. the one SQL Server runs under) and
all
> accounts that may be logged in at the time the command is run.
> A
>
> "Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
> news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
and
date
output.
the
>|||> The only logged in user in both cases is sa.
You can't be logged into Windows as sa. Believe it or not, this user may
have a bearing on how dir returns results.
Also, SQL Server is either running as a specific Windows user, or the "local
system" account. The service itself does not run as dbo or sa -- Windows
has absolutely no idea what those mean.
A

Different date for each instance

Is there a way to run multiple SQL instance on the same server with different date and time for each ?
No. SQL Server uses the system date and time. If you are talking about
time zones though you can always handle time zones in your code.
Jeff Duncan
MCDBA, MCSE+I
"Elsay Papantout" <luc.cloutier@.costco.com> wrote in message
news:E8169651-5200-4E16-915E-279E471683D3@.microsoft.com...
> Is there a way to run multiple SQL instance on the same server with
different date and time for each ?
|||Thanks Jeff. Then I'll try running SQL over Virtual Server.

Different date for each instance

Is there a way to run multiple SQL instance on the same server with differen
t date and time for each ?No. SQL Server uses the system date and time. If you are talking about
time zones though you can always handle time zones in your code.
Jeff Duncan
MCDBA, MCSE+I
"Elsay Papantout" <luc.cloutier@.costco.com> wrote in message
news:E8169651-5200-4E16-915E-279E471683D3@.microsoft.com...
> Is there a way to run multiple SQL instance on the same server with
different date and time for each ?|||Thanks Jeff. Then I'll try running SQL over Virtual Server.