HELP Needed - SQL Server Forums

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)

HELP Needed

Author

Topic

xuhaib
Starting Member

5 Posts

Posted - 2011-03-03 : 04:29:31

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

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.

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-03 : 04:41:13

Please show your working...

Vaibhav T

If I cant go back, I want to go fast...

xuhaib
Starting Member

5 Posts

Posted - 2011-03-03 : 04:46:21

SELECT AccChartOfAccounts_1.accName AS RSO, AccChartOfAccounts_2.accName AS Retailer,
(SELECT SUM(SaleDetails.Qty)
FROM dbo.SaleDetails
WHERE ItemCode IN
(SELECT ItemCode
FROM dbo.ItemInfo
WHERE CategoryCode IN
(SELECT CategoryCode
FROM dbo.ItemCategory
WHERE CategoryName = 'ETOP'))) AS ETOP,
(SELECT SUM(SaleDetails.Qty)
FROM dbo.SaleDetails
WHERE ItemCode IN
(SELECT ItemCode
FROM dbo.ItemInfo
WHERE CategoryCode IN
(SELECT CategoryCode
FROM dbo.ItemCategory
WHERE CategoryName = 'SIM CARD'))) AS SIMs,
(SELECT SUM(SaleDetails.Qty)
FROM dbo.SaleDetails
WHERE ItemCode IN
(SELECT ItemCode
FROM dbo.ItemInfo
WHERE CategoryCode IN
(SELECT CategoryCode
FROM dbo.ItemCategory
WHERE CategoryName = 'SCRATCH CARD'))) AS SCard
FROM dbo.AccChartOfAccounts AccChartOfAccounts_1 INNER JOIN
dbo.AccChartOfAccounts AccChartOfAccounts_2 INNER JOIN
dbo.ItemInfo INNER JOIN
dbo.ItemCategory ON dbo.ItemInfo.CategoryCode = dbo.ItemCategory.CategoryCode INNER JOIN
dbo.SaleDetails ON dbo.ItemInfo.ItemCode = dbo.SaleDetails.ItemCode INNER JOIN
dbo.SalesVoucher ON dbo.SaleDetails.VoucherNo = dbo.SalesVoucher.VoucherNo ON AccChartOfAccounts_2.accCode = dbo.SalesVoucher.accCode ON
AccChartOfAccounts_1.accCode = AccChartOfAccounts_2.SalesmanCode
GROUP BY AccChartOfAccounts_1.accName, AccChartOfAccounts_2.accName, dbo.ItemCategory.CategoryName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 05:02:50

[code]
select c.SalesmanCode, i.Category, sum(d.Quantity)
from ChartOfAccounts c
inner join SalesVoucher s on c.RetailerCode = s.RetailerCode
inner join SalesDetail d on s.VoucherCode = d.VoucherNo
inner join ItemInfo i on d.ItemCode = i.ItemCode
group by c.SalesmanCode, i.Category
[/code]

KH
[spoiler]Time is always against us[/spoiler]

xuhaib
Starting Member

5 Posts

Posted - 2011-03-03 : 05:16:11

That is right. But I want the following columns.

Retailer Item 1 Item 2

Can you Help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 05:23:31

quote:
Originally posted by xuhaib

That is right. But I want the following columns.

Retailer Item 1 Item 2

Can you Help?

what do you mean ? How ? pls show expected result

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 05:24:40

it will be easier for others to help if you can post some sample data and the expected result from the sample data

KH
[spoiler]Time is always against us[/spoiler]

xuhaib
Starting Member

5 Posts

Posted - 2011-03-03 : 15:36:08

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.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 18:03:26

Do they really teach that you should have spaces and special chars as names for database object in school?

What school/ Country is this

And tell the teach, that while it can be done, some thing don't like it and won't work

try bcp

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 23:03:45

[code]
select *
from
(
select c.RetailerCode, Quantity = sum(d.Quantity),
row_no = row_number() over (partition by c.RetailerCode order by i.Category)
from ChartOfAccounts c
inner join SalesVoucher s on c.RetailerCode = s.RetailerCode
inner join SalesDetail d on s.VoucherCode = d.VoucherNo
inner join ItemInfo i on d.ItemCode = i.ItemCode
group by c.RetailerCode, i.Category
) d
pivot
(
sum(Quantity)
for row_no in ([1], [2])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources