No filter based on model

Question

0.00/5 (No votes)

See more:

The table of my car and model is as follows, but filtering based on the car model is not possible

Models Table:

ID
CarModels
Active
FromYear
ToYear

What I have tried:

sp in sql:

SQL

Create [dbo].[ProductionShippingStatisticsReport]
    @CarID		INT = NULL,
    @CarModel   INT = NULL,
    @FromDate   DATETIME,
    @ToDate     DATETIME
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StartDate  DATETIME = DATEADD(d, DATEDIFF(d, 0, @FromDate), 0)
    DECLARE @EndDate    DATETIME = DATEADD(s, -1, DATEADD(d, 1, @ToDate))

IF @CarID IS NOT NULL AND @CarModel IS NOT NULL
    SELECT
        CarID,
        CarTitle,
        CarModels,
        RepDate,
        SUM(LastDayQty) AS LastDayQty,
        SUM(TotalShipmentStatistics) AS TotalShipmentStatistics
    FROM
        dbo.ProductionShippingStatistics INNER JOIN
        dbo.Cars ON dbo.ProductionShippingStatistics.CarID = dbo.Cars.ID INNER JOIN
        dbo.CarModel ON dbo.ProductionShippingStatistics.ModelID = dbo.CarModel.ID
    WHERE
        (CarID = @CarID) AND (CarModels = @CarModel) AND (RepDate BETWEEN @StartDate AND @EndDate)
    GROUP BY CarID, CarTitle, CarModels, RepDate
ELSE IF @CarID IS NOT NULL AND @CarModel IS NULL
    SELECT
        CarID,
        CarTitle,
        CarModels,
        RepDate,
        SUM(LastDayQty) AS LastDayQty,
        SUM(TotalShipmentStatistics) AS TotalShipmentStatistics
    FROM
        dbo.ProductionShippingStatistics INNER JOIN
        dbo.Cars ON dbo.ProductionShippingStatistics.CarID = dbo.Cars.ID INNER JOIN
        dbo.CarModel ON dbo.ProductionShippingStatistics.ModelID = dbo.CarModel.ID
    WHERE
        (CarID = @CarID) AND (RepDate BETWEEN @StartDate AND @EndDate)
    GROUP BY CarID, CarTitle, CarModels, RepDate
ELSE IF @CarID IS NULL AND @CarModel IS NOT NULL
    SELECT
        CarID,
        CarTitle,
        CarModels,
        RepDate,
        SUM(LastDayQty) AS LastDayQty,
        SUM(TotalShipmentStatistics) AS TotalShipmentStatistics
    FROM
        dbo.ProductionShippingStatistics INNER JOIN
        dbo.Cars ON dbo.ProductionShippingStatistics.CarID = dbo.Cars.ID INNER JOIN
        dbo.CarModel ON dbo.ProductionShippingStatistics.ModelID = dbo.CarModel.ID
    WHERE
        (CarModels = @CarModel) AND (RepDate BETWEEN @StartDate AND @EndDate)
    GROUP BY CarID, CarTitle, CarModels, RepDate
ELSE 
    SELECT
        CarID,
        CarTitle,
        CarModels,
        RepDate,
        SUM(LastDayQty) AS LastDayQty,
        SUM(TotalShipmentStatistics) AS TotalShipmentStatistics
    FROM
        dbo.ProductionShippingStatistics INNER JOIN
        dbo.Cars ON dbo.ProductionShippingStatistics.CarID = dbo.Cars.ID INNER JOIN
        dbo.CarModel ON dbo.ProductionShippingStatistics.ModelID = dbo.CarModel.ID
    WHERE
         (RepDate BETWEEN @StartDate AND @EndDate)
    GROUP BY CarID, CarTitle, CarModels, RepDate
END

Posted 5-Dec-20 23:51pm

Member 14615938

Updated 7-Dec-20 3:11am

Richard Deeming

v2

Add a Solution

Comments

CHill60 7-Dec-20 5:14am

Well, filtering on Car Model is possible, but from name you have used [CarModels] suggests that you have more than one car model in that column. How about supplying us with some sample data (for ALL of the tables in your SQL) and your expected results

Member 14615938 7-Dec-20 7:57am

Before adding the following two fields, the filtering was easily done based on the model
FromYear
ToYear

These two fields were added to the car model table

CHill60 7-Dec-20 8:44am

If this comment was meant to be a reply to me then that is not sample data. However, if the query worked before you introduced those columns then it can only mean that you do not have any data that falls between those dates for the model you are filtering on

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-12-07T03:11:00

You can greatly simplify that SQL code which may help you get rid of the problem.

Instead of convoluted IF statements just handle the possibility of one or more of the parameters being NULL

Look at this example table:

SQL

create table #demo (CarID INT, Model varchar(10), repdate datetime)
insert into #demo(CarID, Model, repdate) values
(1, 'Test1', '2020-11-10 22:10:00.000'),
(1, 'Test2', '2020-11-11 22:10:00.000'),
(1, 'Test1', '2020-11-12 22:10:00.000'),
(1, 'Test2', '2020-11-13 22:10:00.000'),
(2, 'Test1', '2020-11-14 22:10:00.000')

And here are my test conditions with expected results

SQL

-- Test 1 CarID not null, Model not null 
set @CarID = 1
set @CarModel = 'Test2'
-- Expect 2 rows 1, Test2,'2020-11-11 22:10:00.000' and 1, Test2, '2020-11-13 22:10:00.000'

-- Test 2 CarID not null, Model null 
set @CarID = 1
set @CarModel = NULL
-- Expect 4 rows (all CarID = 1)

-- Test 3 CarID is null, Model Not Null
set @CarID = NULL
set @CarModel = 'Test1'
-- Expect 3 rows (Model = 'Test1')

-- Test 4 carID is null, Model is null
-- expect all 5 rows entered
set @CarID = NULL
set @CarModel = NULL

This query will handle all 4 scenarios

SQL

select * 
from #demo
where CarId = ISNULL(@CarID, Carid)
and Model = ISNULL(@CarModel, Model)
and repdate between @StartDate and @EndDate

It works because by setting the NULL parameter to the value on each row that we are testing against we are effectively saying WHERE 1=1 which is always true