Master-Detail group by sum query

Question

User740553633 posted

Hi, 

For ease of explanation, i have the following 3 entities (tables) in a SQL Express DB,

BAR CATEGORY (Beers, Spirits, etc)
------------
ID
Name

BARTAKING (Daily capture of sales)

--------
ID
TAKING_DATE
BARMAN_ID

BARTAKING_LINE (Daily amount captured per bar category)
--------------
ID
BARTAKING_ID (fk)
BARCATEGORY_ID (fk)
AMOUNT

I am battling to write a LINQ query to give me a the daily sales (per bar category) for the last three weeks.  I will specify the bar category id in the query, so I can run it for any of the bar categories.

Can anybody assist me

thanks

Mel

Answer 1

User-271186128 posted

Hi Mel,

Welcome to asp.net forum.

As for this issue, you could use join clause and group by statement to join the table and get the result. You could refer to the following code:

                DateTime endDT= DateTime.Now;
                DateTime startDT  = DateTime.Now.AddDays(-15);

                var queryList = (from bb in context.BARTAKING
                                 join bl in context.BARTAKING_LINE
                                 on bb.ID equals bl.BARTAKING_ID
                                 where bb.TAKING_DATE >= startDT && bb.TAKING_DATE <= endDT  //
                                 group bl by bl.BARCATEGORY_ID into newgroup
                                 select new
                                 {
                                     CATEGORYID = newgroup.Key,
                                     AMOUNT = newgroup.Sum(cc => cc.AMOUNT)
                                 }).ToList();

More details about join clause and group by statement, please refer to the following articles:

https://msdn.microsoft.com/en-us/library/gg509017.aspx#SimpleInnerJoin

https://msdn.microsoft.com/en-us/library/bb896250(v=vs.110).aspx

Best regards,
Dillion

Answer 2

User740553633 posted

thanks Dillion,

Will give it a try, and let you know

Answer 3

User740553633 posted

This is what the data looks like,

ID	TakingDate	Till Number	MemberID	DailyTotal
21	1/13/2016 12:00:00 AM	1	19	300.00
22	1/13/2016 12:00:00 AM	1	19	300.00
23	1/12/2016 12:00:00 AM	1	19	1200.00

 

This query,

 

public IEnumerable<TotalSalesMonthly> TotalSalesMonthlyDict

        {

            get

            {

                DateTime endDT = DateTime.Now;

                DateTime startDT = DateTime.Now.AddDays(-21);

 

                var data = (from bt in db.BarTakings

                            where bt.TakingDate >= startDT

                               && bt.TakingDate <= endDT

                            group bt by bt.TakingDate into dataGroup

                            select new TotalSalesMonthly()

                            {

                                SalesDate = dataGroup.Key,

                                TotalSales = dataGroup.Sum(c => c.DailyTotal)

                            }

                            ).ToList();

 

                return data;

            }

        }

Produces the following output (which is correct), 

2016-01-12   1200.00
2016-01-13     600.00

How can I get only the last record, i.e the 600.00?

Answer 4

User-271186128 posted

Hi melvync,

You could use OrderBy method to sort the result and then get the first record using FirstOrDefault() method.

Like this:

                var data = (from bt in db.BarTakings

                            where bt.TakingDate >= startDT

                               && bt.TakingDate <= endDT

                            group bt by bt.TakingDate into dataGroup

                            select new TotalSalesMonthly()

                            {

                                SalesDate = dataGroup.Key,

                                TotalSales = dataGroup.Sum(c => c.DailyTotal)

                            }

                            ).OrderBy(c=>c.TotalSales).FirstOrDefault();

Note, if you are using above code the data is a TotalSalesMonthly entity, not IEnumerable<TotalSalesMonthly>.

Best regards,
Dillion

Answer 5

User740553633 posted

thanks Dillion,

I was sort of on the right track with using .First(), but just not quite there.  Your assistance is greatly appreciated.