Help

I need some help...
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,'Clients' = adversesurname
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
where year(entrydate) = '2004' and month(entrydate) = '10'
GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
COUNT (adversesurname) > 1 )
compute count(adversesurname)
result:
year month name numduplicate
2004 10 ALBERTA 2
2004 10 ANDERSON 2
2004 10 BOISSONNAULT 3
2004 10 BROWN 2
2004 10 CALGARY 12
2004 10 CAMPBELL 3
2004 10 CANADA 2
2004 10 CITY 2
2004 10 COSCO 2
2004 10 CROWN 12
2004 10 EDMONTON 13
2004 10 EVERGREEN 2
2004 10 FRANCIS 2
2004 10 GRANDE PRAIRIE 2
2004 10 GREAT 2
2004 10 LAFONTAINE 2
2004 10 MACDONALD 2
2004 10 MARTIN 2
2004 10 MARTINSON 2
2004 10 MORRIS 2
2004 10 NA 3
2004 10 NONE 13
2004 10 PUBLIC 2
2004 10 RCMP 5
2004 10 REMILLARD 2
2004 10 ROYAL 2
2004 10 RUFF 2
2004 10 SCHMIDT 2
2004 10 SOHAIL 2
2004 10 SPENCER 2
2004 10 TD 2
2004 10 THE 2
2004 10 THOMPSON 2
2004 10 WHITECOURT 2
2004 10 WORKERS 2
cnt
35
Can anyone tell me how can i make the where line to go through months
without hardcoding...to count each row for month october, november,
december and just show the totals without showing the names
this is how i want it to show.
2004 10 35
2004 11 100
2004 12 333
2005 01 1230
2005 02 1233
etc.
can anyone help me?What about:
SELECT
'YEAR' = year(entrydate)
,'MONTH'=month(entrydate)
,COUNT(adversesurname) AS Numduplicate
FROM dbo.atlas_mast
GROUP BY year(entrydate), month(entrydate)
HAVING COUNT (adversesurname) > 1
HTH, jens Suessmeyer.|||DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>I need some help...
> SELECT
> 'YEAR' = year(entrydate)
> ,'MONTH'=month(entrydate)
> ,'Clients' = adversesurname
> ,COUNT(adversesurname) AS Numduplicate
> FROM dbo.atlas_mast
> where year(entrydate) = '2004' and month(entrydate) = '10'
> GROUP BY year(entrydate), month(entrydate), adversesurname HAVING (
> COUNT (adversesurname) > 1 )
> compute count(adversesurname)
> result:
> year month name numduplicate
> 2004 10 ALBERTA 2
> 2004 10 ANDERSON 2
> 2004 10 BOISSONNAULT 3
> 2004 10 BROWN 2
> 2004 10 CALGARY 12
> 2004 10 CAMPBELL 3
> 2004 10 CANADA 2
> 2004 10 CITY 2
> 2004 10 COSCO 2
> 2004 10 CROWN 12
> 2004 10 EDMONTON 13
> 2004 10 EVERGREEN 2
> 2004 10 FRANCIS 2
> 2004 10 GRANDE PRAIRIE 2
> 2004 10 GREAT 2
> 2004 10 LAFONTAINE 2
> 2004 10 MACDONALD 2
> 2004 10 MARTIN 2
> 2004 10 MARTINSON 2
> 2004 10 MORRIS 2
> 2004 10 NA 3
> 2004 10 NONE 13
> 2004 10 PUBLIC 2
> 2004 10 RCMP 5
> 2004 10 REMILLARD 2
> 2004 10 ROYAL 2
> 2004 10 RUFF 2
> 2004 10 SCHMIDT 2
> 2004 10 SOHAIL 2
> 2004 10 SPENCER 2
> 2004 10 TD 2
> 2004 10 THE 2
> 2004 10 THOMPSON 2
> 2004 10 WHITECOURT 2
> 2004 10 WORKERS 2
> cnt
> 35
> Can anyone tell me how can i make the where line to go through months
> without hardcoding...to count each row for month october, november,
> december and just show the totals without showing the names
> this is how i want it to show.
> 2004 10 35
> 2004 11 100
> 2004 12 333
> 2005 01 1230
> 2005 02 1233
> etc.
> can anyone help me?
>|||Guess the op just wanted to count the rows rather than summing up, uh ?
-Jens.|||i tried that but it will only give me the total count of clients per
year, month
what i want to get is the count of duplicates from the total of clients
per month and year.
also i need to order them by year, month
it would show like this
Year month total/names Total multiple Total not mult % of mult
2004 10 450 50 400 12.50
2004 11 300 60 240 25
the above code will only give me the total/names which is good but
where i run into problems is when i get to calculate the total multiple
and single per month.|||this not what you are looking for ?
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eVNuidyBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE Result
> CREATE TABLE Result
> (
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>
> "melindam" <cargowise_melindam@.yahoo.com> wrote in message
> news:1135275094.304489.196740@.z14g2000cwz.googlegroups.com...
>|||no thats not what i was looking for, thank you though.
David J. Cartwright
Dec 22, 11:41 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
messages by this author
Date: Thu, 22 Dec 2005 13:41:54 -0500
Local: Thurs, Dec 22 2005 11:41 am
Subject: Re: Help
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
DROP TABLE Result
CREATE TABLE Result
(
entrydate datetime,
adversesurname char(50),
numduplicate int
)
Go
INSERT Result VALUES('10/01/2004','NAME1',1)
INSERT Result VALUES('10/01/2004','NAME1',15)
INSERT Result VALUES('10/01/2004','NAME2',20)
INSERT Result VALUES('11/01/2004','NAME3',50)
INSERT Result VALUES('11/01/2004','NAME4',50)
INSERT Result VALUES('12/01/2004','NAME5',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('12/01/2004','NAME6',111)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('01/01/2005','NAME6',800)
INSERT Result VALUES('01/01/2005','NAME6',200)
INSERT Result VALUES('01/01/2005','NAME6',230)
INSERT Result VALUES('01/01/2005','NAME6',1)
INSERT Result VALUES('02/01/2005','NAME6',412)
INSERT Result VALUES('02/01/2005','NAME6',411)
INSERT Result VALUES('02/01/2005','NAME6',410)
Go
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)|||what did it not do?
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135278730.580717.269820@.o13g2000cwo.googlegroups.com...
> no thats not what i was looking for, thank you though.
> David J. Cartwright
> Dec 22, 11:41 am show options
> Newsgroups: microsoft.public.sqlserver.server
> From: "David J. Cartwright" <davidcartwri...@.hotmail.com> - Find
> messages by this author
> Date: Thu, 22 Dec 2005 13:41:54 -0500
> Local: Thurs, Dec 22 2005 11:41 am
> Subject: Re: Help
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse
> DROP TABLE Result
> CREATE TABLE Result
> (
>
> entrydate datetime,
> adversesurname char(50),
> numduplicate int
> )
> Go
> INSERT Result VALUES('10/01/2004','NAME1',1)
> INSERT Result VALUES('10/01/2004','NAME1',15)
> INSERT Result VALUES('10/01/2004','NAME2',20)
> INSERT Result VALUES('11/01/2004','NAME3',50)
> INSERT Result VALUES('11/01/2004','NAME4',50)
> INSERT Result VALUES('12/01/2004','NAME5',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('12/01/2004','NAME6',111)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('01/01/2005','NAME6',800)
> INSERT Result VALUES('01/01/2005','NAME6',200)
> INSERT Result VALUES('01/01/2005','NAME6',230)
> INSERT Result VALUES('01/01/2005','NAME6',1)
> INSERT Result VALUES('02/01/2005','NAME6',412)
> INSERT Result VALUES('02/01/2005','NAME6',411)
> INSERT Result VALUES('02/01/2005','NAME6',410)
> Go
> SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
> SUM(numduplicate) FROM Result
> WHERE numduplicate > 1
> GROUP BY MONTH(entrydate), YEAR(entrydate)
>|||I dont need to drop the table and create a table. I already have a
table an existing table where im extracting the information.
and i dont want it to hardcode the dates...the dates will be selected
from the table.|||um...review my post again...i provided the add create as i used it to write
the select statement
SELECT YEAR(entrydate) AS 'Year',MONTH(entrydate) AS 'Month',
SUM(numduplicate) FROM Result
WHERE numduplicate > 1
GROUP BY MONTH(entrydate), YEAR(entrydate)
"melindam" <cargowise_melindam@.yahoo.com> wrote in message
news:1135282541.079930.285980@.z14g2000cwz.googlegroups.com...
>I dont need to drop the table and create a table. I already have a
> table an existing table where im extracting the information.
> and i dont want it to hardcode the dates...the dates will be selected
> from the table.
>