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
200410ALBERTA2
200410ANDERSON2
200410BOISSONNAULT3
200410BROWN2
200410CALGARY12
200410CAMPBELL3
200410CANADA2
200410CITY2
200410COSCO2
200410CROWN12
200410EDMONTON13
200410EVERGREEN2
200410FRANCIS2
200410GRANDE PRAIRIE2
200410GREAT2
200410LAFONTAINE2
200410MACDONALD2
200410MARTIN2
200410MARTINSON2
200410MORRIS2
200410NA3
200410NONE13
200410PUBLIC2
200410RCMP5
200410REMILLARD2
200410ROYAL2
200410RUFF2
200410SCHMIDT2
200410SOHAIL2
200410SPENCER2
200410TD2
200410THE2
200410THOMPSON2
200410WHITECOURT2
200410WORKERS2
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.googlegr oups.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.googlegr oups.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.googlegr oups.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.googlegr oups.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.
>