January 20, 2010 at 9:30 am
I'm writing a query that has to do day count, calculations, etc, off of two tables.
can someone point me in the best direction in creating the query?
Here is what I have so far, but now I'm stuck on creating a calc based off another calc
SELECT DATEDIFF(dd, tbl1.Opendate, tbl1.ClosedDate) as NumberOfDays
tbl2.RequiredCompletionDays, tbl1.AcctNumberr
FROM tbl1.INNER JOIN
tbl2 ON tbl1.Task = tbl2.TaskNumber
WHERE (tbl1.Qrtr = N'3') AND (tbl2.Year = N'2008')
and ClosedDate is not null
Now, I have to use OpenDate, however if OpenDate is NULL I have to use another date column, then I need to calculate a field named
var which is tbl2.RequiredCompletionDays - NumberOfDays
how can I perform the remaind
January 20, 2010 at 9:37 am
Do you mean that your NumberofDays calc needs to test for OpenDate being NULL?
If so, use COALESCE. Since you have not specified where that second date column comes from, I can't give you an exact query, but here's an example of how you can work it:
SELECT DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate) as NumberOfDays
tbl2.RequiredCompletionDays,
tbl1.AcctNumberr
FROM tbl1
INNER JOIN tbl2 ON tbl1.Task = tbl2.TaskNumber
WHERE tbl1.Qrtr = N'3'
AND tbl2.Year = N'2008'
AND ClosedDate IS NOT NULL
January 20, 2010 at 10:20 am
John Rowan (1/20/2010)
Do you mean that your NumberofDays calc needs to test for OpenDate being NULL?If so, use COALESCE. Since you have not specified where that second date column comes from, I can't give you an exact query, but here's an example of how you can work it:
SELECT DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate) as NumberOfDays
tbl2.RequiredCompletionDays,
tbl1.AcctNumberr
FROM tbl1
INNER JOIN tbl2 ON tbl1.Task = tbl2.TaskNumber
WHERE tbl1.Qrtr = N'3'
AND tbl2.Year = N'2008'
AND ClosedDate IS NOT NULL
that worked, now how can I do a calculation from a non calculated field and a non cacluated field such as?
var = RequiredCompletionDays - NumberOfDays
I get an error (NumberOfDays is not a column)
January 20, 2010 at 10:32 am
not sure if this the best way but i did this.
(RequiredCompletionDays - DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate)) as variance
and it returned my correct number.
January 20, 2010 at 10:33 am
SQL Server does not recognize when you use a column alias within the same query. You have to fully define NumberOfDays in your calculated column.
SELECT DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate) as NumberOfDays
tbl2.RequiredCompletionDays,
tbl1.AcctNumberr,
[var] = tbl2.RequiredCompletionDays - DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate)
FROM tbl1
INNER JOIN tbl2 ON tbl1.Task = tbl2.TaskNumber
WHERE tbl1.Qrtr = N'3'
AND tbl2.Year = N'2008'
AND ClosedDate IS NOT NULL
January 20, 2010 at 10:34 am
SQL_NuB (1/20/2010)
not sure if this the best way but i did this.(RequiredCompletionDays - DATEDIFF(dd, COALESCE(tbl1.Opendate,tbl?.YourOtherDateColumn) , tbl1.ClosedDate)) as variance
and it returned my correct number.
Yep, that's exactly how you have to do it.
January 20, 2010 at 10:34 am
that's what i did actually and it worked.
thanks
January 20, 2010 at 10:37 am
I love coding against a moving target. 🙂
Now, I just got asked to count all items (var) that is a negative number,
so if var is -5, -6, -20, etc, how can I count them and show a total of all negative var values?
January 20, 2010 at 10:42 am
January 20, 2010 at 10:43 am
yes
January 20, 2010 at 10:55 am
Some of this depends on how you want to summarize and display the data. Let's rewind a bit.
Can you provide an example of how you want your final result set to look as well as the sample data and table DDL that I would use to produce the final result set? This would help get you a working query that meets your requirements without having to go through several more iterations.
You can use the article in my signature for examples on how to post the table DDL and sample data that I'll need for this.
January 20, 2010 at 11:01 am
i just need a final count, so if i run my query (as above) and I get 100 records and 48 records are below 0, I need to count them. So i need to show 48, That's it. I only need to count only negative variance numbers.
January 20, 2010 at 12:03 pm
SELECT SUM(CASE WHEN RequiredCompletionDays - NumberOfDays < 0 THEN 1 ELSE 0 END) as NegativeVarianceCount
FROM ........
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply