December 9, 2008 at 9:04 pm
Hello Expert,
I have created this scripts below
SELECT COUNT(DEClearedDate) AS DECleared, COUNT(SomaticClearedDate) AS SomaticCleared, COUNT(PsycClearedDate) AS PsycCleared
FROM dbo.Power
The result from the scripts:
DECleared SomaticCleared PsycCleared TotalCleared
3 3 3 9
I wish to create the fourth column and that fourth column is those three adding together. Wish to create the fourth column with the name is "TotalCleared" is 9.
Since on the ASP.NET 2.0 I have textboxes "StartDate:" and "EndDate:" where the user enter the startdate and enddate. I wish to put Where Clause in the script, so the user can know from that startdate to enddate how many cases have been cleared?
Would you please help me to write this scripts in View? so I can save this view.
I know that the variables can not be used in View for the Where Clause.
Thank You.
Very Respectful
December 9, 2008 at 9:20 pm
select count (x), count(y), count(x) + count(y) 'sum'
from mytable
December 10, 2008 at 6:11 am
Hi there,
Thanks for the advice but the scripts that provided by you is not working because "+" is not recognized in this script.
Do you know why?
Thanks
Please help everyone out there
December 10, 2008 at 6:27 am
Is this really what you want to do? COUNT will simply return a count of rows whereas I suspect you want a total generated on whether the relevant ClearedDate column has been set or not in which case you can probably use SUM and CASE. What does each column contain when representing "not cleared"?
December 10, 2008 at 6:45 am
Hi There,
How are you doing?
Here is my question
If I wish to create a query with variable(s) in View, can I do it?
for example, because I have two variables on the ASP.NET 2.0
StartDate: TextBox <-----where the user will enter the startdate into the text box
EndDate: TextBox <-----where the user will enter the enddate into the text box
DISPLAY <----This is a button to display the result
I wish to create a query in view so I can save it. Because there are two variables @Startdate and @EndDate that I could not save the query. What should I do? I also declared the StartDate and EndDate in T-SQL but that declared only available in the table not in view right? would you please show me what I should do?
Here what I wish to do
I have three columns
DEClearedDate SomaticClearedDate PsycClearedDate
11/1/2008 11/4/2008 11/5/2008
11/3/2008 11/6/2008 11/7/2008
I wish to count
DECleared SomaticCleared PsycCleared
2 2 2
And Add all three columns into brand new column and that columns called TotalCleared
TotalCleared
6
and add where clause in order to pull out total cases from that startdate to enddate when the user enters on the interface of ASP.NET 2.0 and click the display button
Thank You
December 10, 2008 at 6:45 am
How are you structuring the script?
count() returns an integer, so + is a valid operator.
December 10, 2008 at 6:47 am
Hi Steve,
I am new to SQL Server, would you please tell me how do I structure the scripts?
Thank You So Much
Joe
December 10, 2008 at 7:49 am
Moving away from your question slightly, if I have a table, MyTable, with two columns say c1,MyDate and a view, MyView, defined as SELECT COUNT(*) FROM MyTable then
from your client you can build up the SQL statement SELECT * FROM MyView WHERE MyDate >= '20081101' and MyDate < '20081201'. This would give you a count of rows for the month of November.
However, the example as you have provided doesn't make much sense to me because all three columns will ALWAYS contain the same value. All you are doing is counting rows so if, for example, there are 100 rows between your start date and end date each COUNT will return 100 and the total of the three columns will be 300 i.e. 3 times the number of rows returned.
If you want to use COUNT to get a total for each ClearedDate for a specific period then you need to do this
select count(*)
from table
where DEClearedDate >=
and DEClearedDate
and have one select for each column you wish to count and then
you could UNION them together to form one select statement.
but even better would be to use a stored procedure.
I suppose you could "really go to town" and declare your view as SELECT * FROM table
and then dynamically build a query along the lines of
SELECT SUM(CASE WHEN DEClearedDate >= '20081101' AND DEClearedDate < '20081201' THEN 1 ELSE 0 END) AS DEClearedTotal,
SUM(CASE WHEN SomaticClearedDate >= '20081101' AND SomaticClearedDate < '20081201' THEN 1 ELSE 0 END) AS SomaticClearedTotal,
SUM(CASE WHEN PsycClearedDate >= '20081101' AND PsyclearedDate < '20081201' THEN 1 ELSE 0 END) AS PSyclearedTotal
FROM MyView
but I wouldn't touch that solution with a barge pole.
December 10, 2008 at 8:13 am
Hi There
I am new to this (SQL SERVER), how do I use store procedure and function? would you please show me? and give me an example.
Thanks
Joe
December 10, 2008 at 9:23 am
I'm feeling a little uneasy now. Might I respectfully suggest that if you are this new to SQL Server and you are going to be writing SQL to interact with a SQL Server database that you book yourself some formal training. There is so much to cover in what you are trying to do that it's not really feasible in a forum like this.
However, if you really want know a stored procedure is simply a batch of T_SQL commands stored in a database. I've included two examples but please be aware both are potentially dangerous. The first will always table scan and so will the second if each of the date columns is not indexed. Putting indexes on each of those columns could be a bad move as well but it is impossible to make a constructive suggestion without knowing a lot more about your database.
The script can be run in query analyser:
DROP TABLE MyTable
GO
CREATE TABLE MyTable
(
DEClearedDatedatetime,
SomaticClearedDatedatetime,
PsycClearedDatedatetime,
)
insert into mytable values ('20081101','20081105','20081121')
insert into mytable values ('20081106','20081112','20081129')
insert into mytable values ('20081109','20081124','20081202')
insert into mytable values ('20081114','20081201','20081208')
GO
DROP PROCEDURE MyProc_1
go
CREATE PROCEDURE MyProc_1
(
@dtStartDatedatetime,
@dtEndDatedatetime
)
AS
SELECTSUM(CASEWHEN DEClearedDate >= @dtStartDate
AND DEClearedDate < @dtEndDate
THEN 1 ELSE 0 END) AS DEClearedTotal,
SUM(CASEWHEN SomaticClearedDate >= @dtStartDate
AND SomaticClearedDate < @dtEndDate
THEN 1 ELSE 0 END) AS SomaticClearedTotal,
SUM(CASEWHEN PsycClearedDate >= @dtStartDate
AND PsycClearedDate < @dtEndDate
THEN 1 ELSE 0 END) AS PSycClearedTotal
FROMMyTable
GO
DROP PROCEDURE MyProc_2
GO
CREATE PROCEDURE MyProc_2
(
@dtStartDatedatetime,
@dtEndDatedatetime
)
AS
DECLARE@iDEClearedCountbigint,
@iSomaticClearedCountbigint,
@iPsycClearedCountbigint
SET NOCOUNT ON
SELECT@iDEClearedCount= COUNT(*)
FROMMyTable
WHEREDEClearedDate>= @dtStartDate
ANDDEClearedDate< @dtEndDate
SELECT@iSomaticClearedCount= COUNT(*)
FROMMyTable
WHERESomaticClearedDate>= @dtStartDate
ANDSomaticClearedDate< @dtEndDate
SELECT@iPsycClearedCount= COUNT(*)
FROMMyTable
WHEREPsycClearedDate>= @dtStartDate
ANDPsycClearedDate< @dtEndDate
SELECT@iDEClearedCountAS DEClearedCount,
@iSomaticClearedCountAS SomaticClearedCount,
@iPsycClearedCountAS PsyclearedCount
GO
-- now run the stored procedures with some different values
exec myproc_1 '20081101','20081201' -- Nov counts
exec myproc_1 '20081201','20090101' -- Dec counts
exec myproc_2 '20081101','20081201' -- Nov counts
exec myproc_2 '20081201','20090101' -- Dec counts
December 11, 2008 at 12:14 pm
How about the following:
CREATE PROCEDURE dbo.COUNT_EVENTS (
@StartDate DateTime,
@EndDate DateTime
)
AS
/* -- Stored Procedure to return the count of the three dates of events that meet criteria.
--
-- The Start Date and End Date are assumed to be "inclusive".
--
*/
BEGIN
--===== Provide a swap variable in case dates come in out of order
DECLARE @SwapDate AS DateTime
--===== Eliminate the time portions, and add 1 to End Date to ensure "inclusive"
SET @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)
SET @EndDate = DATEADD(dd,DATEDIFF(dd,0,@EndDate)+1,0)
--===== If the dates are not in the correct order, swap them...
SELECT @SwapDate = @EndDate, @EndDate = @StartDate, @StartDate = @SwapDate
WHERE @EndDate < @StartDate
;WITH TOTALS AS (
SELECT SUM(
CASE
WHEN DEClearedDate >= @StartDate AND
DECleardDate < @EndDate THEN 1
ELSE 0
END) AS DECleared, SUM(
CASE
WHEN SomaticClearedDate >= @StartDate AND
SomaticClearedDate < @EndDate THEN 1
ELSE 0
END) AS SomaticCleared, SUM(
CASE
WHEN PsycClearedDate >= @StartDate AND
PsycClearedDate < @EndDate THEN 1
ELSE 0
END) AS PsycCleared
FROM dbo.Power
WHERE (DEClearedDate >= @StartDate AND DECleardDate < @EndDate) OR
(SomaticClearedDate >= @StartDate AND SomaticClearedDate < @EndDate) OR
(PsycClearedDate >= @StartDate AND PsycClearedDate < @EndDate)
)
SELECT *, DECleared + SomaticCleared + PsycCleared AS TotalCleared
FROM TOTALS
END
GO
You may need to dispense with the WHERE clause to improve performance, so you'll need to test this. You can do so as follows:
EXEC dbo.COUNT_EVENTS '20081101', '20081130'
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply