Click here to Skip to main content
15,435,848 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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


What I have tried:

I'm starting on sql server so i try to make a computed column but < do not accept
Posted
Updated 20-Jul-22 23:24pm
v4
Comments
0x01AA 20-Jul-22 10:53am    
First of all I sugest you to make your SQL readable. I.e. with this:Instant SQL Formatter[^]
miguel santos 2021 20-Jul-22 11:54am    
thank' for the tip bro
0x01AA 20-Jul-22 13:02pm     CRLF
Btw., I added the code pre tags again to make the statement readable. I assume you like to have these differences? (looks like there is a typo in the title of the question) a.[vendas ultimo ano] and artigo_2.stkactual b.[vendas ultimo semestre] and artigo_2.stkactual If that is correct, how you like to have the difference? In years, days, months, ...? Have a look here: DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^] and here sql - DateDiff to output hours and minutes - Stack Overflow[^]
miguel santos 2021 20-Jul-22 15:17pm     CRLF
Thanks again bro, sorry for my bad SQL and English . a.[vendas ultimo ano] its the pasta usar sales by produts. . b.[vendas ultimo semestre] its the last 6 months sales and gain artigo_2.stkactual its the actual stock by produts. Thaís os for help when i have to perder some produts. Thanks again
miguel santos 2021 20-Jul-22 15:19pm     CRLF
Thanks again bro, sorry for my bad SQL and English . a.[vendas ultimo ano] its the last year sales by produts. . b.[vendas ultimo semestre] its the last 6 months sales and gain artigo_2.stkactual its the actual stock by produts. Thaís os for help when i have to perder some produts. Thanks again

1 solution

There is no reason why you cannot have a calculated field in a view, as far as I know. Take a look at SQL CREATE VIEW, REPLACE VIEW, DROP VIEW Statements[^]. But the main thing you need to do is to build the view from the ground up, rather than (as you have above) from the roof down. Start with the basic field items that you want, and make sure that the view reflects what you are trying to display. Then add your conditional expressions one at a time, and test each one as you add it. Repeat this process for all other fields and conditions until you have the complete view.
 
Share this answer
 
Comments
0x01AA 21-Jul-22 18:45pm     CRLF
Only some notes: I think OP names a simple SELECT statement as VIEW, minor thing. OP's target is to get a difference of two DATETIME fields which is not that unproblematic, because a difference of two DATETIME fields is more a TIMESPAN. Let's see...
Richard MacCutchan 22-Jul-22 3:11am    
Indeed.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900