|
I am trying to create a reporting system which will give me the "Customer ID", "Number of Messages sent", "Is Message a part of a thread or Distinct", Message Status (i.e. New, Under Review, Cancelled or Resolved) within a given period. I honestly do not see no need for a curso in here but I believe one can be used but I do not understand the benefit of using one. The search is working fine except for the last "Select" prior to dropping the table is not providing Distinct User's. Users with multiple messages/Threads are being pulled up as many time as the amount of records found for them. It is the result set from the Last select that is displayed in the GUI.
Any assistance given will be appreciated.
SET NOCOUNT ON /* Don't return row counting to caller */
CREATE TABLE #MS_MessagesFromCustomer(
NumOfMessages BIGINT,
CustomerID VARCHAR(100),
Period datetime,
ThreadID int,
MessageStatus int
)
CREATE TABLE #MS_InquiriesByCustomer(
ThreadID int,
CustomerID VARCHAR(100),
TotalMessages int,
New int,
UnderReview int,
Cancelled int,
Resolved int,
TotalThreads int
)
Insert into #MS_MessagesFromCustomer(CustomerID, NumOfMessages, Period, ThreadID, MessageStatus)
Select MSM.SenderID, count(MSM.MessageID)Messages, Convert(varchar, MSM.SentDate, 101) [Sent Date],MSM.ThreadID,MST.CurrentStatusID [Current Status]
from dbo.MS_Messages MSM INNER JOIN dbo.MS_Threads MST ON MSM.ThreadID = MST.ThreadID
where MSM.SentDate >'3/1/2005'
and MSM.SentDate < '3/31/2005'
group by MSM.SenderID, MSM.SentDate, MSM.ThreadID, MST.CurrentStatusID
order by SenderID
--Select * from #MS_MessagesFromCustomer
INSERT INTO #MS_InquiriesByCustomer(ThreadID,CustomerID, TotalMessages, New, UnderReview, Cancelled,Resolved, TotalThreads)
Select Distinct(ThreadID)'ThreadID', CustomerID'Customer' , count(NumOfMessages) 'Total Messages Recevied',COUNT(CASE MessageStatus when 19 THEN MessageStatus end) AS 'New',
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) AS 'UnderReview',
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) AS 'Cancelled',
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end) AS 'Resolved',
(COUNT(CASE MessageStatus WHEN 19 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end)) AS 'Total Threads'
from #MS_MessagesFromCustomer
group by customerID, ThreadID
order by customerID
SELECT Distinct(CustomerID),* FROM #MS_InquiriesByCustomer
Select * from #MS_MessagesFromCustomer
Select Distinct(CustomerID), TotalMessages 'Total Messages Recevied', New 'New', UnderReview 'Under Review', Cancelled 'Cancelled',Resolved 'Resolved', count(TotalThreads) 'Total Threads'
FROM #MS_InquiriesByCustomer
GROUP BY customerID,ThreadID, TotalThreads,TotalMessages, New, UnderReview, Cancelled, Resolved
ORDER BY CustomerID
DROP TABLE #MS_MessagesFromCustomer
DROP TABLE #MS_InquiriesByCustomer
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
DISTINCT used as an argument of a SELECT clause does not eliminate rows due to a single duplicate column. It eliminates rows when all columns are equivalent. The whole row would have to be the same for it to begin its removal process.
DISTINCT can be used as an argument in an aggregate like AVG(DISTINCT price). Here it will eliminate duplicate prices before calculating the average.
I think you need to re-examine how you have coded your store procedure. It seems a little over done. Not sure what your desired end result is but, I would think a much simpler LEFT JOIN would handle most of the work.
|
|
|
|
|
Hellow everybody
I want a query that will return number of rows in the database , but it will take three parametes from the calling method.
I implemented a query which is as follows:
Select Count(*)
From MainRecord
Where (MealType = @MealType) AND (ID = @ID) AND ( CAST(FLOOR(CAST(DATE AS FLOAT)) AS DATETIME = @DATE)
The purpose of this query is that I want to count number of rows if the ID, MealType and DATE are in the database. Also, the @DATE I am giving in the calling query is now date which is for example ( today date [08/23/2007 00:00:00]). I wanted to be checked with the date that is stored in the database where the DATE that is stored in the database is DATETIME which is in this form (08/22/2007 13:14:50). Therefore, the CAST(FLOOR(CAST(DATE AS FLOOT)) AS DATETIME will convert that datetime to (08/22/2007 00:00:00) so we could compare then just in the date.
I don’t know if this way is right or not and if not how can I do it.
Thaanks,
-- modified at 11:36 Thursday 23rd August, 2007
|
|
|
|
|
CanadianBoy wrote: FLOOT
Did you mean FLOAT?
CanadianBoy wrote: I don’t if this way is right or not and if not how can I do it.
Have you tried experimenting? Building up a part of the query to see if it does what you want?
SELECT GETDATE()
SELECT CAST(GETDATE() AS FLOAT)
SELECT FLOOR(CAST(GETDATE() AS FLOAT))
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
Looks like it works to me.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
Yes, I mean foat. Does this way logical and it will work properly. If there is an other way i can try especially to check the time.
-- modified at 11:53 Thursday 23rd August, 2007
|
|
|
|
|
There is another way to check the date.. try this..
Select * From ....
Where
Convert(DateTime, @ParameterDate, 103) = Convert(DateTime, GetDate(), 103)
This is for checking with current date(GetDate()). For more precision yo can add the following lines of code with the above code.
And Year(@ParameterDate) = Year(GetDate())
And Month(@ParameterDate) = Month(GetDate())
The name is Sandeep
|
|
|
|
|
Thanks a lot for your reply both of you.
|
|
|
|
|
hi all,
i have to loop in a table in sql database to find a certain record and compare it or what ever,....
the question is what steps i have to do to loop in a table
using c# lang in a win app.
regards
|
|
|
|
|
costavo wrote: or what ever
costavo wrote: i have to loop in a table in sql database to find a certain record and compare it or what ever,....
the question is what steps i have to do to loop in a table
using c# lang in a win app.
I presume you are talking about a DataTable rather than one in SQL Server.
foreach(DataRow row in myDataTable.Rows)
{
}
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
Hi everyone,
I was installing sql server 2005 and it installs perfect with all components without any errors now I run SQL Server Management Studio but it wants a name to server but I dont know name of server how do I solve this problem?
Thanks
|
|
|
|
|
If you use Server Authentification use the name of your computer ,the user "sa" and the password that you set to sql server during the installation.
|
|
|
|
|
Im using of:
Server type: Database Engine
Server name: "I dont know what is it"
Authentication: Im trying with "Windows Authentication" and also Sql Server Authentication with sa" but I got this error:
Im trying with your answer(sa) but I got this error:
TITLE: Connect to Server
------------------------------
Cannot connect to local.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1231&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
|
|
|
|
|
It is the name of the machine, or "(local)" for the server on the machine you are using. If you installed a named instance (something you would have to do explicitly) then you will have to add /InstanceName after the machine name.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
Im using of:
Server type: Database Engine
Server name: "I dont know what is it" and also I checked with local
Authentication: Im trying with "Windows Authentication" and also Sql Server Authentication with sa" but I got this error:
TITLE: Connect to Server
------------------------------
Cannot connect to local.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1231&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
|
|
|
|
|
messages wrote: Cannot connect to local.
No, not "local". You need "(local)" with the brackets.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
messages wrote: this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections
Are you trying to connect to a SQL Server running on a separate machine?
If so you have to enable TCP/IP on the server. Use the SQL Server Configuration Manager
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
Im very thanksful for your time.
I try with all various local its not helpful and also I want to connect to sql server on my computer not remote
I went to services but I couldnt see any sql name on the services, then I decide to uninstall Sql server when I went for add/remove window for uninstall sql on the uninstall window I saw that it writes "No instances have been installed." on the Select an instace and on the Remove SQL Server 2005 common components it writes Workstation Components Can you help me I need to help you I searched internet but I cant find an answer
Thanks
|
|
|
|
|
Depends.
If it's installed on your local machine with Windows Authentication:
Server = localhost - or if you're using Sql Server Express localhost\SqlExpress or if you named an instance during installation SqlExpress\TheNameYouChose (or just uninstall and do it again properly).
If you installed it locally, but used mixed mode authentication:
user name = sa
password = whatever you set it to be.
If you installed it on a networked machine:
I can't help you here.
"It was the day before today.... I remember it like it was yesterday."
-Moleman
|
|
|
|
|
I dont know why I cant see any servics of sql server on the services?
|
|
|
|
|
You can connect to your local server by using "." Just type the "." in the name field and then login using credentials either "windows" or SQL.
Hope this helps.
Thanks!
Santhosh Kumar Edukulla
|
|
|
|
|
I jsut moved to a company that uses Oracle for almost all their database stuff. Having never used PL/SQL and being used to SQL server, I'm on a bit of a learning curve. Even though I'm a decent T-SQL programmer, there's a bit to pick up, and I need to be productive quickly.
I reckon I need two things ;
A step-by-step introduction book, with real-world worked examples and the like, to get me going and give me the knowledge I need to do the job as quickly as possible, and;
A small reference guide to the PL/SQL language, to have at my desk 24/7 for when I need specific, indexed information on a subject.
Has anyone had any experience with Oracle or PL/SQL literature? Any recommendations?
All the dude ever wanted... was his rug back.
|
|
|
|
|
hi
get any basics book and go with it dont take huge books first of all and have a glance at index and the perface of the book.
get back for any queries.
i dont say that i am an expert but i a good in oracle for me.
|
|
|
|
|
What exaclty is the purpose of using SELECT 1 ... in a query??
Many Thanks
Regards,
The only programmers that are better that C programmers are those who code in 1's and 0's ![Green Alien | [Alien]](https://www.codeproject.com/script/Forums/Images/alien.gif)
 Programm3r
My Blog: ^_^
|
|
|
|
|
What do you mean by
Programm3r wrote: SELECT 1
I Love SQL
|
|
|
|
|
I presume he means a query that in its entirity is SELECT 1
It is, after all, a valid query. (By that I mean that SQL Server will process it and produce a result set from it)
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
