0

I've got a query which gets the lists of customers who've made a purchase of the product for the current day

select Customer.customerName, sum(InvoiceDetail.itemPrice * InvoiceDetail.itemQuantity) as dailyPurchase from Invoice
inner join InvoiceDetail on Invoice.invoiceID = InvoiceDetail.invoiceID
inner join Customer on Invoice.customerID = Customer.customerID
inner join Item on InvoiceDetail.itemID = Item.itemID
inner join Branch on Branch.branchID = Invoice.branchID
inner join Region on Region.regionID = Branch.regionID
where 
Invoice.inactive = 0 and InvoiceDetail.inactive = 0
and Item.itemTypeID = 3
and Region.regionCode = 'CR'
and cast(Invoice.invoiceDate as date) = convert(date, '01/08/2016', 103)
group by Customer.customerName

What I need is a table with a list of all the dates in the current month, listing ALL customers who have at least purchased the product ONCE. It should resemble something similar to this image here.

Any help on how to get started or a general idea of how to get the desired result, is much appreciated. Thanks!

Sample Data from Results:

customerName                dailyPurchase
AGH COMMUNICATIONS          450.00
ARIEL AMARCORD SHOP         285.00
AKN COMMUNICATION           300.00
AWSDAC TELECOMMUNICATION    2850.00
BARLEY MOBILE & SERVICES    285.00

Table Structure - I'm sorry, I don't know an easier way to copy this.

superflyingcrap
  • 11
  • 6
  • Please provide table structure and if possible sample data. – Esty Oct 04 '16 at 03:23
  • @TanjimRahman added to the post. – superflyingcrap Oct 04 '16 at 03:59
  • What are you doing your 'report' in? Just exporting to excel? The easiest way to do this is to actually just copy paste into excel and use a pivot table. If you need to have _every_ day regardless, you need to create a calendar or tally table - are you able to create tables? – Nick.Mc Oct 04 '16 at 04:04
  • @Nick.McDermaid usually reports are sent in excel, but this time we're trying to make a query for this sales report to be launched into our system, so it'll later be implemented into the .cs file. And yes, I'm able to create tables as well. – superflyingcrap Oct 04 '16 at 04:11
  • The main technical issue with the report is that it is a crosstab, and the column names can be different every time, and there can be a different number every time. This is tricky to achieve in SQL which likes consistent column counts and names. Anyway to get started you need to list _all_ dates, using one of the methods found in this post: http://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates. Then you need to outer join this to the query that you have above. The will get you the answer with dates in rows. Then you need to crosstab that. – Nick.Mc Oct 04 '16 at 04:28

1 Answers1

0

First get the customers who have purchased the product atleast once this month alongwith date. Then use pivot to get the result in the form that you want (as seen in image). Search stackoverflow for pivot in sql server, you will get good info.

Give some information about the table structure and sample data and I might be able to help you with the query to get the results.

Abhay Chauhan
  • 404
  • 3
  • 11