counting and displaying - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

counting and displaying

Author

Topic

msugradus
Starting Member

40 Posts

Posted - 2007-06-08 : 15:57:18

I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report:

FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-08 : 16:04:20

something like:


declare @prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime
select  @prevMonthStart = '20070501 00:00:00.000', @prevMonthEnd = '20070531 23:59:59.999', 
        @thisMonthStart = '20070601 00:00:00.000', @thisMonthEnd = '20070630 23:59:59.999'

select  sum(case when StartedDate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,
        sum(case when StartedDate between @thisMonthStart and @thisMonthStart then 1 else 0 end) as thisMonthCount
from    yourTable
where   StartedDate >= @prevMonthStart

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

msugradus
Starting Member

40 Posts

Posted - 2007-06-08 : 16:34:32

I keep getting a ADO error by the declare when i try and run this

ALTER FUNCTION dbo.Function6
()
RETURNS TABLE
AS
RETURN (

declare @prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime

select @prevMonthStart = '20070501 00:00:00.000', @prevMonthEnd = '20070531 23:59:59.999',
@thisMonthStart = '20070601 00:00:00.000', @thisMonthEnd = '20070630 23:59:59.999'

select sum(case when StartedDate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,
sum(case when StartedDate between @thisMonthStart and @thisMonthStart then 1 else 0 end) as thisMonthCount

from exigo_data_sync.customer

where StartedDate >= @prevMonthStart )

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-08 : 16:38:51

of course you are.
you need to put variables as parameters in your function.

look up the syntax in BOL = books online = sql server help

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 01:18:37

Also you need to define the structure of table that function returns
Why dont you use stored procedure instead of function?

Madhivanan

Failing to plan is Planning to fail

msugradus
Starting Member

40 Posts

Posted - 2007-06-11 : 10:14:42

I have never used any stored procedures, how would I go about creating one for this?

quote:
Originally posted by madhivanan

Also you need to define the structure of table that function returns
Why dont you use stored procedure instead of function?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 10:40:41

Read about Procedures in sql server help file

Madhivanan

Failing to plan is Planning to fail

msugradus
Starting Member

40 Posts

Posted - 2007-06-11 : 10:53:57

Ok, I am trying to figure this out to where a user could enter a start and end date for two months and compare the data...is this at all possible?....stored procedure or function?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 10:56:10

[code]
Create Procedure test
(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)
as

select sum(case when StartedDate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,
sum(case when StartedDate between @thisMonthStart and @thisMonthStart then 1 else 0 end) as thisMonthCount
from yourTable
where StartedDate >= @prevMonthStart
[/code]
Now Execute it by supplying dates
[code]
EXEC TEST @prevMonthStart = '20070501 00:00:00.000', @prevMonthEnd = '20070531 23:59:59.999',
@thisMonthStart = '20070601 00:00:00.000', @thisMonthEnd = '20070630 23:59:59.999'
[/code]

Madhivanan

Failing to plan is Planning to fail

msugradus
Starting Member

40 Posts

Posted - 2007-06-11 : 14:41:45

That worked beautiful when the data that i needed was on one table, but how do you do it when there are two tables involved. Say table1 has two fields: date and prodId and table2 had prodId and order. The two tables are tied by the prodId and I want to be able to enter the dates but display a report containing the number of orders for each month?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:02:38

1 You need to join the tables
2 Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 09:14:14

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

(Copyright by Madhi. Inc.)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:46:10

quote:
Originally posted by harsh_athalye

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

(Copyright by Madhi. Inc.)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

How many Copyrights do I have?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 09:53:42

You are the only person who can tell that!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-06-12 : 15:22:58

FirstName, LastName, StartedDate are the column of table EMPTABLE.

Step 01: Replace the table name, column name according to your table structure.

Step 02: Execute the following stored procedure.

CREATE PROCEDURE RPT_JOINNING_DETAILS
AS
BEGIN

DECLARE @ROWCOUNT AS INT
DECLARE @EMPNAME AS VARCHAR(50)
DECLARE @ID AS INT

CREATE TABLE #JOINTHISMONTHS(ID INT IDENTITY(1,1) NOT NULL, EMPNAME VARCHAR(50))
CREATE TABLE #JOINLASTMONTHS(ID INT IDENTITY(1,1) NOT NULL, EMPNAME VARCHAR(50))

INSERT INTO #JOINTHISMONTHS
SELECT FirstName +' '+ LastName FROM EMPTABLE WHERE CAST(MONTH(StartedDate) AS VARCHAR(10)) = CAST(MONTH(GETDATE()) AS VARCHAR(10))

INSERT INTO #JOINLASTMONTHS
SELECT FirstName +' '+ LastName FROM EMPTABLE WHERE CAST(MONTH(StartedDate) AS VARCHAR(10)) = CAST((MONTH(GETDATE())-1) AS VARCHAR(10))

SELECT @ROWCOUNT = MAX(ID) FROM #JOINTHISMONTHS
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT FOR LAST TWO MONTH-----------------------------'
PRINT '------------------------------------------------------------------------------------------'

PRINT 'TOTAL NO OF EMPLOYEES JOINED THIS MONTHS ('+ CAST(MONTH(GETDATE()) AS VARCHAR(10))+'-'+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(10)),2) +') :' + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @EMPNAME = EMPNAME, @ID = ID FROM #JOINTHISMONTHS WHERE ID = @ROWCOUNT
PRINT ' * '+@EMPNAME
SET @ROWCOUNT = @ROWCOUNT - 1
END

SELECT @ROWCOUNT = MAX(ID) FROM #JOINLASTMONTHS

PRINT 'TOTAL NO OF EMPLOYEES JOINED LAST MONTHS ('+ CAST(MONTH((GETDATE())-1) AS VARCHAR(10))+'-'+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(10)),2) +') :' + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @EMPNAME = EMPNAME, @ID = ID FROM #JOINLASTMONTHS WHERE ID = @ROWCOUNT
PRINT ' * '+@EMPNAME
SET @ROWCOUNT = @ROWCOUNT - 1
END

DROP TABLE #JOINTHISMONTHS
DROP TABLE #JOINLASTMONTHS

END

Step 03: EXEC RPT_JOINNING_DETAILS

Hope, you will get the report.....

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-04-24 : 18:06:36

Subrata,

Im new to SQL. I followed your path here & tried to bring out data exclusive to that table in comparision with another table data. For example I have table A with columns ID, First name, Last Name & table B with ID, First name, last name. These two tables can be bound by ID.
Now I want
1.Data that is in A but not in B &
2.Data that is in B but not in A.

Here is what I wrote in here & it is not working. Please correct me.

_____________________________________________________________________

CREATE PROCEDURE RPT_DETAILS
AS
BEGIN
DECLARE @Rowcount AS INT
DECLARE @First_Name AS VARCHAR(50)
DECLARE @Last_Name AS VARCHAR(50)
DECLARE @Id AS INT

CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))

INSERT INTO #Prowess
SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid

INSERT INTO #SDK
SELECT cast(sa_ss as INT), first_name, last_name from ml

SELECT @ROWCOUNT = MAX(ID) FROM #SDK
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------'
PRINT '------------------------------------------------------------------------------------------'

PRINT 'TOTAL Difference ('+ +
CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Prowess WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END

SELECT @ROWCOUNT = MAX(ID) FROM #Sdk

PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Sdk WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END

DROP TABLE #Prowess
DROP TABLE #Sdk

END

exec RPT_DETAILS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources