Solved: Sales Report Weeknum & Week Days wise

Hi Expert,

Can anyone guide us how to make this type of Query report

Query link - INV1 Quantity less RIN1 Quantity

Sales Report
Parameter – Year
Parameter – From Week Num to Week Num
	Week Days
Week Num	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
1	50	50	45	64	69	65	78
2	45	79	80	45	65	47	65
3	64	64	69	65	78	79	80
4	69	65	78	79	80	45	65
5	65	65	78	79	80	45	65
6	78	65	78	79	80	45	65
7	79	65	78	79	80	45	65
8	80	65	78	79	80	45	65
9	45	65	78	79	80	45	65
10	65	65	78	79	80	45	65
11	47	65	78	79	80	45	65
12	65	79	80	45	65	47	65
13	12	79	80	45	65	47	65

Thanks

Mukesh Singh

SAP Managed Tags:
SQL,
SAP Business One

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hi Mukesh Singh,

Will one of the parameters be ItemCode ?

Regards,

Johan

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Johan,

No All item code if item selected under Invoice.

Regards,

Mukesh Singh

Hi Mukesh Singh,

Okay, how will we know to which item each quantity belongs, or do you need the quantities of all items summed up into one number per weekday per week ?

Regards,

Johan

Hi Johan,

I need all item should be sum per Weekday and per Week.

Regards,

Mukesh

Hi Mukesh,

Please give this a try:

DECLARE @YEAR AS INT = 2016

DECLARE @FROM_WEEK_NR AS INT = 1

DECLARE @TO_WEEK_NR AS INT = 10

SELECT DATEPART(wk, h.DocDate) AS WeekNr

,SUM(ISNULL(Sunday.Quantity, 0)) AS 'Sunday'

,SUM(ISNULL(Monday.Quantity, 0)) AS 'Monday'

,SUM(ISNULL(Tuesday.Quantity, 0)) AS 'Tuesday'

,SUM(ISNULL(Wednesday.Quantity, 0)) AS 'Wednesday'

,SUM(ISNULL(Thursday.Quantity, 0)) AS 'Thursday'

,SUM(ISNULL(Friday.Quantity, 0)) AS 'Friday'

,SUM(ISNULL(Saturday.Quantity, 0)) AS 'Saturday'

FROM OINV h

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Sunday ON h.DocEntry = Sunday.DocEntry AND DATEPART(wk, h.DocDate) = Sunday.WeekNr AND Sunday.WD = 1

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Monday ON h.DocEntry = Monday.DocEntry AND DATEPART(wk, h.DocDate) = Monday.WeekNr AND Monday.WD = 2

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Tuesday ON h.DocEntry = Tuesday.DocEntry AND DATEPART(wk, h.DocDate) = Tuesday.WeekNr AND Tuesday.WD = 3

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Wednesday ON h.DocEntry = Wednesday.DocEntry AND DATEPART(wk, h.DocDate) = Wednesday.WeekNr AND Wednesday.WD = 4

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Thursday ON h.DocEntry = Thursday.DocEntry AND DATEPART(wk, h.DocDate) = Thursday.WeekNr AND Thursday.WD = 5

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Friday ON h.DocEntry = Friday.DocEntry AND DATEPART(wk, h.DocDate) = Friday.WeekNr AND Friday.WD = 6

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Saturday ON h.DocEntry = Saturday.DocEntry AND DATEPART(wk, h.DocDate) = Saturday.WeekNr AND Saturday.WD = 7

WHERE DATEPART(YEAR, h.DocDate) = @YEAR

AND DATEPART(wk, h.DocDate) BETWEEN @FROM_WEEK_NR AND @TO_WEEK_NR

GROUP BY DATEPART(wk, h.DocDate)

ORDER BY WeekNr

Please note that this query assumes that Sunday is the first day of the week. This may be different for your localization.

Regards,

Johan

Hi Johan,

Your Query is working perfectly except Credit Note (RIN1) not less in this query.

Please less Credit note so that my figure will be match with actual sales.

Regards,

Mukesh Singh

Hi Mukesh,

Sorry, I forgot about that. Please try this:

DECLARE @YEAR AS INT = 2016

DECLARE @FROM_WEEK_NR AS INT = 1

DECLARE @TO_WEEK_NR AS INT = 10

SELECT X.WeekNr

,SUM(X.Sunday) AS 'Sunday'

,SUM(X.Monday) AS 'Monday'

,SUM(X.Tuesday) AS 'Tuesday'

,SUM(X.Wednesday) AS 'Wednesday'

,SUM(X.Thursday) AS 'Thursday'

,SUM(X.Friday) AS 'Friday'

,SUM(x.Saturday) AS 'Saturday'

FROM

