x

Group by in the Procedure

I have a Table IA with

GenID(Primary),FacilityGenID,SMemberGenId,IAShipTypeGenID,CGenID,
Amount,VGenID,AGenID

I Have 2 reference Tables
refACC table with

AccGenid, Description

AND refIA table with

IAShipGenId ,IAShipDescription

I have another Table C with ID,CNumber,BeginDate,EndDate

I have another Table V with VId,VName

I have Another Table S with SMEmberGenID,FacilityGenId,FName,LName

I have to write a proc for my SSRS Report. The Parameters for my sql is @IAShipdescription,@ FromC, @ToC

CNumber is split into FromC and ToC which I have written as

Select distinct CNumber from C
Where (CNumber  >= @FromC  and @FromC is not null) or @IAShipdescription 
in('ALL',NULL@IAShipdescription) order by cNumber

ALTER Procedure [dbo].[sp_RPT_IAEYCScholarships]
@IAShipDescription varchar(50)=NUll,
@FromC int =NULL,
@ToC int =NULL

AS
SELECT
      s.FName + ' '  + s.LName as SName
    , v.Name
    , refACC.Description 
    , refIA.Description as IADescription
    , c.CNumber
    , c.ID as CGenID
    , Convert(varchar,c.BeginDate,101) as CBeginDate
    , Convert(varchar,c.EndDate,101) as CEndDate
    , Sum(IA.Amount) as TotalAmount
    , Count(IA.ShipTypeGenId)as IAShipCount
    , refACC.ScholarshipTypeGenID 

FROM A as IA
    INNER JOIN S      ON s.SMemberGenid=IA.SMemberGenID
    INNER JOIN C      ON   c.ID=IA.CGenID
    INNER JOIN V      ON   v.id = IA.VGenID 
    LEFT JOIN refAcc  ON refAcc.AccGenID=IA.AGenID
    LEFT JOIN refIA   ON refIA.IAShipGenID = IA.ShipGenID



 WHERE 
      (
            @IAShipDescription is NULL OR (refIA.IAShipDescription = 
                                                   @IAShipDescription)
         or( @IAShipDescription = 'ALL')
        )

           and (
           ((c.CNumber between @FromC and @ToC) and (@FromC<=@ToC))
           or   (c.CNumber is NUll or c.CNumber = isNull(@FromC,c.CNumber))
           or   (c.CNumber is NUll or c.CNumber = isNull(@ToC,c.CNumber))
           )

Group By 
          s.FName + ' '  + s.LName as SName
    , v.Name
    , refACC.Description 
    , refIA.Description as IADescription
    , c.CNumber
    , c.ID as CGenID
    , Convert(varchar,c.BeginDate,101) as CBeginDate
    , Convert(varchar,c.EndDate,101) as CEndDate
    , refACC.ScholarshipTypeGenID 
Order By c.BeginDate desc

My question is if I need to to group by IA.ShipTypeGenId with totals for each IA.ShipTypeGenId is the above proc correct

Thanks, Hanoi

more ▼

asked Mar 15, 2012 at 06:27 PM in Default

hanoihanoi gravatar image

hanoihanoi
0 4 5 6

It is not http:// but c.ID as CGEnID
Mar 15, 2012 at 06:29 PM hanoihanoi
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

try this:

SELECT s.FName + ' ' + s.LName as SName , v.Name , refACC.Description , refIA.Description as IADescription , c.CNumber , http://c.ID as CGenID , Convert(varchar,c.BeginDate,101) as CBeginDate , Convert(varchar,c.EndDate,101) as CEndDate , Sum(IA.Amount) as TotalAmount , Count(IA.ShipTypeGenId)as IAShipCount , Sum(refACC.ScholarshipTypeGenID) as ScholarshipTypeGenID

FROM A as IA INNER JOIN S ON s.SMemberGenid=IA.SMemberGenID INNER JOIN C ON http://c.ID=IA.CGenID INNER JOIN V ON http://v.id = IA.VGenID LEFT JOIN refAcc ON refAcc.AccGenID=IA.AGenID LEFT JOIN refIA ON refIA.IAShipGenID = IA.ShipGenID

WHERE ( @IAShipDescription is NULL OR (refIA.IAShipDescription = @IAShipDescription) or( @IAShipDescription = 'ALL') )

       and (
       ((c.CNumber between @FromC and @ToC) and (@FromC<=@ToC))
       or   (c.CNumber is NUll or c.CNumber = isNull(@FromC,c.CNumber))
       or   (c.CNumber is NUll or c.CNumber = isNull(@ToC,c.CNumber))
       )
Group By SName , v.Name , refACC.Description , IADescription , c.CNumber , CGenID , CBeginDate , CEndDate Order By c.BeginDate desc
more ▼

answered Mar 15, 2012 at 09:02 PM

alexsdba gravatar image

alexsdba
221 1 1 4

(comments are locked)
10|1200 characters needed characters left

oh my gad,so hard maybe you can buy something for relax example [herve leger][1] so sex.

[1]: http://www.hervelegercp.com
more ▼

answered Mar 19, 2012 at 09:11 AM

maicalljason gravatar image

maicalljason
8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292
x37
x12

asked: Mar 15, 2012 at 06:27 PM

Seen: 1431 times

Last Updated: Mar 19, 2012 at 09:11 AM