Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:
select count(defendanteventpros.vbkey) as countcase from defendanteventpros
where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007')
having count(defendanteventpros.vbkey)>4
The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).
What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.
I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance 201501 201502 201503 TOTAL Number of events 8 10 10 28 Time 5 5 4 14 Ratio 8/5 10/5 10/4 28/14
I am trying to calculate a whole school total tuition. I've got helped with individual tuition total which is
create proc sp_individualtuition as select c.contractNum, (c.tuition-((sum(d.discountPer))* c.tuition)) as totaltuition from contract c, contractDiscount cd, discount d where c.contractNum = cd.contractNum and cd.discountNum =d.discountNum group by c.contractNum, c.tuition
however how can i calculate the whole school total tuition (adding all the individual total tuition)?
my contract, contractDiscount and discount DDL are down below
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
create table contractDiscount( contractNum int not null, discountNum char(3) not null );
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
We keep a database of events that are classified by severity. I've got a good, efficient query that gives me the grand total for these events. And I have a bad slow query that breaks down the information into daily totals.
My fast query will count the totals for 3213586 events in 4 seconds. The slow daily query takes about 60 seconds for each day.
Please help optimize my slow query!
------ Database schema is:
Column Name Data Type Length Allow Nuls
1 msrepl_tran_version uniqueidentifier 16 0
0 [time] GMTtime (int) 4 0
0 msec int 4 1
0 offset GMToffset (smallint) 2 0
0 type nameType (varchar) 15 1
0 host nameType (varchar) 15 1
0 process nameType (varchar) 15 1
0 dbName nameType (varchar) 15 1
0 ptName nameType (varchar) 15 1
0 description descType (varchar) 47 1
0 rtuName nameType (varchar) 15 1
0 groupName nameType (varchar) 15 1
0 message msgType (varchar) 131 0
0 fgInt tinyint 1 1
0 sevInt tinyint 1 1
0 key1 int 4 1
0 key2 int 4 1
0 spooler tinyint 1 1
------- The database view eventView that's used by my queries: SELECT DATEADD([second], [time] + 60 * offset, '01/01/70') AS timeStr, msec, host, process, dbName, ptName AS point, description, rtuName AS rtu, groupName, message, type, sevInt, [time] FROM dbo.summary
------- Fast totals query:
select (select count(*) from [event].[dbo].[eventView]) as Events, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 1)as Low, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 2)as Medium, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 3) as High, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 4) as Low1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 6) as Medium1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 8) as High1, (select count(*) from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 9) as High2
------- Slow daily query:
declare @myDay datetime declare @begDay datetime
declare @myTable table( myDay datetime, Events int, Low int, Medium int, High int, Low1 int, Medium1 int, High1 int, High2 int )
select @myDay = getdate()
select @begDay = convert(datetime, '10/01/2006')
while @begDay <= @myDay begin
insert into @myTable (myDay) values (convert(varchar(20), @begDay, 101))
update @myTable set Events = (select count(*) as Events from [event].[dbo].[eventView] where convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Low = (select count(*) as Low from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 1 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Medium = (select count(*) as Medium from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 2 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High = (select count(*) as High from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 3 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Low1 = (select count(*) as Low1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 4 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set Medium1 = (select count(*) as Medium1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 6 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High1 = (select count(*) as High1 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 8 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
update @myTable set High2 = (select count(*) as High2 from [event].[dbo].[eventView] where type = 'alarm' and sevInt = 9 and convert(varchar, timeStr, 101) = convert(varchar(20), @begDay, 101)) where myDay = convert(varchar(20), @begDay, 101)
I have a sql server express 2005 database with a table named Events with a column named Date (datetime datatype). I want a query that will display all rows that are either current or future but not past. I suspect there is a simple way of doing this. As a Newbie searching archived threads this is what I have come up with so far. I determine the number of days from present:SELECT DATEDIFF(day, Date, GETDATE()) AS NumberOfDays FROM EventsThis yields number of days from present with positive numbers in the past and negative numbers in the future. Thus setting a WHERE clause to <= 0 would limit my results to present or future events. Something like this:SELECT * FROM EventsWhere DATEDIFF(day, Date, GETDATE()) AS NumberOfDays <= 0The error message states: "Incorrect syntax near the keyword 'AS'"This feels like a clumsy way to approach this problem, but I have to start where I am.Any suggestions on how to proceed will be greatly appreciated.
I use this code in a utility procedure (for performance testing) but it is really slow.
For example, a session with three events is taking 5 seconds to complete this query:
DECLARE @xml xml= ( SELECT CAST(xet.target_data AS xml) FROM sys.dm_xe_session_targets AS xet JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address) WHERE xe.name = @name );
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
I have events which require certain things be done several days before the event and things be done several days after the event. I attempted to use the DATEADD function to subtract 3 days from the event date. The SQL Statement I created did just that, but, it displays 3 days back from today's date.
There are 2 tables:
CalendarCategories -- Table
CalCategoryID -- int field CalCategoryName -- varchar field
CalendarEvents -- Table
CalCategoryID -- int Field Title -- varchar Field StartDate -- DateTime Field
I have to perform some tasks 3 days before the event. So, TODAY, I want to see a listing of those events which are scheduled for 3 days FROM NOW.
This is my current SQL Statement: SELECT DATEADD(d,-3,StartDate) AS [Update Payoffs & Ins], Title AS [Closing Description] FROM CalendarEvents WHERE datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate())
This SQL Statement takes TODAY'S events and gives them a date of February 20. See example of the results at http://www.joelwilliamslaw.com/DesktopDefault.aspx?tabid=141
This is what I have already done relative to my calendar listings:
Specific Event Types for the Current Month :
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Specific Event Types for the Current Day:
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Hi all As you know when you run a piece of TSQL scrypt in Query Analizer at the bottom of the page , sql-server will show you the Time of completion of your code....is there any way to capture this time from SQL-Server environment and use it in the Front-End Application to inform the user?
AA Guyz i want to calculate class position of students from a table.Sample data is as follows...Roll # - Name - Marks1 - ABC - 602 - DEF - 603 - GHI - 574 - JKL - 555 -MNO - 506 -PQR - 53The query should return the following result.Roll # - Name - Marks - POSITION1 - ABC - 60 - 12 - DEF - 60 - 13 - GHI - 57 - 34 - JKL - 55 - 45 -MNO - 50 - 56 -PQR - 53 - 6I want query in MS SQL Server 2000
I need to get the Grand Total of the results of this query. The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _ " Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _ " (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _ "(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session ("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
Let say I have this table in MS SQL server table transaction(date,sales)
how to query to get result like this (date,sales,sum(sales last 7 day)) I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.
note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day
select col1,count(*) from client1..table1 group by col1 union select col1,count(*) from client2..table1 group by col1 union select col1,count(*) from client3..table1 group by col1
The results yields
33915 3405 3412
I am trying to get the following result but can't figure out how to get the total in the end.
I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is
Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date)) which gives me a result like inv_cust ' Salescount
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
SELECT vehicleref, capID, make, model, derivative, COUNT(vehicleref) total, SUM(case when inStock=1 then 1 else 0 end) AS stock, (SELECT dealer FROM tblMatrixDealers WHERE id=dealerid) As Dealer FROM tblMatrixStock WHERE inStock = 1 GROUP BY vehicleref, capID, make, model, derivative, dealerid
I need to get the number in stock, i.e. when instock=1 and the total i.e. when instock=1 or 0
But the problem is i'm only showing records where instock=1 so my SUM(case when inStock=1 then 1 else 0 end) AS stock statement is useless.
I have a query that uses a CTE which looks similar to this
WITH cte_Products AS (SELECT SELECT ROW_NUMBER() OVER (ORDER BY ProductName ASC) AS RowNum, RANK() OVER (ORDER BY id DESC) AS rank, FROM Products WHERE SomeColumn = @SomeVariable) SELECT rownum, columns, (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount FROM cte_Product AS products WHERE Rank <= @LastXItems AND RowNum BETWEEN (@StartRowIndex + 1) and (@StartRowIndex + @MaximumRows) ORDER BY RowNum
The (SELECT COUNT(*) FROM cte_Product) AS TotalRowCount is in there because I need the total row count that is queried by the CTE. But I feel like this is an inefficient way of doing this. I would just split out the query for the total row count, but then I have to do another sub query to get the rank again since rank is calculated using the Rank method. Does anyone have any ideas of how best to do this?
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
