CODEWITHSUNDEEP
sqlsql-server
Jo Jo
Jo Jo

Reputation: 73

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

Upvotes: 0

Views: 77

Answers (2)

MrRAW
MrRAW

Reputation: 38

– Sean Lange is right, you cant juse more then one value.. my mistake Go on Deans ide 

   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%'

                WHERE   StatusCode IN ( 'NL' ) and wh.ArrDepDate between @StartDate and @EndDate 
                GROUP BY lpr.RegionID

Upvotes: 0

Dean Savović
Dean Savović

Reputation: 739

Just remove the lpr.RegionID = 1. Here is a sample: 

 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%'
                WHERE   StatusCode IN ( 'NL' ) and wh.ArrDepDate between @StartDate and @EndDate
                GROUP BY lpr.RegionID

Upvotes: 1

Related Questions