none
combining two count result in a month RRS feed

  • Question

  • Hi to all,

    just want to ask if how can i combine two query result in a month?

    fyi, they are from a two different table but what want is to get the statistics per month

     

    here are my two different queries,

    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    Order By Year( v.DatePosted) ASC, Month(v.DatePosted) ASC
    
    
    
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    Order By Year( ap.Datestarted) ASC, Month(ap.Datestarted) ASC;
    


    so the query above will give a result like this.

    Month                    Requirement

    January 2012              5

     

    2nd query will be like this

     

    Month                 Applicants

    January 2012           10

     

    what i want is to be like this

     

    Month                    Requirements    Applicants

    January 2012               5                         10

     

     

    please advise.

    thanks

    Friday, January 27, 2012 9:01 AM

Answers

  • select q1.Month, q1.requirement, q2.acceptanceprocess 
    from (
    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month
    ,  Month(v.DatePosted) M -- keep for sorting
    ,  Year(v.DatePosted) Y -- keep for sorting
    ,  count ( distinct v.requirementID ) as requirement 
    from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    ) q1 
    join (
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    ) q2 on q1.Month = q2.Month
    order by q1.Y, q1.M
    


    Serg
    Friday, January 27, 2012 9:44 AM

All replies

  • Hi,

    You can try by using UNION as follows....

    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    UNION
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    Order By Year( ap.Datestarted) ASC, Month(ap.Datestarted) ASC;
    


    PS.Shakeer Hussain
    Friday, January 27, 2012 9:04 AM
  • Hi Syed,

    i encounter this error, do u have any idea why so?

    Msg 4104, Level 16, State 1, Line 11
    The multi-part identifier "ap.Datestarted" could not be bound.
    Msg 104, Level 16, State 1, Line 11
    ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
    Msg 4104, Level 16, State 1, Line 11
    The multi-part identifier "ap.Datestarted" could not be bound.
    Msg 104, Level 16, State 1, Line 11
    ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

    here is my current code.

    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    --Order By Year( v.DatePosted) ASC, Month(v.DatePosted) ASC
    union
    
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    Order By Year( ap.Datestarted) ASC, Month(ap.Datestarted) ASC;
    



    please advise.

    thanks. 


    • Edited by iamnewtosql Friday, January 27, 2012 9:08 AM
    Friday, January 27, 2012 9:07 AM
  • try..

    select f1.Month,f1.requirement,f2.applicant
    (
    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    --Order By Year( v.DatePosted) ASC, Month(v.DatePosted) ASC
    )f1
    inner join 
    (
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    --Order By Year( ap.Datestarted) ASC, Month(ap.Datestarted) ASC;
    )f2 on f1.Month=f2.Month

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Friday, January 27, 2012 9:08 AM
  • Hi vt,

    i tried to use your code but no luck,

    here is the error,

    Msg 102, Level 15, State 1, Line 7

    Incorrect syntax near ')'.

    Msg 102, Level 15, State 1, Line 15

    Incorrect syntax near 'f2'.

     

    any idea why?

    please advise thanks!

    Friday, January 27, 2012 9:12 AM
  • select f1.Month,f1.requirement,f2.applicant from 
    (
    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    )f1
    inner join 
    (
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    )f2 on f1.Month=f2.Month
    my mistake.. try the above
    VT

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Friday, January 27, 2012 9:14 AM
  • can i sort it by month and year using order by?
    Friday, January 27, 2012 9:16 AM
  • Hi,
    Try with the below query

    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month,
    count ( distinct v.requirementID ) as requirement from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    --Order By Year( v.DatePosted) ASC, Month(v.DatePosted) ASC
    union
    
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    
    


    PS.Shakeer Hussain
    Friday, January 27, 2012 9:16 AM
  • select q1.Month, q1.requirement, q2.acceptanceprocess 
    from (
    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month
    ,  Month(v.DatePosted) M -- keep for sorting
    ,  Year(v.DatePosted) Y -- keep for sorting
    ,  count ( distinct v.requirementID ) as requirement 
    from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    ) q1 
    join (
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    ) q2 on q1.Month = q2.Month
    order by q1.Y, q1.M
    


    Serg
    Friday, January 27, 2012 9:44 AM
  • Hi Serg,

    one last thing, what if i will join another query like this. is this possible cause i am receiving some error,

    use QMStestdatabase
    select q1.Month, q1.requirement, q2.applicant
    from (
    Select DateName(month, v.DatePosted) + ' ' +  + Cast(Year( v.DatePosted) As char(4)) As Month
    ,  Month(v.DatePosted) M -- keep for sorting
    ,  Year(v.DatePosted) Y -- keep for sorting
    ,  count ( distinct v.requirementID ) as requirement 
    from vesselrequirement v
    Group By DateName(month, v.DatePosted), Month(v.DatePosted), Year(v.DatePosted)
    ) q1 
    join (
    Select DateName(month, ap.Datestarted) + ' ' +  + Cast(Year( ap.Datestarted) As char(4)) As Month,
    count ( ap.requirementID ) as applicant from acceptanceprocess ap where ap.acceptprocessID = 1 
    Group By DateName(month, ap.Datestarted), Month(ap.Datestarted), Year(ap.Datestarted)
    ) q2 
    
    join (
    Select DateName(month, ai.DateApplied) + ' ' +  + Cast(Year( ai.DateApplied) As char(4)) As Month,
    count ( ap.requirementID ) as ApplicantFromPool from Applicantinformation ai  
    Group By DateName(month, ai.DateApplied), Month(ai.DateApplied), Year(ai.DateApplied)
    ) q3 on q1.Month = q2.Month
    order by q1.Y, q1.M
    
    

    please advise.

    thanks in advance!
     

    Wednesday, February 1, 2012 2:20 AM