Database

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.

Ho to avoid Not In & In clause in my query

jschell14-Mar-14 12:15

jschell

14-Mar-14 12:15

I will just say that this sort of exercise seldom ends well without business requirements.

I am not going to analyze all possible interpretations but I will provide and example from your own data.

2013-10-17 2015-05-17

A business person asks you for how many years in that. All of the following are possible answers.
- One. 2013-10 to 2014-10 is one year, but 2014-10 to 2015-05 isn't.
- Zero. Because 2013-10 to 2014-10 is one year but it isn't 2014-10 yet
- Three. 2013, 2014, 2015 = 3.
- Two. 2013-10 to 2014-10 is one year, but 2014-10 to 2015-05 is partial, so round up to get 2.
- Five. The dates are supposed to be based on the contract period not the annual service dates (which the above dates are.)
- 17. Because the person that actually wants this wants a count of months covered.

DSN

Lek Plepi11-Mar-14 1:27

Lek Plepi

11-Mar-14 1:27

Hello Guys
I have installed one software that works with local ms access database.
If you remove that database of course the software displays the warning that the database is missing.
Can i migrate this database to Sql server so the program will work with Sql and not with Ms Access anymore????

Re: DSN

cdpsource11-Mar-14 2:14

cdpsource

11-Mar-14 2:14

Yes you can migrate it to SQL DB manually to be in safe side but your software must be compatible with data-source.

Re: DSN

Mycroft Holmes11-Mar-14 2:25

Mycroft Holmes

11-Mar-14 2:25

Yes, there is (was) even migration tool to help you upsize to a proper database (SQL Server). You then only need to change your connection string to point to the SQL Server database.

Never underestimate the power of human stupidity
RAH

member002611-Mar-14 0:43

member0026

11-Mar-14 0:43

select id,name from stud where (id not in (select id from class)) and depid in (select depid from dep where rid=@rid)

Bernhard Hiller11-Mar-14 1:04

Bernhard Hiller

11-Mar-14 1:04

What about:

SQL

Select DISTINCT id, name
FROM stud
INNER JOIN dep on stud.depid=dep.depid
LEFT JOIN class on stud.id=class.id
WHERE dep.rid=@rid 
AND stud.id is null

But I doubt that it's faster...

member002611-Mar-14 18:08

member0026

11-Mar-14 18:08

Im not sure, but compare not in the join will be the faster one, i heared like that Frown | :(

thatraja11-Mar-14 2:07

thatraja

11-Mar-14 2:07

JOIN/EXISTS
SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best[^]

thatraja

Code converters | Education Needed
No thanks, I am all stocked up. - Luc Pattyn
When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

Adding Sales Totals from multiple tables

GuyThiebaut11-Mar-14 3:54

GuyThiebaut

11-Mar-14 3:54

SQL

select id,name
from stud s
where not exists
(
    select c.id
    from class c
    where c.id = s.id
)
and depid in
(
    select depid
    from dep
    where rid=@rid
)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

jkirkerx9-Mar-14 10:54

jkirkerx

9-Mar-14 10:54

I'm trying to write better TSQL to reduce the amount of functions I have to do things.

Here I'm trying to add the total amount of sales from a column called GrandTotal from multiple tables
into a single result. First I'm not sure if this is even possible to do, but I think it can be done.

I've come this far, but I get 2 results returned. I'm just trying to consolidate them into one.

I tried SELECT SUM(TotalAmount) as the wrapper, but I get a syntax error
I tried UNION ALL without the )( around around it, but I get an error on the last )

The tables are identical in columns

DECLARE @StartDate AS Date;
DECLARE @StopDate AS Date;
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101);
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101);

SELECT
(
    SELECT SUM(GrandTotal)
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
)
    UNION ALL
(
    SELECT SUM(GrandTotal)
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
)

Mycroft Holmes9-Mar-14 14:16

Mycroft Holmes

9-Mar-14 14:16

Try - note that I named the sum fields.

SQL

Select sum(GTotal) Grand
From(
SELECT
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
)
    UNION ALL
(
    SELECT SUM(GrandTotal) as GTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
))

Personally I would normally include the common ID and use a Group By and then an inner join to where you need the Grand Totals

Never underestimate the power of human stupidity
RAH

jkirkerx9-Mar-14 17:44

jkirkerx

9-Mar-14 17:44

Thanks!

I understand the example, but I still get the ")" error in which I don't understand. There's only 3 of them.

The common ID and the GroupBy with the inner join is going to take me some time to experiment with.
I'm not really sure where to start with that one.

Jörgen Andersson9-Mar-14 22:01

Jörgen Andersson

9-Mar-14 22:01

Try to turn it into a CTE and see if that works:

SQL

WITH sums AS (
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrdersHistory
    WHERE OrderStatus='COMPLETED'
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate        
    UNION ALL
    SELECT SUM(GrandTotal) GrandTotal
    FROM CompletedOrders
    WHERE OrderStatus='COMPLETED'	
    AND OrderDate >= @StartDate
    AND OrderDate < @StopDate
    )
SELECT  Sum(GrandTotal) AS GrandTotal
FROM    sums

Or use a subquery which is the same thing but different syntax.

Wrong is evil and must be defeated.
- Jeff Ello[^]