sql syntax

Hello Experts
I am trying to run the attached SQL to fetch the data by the user type. All I am trying is to count the number of
registrations by month and show it in the following order

INTERNAL      EXTERNAL Year   Month
18            2       2011      9
4            1       2011      10

however the query i am using giving me the following result

NumCodes      UserTpe  Year   Month
2            EXTERNAL 2011      9
1            EXTERNAL 2011      10
18            INTERNAL 2011      9
4            INTERNAL 2011      10

Can someone please advise?

Thanks
S
SELECT     COUNT(dbo.MatchCertificate.MatchCodeID) AS NumCodes, dbo.MatchCertificateHistory.UserType,
 Year(MatchHistDateAdded) AS YEAR, Month(MatchHistDateAdded) AS MONTH 
FROM         dbo.MatchCertificate INNER JOIN
                      dbo.MatchCertificateHistory ON dbo.MatchCertificate.MatchCodeID = dbo.MatchCertificateHistory.MatchCodeID
WHERE     (dbo.MatchCertificateHistory.StatusID = 'REGISTERED') AND 
(dbo.MatchCertificateHistory.MatchHistDateAdded > '2010-11-01')
GROUP BY dbo.MatchCertificateHistory.UserType, Year(GiftHistDateAdded), Month(MatchHistDateAdded)

Open in new window

LVL 8
newbie27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
try this
select Internal,External,[Year],[Month]
  from (
SELECT     H.UserType,
 Year(MatchHistDateAdded) AS YEAR, Month(MatchHistDateAdded) AS MONTH 
FROM         dbo.MatchCertificate as C
INNER JOIN   dbo.MatchCertificateHistory as H
  ON C.MatchCodeID = H.MatchCodeID
WHERE  (H.StatusID = 'REGISTERED')
 AND (H.MatchHistDateAdded > '2010-11-01')
) as X
Pivot (count(*) for usertype in (Internal,External)) as pvt
order by [year],[month]

Open in new window

0
8080_DiverCommented:
On the other hand, you could use the following:
SELECT  SUM(Internal) Internal
       ,SUM(,External) External
       ,TheYear
       ,TheMonth
FROM
(
SELECT  CASE WHEN MCH.UserType = 'EXTERNAL'
             THEN 0
             ELSE 1
        END Internal
       ,CASE WHEN MCH.UserType = 'EXTERNAL'
             THEN 1
             ELSE 0
        END External
       ,Year(MatchHistDateAdded) AS TheYear
       ,Month(MatchHistDateAdded) AS TheMonth 
FROM   dbo.MatchCertificate  MC
INNER JOIN dbo.MatchCertificateHistory MCH
   ON MC.MatchCodeID = MCH.MatchCodeID
WHERE  (MCH.StatusID = 'REGISTERED') 
  AND  (MC.MatchHistDateAdded > '2010-11-01')
) Z
GROUP BY TheYear
        ,TheMonth
ORDER BY TheYear
        ,TheMonth;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmmarRCommented:
hi newbie27

what you are trying to do here is to convert rows into columns, if you have only External and internel usertypes, i would suggest you hardcode it as 2 columns and get your results

but if you have different other types that is random, the script will be a bit complicated.

check code below in 3 steps

--Step one just get the internal users  and store them in a temp table
Select NumCodes 'INTERNAL', 0 'EXTERNAL' ,x.Year,x.Month
into #NewTEmp from
(
SELECT     
COUNT(dbo.MatchCertificate.MatchCodeID) AS NumCodes, 
dbo.MatchCertificateHistory.UserType,
 Year(MatchHistDateAdded) AS YEAR, 
 Month(MatchHistDateAdded) AS MONTH 
FROM         
dbo.MatchCertificate INNER JOIN
dbo.MatchCertificateHistory ON dbo.MatchCertificate.MatchCodeID = dbo.MatchCertificateHistory.MatchCodeID
WHERE     
(dbo.MatchCertificateHistory.StatusID = 'REGISTERED') AND 
(dbo.MatchCertificateHistory.MatchHistDateAdded > '2010-11-01') And 
(dbo.MatchCertificateHistory.UserType = 'INTERNAL')
GROUP BY dbo.MatchCertificateHistory.UserType, 
Year(GiftHistDateAdded), 
Month(MatchHistDateAdded)
) x


--Step one just get the external users  and update the temp table 
update #NewTEmp set #NewTEmp.[External]  = x.NumCodes
from (
SELECT     
COUNT(dbo.MatchCertificate.MatchCodeID) AS NumCodes, 
dbo.MatchCertificateHistory.UserType,
 Year(MatchHistDateAdded) AS YEAR, 
 Month(MatchHistDateAdded) AS MONTH 
FROM         
dbo.MatchCertificate INNER JOIN
dbo.MatchCertificateHistory ON dbo.MatchCertificate.MatchCodeID = dbo.MatchCertificateHistory.MatchCodeID
WHERE     
(dbo.MatchCertificateHistory.StatusID = 'REGISTERED') AND 
(dbo.MatchCertificateHistory.MatchHistDateAdded > '2010-11-01') And 
(dbo.MatchCertificateHistory.UserType = 'EXTERNAL')
GROUP BY dbo.MatchCertificateHistory.UserType, 
Year(GiftHistDateAdded), 
Month(MatchHistDateAdded)
) x
where #NewTEmp.[Year] = x.Year and   #NewTEmp.month = x.Month

--display records
Select * from  #NewTEmp

Open in new window

0
newbie27Author Commented:
Hello Folks,

Thank you very much for your input.

8080_Diver's solution has worked for me. I believe INTERNAL and EXTERNAL are reserve words in SQL? I have to change it slightly and it works.

Thanks
0
8080_DiverCommented:
I was afraid that those were reserved.  Year and Month are also reserved, by the way. ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.