Select with Condition

[codrutza]

Hi
I have a script (SP) for reports. In the sales, I have to subtract TH.GoodsTotal for the jobs that have TD.Narrative LIKE 'VAT'. My issue is with the select bellow, appear only the jobs which have TD.Narrative LIKE 'VAT', and I need the sales for all the jobs (TD.Narrative has other values).
I don’t know what to do, maybe you can help.

SELECT
Analysis.JobNumber,
Analysis.Sales + Analysis.Charges - TH.GoodsTotal as Sales,
FJ.ReportingDate,
…
C.AccountNumber,
C.Name,

substring(TH.Or,2,7) JobNoTr,
TD.Narrative

FROM
(((Analysis WITH (READUNCOMMITTED)
INNER JOIN FJ WITH (READUNCOMMITTED)
ON Analysis.JobNumber=FJ.Number)

INNER JOIN C WITH (READUNCOMMITTED)
ON C.AccountNumber=Analysis.CustomerAccountNumber)

INNER JOIN TH WITH (READUNCOMMITTED)
ON Analysis.JobNumber=substring(TH.Or,2,7)
AND substring(TH.Or,1,1)='J')

INNER JOIN TD WITH (READUNCOMMITTED)
ON TH.Or=TD.Or



WHERE
YEAR(F.ReportingDate)= 2015
and MONTH(F.ReportingDate)= 12
….

AND TD.Narrative LIKE 'VAT'


Should be:

….

619 210 2015-12-15 00:00:00 41 XXX 619 ATE - (I don’t need to subtract GoodsTotal)
679 4429 2015-12-04 00:00:00 19 YYY 679 VAT - (I subtracted GoodsTotal)
738 436 2015-12-05 00:00:00 19 YYY 738 ATI - (I don’t need to subtract GoodsTotal)
618 4253 2015-12-15 00:00:00 41 XXX 618 VAT - (I subtracted GoodsTotal)
502 4309 2015-12-05 00:00:00 19 XXX 502 VAT - (I subtracted GoodsTotal)
299 1549 2015-12-12 00:00:00 42 ZZZ 299 VAT - (I subtracted GoodsTotal)

……..

 

[Olaf Doschke]

Nobody can say for sure, if just removing this condition will solve it, surely other wise you don't get any other than VAT. This LIKE comparison isn't even using any joker characters, so could also be a simple = comparison, but that doesn't matter much.

Simply remove AND TD.Narrative LIKE 'VAT' and see what you get, but notice, that Analysis.Sales + Analysis.Charges - TH.GoodsTotal as Sales, will then subtract TH.GoodsTotal also from non VAT records.

Bye, Olaf.

 

[philhege]

What Olaf said, plus

Evaluate Narrative for each record:

Analysis.Sales + Analysis.Charges - CASE when TD.Narrative='VAT' then TH.GoodsTotal else 0 END as Sales,

You'll get all records, and it won't subtract GoodsTotal for non-VAT records.

-----------
With business clients like mine, you'd be better off herding cats.

 

[codrutza]

It works, thank you both. Gives me many records with non vat in the report, but I do a running total instead of sum.
I put = before, but I had to replace with LIKE, because I got the error: The data types text and varchar are incompatible in the equal to operator. The field is Narrative(text, not null). Can be converted maybe in varchar?

 

[gmmastros]

The 90's called and want their text data type back! (joking)

The text data type was always difficult to work with. Instead, you should use varchar(max).

I would recommend that you change the data type of the column to varchar(max). You should not just blindly do this, but I do encourage you to look in to it. If you have SQL code that handles strings, then you are likely using the following functions: TextPtr, UpdateText, and WriteText.

You can search your code like this:

select  *
From    sys.procedures 
where   object_definition(object_id) like '%TextPrt%'
        Or object_definition(object_id) like '%UpdateText%'
        Or object_definition(object_id) like '%WriteText%'

The above query will search through code in the database. If you have front end code with these functions, the query will not see it.

Basically it's this... You should change all your text columns to varchar(max) columns. It will make a lot of things easier.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom