Hello. I need a computed field im my view

Question

Post reply

Hello. I need a computed field im my view

ograndioso

SSC Veteran

Points: 229

July 21, 2022 at 8:48 am

Go to Answer

#4066353

Good Morning

I need to subtract :

a.[Vendas Ultimo Ano]-2.STKActual and

b.[Vendas Ultimo Semestre] -Artigo_2.STKActual in this view.

cn you help me please?

SELECT a.artigo,
       a.descricao,
       Artigo_2.unidadebase,
       b.[vendas ultimo semestre],
       a.[vendas ultimo ano],
       Artigo_2.stkactual
FROM   (SELECT DISTINCT dbo.linhasdoc.artigo,
                        dbo.artigo.descricao,
                        dbo.artigo.familia,
                        Sum(dbo.linhasdoc.quantidade) AS [Vendas Ultimo Ano]
        FROM   dbo.linhasdoc
               INNER JOIN dbo.artigo
                       ON dbo.linhasdoc.artigo = dbo.artigo.artigo
               INNER JOIN dbo.cabecdoc
                       ON dbo.linhasdoc.idcabecdoc = dbo.cabecdoc.id
        WHERE  ( dbo.linhasdoc.datasaida >= Dateadd(day, -365, Getdate()) )
               AND ( dbo.cabecdoc.tipodoc <> 'fp' )
               AND ( dbo.cabecdoc.tipodoc <> 'gte' )
               AND ( dbo.cabecdoc.tipodoc <> 'con' )
        GROUP  BY dbo.linhasdoc.artigo,
                  dbo.artigo.descricao,
                  dbo.artigo.familia) AS a
       INNER JOIN dbo.artigo AS Artigo_2
               ON a.artigo = Artigo_2.artigo
       LEFT OUTER JOIN
       (SELECT DISTINCT LinhasDoc_1.artigo,
                                        Artigo_1.descricao,
                                        Artigo_1.familia,
                                        Sum(LinhasDoc_1.quantidade) AS
                        [Vendas Ultimo Semestre]
                        FROM   dbo.linhasdoc AS LinhasDoc_1
                               INNER JOIN dbo.artigo AS Artigo_1
                                       ON LinhasDoc_1.artigo = Artigo_1.artigo
                               INNER JOIN dbo.cabecdoc AS CabecDoc_1
                                       ON LinhasDoc_1.idcabecdoc = CabecDoc_1.id
                        WHERE  ( LinhasDoc_1.datasaida BETWEEN
                                 Dateadd(day, -180, Getdate()) AND
                                         Dateadd(day, -0, Getdate()) )
                               AND ( CabecDoc_1.tipodoc <> 'fp' )
                               AND ( CabecDoc_1.tipodoc <> 'gte' )
                               AND ( CabecDoc_1.tipodoc <> 'con' )
                        GROUP  BY LinhasDoc_1.artigo,
                                  Artigo_1.descricao,
                                  Artigo_1.familia) AS b
                    ON a.artigo = b.artigo
                       AND a.descricao = b.descricao
                       AND a.familia = b.familia

ScottPletcher

SSC Guru

Points: 101118
More actions
July 21, 2022 at 5:07 pm
Answer
#4066562
Could just be?:
```
SELECT a.artigo,
       a.descricao,
       Artigo_2.unidadebase,
       b.[vendas ultimo semestre],
       a.[vendas ultimo ano],
       Artigo_2.stkactual,
       a.[Vendas Ultimo Ano] - Artigo_2.STKActual AS new_column_1, --<<--<<--
       b.[Vendas Ultimo Semestre] - Artigo_2.STKActual AS new_column_2 --<<--<<--
...rest_of_query_same_as_before...
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

So what's wrong with putting your subtraction expression in your outer query? Subtraction in T-SQL works like any other language: {Expression} - {Expression}.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Collins SSCrazy Points: 2169 More actions · Answer 2

There are a number of issues with the code provided imo. Minor issues are DISTINCT is not necessary when used in conjunction with GROUP BY and inconsistent or absent table and column aliasing (!). The major issue afaik is the 2 subqueries are nearly identical but for the date range. Instead of aggregating twice and then joining the results it seems possible you could conditionally aggregate (aka "pivot") once. No data so there could be blunders. Maybe something like this

select l.artigo, a.descricao, a.familia,
       max(a.unidadebase) AS unidadebase,
       sum(l.quantidade) AS [Vendas Ultimo Ano],
       sum(iif(l.datasaida >= dateadd(day, -180, getdate()), l.quantidade, 0)) AS [Vendas Ultimo Semestre],
       max(a.stkactual) AS stkactual,
       sum(l.quantidade)-max(a.stkactual) [Ano diff],
       sum(iif(l.datasaida >= dateadd(day, -180, getdate()), l.quantidade, 0))-max(a.stkactual) [Semestre diff]
from dbo.linhasdoc l
     join dbo.artigo a ON l.artigo = a.artigo
     join dbo.cabecdoc ON l.idcabecdoc = c.id
where l.datasaida >= dateadd(day, -365, getdate())
      and c.tipodoc not in ('fp', 'gte', 'con' )
group BY l.artigo, a.descricao, a.familia;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ograndioso SSC Veteran Points: 229 More actions · Answer 3

Bro ,Thanks a lot for the help. But unfortunately it doesn't work. I need 3 database tables:

-dbo.linhasdoc where are the sales amounts by product and the sales dates by product.

-dbo.article

where is this product code; description ; date of last sale and current stock

-dbo.cabecdoc

where are the types of documents, in this case I need only 4 documents: FR-invoice/receipt; FA-invoice; NC-Credit Note and ND-Debit Note.

This view is to assist in the purchase of merchandise, the one I did works but doesn't do what I need.

If you can help me I would be very grateful. Thank you very much again

ograndioso SSC Veteran Points: 229 More actions · Answer 4

Thank you very, very much for your patience and attention you had with me.

Your tip was worth 50 tips that are on the net.

And unlike many, you understand that, in addition to helping, you are also teaching.

We are together bro.

thank you very much again