sql server new users: Query Help -- sql server new users

You're in the

sql server new users

group:

Query Help

Re: Query Help

Roy Harvey
6/20/2007 12:00:00 AM

sql server new users:

On Wed, 20 Jun 2007 04:02:51 -0700, Hulicat <dennis_A_white@yahoo.com>
[quoted text, click to view]

Since the SELECT does not calculate them, how could it provide
different values by location? If the values have to vary with a
SELECT they can not be @variables, which are constant when used that
way.

This might give you some ideas to think about. Note that it does not
use any pre-calculated values and the only @variables are the two
dates. Note also that the open at start calculation was simple
because the date range matched the date range of the outer query,
while the test for open at end tested a different date and so required
its own subquery. That subquery is a CORRELATED subquery, meaning it
references a value (location) from the outer query to limit the rows
it uses.

And finally note that to calculate the difference without repeating
the derivation of the two values I put the entire query into a derived
table (X) and calcualted the difference in the outer query.

SELECT Opened, [Total Open at Start], [Total Open at End],
[Total Open at End] - [Total Open at Start] as [Total Closed],
Cient, [Average days open]
FROM (SELECT count(*) AS 'Opened',
SUM(CASE WHEN status_type_id LIKE '[1,2,6,7]'
THEN 1 ELSE 0
END) AS 'Total Open at Start',
(select count(*) from dbo.job_ticket as B
where j.location_id = B.location_id
and status_type_id like '[3,4,5]'
and last_updated between @startdate and @enddate)
AS 'Total Open at End',
location_name AS 'Cient',
AVG(datediff(d,report_date,getdate() ))
AS [Average days open]
FROM job_ticket j
JOIN location l on l.location_id = j.location_id
WHERE (report_date BETWEEN @startdate AND @enddate)
GROUP BY l.location_name) as X

One last warning... the use of BETWEEN to test date ranges is a
problem when the dates being tested, such as last_updated, are not
all at midnight. If there is any non-zero time portion the data for
the last day will be ignored. It is generally better for the end date
of the range to be the NEXT day and instead of useing BETWEEN use:

last_updated >= @startdate AND last_updated < @enddat

noting that the second test is < and that @enddat is the NEXT day
after the end.

Roy Harvey

Query Help

Hulicat
6/20/2007 2:30:02 AM

Below is a select statement that will be a stored procedure at some
point.
I will only be passing @startdate and @enddate as user defined.
For the purpose of testing I set them to dates

Note:
I am trying to get the data for @totalatstart"total opened at start"
and @totalatend "total opened at end" to be specifc to "Client"

declare @startdate datetime
declare @enddate datetime
declare @totalatstart int
declare @totalatend int
DECLARE @difference int

set @startdate ='03/01/2007'
set @enddate = '06/11/2007'

Set @totalatstart =(select count(*) from job_ticket where report_date
[quoted text, click to view]
Set @totalatend =(select count(*) from job_ticket where report_date <
@enddate)
set @difference =(@totalatstart-@totalatend)

select count(*) as 'Opened', @totalatstart 'Total Open at Start',
@totalatend 'Total Open at End', @difference 'Total Closed',
location_name 'Cient',
AVG(datediff(d,report_date,getdate() )) AS [Average
days open]
from job_ticket j
inner join location l on l.location_id = j.location_id
where (report_date between @startdate and @enddate)

group by l.location_name

Current resluts look like this:

Opened | Total Open at Start | Total opened at End | Total Closed |
Client AVG
47 341|48293
Alexandria, 70
59|34148 293 Austin, 63

Obviously something is wrong with my set statment or my entire
approach to this
Any suggestions or help in the right direction would be greatly
appreciated.

I have been reading and can not seem to figure this out.

Regards,

Re: Query Help

Hulicat
6/20/2007 4:02:51 AM

[quoted text, click to view]

Thanks for the reply...
I modified the two set statements:

Set @totalatstart =(select count(*) from dbo.job_ticket where
status_type_id like '[1,2,6,7]' and report_date between @startdate and
@enddate)

Set @totalatclose =(select count(*) from dbo.job_ticket where
status_type_id like '[3,4,5]' and last_updated between @startdate and
@enddate)

The results should return opened status for the 1st set statment
within the date range and the 2nd should return closed status.

My problem is the results for those sets are an aggragate it does not
group by location as specified in the select.

Results look like this:

@totalatstart
@totalatclose
2 596 30
596 Cal
17 596 30
596 Ore
11 596 30
596 Wash
110 596 30
596 Md

Thanks,

Re: Query Help

Roy Harvey
6/20/2007 5:58:52 AM

The first two SET queries appear suspect to me. They are apparently,
based on the name, intended to track the total open at two different
points in time. I would expect the SAME test (less than) for both,
with just the date different, but instead the first date is testing
for greater and the second for lesser. I would expect the date in the
table to be tested for being less than the startdate or enddate
because it sounds like you want to know about what has happened up to
that point.

Also I see no test for "open" status anywhere, so I guess that part of
the specification simply means created.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 02:30:02 -0700, Hulicat <dennis_A_white@yahoo.com>
[quoted text, click to view]

Re: Query Help

Hulicat
6/20/2007 12:51:25 PM

[quoted text, click to view]

Thanks for explaining this, it's greatly appreciated.

Re: Query Help

Hulicat
6/20/2007 1:37:03 PM

[quoted text, click to view]

Once again thanks Roy...

I know I am a total newbie and this is trivial.....However, attempts
to remedy have failed.

I understand the flow of what you did....I just ddn't know some of the
methods.

when I run that query I get:
el 16, State 1, Line 12
Column 'job_ticket.LOCATION_ID' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY
clause.

So the obvious whould be to add it to the Group By clause? which I
tried....
The line it errors on is the 1st line of the select statement which
does not make sense to me.

either the lack of sleep has got me beat or I need to read a lot more

Re: Query Help

Roy Harvey
6/20/2007 8:25:36 PM

Adding it to the GROUP BY sounds right to me.

It is hard to debug complex queries from a distance (even when I put
the bug there!)

When I write something large like this I start by writting a bit and
getting that working, then adding a bit more and making sure that
works, and so on. It is hard to show that approach here, and bugs are
harder to find once all the complexity is together. I suggest picking
the query out of the middle trying to get that to work.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 13:37:03 -0700, Hulicat <dennis_A_white@yahoo.com>
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn