I have a query which I am running accross several DB's to get information on several of our store's oulet. The query Below provided the info I need but I am not sure if it is the best way to do it and how it will be affected when we have millions of record in our DB. Or can I make it into a single query so when executed in C# I do do get two table in my dataset. Any comment or suggestion is appreciated.

Select 'My Business' AS BusName, sum(PassVer.PassedVerifier) AS 'Passed Verifiers', Sum(PassVer.CashFunded)AS 'Cash Funded', SUM(PassVer.OtherFunding) AS 'Other Funding', Sum(PassVer.CompletedApplications) AS 'Completed Applications', SUM(PassVer.CompleteFundedCash) AS 'Complete - Funded Cash', SUM(Passver.CompleteNotFundedCash) AS 'Complete-Not Cash', SUM(PassVer.Total) AS 'Total Applications',
	cast((Sum(PassVer.PassedVerifier) * 100.00) / sum(PassVer.CompletedApplications)  AS Float) AS '% Completed and verified',
	cast((SUM(PassVer.CompletedApplications)* 100.00) / Sum(PassVer.Total)  AS Float) AS '% of Completed from Total'
FROM (
--Selects Applications which Passed Verifier
Select 1 AS 'PassedVerifier', 0 As 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which passed the verifier and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 1 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which passed the verifier and are funded by methods other than Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 1 As 'OtherFunding', 0 AS 'CompletedApplications',0 AS 'CompleteFundedCash',  0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId != 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL

--Selects Applications which are completed.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 1 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.Queue

ThanksPmar, however, I have used "Rollup" in the SP itself thus the calculation. The result is table[1]. However table[0] counts the transcation based on a different criteria. I got all the information I need from the DB, however it can not be done in single query. All I need is to display the dat from the dataset.

Skan If you knew it would not compile why didn't you tell me?!?!?!

Hi Guys, I've been trying for quite sometime to accomplish this task but now I am at my wits end so I turn to you "the professionals" for assistance. My problem is as follows: I have a method which executes a stored procedure with two select queries which return two tables of data which is rans accross the several databses to view individual store performace. This data is then available to the application in a data set. The first table (table[0]) returns the transactions details (i.e. ProductID, ProductName, Cost, Sales date) while the second table(table [1]) returns a summary of each stores sales activity. What I am hoping to is display the "Details"(Table[0]) the below those records display the summary of the stores activity. However, this query is ran over several databse (one per store) thus when the dataset is returned information for all the stores are in there hence it looks something like this:

Table[0]
StoreID StoreName Product TotalSold TotalIncome
0015 Joe's Store SomeBeer 1000 $10,0000
0016 Mike's Store FoodItem 500 $2500
0017 Mary's Store Clothing 500 $2500

and so on...

Table[1]

StoreID ProductName Unitcost UnitSold Total

0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00 50 500.00
0016 SomeBeer4 $10.00 50 500.00
0017 SomeBeer1 $10.00 50 500.00
0017 SomeBeer2 $8.00 50 400.00
0017 SomeBeer3 $10.00 50 500.00
0017 SomeBeer4 $10.00 50 500.00
and so on....

What I need is: Store 15

StoreID ProductName Unitcost UnitSold Total

0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00

StoreID StoreName Product TotalSold TotalIncome

0015 Joe's Store SomeBeer 1000 $10,0000

Store 16

StoreID ProductName Unitcost UnitSold Total
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00

Thanks for the help guys. I got it working with the following lines of code. I will simplify my code later but just need to get it working.

SqlParameter paramReturnValue = new SqlParameter();
paramReturnValue.ParameterName = "@return_value";
paramReturnValue.SqlDbType = SqlDbType.Int;
paramReturnValue.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(paramReturnValue);

Skan If you knew it would not compile why didn't you tell me?!?!?!

This worked perfectly!!! IF EXISTS (SELECT Emp_Login_ID FROM Employees where Emp_Login_ID = @Emp_Login_ID ) RETURN -1 ELSE IF EXISTS (SELECT Emp_SSN FROM Employees where Emp_SSN = @Emp_SSN ) RETURN -2 ELSE INSERT INTO........ I am no longer receiving the Exception. However, I am not sure how to throw the Error message in C#. How would C# know to throw and Exception if the insert does nto execute? Thanks for you help. Note: All this is done in a single stored procedure. Which is then called in the application in a try, catch statement.

Skan If you knew it would not compile why didn't you tell me?!?!?!

modified on Thursday, December 06, 2007 12:44:52 AM

Thanks dude, I found them. I would still like to know what best practice for a situation like this.

Skan If you knew it would not compile why didn't you tell me?!?!?!

I am writing an HR application to add new employees. Currently, I am having an issue where I want to send a specific message base on the exception that is received. In my database SNN is a unique key hence if an SSN is being added that already exist in the DB a Unique key violation exception is thrown. If the UserID already exist a Primary Key violation exception is thrown. I am certain this is possible but just not sure what is best practice. I can write a method which search the DB first and if the record is found thrown an error message based on whether it was the Unique key violation or the PK violation. I have also been searching MSDN to see if I can identify the C# SqlClient error ID for either scenarios and based on the exception ID, alert the user with the right error message. Any advice will be greatly appreciated.

Skan If you knew it would not compile why didn't you tell me?!?!?!

You are totally right and that's what I thought was happening but was not sure how to validate a "Void" method since nothing is ever returned. What is the best practice and how would you re-write this code to evaluate if all values have been assigned?

Skan If you knew it would not compile why didn't you tell me?!?!?!

If you notice I wrote a bool isFieldEmpty() for every field since I need to ensure that the field has a value. If a field does not have a value then an error message is displeyd with the error message on submit. The ValidateAssign() does two thing throws the error message and assigne that value id the boolean is false. How I am not sure why these Values are not being passed to the Insert method. I am not sure hwo to crrect this. Help? Anyone?

Skan If you knew it would not compile why didn't you tell me?!?!?!

I have written the code belowin anticipation of verifying, assigning and lastly insert the Value into a DB table. The ver ification work as inteded but if I can all it in the Insert Method or in the Submit click event it does not work. if the field contains data it saves it to the DB but no everification is done. The Validation itself works fine but when called in the insert method it does not validate and the inserts execut without validating the values. Any assistance will be greatlly apprciated.

  private bool isUserIDEmpty()
	{
		return(txtUserID.Text.Length==0);
	}
	
private bool isFNameEmpty()
	{
		return(txtFName.Text.Length==0);
	}

private bool isMidNameEmpty()
	{
		return(txtMidInitial.Text.Length==0);
	}

private bool isLNameEmpty()
	{
			return(this.txtLName.Text.Length==0);
	}

private bool isSSNEmpty()
	{
		return(this.txtSSN.Text.Length == 0);
	}

	
      internal void ValidateAndAssignValues()
	{
		Queue messages = new Queue();
		Control focusControl = null;

	if(!isUserIDEmpty())
		{
			EmpID = this.txtUserID.Text;
		}
	else
	        {
			messages.Enqueue("Please Enter a User ID");
			focusControl = this.txtUserID;
		}

	if (!isFNameEmpty())
		{
			FName = this.txtFName.Text;
		}	
	else
		{
			messages.Enqueue("Please Enter a First Name.");
			if(focusControl == null)
				focusControl = this.txtFName;
		}
           if(!isSSNEmpty())
		{//validating
			//validate integer
			if(isSSNNineDigits()!= true)
			  {
				  messages.Enqueue("You have not enter nine characters");
				if (focusControl == null)
					focusControl = this.txtSSN;
			    }
			else 
			    {
				try
				   {
					  SSN = int.Parse(this.txtSSN.Text);	
				    }
				catch
				     {
					messages.Enqueue("Please Enter a valid SSN.                                         Please ensure that you have entered numbers only.");
				
					if (focusControl == null)
						focusControl = this.txtSSN;
				}
			      }
		            }

                if (messages.Count > 0)
		{
			StringBuilder sb = new StringBuilder();
			bool first = true;
			foreach (string message in messages)
			{
				if (first)
					first = false;
				else
					sb.Append('\\n');
				sb.Append(message);
			}

			lblError.Visible = true;
			lblError.Text = sb.ToString();
		}
		else
		     {
			     lblError.Visible = false;
			       lblError.T

Thanks for your response. I figured it out, using the code below. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where RecordNum = (SELECT TOP 1 RecordNum FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) What was wrong with it you may ask? Well, I was selecting the Emp_timeout field which was null. I was then telling to update a record where getdate() equals the value in the Emp_Timeout field. That would never happen because at the time of the query Emp_timeout field is NULL thus it will never equal getdate(). Hope this helps.

Skan If you knew it would not compile why didn't you tell me?!?!?!

This article help you. Otherwise check emergency recoverin BOL. http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx[^]

Skan If you knew it would not compile why didn't you tell me?!?!?!

This is a 1:M relationship. The reason being that you can have many Mr. or Mrs. Customers but a customer can only be ONE either Mr. or Mrs. Anyone???

Skan If you knew it would not compile why didn't you tell me?!?!?!

Have u tried this: Begin StoredProc() IF @@Error = 0 Commit trans Else Rollback Trans End Hope this helps.

Skan If you knew it would not compile why didn't you tell me?!?!?!

Are u checking for duplicate values in a Database or a table? If it is a table the you use the following script. SELECT count(Column(s)) FROM table_Name ORDER BY Column(s) GROUP BY column(s) HAVING Count(Column(s)) > 1 Hope this helps.

Skan If you knew it would not compile why didn't you tell me?!?!?!

I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data. UPDATE dbo.EmpWorkHours SET Emp_Timeout = getdate() where Emp_Timeout in (SELECT TOP 1 Emp_timeout FROM dbo.EmpWorkHours WHERE FK_Emp_Login_ID = 'MDoe74' and Emp_Timeout IS NULL ORDER BY RecordNum DESC ) The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist. Any assistance will be great appreciated.

Skan If you knew it would not compile why didn't you tell me?!?!?!

I am currently working on a project to develop an application which employees will use to clock in, clock out on break, clock in after break, do the same for lunch, and clock out for the end of the day. This application will record time an employee works, calculate regular and over time then prepare a report for HR so employees can get paid. HR is also able to correct any error the employee may had made, e.g. forgot to log out or login. Employees work by shift thus an employee may start working on one date and end on the following date. I was thinking about creating the calculation logic in SQL as a stored procedure but was advised that doing so may not be best idea. I am not sure where to start on this, besides creating the tables needed for the project in SQL. Any sample or advice on this issue will be greatly appreciated.

Skan If you knew it would not compile why didn't you tell me?!?!?!

Dude you are a Genius. I modified the script a but and it worked like a baby. Thnks dude.. I owe u a beer. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_MS_IssuesReport_Num2](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS BEGIN SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period, SUM(A.IssueReceived) AS IssuesReceived, SUM(A.IssueClosed) AS IssuesClosed FROM( SELECT CreateDate AS PeriodDate, 1 AS IssueReceived, 0 AS IssueClosed FROM Ms_threads WHERE CreateDate BETWEEN @BeginningDate and @EndDate UNION ALL SELECT StatusDate AS PeriodDate, 0 AS IssueReceived, dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed FROM Ms_threads WHERE StatusDate BETWEEN @BeginningDate and @EndDate ) A GROUP BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) -- Added the date floor function. ORDER BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) end Thank Code Project....This deserves a 10 not a 5...hehehehe!!

Skan If you knew it would not compile why didn't you tell me?!?!?!

A simple 'OR' Statement should work. SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period, COUNT(*) AS IssuesReceived, SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)=1) AS IssuesClosed FROM Ms_threadsWHERE CreateDate BETWEEN @BeginningDate and @EndDateGROUP or StatusDate BETWEEN @BeginningDate and @EndDate(This would have to be union with CreateDate for it to work.) GROUP BY CreateDate ORDER BY CreateDate unfortunately the OR statement as scripted is not working either.

Skan If you knew it would not compile why didn't you tell me?!?!?!

Thanks for the your help. It sort does what I want however because a message that was created 1/5/2005 may not be resolved until sometime in March its current Status will not be 'Resolved' thus will not fall within this status period. Looking at the code that messages status may be counted during a January monthly report the status is not resolved only becuase the message was created in January. I guess I did not provide information but I am trying to extract and count ALL messages created during the period and all messages Resolved during the period. Agian, a messages created during that period may not be resolved until a future date hence we do not need to count their status on their status is 'Resolved' thus keying off the statusDate. Thanks for you response. I like the way you clean up the code.

Skan If you knew it would not compile why didn't you tell me?!?!?!