Thursday, September 5, 2013

[SQL Server] SSIS package - Send email if dates are off

[SQL Server] SSIS package - Send email if dates are off

SSIS package - Send email if dates are off

Posted: 05 Sep 2013 11:31 AM PDT

I'm looking to create an SSIS package that sends me an email if certain dates aren't where they are supposed to be within our 2008 database.After nightly system processing, 7 date fields need to be "todays" date and 2 of the date fields need to be "yesterdays" date. If all 7 don't equal "todays" date OR the other 2 fields don't equal "yesterdays" date, I want the SSIS package to send me an email.Me getting an email would be sufficient (I know how to create that part of the package) but if I could get an email with the dates, that would be even better.I'm not looking for a step by step suggestion but rather a suggestion on how I'd start this type of a package to see if I could take it from there....such as what control flow items I'd need to start out with. I already have the script to pull the dates, just not sure where to take it from there.TIA,John

case statements and UDF's

Posted: 05 Sep 2013 05:54 AM PDT

Hi there. It has been a while since being on here but I have a question. I am trying to create a table that has to be backward compatible with previous versions and it is being a pain. I need to convert and age field into an age group field. Or make a new field for it. either way works. I am trying to use a UDF because I need this to work on 6 different tables and I dont want to have to type it in all the time, also because i dont like "messy" code. here is what i have so far. [code="sql"]USE [Test]GO/****** Object: UserDefinedFunction [dbo].[AGE_GROUP] Script Date: 09/05/2013 11:40:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[AGE_GROUP](@string VARCHAR(MAX))RETURNS VARCHAR(MAX)BEGINRETURN CASE @STRING WHEN 0 THEN ' 0' WHEN 1 THEN ' 1' WHEN 2 THEN ' 2' WHEN 3 THEN ' 3' WHEN 4 THEN ' 4' WHEN 5 THEN ' 5' WHEN 6 THEN ' 6' WHEN 7 THEN ' 7' WHEN 8 THEN ' 8' WHEN 9 THEN ' 9' WHEN 10 THEN ' 10' WHEN 11 THEN ' 11' WHEN 12 THEN ' 12' WHEN 13 THEN ' 13' WHEN 14 THEN ' 14' WHEN 15 THEN ' 15' WHEN 16 THEN ' 16' WHEN 17 THEN ' 17' WHEN 18 THEN ' 18' WHEN 19 THEN ' 19' WHEN BETWEEN 20 AND 24 THEN '20-24' ENDEND[/code]There would be age groups like the "when age(between XX and xx) then 'xx-xx' Does anyone have any ideas on how to do this?Also it works for the 0-19 just fine.

SQL Server 2012 Activity Monitor

Posted: 05 Sep 2013 05:23 AM PDT

I opened SQL Server 2012 Activity Monitor and was trying to understand the data it was displaying. I saw a statement under "Recent Expensive Queries" and I clicked the dropdown for databases. I then opened the "Processes" section in Activity Monitor and clicked the dropdown for "Database". The database that was using the query in the "Recent Expensive Queries" section was not in the list. How is it possible for the database to be using a query, yet not in the database list in the dropdown under "Processes"?Thank you in advance,Charlie

Recalculate Wages (Reposted for SQL 2008 with additional columns)

Posted: 05 Sep 2013 01:56 AM PDT

Recalculate "Amount" Column--------------------------------------------------------------------------------Our Salaried Employees have to log in their time by department and by type of Earnings (Regular, Holiday, Sick, etc). We are on a semi monthly pay period - therefore the number of hours worked each pay period vary - but the amount of pay is exactly the same each pay period.On each employee's pay record there are 2 fields, Pay Per Period and Equivalent Hourly Rate.When the payroll is calculated the, the payroll application calculates the Amount column based on a prorated hourly rate - so the total amount of payroll is exactly the same each pay period. (The prorated hourly rate may be different if the pay period has 80 hours vs. 104 hours - as the pay should be the same.In the example below there are 5 employees each with a pay per period of $2000.The business requirements are such that if an Employee has Vacation on their timecard - the Amount for the Vacation Hours should be calculated based on the "Equivalent Hourly Rate" and the balance on a prorated rate. The total of Vacation and Regular/Sick, etc. should equal the employee's pay for pay period - accounting also for any rounding differences. Below is a sample table - the amount column needs to be recalculated whenever there is VACATION used by Employee. For the Vacation Row the Amount should be the Hours times the Equivalent Hourly Rate and the other earnings items should be based on a prorated rate. I only need to Update the values of the Amount column.For clarity sake I have added 2 additional columns - newrate and newamount. The newamount column values are the ones that I want to be replaced in the amount column. The newrate is a prorated rate that is based on number of hours worked less the vacation hours. For employees who do not have Vacation - those records should be ignored. Lastly, the round error should be resolved so the employee gets their gross wages in this example as 2000. create TABLE Payroll(EmpID int,EarningCode varchar(255),PayPerPeriod varchar (255),EQHourlyRate varchar(255),HoursWorked varchar(255),Amount varchar(255),Dept varchar (255),NewRate varchar (255),NewAmount varchar (255));Insert Into Payroll Values ( '100','Regular','2000','23.0769','52','1000','SALES','18.315','952.38');Insert Into Payroll Values ( '100','Holiday','2000','23.0769','12','230.77','SALES','18.315','219.78');Insert Into Payroll Values ( '100','Sick','2000','23.0769','12','230.77','SALES','18.315','219.78');Insert Into Payroll Values ( '100','Jury Duty','2000','23.0769','8','153.85','SALES','18.315','146.52');Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.31','SALES','23.0769','230.77');Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.3','MKT','23.0769','230.77');Insert Into Payroll Values ( '200','Regular','2000','23.0769','104','2000','ADMIN','n/a','2000');Insert Into Payroll Values ( '300','Regular','2000','23.0769','88','1692.31','ACCT','n/a','1692.31');Insert Into Payroll Values ( '300','Holiday','2000','23.0769','8','153.85','ACCT','n/a','153.85');Insert Into Payroll Values ( '300','Sick','2000','23.0769','8','153.84','ACCT','n/a','153.84');Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','HR','18.077','723.08');Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','ADMIN','18.076875','723.08');Insert Into Payroll Values ( '400','Vacation','2000','23.0769','24','461.54','HR','23.0769','553.84');Insert Into Payroll Values ( '500','Regular','2000','23.0769','66','1269.23','ACCT','18.0136708860759','1188.9');Insert Into Payroll Values ( '500','Holiday','2000','23.0769','8','153.85','ACCT','18.0136708860759','144.11');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','5','96.15','ACCT','18.0136708860759','90.07');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','10','192.31','ADMIN','23.0769','230.77');Insert Into Payroll Values ( '500','Vacation','2000','23.0769','15','288.46','HR','23.0769','346.15');

Adding User to SQL Server

Posted: 05 Sep 2013 02:13 AM PDT

Hi,I got a scenario where I am using [b]Windows Application[/b].I want to create a SQL Server User satisfying the password policy. I have couple of questions related to it.1. How can I create such a user?2. After creation of the user, I want to store its password somewhere at a central location[don't know where? can somebody help?]. While connecting to DB, read this password from central location on the network and login to SQL Server.Please help.Thanks,Paresh

Using IIF to test for Null

Posted: 05 Sep 2013 01:56 AM PDT

This should be very easy and I am predicting a big forehead slap in just a few minutes after I post this.I am simply trying to test a field for being null in my SELECT and display a true or false condition. Neither example below works and I've crossed referenced a couple books and looked online as well. No luck. Thanks for looking! (MSSL 2008)SELECT IIF(Field1 IS NULL, 'NULL', 'NOT NULL') *** XXFROM MyTable[color="#FF0000"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'IS'.[/color]SELECT IIF(IsNothing(Field1), 'NULL', 'NOT NULL') AS XXFROM MyTable[color="#FF0000"]Msg 195, Level 15, State 10, Line 5'IsNothing' is not a recognized built-in function name.[/color]Slap in 5...4....3...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog