Transact SQL :: Notification When A Group Of Jobs Complete
Apr 23, 2015
I have a large number of jobs that I run using a cursor (All jobs start with PS_ and each job loads a single table from ORACLE) so they can run in parallel. I want to be able to be notified when the last job completes. Should this done using a trigger? Or should I just add a loop statement to the stored procedure that fires this job? I know which individual takes the longest and I could simply just add a notification to send email on success but I want it to be more robust. Below is what I am using to trigger the jobs.
BEGIN
SET NOCOUNT ON;
DECLARE @year CHAR(4)
DECLARE @month CHAR(2)
DECLARE @day CHAR(2)
SET @year = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)
I am trying to write a T-SQL script to change about 200 SQL Agent jobs to send notification to a different email notification list. I could do this manually but it would take me a lot more time. Is there a way to script this out in T-SQL? Thanks!
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
I have a query which sends email notification of count of pending name and suggested name.. on a daily basis. So this works fine but now I want like if the pending and suggested are ' o count' I don't want the email notification. It should not send an email, if it is o count, but if we have the number for pending and suggested this email should be sent.
I try to find out how many jobs where run in parallel on my server in an interval of time. For example: between 1:00 AM and 2:00 AM there were MAX 66 jobs that run in parallel and MIN 4 jobs. I am not sure if I can find this info out from a system view or I need to play with sysjobhistory view.
-Whenever terminatedate is not null -Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No - with in the ID group Whenever terminatedate is null
My output should look like this..
ID WorkId HireDate TerminateDate Flag 1 123 2006-01-05 2013-01-13 yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null) 1 456 2006-02-23 2013-04-13 yes 1 789 2005-12-12 2013-06-13 yes 2 913 2004-12-15 2012-01-13 yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
I have 3 columns of data CustomerNum, Newsletter, and NumSent.
I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.
Is this possible? I have tried using row over partition but I'm not sure how group it correctly, so basically every time there is a new 1 in new_commsstream within a personid the row number goes up by one.
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty 1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis same) 3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.
Here is my DDL:
CREATE TABLE [dbo].[SV00403]( [CUSTNMBR] [char](15) NOT NULL, [ADRSCODE] [char](15) NOT NULL, [Contract_Number] [char](11) NOT NULL, [WSCONTSQ] [int] NOT NULL,
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv FROM (`DailyReport` `DailyReport` INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.
For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.
BRESRAD1 Normal Appointment 46 BRESRAD1 Diagnostic Appointment 15 BRESRAD2 Normal Appointment 17 BRESRAD2 Diagnostic Appointment 12 BRESRAD3 Normal Appointment 34 BRESRAD3 Diagnostic Appointment 43
My SQL is as follows:
SELECT ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt], COUNT(OPAppointmentID) AS CountOfOPAppointmentID FROM dbo.OP_APPOINTMENT WHERE (AttendStatusNatCode IN ('5', '6')) AND (ApptFinYr = '2014/15') GROUP BY ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END ORDER BY ClinicCode
How can I add a group number to the following query?
For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):
RN ReportDate Category Division TotalBalance -------------------- ---------- ------------------------------ ------------------------------ --------------------- 1 2015-06-08 Field Sales CA BDM 299743154.3912 2 2015-06-07 Field Sales CA BDM 299765954.0354 3 2015-06-01 Field Sales CA BDM 297902654.4172 1 2015-06-08 Key Accounts Life Office 49954981.74 2 2015-06-07 Key Accounts Life Office 50016989.22 3 2015-06-01 Key Accounts Life Office 50169967.26 4 2015-05-31 Key Accounts Life Office 50169918.01
Becomes
GN RN ReportDate Category Division TotalBalance -------------------------- ---------- ------------------------------ ------------------------------ --------------------- 1 1 2015-06-08 Field Sales CA BDM 299743154.3912 1 2 2015-06-07 Field Sales CA BDM 299765954.0354 1 3 2015-06-01 Field Sales CA BDM 297902654.4172 2 1 2015-06-08 Key Accounts Life Office 49954981.74 2 2 2015-06-07 Key Accounts Life Office 50016989.22 2 3 2015-06-01 Key Accounts Life Office 50169967.26 2 4 2015-05-31 Key Accounts Life Office 50169918.01
i.e. each combination of Category+Division results in a new GN.
The query is:
selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN' , ReportDate , Category , Division , sum(BalanceGBP) as 'TotalBalance' FROM FlowsAndOpenings group by ReportDate, Category, Division order by Category, Division, RN
I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..
I am using SQL 2012. I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number). I know the code COUNT(DISTINCT customer_number) works for getting that. I need to select both of those fields on top of what I have. I have the following:
DECLARE @Providers TABLE (ID INT IDENTITY(1,1), Provider_Name VARCHAR(20), Uniq_Id VARCHAR(10), Total_Spent MONEY, Total_Earned MONEY) INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent, Total_Earned)
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select t.Name 'Task', d.FirstName + d.LastName 'User' from [dbo].[Tasks_TemplateAssignTo] a join Task_Template t on a.template_id = t.ID join Doctor d on d.id = a.provider_id
Results from query above:
TaskUser Call CustomerJohn Smith Call CustomerBetty White Call CustomerTammy Johnson Order suppliesGreg Bullard Order suppliesJosephine Gonzalez
Expected Results:
TaskUser Call CustomerJohn Smith, Betty White, Tammy Johnson Order SuppliesGreg Bullard, Jospehine Gonzalez
I've got this set of registers (just an example) after ordering by the first 3 columns:
value_A value_B value_C ID date 1 2 3 YVIR 29/08/2015 1 2 3 ANTE 27/04/2015 1 2 3 REGO 20/02/2015
I need to get as a final result:
value_A value_B value_C ID date 1 2 3 REGO 29/08/2015
In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.
I've been trying to do this with the group by clause:
select value_A, value_B, value_C, min(ID), max(date) -- or max(ID) from table group by value_A, value_B, value_C
But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.
In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.
This table is update by some one else, I just can access the information and use it as a source.
I am using Sql Server 2008 R2.I have a existing query that basically says
Select Top 50 Subscriber_ID, Member_Name, Group_ID from my_table order by rand(checksum(newid()))
However the client now wants to have at least two from each group_id. There are 17 different groups. When I run this as is I get about six of the 17 groups in the results. How can I change this to get at least two results from each group_id?
