Database Discussion Boards

Mycroft Holmes15-Jul-09 12:31

15-Jul-09 12:31

I work for the mothership and disk space is not an issue. Now I understand why you have such a horrible setup.

I would think replication would not be a good solution with your space issue. That leaves your solution, linked server. I'd kill the lot and set up 1 id across all environments purely for this requirement. None of them integrated.

Never underestimate the power of human stupidity
RAH

Average value after 3 time intervals.

Robert Vrinceanu14-Jul-09 5:52

Robert Vrinceanu

14-Jul-09 5:52

I have a simple table with 3 fields: ID, Tag1, Data

ID      Tag1            Data
________________________________
1     2     6/1/2009 22:00
2     1     6/1/2009 23:00
3     2     6/2/2009 6:00
4     3     6/2/2009 7:00
5     2     6/2/2009 8:00
6     2     6/2/2009 9:00
7     2     6/2/2009 10:00
8     1     6/2/2009 11:00
9     2     6/2/2009 11:30
10     1     6/2/2009 13:00
11     2     6/2/2009 14:00
12     2     6/2/2009 15:00
13     2     6/2/2009 16:00
14     1     6/2/2009 17:00
15     2     6/2/2009 18:00
16     1     6/2/2009 19:00
17     1     6/2/2009 20:00
18     2     6/2/2009 21:00
19     2     6/2/2009 22:00
20     3     6/2/2009 23:00
21     1     6/2/2009 23:59
22     2     6/3/2009 1:00
23     3     6/3/2009 2:00
24     2     6/3/2009 3:00
25     3     6/3/2009 4:00
26     2     6/3/2009 5:00
27     3     6/3/2009 6:00
28     2     6/3/2009 7:00
29     3     6/3/2009 8:00
30     2     6/3/2009 9:00
31     3     6/3/2009 10:00
32     2     6/3/2009 11:00
33     1     6/3/2009 11:55
34     2     6/3/2009 22:00
35     2     6/3/2009 23:00

Must I do a average AVG(Tag1) after 3 hourly intervals over a period of time (3 tours).
Hourly intervals are:

10.30 PM - 6.30 AM,   (represents tour1)
06.30 AM - 14.30 PM, (represents tour2)
14.30 PM - 22.30 PM, (represents tour3)

I tried this:

SELECT
        AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM')   AND DATEPART(hour,'06:30:00 AM')   THEN Tag1 ELSE NULL END) AS tour1,
        AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'6:30:00 AM')   AND DATEPART(hour,'2:30:00 PM')   THEN Tag1 ELSE NULL END) AS tour2,
           AVG(CASE WHEN DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'2:30:00 PM')   AND DATEPART(hour,'10:30:00 PM') THEN Tag1 ELSE NULL END) AS tour3
FROM         dbo.TableTest
WHERE         (DataTime BETWEEN '6/1/2009 10:00:00 PM' AND '6/3/2009 11:00:00 PM')
GROUP BY DATEPART(DAY,DataTime)

The result looks like:

tour1         tour2                     tour3
__________________________________________________________
NULL           NULL                 1.0
NULL           2.2222222222222223     1.7777777777777777
NULL           1.7142857142857142     2.0

Note:
For tour1 and tour2, belonging on the same day, is well calculated. The problem is tour1 - time which belongs to two days.
Can help me someone in this problem?
Thanks.

Regards,
Robert

Re: Average value after 3 time intervals.

Luc Pattyn14-Jul-09 6:22

Luc Pattyn

14-Jul-09 6:22

Hi,

I'm no SQL expert however IMO DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM') can't be right since that ignores the half hours completely.

Can't you just use some function to get the time, something like TIME(DataTime) NOT BETWEEN '06:30:00' AND '22:30:00'?

BTW: I felt a need to eliminate the 12hour timing, and to invert the statement.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: Average value after 3 time intervals.

Robert Vrinceanu14-Jul-09 8:52

Robert Vrinceanu

14-Jul-09 8:52

Thanks for your answer.
Unfortunately, I can't waive these time intervals.
What I don't understand is why

SELECT ID
FROM dbo.TableTest
WHERE Data BETWEEN CONVERT(DATETIME,(CONVERT(varchar(10),Data, 101)+ ' 10:00:00 PM'),101) AND CONVERT(DATETIME,(CONVERT(varchar(10),DATEADD(DAY,1,Data), 101)+ ' 06:00:00 AM'),101)

return this:                           and not return:
ID                              ID
__                              __
1                              1
2                              2
19                              3
20                              19
21                              20
34                              21
35                              22
                              23
                              24
                              25
                              26
                              27
                              34
                              35
Regard,
Robert

Setting up User Accounts.

Droze14-Jul-09 5:35

Droze

14-Jul-09 5:35

Hello everyone;

I am not asking for Code or anything to time consuming , just would love to have some direction/web guides.

I've recently started a new job and my boss wants me to implement user account into their current website. I'll need to collect use information, send a verification email and than store their information.
a few problems i have at the moment
-Unsure of who is hosting the website, but i do have an ftp upload to it through fetch.
-office runs on macs

Any help would be very appreciated.

Thank you.

- Droz -
___ ___
/\/\ /\ | |

Re: Setting up User Accounts.

DoctorMick14-Jul-09 6:23

DoctorMick

14-Jul-09 6:23

Not sure if this question would be more suited to the web dev forum.

What language is the current web site developed in?
What DB does it use?

Re: Setting up User Accounts.

Droze14-Jul-09 7:14

Droze

14-Jul-09 7:14

The current website is using HTML/CSS/small amounts of javascript. I can recode the site if needed, i'll have to add a lot to it for the account things anyhow. I'll copy this to the web development forum also.
Thank you for any advice you are able to share.

- Droz -
___ ___
/\/\ /\ | |

Re: Setting up User Accounts.

ScottM115-Jul-09 2:43

ScottM1

15-Jul-09 2:43

Rebuild the website using ASP.Net and use the built-in membership DB and controls, ASP.Net membership tutorial[^]

AS 400 System tables

Ersan Ercek14-Jul-09 0:28

Ersan Ercek

14-Jul-09 0:28

Hello All,

I know it is out of scope of this group but I was wondering if anybody knows about AS400 whether or not it has a system database holding user_defined item names (tables fields etc).

Thanks alot.

Re: AS 400 System tables

David Skelly14-Jul-09 6:19

David Skelly

14-Jul-09 6:19

Yes, it has all this information available in what is called the system catalog. You can get hold of the information through native commands like DSPFD or DSPFFD but you can also get hold of it via SQL queries if that's better for you. I'm a bit rusty with DB2/400 so I don't remember the exact table names to query but if you google for "AS/400 system catalog" you should find what you're looking for.

Re: AS 400 System tables

Giorgi Dalakishvili14-Jul-09 6:21

Giorgi Dalakishvili

14-Jul-09 6:21

i5/OS catalog tables and views[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

how to know current date and time

janani1313-Jul-09 21:37

janani13

13-Jul-09 21:37

how to know current date and time.(i.e) date and time may vary across countries.how sql server will find the correct date and time.

Re: how to know current date and time

Eddy Vluggen13-Jul-09 22:31

Eddy Vluggen

13-Jul-09 22:31

Convert your DateTime to GMT; you'd convert every date you get from the user to GMT before sending it to the database. When reading the datetime from the database, adjust to the local timezone of the user. More information can be found on MSDN[^].

Good luck Smile | :)

I remain troll Smile | :)

Re: how to know current date and time

janani1313-Jul-09 23:26

janani13

13-Jul-09 23:26

thx for your info.

Re: how to know current date and time

Md. Marufuzzaman14-Jul-09 19:18

Md. Marufuzzaman

14-Jul-09 19:18

Hi..
You can use the following:

SELECT GATEDATE() AS CURRENT_DATE

Md. Marufuzzaman

Re: how to know current date and time

ScottM115-Jul-09 2:47

ScottM1

15-Jul-09 2:47

It would be "SELECT GETDATE()" but that would not work if the user is in a different timezone.

display exactly 2 digits after decimal point in money

janani1313-Jul-09 18:59

janani13

13-Jul-09 18:59

how to display exactly 2 digits after decimal point in money???

Re: display exactly 2 digits after decimal point in money

_Damian S_13-Jul-09 19:27

_Damian S_

13-Jul-09 19:27

If you mean when selecting a value in SQL Server (using TSQL), you can use cast like this:

select cast([FIELDNAME] as decimal(10,2))

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: display exactly 2 digits after decimal point in money

goodideadave14-Jul-09 7:24

goodideadave

14-Jul-09 7:24

Check out STR function in BOL.

My other signature is witty and insightful.

Re: display exactly 2 digits after decimal point in money

Niladri_Biswas15-Jul-09 16:00

Niladri_Biswas

15-Jul-09 16:00

Hi,

Suppose I have a number like 123456.98072340

My output should look like 123456.98 i.e. precisely 2 digits after decimal.

The query is:

SELECT CAST(ROUND(123456.98072340, 2, 1) AS DECIMAL(18, 2))

The generalised query will be

SELECT CAST(ROUND(InputNumber, N, 1) AS DECIMAL(18, N))

Where N=1,2,3,4,5,....

and it indicates the number of decimal places you want to display.

Hope this helps
Smile | :)

Niladri Biswas

sqlpubwiz Command Line Issue.

_Maxxx_13-Jul-09 17:33

_Maxxx_

13-Jul-09 17:33

I didn't know whether to reply to my original question or post new - but as the original was asked (accidentally) under a different user name, I though I'd start a new thread. Apologies to anyone offended by that. (Original Here)[^]

I have sqlpubwiz 1.3 and it fails to script correctly on SQL Server 2008 if there are any foreign keys (or other constraints).
I'm kind of assuming this is something I am doing - as it's a pretty useless tool otherwise -
I have test db with two tables

CREATE TABLE [dbo].[Table_1](
	[id] [int] NOT NULL,
	[num] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table_2](
	[id] [int] NOT NULL,
	[num] [int] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_2]  WITH CHECK ADD  CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([id])
REFERENCES [dbo].[Table_1] ([id])
GO

ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GO

Then I (try to) script it...

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2> sqlpubwiz script -d myDatabase -S localhost\MYDATABASE -f .\test.txt

and get the error...

Microsoft (R) SQL Server Database Publishing Wizard
Version 10.0.0.0
Copyright (c) Microsoft Corporation.  All rights reserved.

Generating script for database stit1
- Generating script targeted for SQL Server 2005
Gathering list of objects to script
Scripting objects
.....Error: Operation is not valid due to the current state of the object.

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2>

Delete the foreign key and all is well.

I'd really appreciate it if someone could either confirm or deny the fact that this is 'just me' or, in fact, sqlpubwiz is a pile of donkey doo doo

___________________________________________
.\\axxx
(That's an 'M')

Re: sqlpubwiz Command Line Issue.

Josh Handley13-Jul-11 0:20

Josh Handley

13-Jul-11 0:20

I'm able to use the publishing wizard command line to script a database with foreign keys. I was getting the same error but then I found this post:

http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/ecfd4379-ee88-4a60-81a4-d6cc9542b1ff[^]

which said to make sure that the case of the database name has the correct case. When I fixed that the error went away.

Help with Built-in Database: Visual Web Developer 2008 Express

pstyles113-Jul-09 5:05

pstyles1

13-Jul-09 5:05

Hi Guys!

Am at my wits end! Please help me with any suggestions.

I have recently created a website for my father's Driving School which has database properties built in. I created it using Visual Web Developer 2008 and am now trying to make it possible for his clients to view any lesson dates and times of future lessons. Each client has their own username and password to access their own user area on the site. Obviously I want each client to be able to see their own times but not those of other clients. My father will be inputting the dates and times of all clients lessons himself.

How would I best go about this? My father is fairly IT literate, but would like all dates and times in a central database if possible. Please help. Many thanks.

Re: Help with Built-in Database: Visual Web Developer 2008 Express

_Damian S_13-Jul-09 19:32

_Damian S_

13-Jul-09 19:32

Well, when your father saves the dates and times into the database, have him select a client from a dropdown list (so that he selects a valid client), then when your clients log into the system, store their ClientID in the session state or similar, and when selecting values from your database for lessons, restrict it by ClientID.

eg:

select LessonDate, LessonTime from tblLesson where ClientID = @ClientID

populating @ClientID with the ClientID of the logged in user.

Help required for CTE

Zafar Sultan13-Jul-09 3:33

Zafar Sultan

13-Jul-09 3:33

This is my stored procedure

ALTER PROCEDURE TeamStructure
(
@rootid int
)
AS
WITH MyTeam
AS
(
SELECT *, [Name] AS ParentName, 0 AS TeamLevel FROM tbl_team
WHERE ParentID =@rootid UNION ALL
SELECT Fam.*,MyTeam.Name AS ParentName, TeamLevel + 1 FROM tbl_team AS Fam
INNER JOIN MyTeam ON Fam.ParentID = MyTeam.ID)
SELECT * FROM MyTeam

It will return the complete hierarchical records.
Level 0 will have root member
Level 1 will have 2 members
Level 2 willhave 4 members
Level 3 will have 8 members
or if level =n
members= 2^n
I need to perform the following operation:
for any level if members are less than 2^n the iteration should stop and return the number of members on that level and the number of level
For example if the number of members on level 4 are 12
The procedure should stop and return
level :4
Members :12
but if the number of members on nth level is exactly 2^n the iteration must look for the next level
Can anyone modify this procedure and help me please?
TIA

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.