July 27, 2015 at 7:16 am
Hello comunity
I need on my query to return all documents names even on this date the value of TotalDay is equal to ZERO, this is my code.
SELECT CAST(YEAR(ft.fdata) AS VARCHAR) + CAST(MONTH(ft.fdata) AS VARCHAR) AS Mes,
XTD.Stores, Isnull(SUM(ft.ettiliq),0) AS [TotalDay]
FROM (
SELECT nmdoc,ndoc, (
CASE
WHEN td.ndoc IN (1, 26) THEN 'Sede'
ELSE CASE
WHEN td.ndoc IN (30, 39) THEN 'Store Beira'
ELSE CASE
WHEN td.ndoc IN (83, 84) THEN
'Store Maputo'
ELSE CASE
WHEN td.ndoc IN (31, 38) THEN
'Store Nacala'
ELSE CASE
WHEN td.ndoc IN (54, 55) THEN
'Store Nampula'
ELSE CASE
WHEN
td.ndoc IN (73, 74) THEN
'Store Quelimana'
ELSE
''
END
END
END
END
END
END
) AS Stores
FROM td WITH (NOLOCK)
) AS XTD
LEFT JOIN ft WITH (NOLOCK)
ON XTD.ndoc = FT.ndoc AND XTD.nmdoc = FT.nmdoc
WHERE (ft.fdata = '20150701')
AND (
XTD.ndoc IN (1, 26, 30, 39, 83, 84, 31, 38, 54, 55, 73, 74)
)
GROUP BY
XTD.nmdoc, XTD.Stores,CAST(YEAR(ft.fdata) AS VARCHAR) + CAST(MONTH(ft.fdata) AS VARCHAR)
Someone could give me some help.
Best regards
Luis
July 27, 2015 at 8:08 am
One thing you don't need is all the additional CASE constructions. Also, your CASTs are to VARCHAR, without specifying the length, and that will cause you grief because it's going to cast them to varchar(1). Try the following instead (WITH (NOLOCK) hints removed - they are almost always a bad idea, and if anyone told you to use them all the time, they don''t know what they are talking about):
SELECT CAST(YEAR(ft.fdata) AS varchar(4)) + CAST(MONTH(ft.fdata) AS varchar(2)) AS Mes,
XTD.Stores, ISNULL(SUM(ft.ettiliq),0) AS [TotalDay]
FROM (
SELECT td.ndoc, td.nmdoc,
CASE
WHEN td.ndoc IN (1, 26) THEN 'Sede'
WHEN td.ndoc IN (30, 39) THEN 'Store Beira'
WHEN td.ndoc IN (83, 84) THEN 'Store Maputo'
WHEN td.ndoc IN (31, 38) THEN 'Store Nacala'
WHEN td.ndoc IN (54, 55) THEN 'Store Nampula'
WHEN td.ndoc IN (73, 74) THEN 'Store Quelimana'
ELSE ''
END AS Stores
FROM td
WHERE td.ndoc IN (1, 26, 30, 39, 83, 84, 31, 38, 54, 55, 73, 74)
) AS XTD
LEFT JOIN ft
ON XTD.ndoc = FT.ndoc
AND XTD.nmdoc = FT.nmdoc
WHERE ft.fdata = '20150701'
GROUP BY XTD.nmdoc, XTD.Stores, CAST(YEAR(ft.fdata) AS varchar(4)) + CAST(MONTH(ft.fdata) AS varchar(2))
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 8:18 am
Hello Steve
First , thanks for all your precious advises, but running the query the result don´t return any records.
Maybe i´am not explain correctly what i need, because on this date i don´t have any invoice documents, but i need to show in the same all documents with the sum of Value = to zero.
I hope you could help me.
Best regards,
Luis
July 27, 2015 at 8:27 am
luissantos (7/27/2015)
Hello SteveFirst , thanks for all your precious advises, but running the query the result don´t return any records.
Maybe i´am not explain correctly what i need, because on this date i don´t have any invoice documents, but i need to show in the same all documents with the sum of Value = to zero.
I hope you could help me.
Best regards,
Luis
If you want to return records, then there have to be records that meet your criteria specified in the WHERE clauses, and that match the JOIN conditions. Without some sample data and expected results, it's almost impossible to do much more than guess as to where the problem lies. You have to determine what criteria will allow records to pass through. My first question is why restrict the data to a particular value for ft.fdata?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 8:35 am
Hello Steve
I solve the problem changing your Query:
[Code="sql"]
SELECT td.ndoc, td.nmdoc,
CASE
WHEN td.ndoc IN (1, 26) THEN 'Sede'
WHEN td.ndoc IN (30, 39) THEN 'Store Beira'
WHEN td.ndoc IN (83, 84) THEN 'Store Maputo'
WHEN td.ndoc IN (31, 38) THEN 'Store Nacala'
WHEN td.ndoc IN (54, 55) THEN 'Store Nampula'
WHEN td.ndoc IN (73, 74) THEN 'Store Quelimana'
ELSE ''
END AS Stores, Isnull(SUM(XFT.TotalDay),0) [TotDay]
FROM
(
Select fdata,ndoc, CAST(YEAR(ft.fdata) AS varchar(4)) + CAST(MONTH(ft.fdata) AS varchar(2)) AS Mes,
ISNULL(SUM(ft.ettiliq),0) AS [TotalDay]
FROM FT
WHERE (ft.fdata = CONVERT(DATETIME, '2015-07-01 00:00:00', 102))
GROUP BY fdata,ndoc, CAST(YEAR(ft.fdata) AS varchar(4)) + CAST(MONTH(ft.fdata) AS varchar(2))
) XFT
RIGHT JOIN TD ON XFT.ndoc = TD.NDOC
WHERE td.ndoc IN (1, 26, 30, 39, 83, 84, 31, 38, 54, 55, 73, 74)
GROUP BY td.ndoc,td.nmdoc
[/code]
Best regards and thanks again,
Luis
July 27, 2015 at 8:41 am
Glad I could help. Sometimes, just being able to bounce your ideas off of someone else can get the juices flowing, so to speak, and you can solve your own problem faster because you took a new path toward a solution, and the creativity involved in doing so inspires the solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2015 at 8:49 am
Further analysis suggests the problem was here:
WHERE ft.fdata = '20150701'
and was solved by:
WHERE (ft.fdata = CONVERT(DATETIME, '2015-07-01 00:00:00', 102))
And it might have been sufficient to use:
WHERE ft.fdata = '2015-07-01'
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply