How To View Activities Of Companies

Jun 16, 2008

Hi, I've a table named Attività there I can have many type "IDAttivitaTipo" field:

T Telefonata
V Visita
P Preventivo
C Chiuso
 
CREATE TABLE [dbo].[Attivita](
[IDAttivita] [int] IDENTITY(1,1) NOT NULL,
[IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
[IDAnagrafica] [int] NULL,
[Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()),
[Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED
(
[IDAttivita] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
with "IDAttivitaTipo":

T Telefonata
V Visita
P Preventivo
C Chiuso
 
with "IDAttivitaStato"
E Eseguita  (held)
D Da Eseguire (not held)



I would like to see the state of each IDAnagrafica (company) creating a query with X rows (X companies ... X IDAzienda) and 4 fields 1/0 (true/false ....bit) that tell me if there is or there isn't an activity (IDAttivita) in T V P C type for each IDAzienda (company) .... with IDAttivitaStato E (held)
T -> 1/0
V -> 1/0
P -> 1/0
C -> 1/0



I've created a SP with a parameter "IDAttivitaTipo" that allow me to view all campanies (IDAnagrafica) stopped in the "IDAttivitaTipo"

if I use 'V' parameter in the SP
I want to see all the companies stopped in V (IDAttivitaStato E -> Held) ...that don't have activities in P and C state .... I'm not interested if there are activities in T state


if I use 'P' parameter in the SP

I want to see all the companies stopped in P (IDAttivitaStato P ->
Held) ...that don't have activities  C state .... I'm not interested if there are activities in T or V state


if I use 'C' parameter in the SP

I want to see all the companies stopped in C (IDAttivitaStato C ->
Held) ...that don't have activities  after C .... I'm not interested if there are activities in T or V or P state


I've tried with these query but the result insn't right ... I can't to see some activies

where is it my error??

IF(@IDAttivitaTipo= 'V')
BEGIN

SELECT

(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata,
(case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita,
(case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo,
(case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso
FROM Anagrafica
WHERE
('V' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND
('P' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND
('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica))
END



IF(@IDAttivitaTipo = 'P')
BEGIN

SELECT

(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata,
(case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita,
(case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo,
(case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso
FROM Anagrafica
WHERE
('P' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND
('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND
END

View 1 Replies


ADVERTISEMENT

Is Folder App_data Uses To Store Database By .net 2.0 Hosting Companies?

Mar 21, 2006

I am workin on a website for me and my friend. I want to host it at a hosting company. In asp.net 2.0 projects are build with the App_data folder, which can stores data source such as a SQL Server 2005 database. But when i want to host the site at a hosting company, will the database goed into this folder?

View 1 Replies View Related

Dba Activities

Sep 20, 2006

can any one tell me what are the sql server dba activities?
i'm a junnior dba in hyderabad, my organization is product based company.

i need the general activities, so that i can become master in atleast some aspects.

generally what is the expectations of big organizations from DBA's? especially i'm plannning for H1.

View 5 Replies View Related

Can Not See Current Activities

Mar 31, 1999

Today when I opened current activity window on my SQL6.5 server, I could not able to see any activities listed. What will be the problem?

But sp_who works fine.

Srini

View 1 Replies View Related

Auditing Sa Activities

Jun 24, 1999

Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done?
Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?

View 1 Replies View Related

Current Activities - No Items

Jun 4, 2001

We are having blocking issues on our server. Recently, I noticed that we no longer have any thing under Current Activity. When I click the plus sign beside it, it shows no items - that is, no process info, locks/process ID, or the locks/object. Does anybody know why and how I can reset it?? Thanks for your help.

View 3 Replies View Related

Auditing Database Activities

Aug 5, 1999

We are finding ourselves editing data within a sql database using tools such as MS Query, Access or VB. Is there anyway to log these edits? Auditing is set up within the application to log changes made by the users but not by third pary applications. ANy thoughts?

Thanks,
David

View 2 Replies View Related

Tracking User Activities

Oct 14, 2005

Sybase and DB2 both have the capability of tracking user activities ata number of levels: invalid access attempts to databases, table, etc.;creation/deletion/modification of database objects/users/groups,grants/revokes.For MS SQLServer, the only setting that I've seen in the documentationis access attempts (none, fail only, etc.)The monitor program has the capability of tracking the events that Iwant to be monitored, but it seems as though these settings persistonly while the monitor program is running.I'd like these settings to persist permanently and the event records tobe sent to the system log.I can't seem to find the right term to get this information out of theMS Books On LIne.Help!

View 5 Replies View Related

Scheduling Maintenance Activities

Jun 26, 2006

Am I able to do this in SS Express -like a backup or something or do I need to purhcase a SQL Server Agent (I believe I saw a 3rd party company always advertising this in SQL Server Central.com). It's cheap -- just curious if I really need it.

View 1 Replies View Related

How To Monitor All Sql Query Activities On Sql Server 7.0?

Nov 4, 2004

Hi Gurus,
I have a database server running on a sql server 7.0. Some external app is accessing this db server, reading and updating some tables. For some reason, some updating actions didn't work. I just want to track those updating actions, and see if there're some logs reporting failure reason. Is there some tool in sql server 7.0 that could help me do that? Thank you.

View 2 Replies View Related

Calculating Duration Of Activities Per Hour

Jul 25, 2014

My data is in the following form:

CREATE TABLE Temp (ORG_NAME VARCHAR(20), START_TIME datetime, END_TIME datetime)

INSERT INTO Temp VALUES('Org Name', '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000') - AND many more like these with different START_TIME and END_TIME.

The Task:

- I need to calculate the duration of the activities PER HOUR.

i.e. in the example above, if I want to see the productivity for 2PM (i.e. activity duration from 2-3PM), I should only get 35 mins (as the activity started at 14:25). In the same way, if I see the productivity for 3PM (i.e activity duration from 3-4 PM), I should only see 25 minutes (as activity ended at 15:25).

There would technically be many activities with overlapping times - for example, there might be 5 activities starting at 14:30 and ending at 15:10. In this case, if I were to see the productivity for 2PM, I'd see 150 minutes (as each activity starts at 14:30, so 30 min per activity = 150 min). In the same way, if I saw the productivity for 3PM for those 5 activities, I'd see 50 minutes.

View 1 Replies View Related

T-SQL (SS2K8) :: CDC Activities On Receiving Machine?

Aug 19, 2014

Is there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.

View 3 Replies View Related

Auditing User And Security Related Activities In SQLServer

Jul 23, 2005

On the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!

View 2 Replies View Related

Customize Database Admin Activities By Creating New User Group?

Aug 17, 2012

The requirement is to customize database admin activities by creating new user group.

Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.

This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.

View 1 Replies View Related

Transact SQL :: Server Trigger To Monitor Member Of Sysadmin Activities

Jul 22, 2015

I have to make server trigger to monitor the actitites if sysadmin members. i need to get the login name,hostname and query which they are running.

View 3 Replies View Related

Analysis :: Creating User Who Can Only Do User Management Activities?

Nov 25, 2015

I would like to limit the role of an user In Visual Studio only to assign roles to other users for the cubes. Other than that the user should not be able to create / delete the exisiting cubes or dimensions.

View 2 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

Updating My View Changes My View Content

Feb 17, 2006

I have this view in SQL server:

CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt


When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:

SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt

I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help

View 4 Replies View Related

View Works, But The Sql From The View Does Not

Oct 27, 2006

I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)

View 1 Replies View Related

Selecting A View And Selecting FROM A View Is Wildly Different

Feb 21, 2006

A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.

He wants to use the data returned from that view in an OLE DB Source component.

When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).



He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>

In this instance all the data was returned in approx 60 seconds (as expected).





This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.

Can anyone explain why?

Thanks

-Jamie

View 2 Replies View Related

Need Help With A View

Mar 29, 2007

I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year.
tblPayments (ClientID, PmtID, PmtDate, PmtAmt)
How would I set the criteria for this?

View 1 Replies View Related

Need Help Using A View.

Feb 7, 2008

How do I set up a sql statement to use as a view?
I want to use two tables but they are both from different databases.
From the Offices database I want to use a table called Office Code and only the Name column
The other database is called Library and I want to use a table called Requestors and join it to others.
I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database.
Create view GetDataview
select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name],  Titles.title as Title from requestors
join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column?
 I hope this  makes sense

View 6 Replies View Related

Sql View Help

May 18, 2008

how can i   make a view in my sqlexpress 2005  database that get data from oracle database or from RDB (old oracle database)

View 6 Replies View Related

View ???

May 18, 2001

I am trying to write a view to retrieve data from
two different servers..
create view as
select c1 from ser1.database1.dbo.table1
union
select 1 from ser2.database2.dbo.table1

First it is not accepting to write it from EM, hence I wrote it from QA,
the problem when I am trying to execute it is giving following error

"Could not find server 'hercules' in sysservers"

Help me out what needs to be done?

View 2 Replies View Related

View S

Jan 12, 2001

i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.

ur help is appriciated.

View 1 Replies View Related

View

Sep 21, 2000

I have a view which I am supposed to run manually first thing tomorrow morning but I will not be
in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.

View 1 Replies View Related

View

May 7, 2002

How many maximum number of tables I can used to create the view in SQL 6.5..?

View 1 Replies View Related

VIEW Bug??

Oct 24, 2007

When I ran this SQL Query, I got an unexpected result.

//Sql Query

Code:

SELECT distinct Year FROM vw_EMD_Options_Pricing WHERE (Year >= '2003') AND (Year <= '2008')



The view is the "vw_EMD_Options_Pricing". The request I got is the year that go from 1981 to 2008. This is for MS-SQL 2000. Why does the SQL Query does that?

View 2 Replies View Related

View

Oct 20, 2004

I have a database with a view to another table in another database which I created a form out of. I was wondering when if this rule applies
"Data Entry" property of the form is set to True. The default value for this property is "False" which means that Access opens the form and shows the existing records. However, if set to True, the Data Entry property of the form specifies that the form will only show a blank record.


The reason I am asking is because you cant see the records after you close the form and reopen it again.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved