Need assistance with SQL query

Hi,

I have a table which I want to analyse and get the following results from:

The table contains is a helpdesk table and contains information on a job, who it is allocated to and when it was submitted.

I'm trying to break down the table and find out what jobs are submitted to a certain group of people and the length of time the job was sitting there.

So for example if there are 10 jobs in the queue, I want to be able to say ok out of the 10 jobs, 5 of them were logged in the last 7 days, 2 were logged in the last fortnight and the rest in the last month.

SELECT ticket_id, priority,submitted_on,deadline,assigned_to
FROM pt6hd_ticket_view
WHERE (NOT status = 'closed' and (assigned_to = 'sean' or assigned_to = 'andrew' or assigned_to = 'matthew' or assigned_to = 'kevin' or assigned_to = '!service_desk' or assigned_to = 'belinda'))
ORDER BY submitted_on ASC

The above is my code so far...

Any help would be appreciated.. thanks.very decent way of doing this in the arena of data warehousing or other wise is to have a table to define week,quarter,half yearly,yearly fields
and then you can run joins to run between any two dates/weekly or etc... I guess it would be of help.. Sorry I cant give you any code as it is boring to code some old sql...|||Hi,
Your explanation and the example is a bit confusing.
Still as per my understanding.

if ur table is TRY with the following structure

Name Null? Type
---------- --- --
TID NUMBER
SUBMITiON DATE
DEADLINE DATE
ASSIGNEDTO VARCHAR2(4)
STATUS VARCHAR2(1)
PRIORITY NUMBER

The following query will give the GROUP ASSIGNED TO, NUMBER OF DAYS PENDING FROM , NUMBER OF JOBS Pending.

SELECT ASSIGNEDTO ,COUNT(TID), TRUNC(SYSDATE-SUBMITION) FROM TRY
WHERE STATUS !='C'
GROUP BY ASSIGNEDTO , TRUNC(SYSDATE-SUBMITiON);

I hope this helps
Regards

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >