Trouble with SELECT statement for calculating total from a ranges

Question

Apologies if my title is misleading in anyway. Had trouble trying to come up with an accurate descriptive title for what I am having issues with.

I have some data I need to pull out from my SQL Server 2008 R2. I am just looking to output this data to a GridView using C# ASP.Net.

I am just trying to see if there is a way I can just utilise a single query to pull out all of the data I need. I will try my best to explain my setup and show you what I mean:

I have 2 tables and the example structure and data is below.

TABLE: Rates

int  nvarchar         nvarchar         decimal nvarchar                  nvarchar                    nvarchar
ID   Location_Code_A  Location_Code_B  Rate    EffectiveRateDate_YYMMDD  TerminationRateDate_YYMMDD  Classificaton
54   123456           456789           400     110801                    999999                      C
55   123456           456789           885.3   110801                    999999                      D
56   123456           456789           901.75  110801                    999999                      T
57   123456           456789           407.5   120501                    120630                      C
58   123456           456789           850     120501                    120630                      D
59   123456           456789           898     120501                    120630                      T
60   123456           987654           325     110801                    999999                      C
61   123456           987654           345.5   120501                    120630                      C

TABLE: Tracking

int nvarchar           nvarchar         nvarchar         nvarchar              nvarchar
ID  TrackCode          Location_Code_A  Location_Code_B  DepartureDate_YYMMDD  Classification
7   124FWDSE48W621543  123456           456789           111025                C
8   124FWDSE48W621544  123456           456789           111025                C
9   124FWDSE48W621545  123456           456789           111025                C
10  124FWDSE48W621546  123456           456789           111025                D
11  124FWDSE48W621547  123456           456789           111025                D
12  124FWDSE48W621548  123456           456789           111028                T
13  124FWDSE48W621549  123456           456789           111025                C
14  124FWDSE48W621550  123456           456789           111125                C
15  124FWDSE48W621551  123456           456789           120525                D
16  124FWDSE48W621552  123456           456789           120526                C
17  124FWDSE48W621553  123456           987654           110927                C
18  124FWDSE48W621554  123456           987654           111027                C
19  124FWDSE48W621555  123456           987654           111027                C
20  124FWDSE48W621556  123456           987654           120514                C
21  124FWDSE48W621557  123456           987654           120515                C
22  124FWDSE48W621558  123456           987654           120515                C

What I want to achieve is a grouped query that pulls out a count of the Tracking.TrackCode, by the Tracking.Location_Code(s), which is multiplied by the Rates.Rate to give a total and where Tracking.DepartureDate_YYMMDD falls between the Effective/Termination dates of rates. Classification does play a role in making sure to use the correct rate but not in the actual final GridView.

So my GridView result would look something like:

Location_A  Location_B  TotalTrackCodes  RateTotal
123456      456789      10               5522.35
123456      987654      6                2011.5

You can see that my issue occurs when a rate has a departureDate that falls in between 2 different records for their Rates.EffectiveRateDate & Rates.TerminationRateDate. When this happens I want to take the most recent rate information, basically the one with the more recent Rates.EffectiveRateDate (the highest EffectiveRateDate if converted to an int).

As the EffectiveRateDates are not date formats I just convert them to int in the query and use them as a sort of range instead for doing the checks etc. Seems to work fine that way.

Is it at all possible to do a single query that I can pass as a DataSet to my GridView to give me the correct results?

As a note I didn't construct these tables/formats etc. They are already populated by a 3rd party and I am just looking to query these tables so can't adjust the tables themselves.

Many thanks for any help.

To maybe save people time and aid with any help see below for a SQL dump of each table and their data contents:

/****** Object:  Table [dbo].[Tracking]    Script Date: 08/21/2012 14:03:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tracking](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TrackCode] [nvarchar](50) NULL,
    [Location_Code_A] [nvarchar](50) NULL,
    [Location_Code_B] [nvarchar](50) NULL,
    [DepartureDate_YYMMDD] [nvarchar](50) NULL,
    [Classification] [nvarchar](50) NULL,
 CONSTRAINT [PK_Tracking] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Tracking] ON
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (1, N'124FWDSE48W621543', N'123456', N'456789', N'111025', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (2, N'124FWDSE48W621544', N'123456', N'456789', N'111025', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (3, N'124FWDSE48W621545', N'123456', N'456789', N'111025', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (4, N'124FWDSE48W621546', N'123456', N'456789', N'111025', N'D')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (5, N'124FWDSE48W621547', N'123456', N'456789', N'111025', N'D')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (6, N'124FWDSE48W621548', N'123456', N'456789', N'111028', N'T')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (7, N'124FWDSE48W621549', N'123456', N'456789', N'111025', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (8, N'124FWDSE48W621550', N'123456', N'456789', N'111025', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (9, N'124FWDSE48W621551', N'123456', N'456789', N'120525', N'D')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (10, N'124FWDSE48W621552', N'123456', N'456789', N'120526', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (11, N'124FWDSE48W621553', N'123456', N'987654', N'110927', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (12, N'124FWDSE48W621554', N'123456', N'987654', N'111027', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (13, N'124FWDSE48W621555', N'123456', N'987654', N'111027', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (14, N'124FWDSE48W621556', N'123456', N'987654', N'120514', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (15, N'124FWDSE48W621557', N'123456', N'987654', N'120515', N'C')
INSERT [dbo].[Tracking] ([ID], [TrackCode], [Location_Code_A], [Location_Code_B], [DepartureDate_YYMMDD], [Classification]) VALUES (16, N'124FWDSE48W621558', N'123456', N'987654', N'120515', N'C')
SET IDENTITY_INSERT [dbo].[Tracking] OFF
/****** Object:  Table [dbo].[Rates]    Script Date: 08/21/2012 14:03:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rates](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location_Code_A] [nvarchar](50) NULL,
    [Location_Code_B] [nvarchar](50) NULL,
    [Rate] [nvarchar](50) NULL,
    [EffectiveRateDate_YYMMDD] [nvarchar](50) NULL,
    [TerminationRateDate_YYMMDD] [nvarchar](50) NULL,
    [Classification] [nvarchar](50) NULL,
 CONSTRAINT [PK_Rates] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Rates] ON
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (1, N'123456', N'456789', N'400', N'110801', N'999999', N'C')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (2, N'123456', N'456789', N'885.3', N'110801', N'999999', N'D')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (3, N'123456', N'456789', N'901.75', N'110801', N'999999', N'T')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (4, N'123456', N'456789', N'407.5', N'120501', N'120630', N'C')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (5, N'123456', N'456789', N'850', N'120501', N'120630', N'D')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (6, N'123456', N'456789', N'898', N'120501', N'120630', N'T')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (7, N'123456', N'987654', N'325', N'110801', N'999999', N'C')
INSERT [dbo].[Rates] ([ID], [Location_Code_A], [Location_Code_B], [Rate], [EffectiveRateDate_YYMMDD], [TerminationRateDate_YYMMDD], [Classification]) VALUES (8, N'123456', N'987654', N'345.5', N'120501', N'120630', N'C')
SET IDENTITY_INSERT [dbo].[Rates] OFF

podiluska · Accepted Answer

select
    Location_Code_A,
    Location_Code_B,
    COUNT(*),
    SUM(rate)
from
(    
    select 
    ROW_NUMBER() over (partition by tracking.id order by rates.effectiveratedate_yymmdd desc) rn,
    tracking.*, 
    convert(decimal(9,5),rates.Rate) rate
from 
    tracking
        inner join  
    rates
        on tracking.Location_Code_A = rates.Location_Code_A
        and tracking.Location_Code_B = rates.Location_Code_B
        and tracking.Classification = rates.Classification
        and tracking.DepartureDate_YYMMDD>=rates.EffectiveRateDate_YYMMDD
) v
where rn=1  
    group by Location_Code_A, Location_Code_B
    order by Location_Code_A, Location_Code_B

produces

123456   456789 10  5929.85000
123456   987654 6   2011.50000

Trouble with SELECT statement for calculating total from a ranges

Answers (1)

Related Questions