help constructing a headache query...

I have two tables, both with phone numbers and call times.

one is for incoming calls, one for outgoing calls.

I need to find all phone numbers from the incoming calls table where the number of calls exceeds 100 within the last 30 days, where the last call was within the last 15 mins, and where the number does Not exist in the outgoing call table within the last 30 days.

so far I have this...
(call record is the incoming, callout is the outgoin)

I believe this is giving me all records in the call record table that are within the last month, and not in the outgoing call table OR have not ben called within the last month..

SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN callout co
ON cr.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
and datediff(dy,cr.starttime,getdate())>30
group by cr.cli
order by cr.cli

i need to add in the 15 minute call check, and also only return those with a count of > 100

can anyone assist? i'm getting a headache :D

tia

aHi

Untested aircode but perhaps:


SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN (SELECT cli FROM callout WHERE datediff(dy,co.calltime,getdate())>30) co
ON cr.cli = co.cli
where co.cli is null
group by cr.cli
HAVING COUNT(DISTINCT cr.Cli) >100 AND DATEDIFF(n ,MAX(cr.endtime), getdate()) <=15
order by cr.cli

I've assumed there is an end time to the calls that this is when the clock starts ticking...|||there isn't an endtime in these tables.. the call duration isn't relevant, only that a call was made within the last 15 mins, and that the nuimber of calls over the last 30 days have exceeded 100.

that does seem to return a better resultset...

I'll have a look.. thanks :D

a|||there isn't an endtime in these tables.. the call duration isn't relevant, only that a call was made within the last 15 mins, and that the nuimber of calls over the last 30 days have exceeded 100.

Ah - in that case replace the end time with the start time (obviously - reckon you probably figured that :D )|||Select
CallIn.cli
,CallIn.firstcall
,CallIn.lastcall
,CallIn.numberofcalls
from
(
SELECT
cr.cli
,min(cr.starttime)as "firstcall"
,max(cr.starttime)as "lastcall"
,count(cr.cli) as "numberofcalls"
FROM
callrecord cr
where
datediff(dy,cr.starttime,getdate())>30
group by
cr.cli
having
count(cr.cli) > 100 and max(cr.starttime) > dateadd(mi,-15,getdate())
) CallIn
LEFT JOIN callout co
ON callin.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
Here You go ...

Help calling DTS package.

I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}

Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}

This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.

Any ideas??

ThanksAfter a few modifications I'm getting a different error now. But it still is obviously a permissions problem. Everything I've read says set Impersonation equal to true. Unfortunately it already is. Any suggestions from you SQL Server gurus?

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Thankssql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help - how to execute an sp using linked server

When I run from Main server which has linked server connection - sp executes but the resultset does not get displayed. When I call the same proc from SSRS - I am getting 'an error occured when retrieving parameters for the query. sp does not exist'

Thanks

you will have a tab called 'Command Type' there you change it to Stored Procedure. Then try to execute also make sure you have declared all the parameters before executing this.

Hope it solves your problem.

HELP - Combining Rows in a View

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

HenryRemember, you asked! I'd use:CREATE TABLE tHenry (
grade INT
, name VARCHAR(20)
)

INSERT INTO tHenry (grade, name)
SELECT 8, 'John'
UNION ALL SELECT 8, 'Mike'
UNION ALL SELECT 8, 'Ed'
UNION ALL SELECT 9, 'Tom'
UNION ALL SELECT 9, 'Greg'
UNION ALL SELECT 10, 'Jack'
UNION ALL SELECT 10, 'Tony'
GO

CREATE FUNCTION dbo.fHenry(@.piGrade INT) RETURNS VARCHAR(200) AS
BEGIN
DECLARE @.cList VARCHAR(8000)

SELECT @.cList = Coalesce(@.cList + ', ' + name, name)
FROM tHenry
WHERE grade = @.piGrade

RETURN @.cList
END
GO

CREATE VIEW vHenry AS SELECT DISTINCT TOP 100 PERCENT
grade, dbo.fHenry(grade) AS students
FROM tHenry
ORDER BY grade
GO

SELECT * FROM vHenry
GO

DROP VIEW vHenry
DROP FUNCTION dbo.fHenry
DROP TABLE tHenry-PatP