Hello,
I have two tables.
Table 1 name is Company
Table 2 is Sales
Data in 'Company' table is,
CompanyID CompanyName
--------------------------------------------
1 Company1
2 Company2
3 Company3
Data in 'Sales' table is
ID CompanyID SalesDate Units
---------------------------------------------------------------
1 1 01/23/2008 40
2 1 01/24/2008 20
2 2 01/23/2008 30
2 2 01/24/2008 10
2 2 01/23/2008 100
2 3 01/24/2008 75
2 3 01/25/2008 125
I want result set as follwoing
Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0
Does anyone has idea how to achieve it?
7 3665
Also i would ike to add that
with following querry, -
SELECT DATENAME(MONTH,sales.SalesDate) AS [Month], YEAR(sales.SalesDate) AS [Year],
-
SUM(ISNULL(sales.Units,0)) AS Units
-
FROM Sales
-
WHERE Sales.CompanyID = '1'
-
AND HORevenueTranscation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
-
GROUP BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
-
ORDER BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
-
i get result set as
Month Year Units
-----------------------------------------
January 2007 60
But i have no idea how to get result set as specified previously.
ie,
Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0
Also i would ike to add that
with following querry, -
SELECT DATENAME(MONTH,sales.SalesDate) AS [Month], YEAR(sales.SalesDate) AS [Year],
-
SUM(ISNULL(sales.Units,0)) AS Units
-
FROM Sales
-
WHERE Sales.CompanyID = '1'
-
AND HORevenueTranscation.EntryDate BETWEEN '01/01/2008' AND '12/31/2008'
-
GROUP BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
-
ORDER BY YEAR(sales.SalesDate), DATENAME(MONTH,sales.SalesDate)
-
i get result set as
Month Year Units
-----------------------------------------
January 2007 60
But i have no idea how to get result set as specified previously.
ie,
Unit Sales Company1 Company2 Company3
-----------------------------------------------------------------------------
Jan 08 60 40 300
Feb 08 0 0 0
Mar 08 0 0 0
i think this is OLAP Concept... search for olap queries
-
-
SELECT SalesDate,
-
sum(case when companyID=1 then units else 0 end) as Company1,
-
sum(case when companyID=2 then units else 0 end) as Company2,
-
sum(case when companyID=3 then units else 0 end) as Company3
-
FROM Sales
-
group by SalesDate
-
adjust to suit your needs
Delerna,
You suggested a good option, but companies are not going to be 3 only.
Think about the case when someone add a new company 'Company4' and 'Company5' in "Company' table.
I think then your querry will show result only for company 1,2 and 3 but not 4 and 5.
I am looking for something else here. -
-
SELECT SalesDate,
-
sum(case when companyID=1 then units else 0 end) as Company1,
-
sum(case when companyID=2 then units else 0 end) as Company2,
-
sum(case when companyID=3 then units else 0 end) as Company3
-
FROM Sales
-
group by SalesDate
-
adjust to suit your needs
If you need to cater for a variable number of companies the you could use a cursors to build a dynamic query string
Here is an example using the query i presented before -
DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)
-
-
DECLARE Company_Cursor CURSOR FOR
-
select CompanyID,CompanyName From Company order by ID
-
OPEN Company_Cursor
-
FETCH NEXT FROM Company_Cursor
-
INTO @CompanyID,@Company
-
-
set @SQL='SELECT SalesDate,'
-
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
-
FETCH NEXT FROM Employee_Cursor
-
INTO @CompanyID,@Company
-
END
-
-
-
CLOSE Employee_Cursor
-
DEALLOCATE Company_Cursor
-
-
set @SQL=@SQL + ' FROM Sales group by SalesDate'
-
-
EXECUTE sp_executesql @SQL
-
-
this way the query can handle any number of companies and the number of companies in the table can be changed without having to change the query.
PS I havn't tested the code so you may have to erradicate some bugs before it will work. I did try as carefully as I could to get it right.
sounds like a PIVOT table...
try reading this
-- ck
Hey that's what exactly i was looking for.
Thanks a lot .......
If you need to cater for a variable number of companies the you could use a cursors to build a dynamic query string
Here is an example using the query i presented before -
DECLARE @CompanyID bigint, @Company nvarchar(50),@SQL nvarchar(4000)
-
-
DECLARE Company_Cursor CURSOR FOR
-
select CompanyID,CompanyName From Company order by ID
-
OPEN Company_Cursor
-
FETCH NEXT FROM Company_Cursor
-
INTO @CompanyID,@Company
-
-
set @SQL='SELECT SalesDate,'
-
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
set @SQL=@SQL + ',sum(case when companyID=' + @CompanyID + ' then units else 0 end) as ' + @Company
-
FETCH NEXT FROM Employee_Cursor
-
INTO @CompanyID,@Company
-
END
-
-
-
CLOSE Employee_Cursor
-
DEALLOCATE Company_Cursor
-
-
set @SQL=@SQL + ' FROM Sales group by SalesDate'
-
-
EXECUTE sp_executesql @SQL
-
-
this way the query can handle any number of companies and the number of companies in the table can be changed without having to change the query.
PS I havn't tested the code so you may have to erradicate some bugs before it will work. I did try as carefully as I could to get it right.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jay |
last post by:
I'm wondering if anyone has even encountered a control or other scripting
that will automatically rotate the rows of a datagrid... I am in the process
of building an asp.net application that will...
|
by: João Roberto Alonso |
last post by:
Hi
I wanto to know if there is a way to convert a DBNull.Value to Decimal and
returns to me Decimal.Zero?
Examples
COLUMN1 = NULL from DB
Ex1.: cstr((dt.rows(0).item("COLUMN1"))
|
by: Coleen |
last post by:
Hi All :-)
I found the way to get my column sum (Thanks Cor I did it a little different, but the result is what I wanted) I used:
dt_stat_report_3b.Columns.Add(New DataColumn("Sum",...
|
by: skOOb33 |
last post by:
I successfully autosized the columns and rows on my Datagrid, and am
now facing another issue. Having the sorting ability by clicking the
column headers is key, but when I do that, it resizes all...
|
by: ppateel |
last post by:
Hi,
I am new to c++ and I am converting a c program to c++. I changed
malloc call to new and I am getting an exception violation. Here is the
relevant piece of code.
Compiler vc++ 7.0 (.Net...
|
by: Otie |
last post by:
Hello,
I am using the MSFlexGrd Control in VB5. I have 1 fixed row and one fixed
column. I am trying to do a sort when the user clicks a column in the FIXED
ROW. But when I capture the row...
|
by: py_genetic |
last post by:
Hello,
I'm importing large text files of data using csv. I would like to add
some more auto sensing abilities. I'm considing sampling the data
file and doing some fuzzy logic scoring on the...
|
by: graphicguru |
last post by:
Hi
i got some javascript from (*) these site for lock one column with js and header row with css.
i am not a programmer i am a designer. i am working in a small company. i don't find such...
|
by: jfkjfk |
last post by:
Hello,
I am trying to make a page with a design as follows:
page is centered horizontaly with a total width of 1000
- a Header on top
- a Footer at the bottom
- in between the header and the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |