SQL/LINQ vs. Data to feed

Asked

Viewed 52 times

0

I’m trying to generate a query in VS2015 with Linq (here, I exemplified in SQL, to speed up my tests right in the database) that returns the data grouped and counted from 6 months ago, it happens that in some of the months there is no data so it does not appear in the result.

What I get:

total | data 2 06/2017 4 08/2017 18 11/2017

As expected:

total | data 2 06/2017 0 07/2017 4 08/2017 0 09/2017 0 10/2017 18 11/2017

My query

    SELECT COUNT(Curso.ID_VENDA) as total, FORMAT(MAX(Venda.DATA),'MM/yyyy') AS data 
    FROM Curso 
    INNER JOIN Venda ON Curso.ID_VENDA = Venda.ID_VENDA
    INNER JOIN Produto ON Curso.ID_PRODUTO = Produto.ID_PRODUTO
    WHERE Produto.ID_CATEGORIA = 8
    AND Venda.DATA > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1) 
    GROUP BY (YEAR(Venda.DATA) * 100) + MONTH(Venda.DATA)
    ORDER BY MAX(Venda.DATA) ASC

Has anyone experienced a similar case?

c# sql sql-server linq

2 answers

0

It probably does not return the zeroed values because you are trying to pull courses that do not exist (because the Count is reset) use RIGHT JOIN and will probably work.

SELECT COUNT(Curso.ID_VENDA) as total, FORMAT(MAX(Venda.DATA),'MM/yyyy') AS data 
FROM Curso 

RIGHT JOIN Produto ON Curso.ID_PRODUTO = Produto.ID_PRODUTO
RIGHT JOIN Venda ON Curso.ID_VENDA = Venda.ID_VENDA

WHERE Produto.ID_CATEGORIA = 8
AND Venda.DATA > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1) 
GROUP BY (YEAR(Venda.DATA) * 100) + MONTH(Venda.DATA)
ORDER BY MAX(Venda.DATA) ASC

  • Hello, thank you, but had tried it. Produces the same initial result.

0


Create a temporary table with every month to join. Follow the example:

CREATE TABLE #TB_DATAS(Data   varchar(10))

INSERT INTO #TB_DATAS (Data) VALUES ('06/2017')
INSERT INTO #TB_DATAS (Data) VALUES ('07/2017')
INSERT INTO #TB_DATAS (Data) VALUES ('08/2017')
INSERT INTO #TB_DATAS (Data) VALUES ('09/2017')
INSERT INTO #TB_DATAS (Data) VALUES ('10/2017')
INSERT INTO #TB_DATAS (Data) VALUES ('11/2017')

And include this table in JOIN

    SELECT COUNT(Curso.ID_VENDA) as total,  FORMAT(MAX(Venda.DATA), 'MM/yyyy') AS data , #TB_DATAS.Data as data2
FROM Curso 
INNER JOIN Venda ON Curso.ID_VENDA = Venda.ID_VENDA
INNER JOIN Produto ON Curso.ID_PRODUTO = Produto.ID_PRODUTO
RIGHT JOIN #TB_DATAS ON FORMAT(Venda.DATA,'MM/yyyy') = #TB_DATAS.Data
WHERE Produto.ID_CATEGORIA = 8
AND ((Venda.DATA > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1) ) OR (Venda.DATA  is NULL))
GROUP BY (YEAR(Venda.DATA) * 100) + MONTH(Venda.DATA), #TB_Datas.Data
ORDER BY MAX(Venda.DATA) ASC

  • Hmm, I get the idea! However, when trying to apply the proposed solution, I received the following error: Msg 1015, Level 15, State 1, Line 5 An Aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an Outer Reference.

  • You’re right I’ll adjust the query, I hadn’t noticed the group by, but that’s the idea

  • I made an adjustment removing Max from Join, see if it solves so

  • I tested, but unfortunately, it still produces the same initial result.

  • I set the name of the temporary table in Join that it was wrong

  • I made other adjustments and tested, it worked here

  • Alexandre, thanks for the help. In fact I could not get the same result as your test. However, his line of reasoning gave me the idea of solving differently by C#. I create a list of months inside C#, run the query and cross the data to leave in the format the chart accepts.

Show 2 more comments

Browser other questions tagged c# sql sql-server linq

You are not signed in. Login or sign up in order to post.