(SELECT h.WeekNr

,SUM(ISNULL(Sunday.Quantity, 0)) AS 'Sunday'

,SUM(ISNULL(Monday.Quantity, 0)) AS 'Monday'

,SUM(ISNULL(Tuesday.Quantity, 0)) AS 'Tuesday'

,SUM(ISNULL(Wednesday.Quantity, 0)) AS 'Wednesday'

,SUM(ISNULL(Thursday.Quantity, 0)) AS 'Thursday'

,SUM(ISNULL(Friday.Quantity, 0)) AS 'Friday'

,SUM(ISNULL(Saturday.Quantity, 0)) AS 'Saturday'

FROM (SELECT DocEntry, DATEPART(wk, DocDate) AS WeekNr, DATEPART(YEAR, DocDate) AS [Year] FROM OINV) h

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Sunday ON h.DocEntry = Sunday.DocEntry AND h.WeekNr = Sunday.WeekNr AND Sunday.WD = 1

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Monday ON h.DocEntry = Monday.DocEntry AND h.WeekNr = Monday.WeekNr AND Monday.WD = 2

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Tuesday ON h.DocEntry = Tuesday.DocEntry AND h.WeekNr = Tuesday.WeekNr AND Tuesday.WD = 3

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Wednesday ON h.DocEntry = Wednesday.DocEntry AND h.WeekNr = Wednesday.WeekNr AND Wednesday.WD = 4

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Thursday ON h.DocEntry = Thursday.DocEntry AND h.WeekNr = Thursday.WeekNr AND Thursday.WD = 5

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Friday ON h.DocEntry = Friday.DocEntry AND h.WeekNr = Friday.WeekNr AND Friday.WD = 6

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM INV1 dr1

INNER JOIN OINV dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS Saturday ON h.DocEntry = Saturday.DocEntry AND h.WeekNr = Saturday.WeekNr AND Saturday.WD = 7

WHERE h.Year = @YEAR

AND h.WeekNr BETWEEN @FROM_WEEK_NR AND @TO_WEEK_NR

GROUP BY h.WeekNr

UNION ALL

SELECT c.WeekNr

,0 - SUM(ISNULL(cSunday.Quantity, 0)) AS 'cSunday'

,0 - SUM(ISNULL(cMonday.Quantity, 0)) AS 'cMonday'

,0 - SUM(ISNULL(cTuesday.Quantity, 0)) AS 'cTuesday'

,0 - SUM(ISNULL(cWednesday.Quantity, 0)) AS 'cWednesday'

,0 - SUM(ISNULL(cThursday.Quantity, 0)) AS 'cThursday'

,0 - SUM(ISNULL(cFriday.Quantity, 0)) AS 'cFriday'

,0 - SUM(ISNULL(cSaturday.Quantity, 0)) AS 'cSaturday'

FROM (SELECT DocEntry, DATEPART(wk, DocDate) AS WeekNr, DATEPART(YEAR, DocDate) AS [Year] FROM ORIN) c

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cSunday ON c.DocEntry = cSunday.DocEntry AND c.WeekNr = cSunday.WeekNr AND cSunday.WD = 1

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cMonday ON c.DocEntry = cMonday.DocEntry AND c.WeekNr = cMonday.WeekNr AND cMonday.WD = 2

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cTuesday ON c.DocEntry = cTuesday.DocEntry AND c.WeekNr = cTuesday.WeekNr AND cTuesday.WD = 3

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cWednesday ON c.DocEntry = cWednesday.DocEntry AND c.WeekNr = cWednesday.WeekNr AND cWednesday.WD = 4

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cThursday ON c.DocEntry = cThursday.DocEntry AND c.WeekNr = cThursday.WeekNr AND cThursday.WD = 5

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cFriday ON c.DocEntry = cFriday.DocEntry AND c.WeekNr = cFriday.WeekNr AND cFriday.WD = 6

LEFT OUTER JOIN (SELECT dr1.DocEntry

,SUM(dr1.Quantity) AS Quantity

,DATEPART(wk, dh1.DocDate) AS WeekNr

,DATEPART(dw, dh1.DocDate) AS WD

FROM RIN1 dr1

INNER JOIN ORIN dh1 ON dr1.DocEntry = dh1.DocEntry

GROUP BY dr1.DocEntry,DATEPART(wk, dh1.DocDate),DATEPART(dw, dh1.DocDate)) AS cSaturday ON c.DocEntry = cSaturday.DocEntry AND c.WeekNr = cSaturday.WeekNr AND cSaturday.WD = 7

WHERE c.Year = @YEAR

AND c.WeekNr BETWEEN @FROM_WEEK_NR AND @TO_WEEK_NR

GROUP BY c.WeekNr) X

GROUP BY X.WeekNr

ORDER BY WeekNr

Regards,

Johan

Hi Johan,

Your query working fine thanks a lot.

Regards,

Mukesh Singh

Answers (0)

Ask a Question

Top Q&A Solution Author

User

Count

107

Sales Report Weeknum & Week Days wise

Accepted Solutions (1)

Accepted Solutions (1)

Answers (0)

Re: FPCOPARA - Error EZ488 Sender address does not...

FPCOPARA - Error EZ488 Sender address does not con...

Error in editing App Manage Global Hierarchies in ...

Quarterly vat declaration Italy

How to create a plugin session timeout with a coun...