Diana Diana -4 years ago 59
SQL Question

Use of a variable in Sql Server

I have a table called lprPostcodeRegion with Region Name and Region ID. I would like to calculate the following sum based on the region ID. For instance, for region ID 1 I would get a certain sum, for region ID 2 I would get a different sum and this happens for approximately 15 IDs.

As you can see I have declared the RegionID as 1 therefore, I only get the sum for RegionID1 (lpr.regionID = 1/ lpr.regionID = @RegionID). How can I automatically calculate the sum for all of the Region IDs?

DECLARE @RegionID int;
SET @RegionID = 1;
SELECT lpr.RegionID,
--wc.whsHeaderID,
SUM(c.[Chargeable Weight]) AS LeftOffWt
FROM dbo.whsConsignment wc
INNER JOIN whsHeader wh on wh.WhsHeaderID = wc.whsHeaderID
INNER JOIN dbo.cgtConsignment c ON c.[Consignment Reference] = wc.ConsignmentReference
INNER JOIN dbo.genAddress ga ON ga.AddressID = c.Consignor
INNER JOIN dbo.lstPostcode lp ON lp.Postcode = ga.PostcodePrefix
INNER JOIN dbo.lstPostcodeRegion lpr ON lpr.RegionID = lp.RegionID
AND c.Cancelled = 0
--AND wc.ConsignmentReference LIKE '4%'
and lpr.RegionID = 1
WHERE StatusCode IN ( 'NL' ) and wh.ArrDepDate between @StartDate and @EndDate
GROUP BY lpr.RegionID

Answer Source

If you dont want to juse all IDs you can do like this

   DECLARE @RegionID int; 
   select @RegionID = select RegionID from dbo.lstPostcodeRegio where RegionID in (1,2,3,5,etc)

   SELECT lpr.RegionID,
                        --wc.whsHeaderID,
                        SUM(c.[Chargeable Weight]) AS LeftOffWt
                FROM    dbo.whsConsignment wc
                        INNER JOIN whsHeader wh on wh.WhsHeaderID = wc.whsHeaderID
                        INNER JOIN dbo.cgtConsignment c ON c.[Consignment Reference] = wc.ConsignmentReference
                        INNER JOIN dbo.genAddress ga ON ga.AddressID = c.Consignor
                        INNER JOIN dbo.lstPostcode lp ON lp.Postcode = ga.PostcodePrefix
                        INNER JOIN dbo.lstPostcodeRegion lpr ON lpr.RegionID = lp.RegionID
                                                          AND c.Cancelled = 0
                                                          --AND wc.ConsignmentReference LIKE '4%'
                                                          and lpr.RegionID = 1
                WHERE   StatusCode IN ( 'NL' ) and wh.ArrDepDate between @StartDate and @EndDate and lpr.regionID = @RegionID
                GROUP BY lpr.RegionID
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download