SAP® Business One: Sales Analysis UNION Query with Date Range - Part#3
Ғылым және технология
//--Ways to support me so i make more videos (some free)
supportme.battleshipcobra.com/
//--Video Description
SAP® Business One sales query including invoices and credit memos in the same query. Instructions on how to use a UNION ALL function. We also mix in my trick for doing date ranges properly.
//-Final SQL Query
Download TXT file here: goo.gl/XUvdrW
//-QOTD - Quote of the Day
"Nothing in this world can take the place of persistence. Talent will not; nothing is more common than unsuccessful men with talent. Genius will not; unrewarded genius is almost a proverb. Education will not; the world is full of educated derelicts. Persistence and determination alone are omnipotent. The slogan Press On! has solved and always will solve the problems of the human race." ~ Calvin Coolidge
Пікірлер: 20
Signed in to like this video. Thanks for the tutorial!
Thank you, as always great video. Regards
Mike. thanks a lot! Awesome
Thank you so much!
Please do a video on stored procedure too
Hi Michael, Do you have a video on transaction blocking using procedure.
Hello Mike, very good video, thanks so much for sharing with us :) I have a question : Id do not understand the logic of how to create the dates variable . Is that possible that you explain a bit about that? Or maybe there is another video about it? Many thanks.
Another great one Michael. Thanks for sharing the code too, that is a big help! Ps. I love the logo! Were you a navy guy?
@wesleyjake1509
7 жыл бұрын
It does sound cool , I'm sure your grandpa is a cool cat. I'll definitely have some requests bro. Will hit you up this week in a comment. Working on a cash flow deal right now similar to how the Hana version has it set up but waiting for a colleague to mark the accounts that should be included first. Keep up the good work mate.
Hi Taylor , May I know how to write same query using Date Range Parameter in HANA ??
What if you want to sum the total per Account?
Hi Mike, This is good and simplified way of doing the query. Thank you for sharing. I have been racking my brains to get a query to give same results as SAP B1 built in sales analysis for a day, MTD and YTD in a single shot. But some how my results are out by a couple hundred dollars. For the life of me I cant understand what am I missing. Would you be keen to give it a shot?
@yashpatel6086
6 жыл бұрын
Thanks! I am going to try your query. If I cant get it going with your suggestions, I will proceed to the LinkedIn message.
@fewrose
4 жыл бұрын
Sales analysis for a day, MTD and YTD in a single shot along with Live Stock. SELECT T0.ITEMCODE AS "AZ Code",T0.[CodeBars], T2.ItmsGrpNam AS "Family", T0.ItemName,T0.OnHand AS "AZ In Stock", (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JAN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'FEB ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'APR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAY ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUL ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'AUG ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'SEP ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'OCT ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'NOV ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'DEC ' FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] GROUP BY T0.ItemCode,T2.ItmsGrpNam,T0.[CodeBars], T0.Itemname,T0.OnHand,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())-0 ORDER BY T0. ITEMCODE
Hi Mike, can you do video on DTW for UDFs with how to map UDFs which without data target? Thanks for your videos, though
@armstrongkwakye9198
6 жыл бұрын
Hi Michael. Apologies for the confusion. I have found a way around it myself. Thanks
@armstrongkwakye9198
6 жыл бұрын
But I have another question for You, can you please give me your email address so that I can email it to you?
@armstrongkwakye9198
6 жыл бұрын
Michael Taylor Thanks.
I cant open the source code