SQL / LINQ vs. Data to feed graphic

Question

SQL / LINQ vs. Data to feed graphic

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I am trying to generate a query in VS2015 with Linq (here, I exemplified in SQL, to speed up my tests directly in the bank) that returns the data grouped and counted from 6 months ago, it happens that in some of the months there is no data soon 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 ever had a similar case?

sql sql-server c# linq

asked by anonymous 05.12.2017 / 13:16

2 answers

0

Probably does not return the zeroed values because you are trying to pull courses that do not exist (because count is zeroed) 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

05.12.2017 / 13:31

How to get the Text of a Div Class and Place inside a Label? Error creating a table in mysql

score 0 · Accepted Answer

Create a temporary table with all months to JOIN. Here's 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