Week To date sales

[gmenon100 via AccessMonster.com]

Dear friends,
I am working on access ADP with SQL back end
I have a table with the following columns:
Code#, OrdDt, Quantity, amount, Salesrep, Customer,

I want to form a query / SP to show the following:

Weekly:

Code Mon Tue Wed Thu Fri
Sat Sun TOTAL
qty amt qty amt qty amt qty amt qty amt qty
amt qty amt QTY AMT

7778 2 3000 4 322 7 9992 11 2828 3 847 83
3377 6 773 116 18616
5466 ...........

Same for the monthly report and yearly.

Can someone please advise how I can go with this?

Thank you

Gimen

 

[Sylvain Lafontaine]

First, it's not clear that OrdDt is the date field in your table. Giving
some exemples of data with the propert DDL and DML statements (such as «
create table #t ...; insert into #t .... ») would also be a bonus here.

This is a pivot transformation (or crosstable query in Access or Excel).
There is no pivot transformation operator in SQL-2000 but there is one in
SQL-2005.

In SQL-2000, there is two possibilities. The first one is to use a Case
statement for each day and the second one is to create a temporary table
with Mon to Sun columns and insert into it all the values for Mon, then all
the values for Tue, etc. These values can already be summed or not,
depending of the complexity of what you need. A third possibility would be
to use subqueries but this is much more complicated.

The following article give an example for all these three methods (Case
statement, temporary table and Pivot operator in the case of SQL-2005):

http://www.setfocus.com/technicalarticles/sql-server-2005-tsql.aspx

 

[Robert W. Storck]

Test.