URGENT: HELP Needed - All Details Given

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

URGENT: HELP Needed - All Details Given

Author

Topic

xuhaib
Starting Member

5 Posts

Posted - 2011-03-03 : 15:32:56

Following are the tables that I have to work with. Respective key columns are also mentioned.

ChartOfAccounts
Salesman Code
Retailer Code

Sales Voucher
Retailer Code
Voucher Code

Sales Detail
Voucher No
Item Code
Quantity

Item Info
Item Code
Category Code

ItemCategory
Category Code

Let me explain the DB a little. Invoices are entered for Retailer-wise sales. Each Retailer is linked with only one salesman. Example of ItemCategory can be a simple Ball. Items within this category will be Blue Ball, Red Ball, Yellow Ball etc.

Now, I want to extract a report that gives me Category-wise sales of each retailer for any given time frame. Furthermore, I need this report separately for all Salesmen.

Note: Each retailer is linked with one Salesman only.
All the links (PKs and FKs) are as they should be. They must not be much of a problem to understand.

Report format required is as follows.

Retailer - CATEGORY1 - Category2

1. Smith Toys - 355 - 230
2. XYZ Store - 520 - 320
3. Delta Store - 140 - 165

Please note that this report (the format given above) must be generated separately for each salesman.

PLEASE HELP ASAP. I HAVE BEEN CONFUSED IN THIS FOR OVER TWO MONTHS NOW.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-04 : 10:11:55

This is ALMOST all the information we need. Follow the "How to ask" link in my signature. It will show you how to give us DDL for your tables, and how to fill them with sample data.

Oh, and Formatting = Front end (i.e. Your reporting app, such as Crystal Reports).

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-04 : 10:34:04

Well, there are a few issues:

1. How can anyone make report for any given time, when there is not date and/or time column mentioned?
2. Can you provide sample result for report by salesman?

Anyway, assuming I understood your requirements correctly, when you find out date column, the answer should be pretty simple, not something needed two months investigation.

So I must got something wrong.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-03-06 : 00:42:01

quote:
Originally posted by xuhaib

Retailer - CATEGORY1 - Category2

1. Smith Toys - 355 - 230
2. XYZ Store - 520 - 320
3. Delta Store - 140 - 165

Please note that this report (the format given above) must be generated separately for each salesman.

PLEASE HELP ASAP. I HAVE BEEN CONFUSED IN THIS FOR OVER TWO MONTHS NOW.

You are displaying what appears to be a Retailer Name, but there is no column like that in the table structures you provided, only a Retailer Code.

The Category1 and Category2 columns make it look like you want to do what is called a crosstab query.

If you have a fixed number of categories, you can refer to the PIVOT operator:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

If you have an unknown number of categories, you'll need to look at TSQL for a dynamic crosstab. I can refer you to the following example:

[url]http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources