question

hanoihanoi avatar image
hanoihanoi asked

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
tsqlgroup-byquery-plan
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It is not http:// but c.ID as CGEnID
0 Likes 0 ·
alexsdba avatar image
alexsdba answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

maicalljason avatar image
maicalljason answered
oh my gad,so hard maybe you can buy something for relax example [herve leger][1] so sex. [1]: http://www.hervelegercp.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.