Passing Variables In SP - Send Out Emails Based On Certain Criteria

Feb 11, 2015

I was tasked with witing a stored procedure to send out emails based on certain criteria. One of our developers decided to take care of the criteria in another process and pass variables for me to use. I am not quite sure how to go about this, but below is what I started with. I declared his variables, and then set up a couple of my own for the email subject line and text.

create PROCEDURE [dbo].[SendWelcomeEmails]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

Do I need to use a cursor or can I just specify using if, then else? Also not sure where to SET the individual subject lines.

View 2 Replies


ADVERTISEMENT

SQL 2012 :: Using Database Mail To Send Emails Based On Certain Actions

Jun 12, 2014

I have been using database mail for quite a few years now. I started using it with SQL Server 2005 and then it worked fine. Then we went over to SQL 2012 and the problems started. At first I could not get any e-mails out and read a post by Microsoft that a patch needs to come out but not available yet. That was in 2012. Presumably the patch came out with windows updates and my DB Mail started working. Now, however, I have the problem that some e-mails do get sent but the majority failed.

I use this so that whenever a user captures certain data to update a person's status on the database then an automatic e-mail will be sent to that person to inform them of their status at this company.

View 0 Replies View Related

T-SQL (SS2K8) :: Send SSRS Report Based On Certain Criteria?

Mar 11, 2014

I have an SSRS Report that I created. I'd like to set up a job that will run every hour, and what the job will do is send an SSRS report to a select group of recipients when a certain criteria is met. How do I go about doing this? My SSRS knowledge is very limited.

Additional Info:I created the report with a one time schedule to a certain email address. I do see the report schedule in the reportserver DB. Because I already have the schedule and the subscription id, I think all I have to do is call that subscription, but that's where I'm lost.

View 3 Replies View Related

Passing Variables To SQL From C Based DB-Library

Aug 18, 1998

Hi!!
If I have declared a variable, such as char variable_name[x], and have assigned it a value, how do I go about passing this value to an SQL query such as:
SELECT * FROM variable_name
where my value replaces variable_name.
Cheers, Marc

View 1 Replies View Related

SQL Server 2008 :: 2 Emails Being Send From Job When Only 1 Should Send?

Apr 18, 2012

I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'

[code]....

View 9 Replies View Related

SQL 2012 :: Reporting Services Would Not Send Subscription Emails

May 5, 2014

I am running RS 2012 installation and everything worked until few days ago. Now the subscriptions would not even run. When I configure new ones, after the time passes, there is not error message, not even a sign it ran.

View 2 Replies View Related

SQL 2012 :: Send Emails With Query Results As Attachment?

Jul 16, 2014

What is the best way to send emails with query results as attachment?

There is a stored proc sp_send_dbmail but the formatting is not very good.

View 3 Replies View Related

SQL 2012 :: Data Driven Subscriptions - Any Way To Send All Emails At Once?

Mar 18, 2015

We have a rather large query that takes time to run between each data-driven instance. Is there a way to only send out the first email after the last subscription has finished processing? Any advanced input on data-driven subscriptions .

View 0 Replies View Related

SQl Express 2005 Suppoerts For Send Emails Using Stored Porcedures

Jun 3, 2008

Hello to all,Can anybody please tell is SQl express 2005 suppoerts for send emails using stored porcedures?
 

View 1 Replies View Related

SQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?

Jul 15, 2015

I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.

I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.

View 1 Replies View Related

Passing Like Criteria In Parameter To SQL Server Stored Procedure

Dec 13, 2006

Hello All,
 I was hoping that someone had some wise words of wisdom/experience on this.  Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
  I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion  errors but I am able to test the query successfully in the SQLDatasource.  What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like.   I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
 Thank you for any assistance,
 

View 3 Replies View Related

Retrieving Data Based On Criteria

May 31, 2008

Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;

dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.

dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.

dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1

dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2

What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.


Code:


SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM

View 1 Replies View Related

Retrieving Data Based On Criteria

May 31, 2008

Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;

dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.

dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.

dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1

dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2

What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.

SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM

View 2 Replies View Related

Case Statement Based On Criteria

Sep 23, 2013

I have to produce a report, but not sure of the best way to get the required results

Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]

E.g.

10 Fdmsaccountno in Jan

Those 10 Fdmsaccountno vary in [Retail_amount]

I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>

My query is

SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'

View 5 Replies View Related

Adding A Y/N Column Based On Criteria

Sep 21, 2005

Currently I'm trying to add a column based on certain criteria based on the following data:

CallID GroupName CustomerPending
------ ----------- ------------
00500588FollowupN
00500588FollowupN
00500588Server N
00500588Service DeskN
00500588Service DeskN

Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.

--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)

UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------

Any assistance appreciated.

View 4 Replies View Related

Select Based Upon Multiple Criteria

Sep 13, 2006

Hi

I would like to get records from a table and present a result set based upon the search fields

the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,

they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.

Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?

Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage


adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg

View 12 Replies View Related

Excluding Based On Specific Criteria

Jan 2, 2007

I am trying to accomplish the following.

There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.

The first and second table have one field in common - the Account number.(ACCT_NUM)


the exclusion is based in steps..

1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.

how do i do this... pls suggest

Thanks

View 1 Replies View Related

Delete File Based On Criteria

Jul 10, 2006

Hi,

I want to delete all files in a folder where creation date is an year older. I created a For Each Loop container to go over the whole folder and then execute the following Script Task

Dim FileArchive As Int16

Dim dteArchive As Date

Dim FilePath As String

FileArchive = CType(Dts.Variables("FileArchive").Value, Int16) ' One Year FileArchive = 365

dteArchive = DateAdd("d", -FileArchive, Now.Date)

FilePath = Dts.Variables("FilePath").Value.ToString

If File.GetCreationTime(FilePath) <= dteArchive Then

Try

File.Delete(FilePath)

Catch ex As Exception

'Do Nothing

End Try

End If

Dts.TaskResult = Dts.Results.Success

Is there a better approach to this? As most of the time job may have to delete 10 or 15 files but he has to read each file and then check the date if it matches the criteria and it delete.

View 1 Replies View Related

Combining Multiple Rows Based Off Criteria

Mar 21, 2006

Hello again,

Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:


Code:


Table A

ID DSN VN AX Diag
1111296.54
3212318.00



Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.


Code:


Table B

DSN Patient_id
100000001
200000001



So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.


Code:


Table A

ID DSN VN AX Diag
1111296.54
3112318.00

Note: The second rows DSN changed to 1 from 2




Code:


Table B

DSN Patient_id
100000001
(Duplicate row removed with same patient_id)



The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.

Table A:

DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)

Table B:

Second row with same DSN must be removed.

Any takes, ideas? I need to do this on a couple thousand rows....

Thanks, and im happy to clarify if needed.

View 1 Replies View Related

Update Table With SEQUENTIAL # Based On Criteria

May 9, 2007

***** SQL Server 2005 ********

I have a table that needs to be updated with a sequential number based on criteria.

I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.


SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8


This is what the table should look like after the update in complete.

SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1



Can anyone HELP me please?

View 4 Replies View Related

T-SQL (SS2K8) :: Get Start And End Date Based On A Criteria?

Apr 17, 2014

I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .

Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')

[code]....

Expected Output

---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'

View 9 Replies View Related

Selecting Only 1 Record Based On Multiple Criteria

Jan 31, 2014

I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:

Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,

[Code] ....

Each work order should only be returned once, and with the following additional criteria:

1. i.meter - this should return only the lowest number from that file.

2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment

3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.

View 1 Replies View Related

How To Count Cases For Different Groups Based On Different Criteria

Apr 25, 2008



Hello,

I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)

Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)

MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null

BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')








12-Jan

19-Jan

26-Jan

2-Feb

9-Feb

16-Feb



MTS





















New Cases Received

85

84

79

98

79

95



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

44

29

26

35

55

54



CreditB

6

12

9

5

7

13



CreditS

-

-

-

-

3

-



CreditP

10

11

11

24

17

7



MTS Subtotal

140

125

110

144

151

150

























BNB





















New Cases Received

12

13

14

14

6

11



Sales

-

-

-

-

-

-



SalesD

-

-

-

-

-

-



CreditR

12

11

12

10

5

9



CreditB

8

13

9

17

16

6



CreditS

-

-

2

-

-

-



CreditP

1

1

1

1

4

3



BNB Subtotal

21

25

24

28

26

19

























Total





















New Cases Received

97

97

93

112

85

106



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

56

40

38

45

60

63



CreditB

14

25

18

22

23

19



CreditS

-

-

2

-

3

-



CreditP

11

12

12

25

21

10



Grand Total

161

150

134

172

177

169



This is just a very brief bit of code

SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.

FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...



Can you please show me how to accomplish this?

Thank you in advance for your effort,



Rhonda

View 2 Replies View Related

Select Criteria Based On Date - Cleaner Way To Write?

Sep 7, 2005

Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT



I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be

Code:

SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT



Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).

Any suggestions on the best way to write this would be valued.

View 2 Replies View Related

Select Record Based On Multiple Criteria (vars)

Apr 17, 2008

Hi! I'm new to SQL and have a question...

I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.

Basically my SELECT statement looks something like this, then is followed by an If... Else statement


SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress

If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If

RecordSet.Update
RecordSet.Close


Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??

View 5 Replies View Related

Report Builder : Having More Than One Total Columns Based On Different Criteria

Feb 14, 2008

Hi,

I would like to get some advises on how creating some kind of reports with Report builder

Lets say i have these two tables with these columns

Table Product

ProductName

Table ProductInstance

Product ID
CreationDate
CompletedDate


I would like to be able to create with report builder a report that looks this



Start Date : 2008/10/01 - End Date : 2008/10/30





Total Total Total
In Out OutStanding
ProductName1 10 0 3
ProductName2 5 5 2
ProductName3 8 8 5
ProductName4 12 5 6




Total In : total number of product created between StartDate and EndDate
Total Out : Total number of product completed between Start Date and End Date
Total OutStanding : Total number of product that have not yet been completed.


Any suggestion and advise on how to achieve this ?









View 2 Replies View Related

Selecting Column Criteria Based On Report Parameter

Feb 13, 2008

I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?

Sincerely appreciate all the help I can get.

Thanks in advance.

View 11 Replies View Related

Simple Function For Returning A Character Based On Search Criteria..

Feb 20, 2007

Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen

View 2 Replies View Related

Transact SQL :: Function To Return Unit Price Based On Various Criteria

Apr 30, 2015

We were asked to create an SQL function to return a unit price based on various criteria. The function works fine except for the tiered pricing (use of BillingPriceTable) calculation.  What we need to do is break up the total quantity passed to the function and return the total of prices found.  In our example, we passed a quantity of 9,721 units and need to return a total price of 231.92 using the table below.

Low Qty    High Qty    Fee        Actual Qty        Price
0                  7500        0.025            7500           187.50
7501           15000        0.020            2221          44.42

Below is the table definition that we have to work with (ugghh).

CREATE TABLE [dbo].[BillingPriceTable](
[PriceTableID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[PriceTableCode] [varchar](10) NULL,
[PriceTableName] [varchar](40) NULL,

[Code] ....

What we have so far is shown below.  The columns that start with bdxx are the "High Qty" values and the columns that start with prxx are the price for that quantity range.  So, the current SELECT is shown below and it returns the price based on the entire qty of 9,721 and returns a unit price of 0.020 and should return 0.023857628

The current SELECT is shown below and is returning 0.020 which is the fee for the total rather than calculating the fee twice, once for the 0-7500 and again for the 7501-15000 (actually 7501-9721). Two things came to mind, one was a WHILE loop and the other was possibly a ranking function of some sort. 

ALTER FUNCTION [dbo].[fn_GetPrice]
(
@plincdvarchar(3),
@pgrpcodevarchar(4),
@pitmcodevarchar(4),
@qtydecimal(10,1) = 1,
@corpnbrvarchar(9)
)

[Code] ....

View 9 Replies View Related

Send Mail Using Variables From Select Statement

Jan 5, 2007

I'm getting the error "No recepient is specified".

I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.

I couldn't figure out how to included screen shots.

Any ideas?

View 4 Replies View Related

SQL / PHP / Passing Variables

Apr 28, 2008

I am pretty new to this and hope someone can help and I hope I can explain my problem.

I have a php form

<form action="cpesearch.php">
State: <select name="stname">
<option value ="ALL">all states</option>
<option value ="vic">VIC</option>
<option value ="sa">SA</option>etc etc etc

I am then connecting to my datatbase, assign the variables and doing the SQL query.

if ($dbc = mysql_connect ('localhost','root',''))
{
if (@mysql_select_db('database'))
{
$sname = $_GET['stname'];
$pname = $_GET['prname'];
$sql = 'SELECT * FROM `cpe` WHERE eventdate >= CURDATE() AND state = ''.$sname.'' AND presenter = ''.$pname.'' ORDER BY eventdate ASC';
$r = mysql_query($sql) or die("Query failed".mysql_error($dbc));
while ($row = mysql_fetch_array($r))
{
print "<div id="mainContent"> <p>".$row['state']."</p>".
"<p>".$row['eventdate']."</p>".
"<p>".$row['eventtitle']."</p>".
etc etc etc

So my problem is... I have an events table with presenters and state (and other info). I want to be able to select each presenter and state or all presenters and/or states. Not sure then how to pass the variable into my sql statement if they want to select ALL.

View 2 Replies View Related

Putting System Variables In A Send Mail Task

Jul 13, 2006

KB Article 906547 suggest that it's possible to have system variables in the Subject & Body of a Send Email Task. Such as this:

"Package>>> " + @[System::PackageName] +" was executed at>>> " + (DT_WSTR, 40) @[System::StartTime] + " by user>>> " + @[System::UserName] + " on Machine>>> " + @[System::MachineName]

Or this in an OnError event handler:

"Error in the task: " + @[System::SourceName] + "with the ID: " + @[System::SourceID] + " has failed at: " + (DT_WSTR, 20) @[System::ContainerStartTime] + "."

But when I do this I just get this exact text, no variables.



I'm obviously missing something basic but I can't find it. Can anyone offer a suggestion please?

View 3 Replies View Related

Passing Variables Between DTSs

Apr 23, 2007

Hi,
I have a DTS (DTS1) that call another DTS(DTS2). DTS2 has a global variable defined, I need to pass a value to that variable from DTS1.
 
Thanks,
 Arturo

View 2 Replies View Related







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