Database

Jassim Rahma13-Mar-14 10:51

Jassim Rahma

13-Mar-14 10:51

so declaring it as foreign key is a good practice and will have an impact on the performance but after declaring it do you still recommend to index it or declaring is enough?

Technology News @ www.JassimRahma.com

_AK_13-Mar-14 11:07

_AK_

13-Mar-14 11:07

Creating a foreign key is not mandatory but is advised to create. Here[^] is a link which tells about the importance of it.
Now here[^] is another article which tells about refrential integrity and performance degradation which might give you a bit more idea about this.

.AK.

Jassim Rahma13-Mar-14 11:16

Jassim Rahma

13-Mar-14 11:16

Great

Thanks

Technology News @ www.JassimRahma.com

Re: Group by (first letter)

_AK_13-Mar-14 11:21

_AK_

13-Mar-14 11:21

You are welcome.

.AK.

Group by (first letter)

nelsonpaixao12-Mar-14 16:42

nelsonpaixao

12-Mar-14 16:42

Hello,

in some table, how can i perform a group by not by the column string but only by the letter of that column

thanks Big Grin | :-D

nelsonpaixao@yahoo.com.br

trying to help & get help

Kornfeld Eliyahu Peter12-Mar-14 20:03

Kornfeld Eliyahu Peter

12-Mar-14 20:03

Yes.

SQL

GROUP BY SUBSTR([COLUMN], 1, 1)

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Re: Group by (first letter)

nelsonpaixao16-Mar-14 9:50

nelsonpaixao

16-Mar-14 9:50

thanks

nelsonpaixao@yahoo.com.br

trying to help & get help

sum of datediff to many dates

ahmad_yossef11-Mar-14 4:08

11-Mar-14 4:08

i have this periods of experience i want to calculate the sum or total period of these periods

2004-01-06 2004-07-05
2004-07-07 2004-11-30
2010-07-01 2010-07-29
2011-06-12 2011-07-28
2012-07-22 2012-08-20
2013-10-17 2015-05-17

Lost & Forgotten

Tim Carmichael11-Mar-14 5:40

Tim Carmichael

11-Mar-14 5:40

If you provide the query you are trying to get working, you are more likely to get help.
What is the underlying database? SQLServer, Oracle, Postgres, etc?

ahmad_yossef11-Mar-14 23:05

11-Mar-14 23:05

sql
i made a query calculate the datediff in days and sumed all days now i want to convert this days (integer) to years months and days
thanks

Lost & Forgotten

Tim Carmichael12-Mar-14 10:57

Tim Carmichael

12-Mar-14 10:57

ahmad_yossef wrote:
i want to convert this days (integer) to years months and days

Well, break it down.. if you have the number of days, how many days in a year? Let's assume 365; so number of years in int(number of days / 365).
Months... let's assume 30 days on average... Months = int((number of days - years * 365) / 30)
And days are then number of days - years * 365 - months * 30

If that doesn't work, better define what you're after.
Given the sample dates you provided, what are the values you calculated for years, months and days?

ahmad_yossef12-Mar-14 23:19

12-Mar-14 23:19

thanks for your replay
but i face a problem when i have a number like 1456

the query will be like that

SELECT dayss, dayss / 365 AS years, (dayss - dayss / 365 * 365) / 30 AS Months, (dayss - dayss / 365 * 365) - (dayss - dayss / 365 * 365) / 30 * 30 AS days
FROM dbo.lv_npay_tmp3

which will return

days1 years months days
1456 3 12 1

and it should be

days1 years months days
1456 4 0 1

i hope you got me

thanks alot

Lost & Forgotten

Richard Deeming13-Mar-14 1:10

Richard Deeming

13-Mar-14 1:10

1456 == (3 × 365) + 361.

That's clearly not 4 years and 1 day.

The problem is that you're assuming 365 days per year, but only 30 days per month. (30 × 12) = 360, so you've lost five days.

You need to assume (365 / 12) = 30.4166666... days per month, which will give you an answer of 3 years, 11 months and 27 days.

SQL

SELECT
    dayss, 
    dayss / 365 As years, 
    Cast(Floor((dayss % 365) * 12 / 365.0) As int) As Months, 
    (dayss % 365) - Cast(Floor((dayss % 365) * 12 / 365.0) * 365.0 / 12 As int) As days
FROM
    dbo.lv_npay_tmp3

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kornfeld Eliyahu Peter11-Mar-14 23:48

Kornfeld Eliyahu Peter

11-Mar-14 23:48

If the answer for your question isn't satisfying, than refine your question - do not post it again...

http://www.codeproject.com/Forums/1725/Database.aspx?select=4775204&tid=4775204[^]

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

ahmad_yossef12-Mar-14 2:23

12-Mar-14 2:23

i am sorry

Lost & Forgotten

Richard MacCutchan12-Mar-14 3:53

Richard MacCutchan

12-Mar-14 3:53

Click on your name and you can find all your previous messages.