November 20, 2009 at 1:52 am
This is an edited post:
My wife kicked me out of bed Friday night because I insulted her friend at a dinner party. Her friend does in fact have
a double-chin, but I should not have stated that in front of our minister. So I was very tired when I wrote the original post
and provided data with duplicates. I perhaps insulted a DBA on this forum when expressing an unsolicited opinion.
The UNION ALL statement below gives me exactly what I need
It needs to be condensed using ROLLUP to both district and company level
Also I want to eliminate the companyname and districtname columns based on the following:
When storenbr is null, districtname should show in Storenbr,
When districtname is null the companyname should show up in districtname
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Booksales]') AND type in (N'U'))
DROP TABLE [dbo].[Booksales]
GO
CREATE TABLE [dbo].[Booksales](
[Storeid] [decimal](18, 0) NULL,
[BusinessDate] [datetime] NULL,
[Mature] [decimal](18, 0) NULL,
[Math] [decimal](18, 0) NULL,
[AudioBooks] [decimal](18, 0) NULL
) ON [PRIMARY]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BOOKSTORES]') AND type in (N'U'))
DROP TABLE [dbo].[BOOKSTORES]
CREATE TABLE [dbo].[BOOKSTORES](
[COMPANYNAME] [varchar](40) NULL,
[DISTRICTNAME] [varchar](50) NULL,
[STOREID] [char](4) NULL,
[STORENAME] [varchar](70) NULL,
[STORENBR] [char](4) NOT NULL
) ON [PRIMARY]
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 25 2008 12:00:00:000AM',10606,11806,6370)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 24 2009 12:00:00:000AM',7371,8271,4425)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Aug 31 2009 12:00:00:000AM',9374,10574,5839)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Sep 7 2009 12:00:00:000AM',3265,3452,1668)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(20,'Sep 21 2009 12:00:00:000AM',4965,5565,2830)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Aug 24 2009 12:00:00:000AM',15160,16960,8876)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Aug 31 2009 12:00:00:000AM',2099,2399,1501)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 7 2009 12:00:00:000AM',14634,16021,7684)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 14 2009 12:00:00:000AM',2752,3052,1481)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(21,'Sep 21 2009 12:00:00:000AM',2137,2437,1426)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Aug 24 2009 12:00:00:000AM',6286,6886,3398)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Aug 31 2009 12:00:00:000AM',2456,2756,1521)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 7 2009 12:00:00:000AM',4873,5473,2938)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 14 2009 12:00:00:000AM',10652,11852,6518)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(22,'Sep 21 2009 12:00:00:000AM',17505,19475,10132)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 25 2008 12:00:00:000AM',2617,2848,1484)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 24 2009 12:00:00:000AM',2156,2456,1408)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Aug 31 2009 12:00:00:000AM',5628,6228,3116)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 7 2009 12:00:00:000AM',4140,4740,2879)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 14 2009 12:00:00:000AM',6624,7524,4461)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(23,'Sep 21 2009 12:00:00:000AM',1987,2287,1339)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Aug 25 2008 12:00:00:000AM',2617,2848,1484)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Aug 31 2009 12:00:00:000AM',4735,5335,2905)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Sep 7 2009 12:00:00:000AM',2333,2633,1421)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(24,'Sep 14 2009 12:00:00:000AM',10338,11538,6248)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 25 2008 12:00:00:000AM',5580,6180,3106)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 24 2009 12:00:00:000AM',5189,5789,2961)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Aug 31 2009 12:00:00:000AM',10076,11276,6090)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 7 2009 12:00:00:000AM',6133,7033,4258)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 14 2009 12:00:00:000AM',6054,6654,2906)
INSERT INTO [Booksales] ([Storeid],[BusinessDate],[Mature],[Math],[AudioBooks])VALUES(25,'Sep 21 2009 12:00:00:000AM',7026,7926,4303)
--== Populate Bookstores
INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Memphis','20','Beale','0001')
INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Memphis','21','Crabtree','0002')
INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Tupelo','23','Downtown','0003')
INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Tupelo','24','Jonestreet','0004')
INSERT INTO [bookstores] ([COMPANYNAME],[DISTRICTNAME],[STOREID],[STORENAME],[STORENBR])VALUES('OnlineDotCom','Huntsville','25','SpaceCenter','0005')
--== The following SELECT gives me the data I need
SELECT companyname
, districtname
, storenbr
, businessdate
, Mature
, Math
, AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
union all
SELECT stores.companyname as companyname
, stores.districtname as districtname
, '' as storenbr
, businessDate
,SUM(Mature) as Mature
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
group by companyname, districtname, businessdate
union all
SELECT stores.companyname as companyname
, '' as districtname
, '' as storenbr
, CONVERT(VARCHAR(9), businessdate, 6) as businessdate
,SUM(Mature) as Mature
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
group by companyname, businessdate
order by companyname, districtname, storenbr, businessdate
Records must be in this specific order because end-user must see it this way:
OnlineDotCom (Company totals)
Memphis (District totals)
0001 (storeNbr totals)
0002 (storeNbr totals)
Tupelo (districtName totals)
0003 (storeNbr totals)
0004 (storeNbr totals)
There is a PDF Spreadsheet attached with the results of the UNION ALL query although results need to be reversed.
Challenges I had with my original query:
when I rolled up to district, the storenbr was null. This causes the client application to blowup.
I tried this in Oracle10g as well thinking that perhaps that the syntactical differences may be causing my
problem but was not the case. So for anyone who knows Oracle, I would not mind seeing Oracle version as well.
November 20, 2009 at 8:27 pm
Hi,
Post the schema of the booksales table, its helpful us to understand better.
November 20, 2009 at 9:14 pm
Used the code given by you. Still not complete code.. at least as of now....
I get the following error
Msg 207, Level 16, State 1, Line 29
Invalid column name 'week_date'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'weeknbr'.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 20, 2009 at 11:04 pm
the select statement should be:
SELECT
[COMPANYNAME]
,[DISTRICTNAME]
,[STORENBR]
,[BusinessDate]
,SUM(Mature) as Mature
,Sum(Cooking) as Cooking
,Sum(Exercise) as Exercise
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
,Sum(Childrens) as Childrens
,Sum(Spanish) as Spanish
,[STORENAME]
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
group by businessdate
, stores.companyname
, stores.districtname
, STORES.STORENBR
, STORES.STORENAME
Order By businessdate, storenbr
November 20, 2009 at 11:07 pm
Post the schema of the booksales table
Do you mean my first table definition?
November 20, 2009 at 11:26 pm
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table
Do you mean my first table definition?
Yes, but not now, because you edit the first post, and what formats do need to retrieved?
From the original post, you need the format by doing just like
Order By DISTRICTNAME,storenbr ,businessdate
November 20, 2009 at 11:38 pm
arun.sas (11/20/2009)
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table
Do you mean my first table definition?
Yes, but not now, because you edit the first post, and what formats do need to retrieved?
From the original post, you need the format by doing just like
Order By DISTRICTNAME,storenbr ,businessdate
Sorry, we must have crossed paths.
Make the order companyname, districtname, storenbr, businessdate
November 20, 2009 at 11:46 pm
TheHTMLDJ (11/20/2009)
arun.sas (11/20/2009)
TheHTMLDJ (11/20/2009)
Post the schema of the booksales table
Do you mean my first table definition?
Yes, but not now, because you edit the first post, and what formats do need to retrieved?
From the original post, you need the format by doing just like
Order By DISTRICTNAME,storenbr ,businessdateSorry, we must have crossed paths.
Make the order companyname, districtname, storenbr, businessdate
Ok, from the sample data I assumed that the company is static, so that I removed the company from order by class, how ever when the change of the order class, your needed is fulfilled? Or what formats do need to retrieved?
November 20, 2009 at 11:57 pm
Ok, from the sample data I assumed that the company is static, so that I removed the company from order by class, how ever when the change of the order class, your needed is fulfilled? Or what formats do need to retrieved?
yes company is static. I put it in the ORDER BY for future maintenance.
Im not sure what you meant by what formats need to be retrieved.
November 21, 2009 at 12:05 am
TheHTMLDJ (11/20/2009)Im not sure what you meant by what formats need to be retrieved.
I mean
Records must be in this specific order:
OnlineDotCom (Company totals)
Memphis (District totals)
0001 (storeNbr totals)
0002 (storeNbr totals)
Tupelo (districtName totals)
0003 (storeNbr totals)
0004 (storeNbr totals)
is now retrieved correctly?
November 21, 2009 at 12:36 am
arun.sas (11/21/2009)
TheHTMLDJ (11/20/2009)Im not sure what you meant by what formats need to be retrieved.
I mean
Records must be in this specific order:OnlineDotCom (Company totals)
Memphis (District totals)
0001 (storeNbr totals)
0002 (storeNbr totals)
Tupelo (districtName totals)
0003 (storeNbr totals)
0004 (storeNbr totals)
is now retrieved correctly?
Yes, records must be in exactly that order where the grand total is the first record, then the Memphis district totals, then the 2 stores within that district, then the Tupelo district and its 2 stores. There are more stores and more districts but i have only provided a subset of data.
I'm a little tired but the reason I had the sort the first way is because the numbers in the left columns above are the store nbrs. meaning that when the store number is blank due to the rollup, then the district name should be put in the storenbr. So you end up with the sort just being on storenbr and businessdate.
November 21, 2009 at 11:42 am
Hi,
I understand that is not simple, since you must be working in different time zone, I ma in EST in USA, so I could not reply in between, while you guys are trying to figure it out.
My question is ,
what is the output you want to look like?
Can you put that in a tabular format in excel and attach here, so that makes easier to figure out what you need. I could only see in your first post how you wanted the out put but what was this
Records must be in this specific order:
OnlineDotCom (Company totals)
Memphis (District totals)
0001 (storeNbr totals)
0002 (storeNbr totals)
Tupelo (districtName totals)
0003 (storeNbr totals)
0004 (storeNbr totals)
Almost all the replies in between were how you want,what are the columns in order by, etc etc..
What columns constitutes Totals? Which columns should be summed up to get that total figure? A better way of displaying would be in a tabular format in excel, that would be helpful even any one trying to figure out what you want achieve.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 21, 2009 at 1:32 pm
If I understand correctly you want to have the rollup result value before the values rolled up as opposed to the SQL standard.
The closest I could come up with is the following:
SELECT
isnull(CAST(storenbr AS VARCHAR(50)),isnull(stores.districtname,stores.companyname)) AS Grp
,SUM(Mature) as Mature
,Sum(Cooking) as Cooking
,Sum(Exercise) as Exercise
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
,Sum(Childrens) as Childrens
,Sum(Spanish) as Spanish
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
INNER JOIN
(SELECT [COMPANYNAME],[DISTRICTNAME],MIN([STORENBR]) AS MIN_STORENBR
FROM bookstores
GROUP BY [COMPANYNAME],[DISTRICTNAME]
) stores2 ON stores2.[COMPANYNAME] = stores.[COMPANYNAME]
AND stores2.[DISTRICTNAME] = stores.[DISTRICTNAME]
group BY stores.companyname
, stores.districtname
,MIN_STORENBR
, STORES.STORENBR
WITH rollup
HAVING
ISNULL(MIN_STORENBR,stores.storenbr) IS NOT NULL
OR stores.companyname IS NOT NULL
AND stores.districtname IS NULL
ORDER BY
isnull(MIN_STORENBR,' ' + stores.companyname),
isnull(storenbr,' ' + isnull(stores.districtname,' '+ stores.companyname))
The result set will display the rollup values first and the district order as requested.
However, your requirement to sort by district with the smallest shop number first is really strange (and it requires an additional join just for display order)...
Another question: What is the specific reason to do this with SQL anyway? Usually, rollup and sorting (especially nonstandard) can and should be done by the application...
Edit: wrong SQL posted before ...
November 22, 2009 at 2:29 am
This following query gives me exactly what I need except the companyname and districtname columns need to be eliminated
1) when the districtname is empty, the companyname becomes the districtname
2) when the storenbr is empty the districtname becomes the storenbr
The districts and companies are all unique and storenbrs are all unique ,
the client applicaiton will know that a particular record represents a store, a district,
or a company
SELECT companyname
, districtname
, storenbr
, businessdate
, Mature
, Math
, AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
union all
SELECT stores.companyname as companyname
, stores.districtname as districtname
, '' as storenbr
, businessDate
,SUM(Mature) as Mature
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
group by companyname, districtname, businessdate
union all
SELECT stores.companyname as companyname
, '' as districtname
, '' as storenbr
, businessdate
,SUM(Mature) as Mature
,Sum(Math) as Math
,Sum(AudioBooks) as AudioBooks
from booksales sales
inner join bookstores stores on stores.storeid = sales.storeid
group by companyname, businessdate
order by companyname, districtname, storenbr, businessdate
Of course, this can be simplified with rollup.
Note:
-- all columns in my original query were not necessary
-- the businessdate needs to be in dd-Mon-yy format
November 22, 2009 at 11:29 am
It looks like either your result set does not match your requirement or your requirement is still unclear...
Example:
companynamedistrictnamestorenbrbusinessdateMatureMathAudioBooks
OnlineDotComHuntsville00052009-09-07 00:00:00.00017521752360
OnlineDotComHuntsville00052009-09-07 00:00:00.00023882388519
OnlineDotComHuntsville00052009-09-07 00:00:00.00019931993379
It shows that there is more than one row for the same day and the same store.
That's different than your requirement from a few posts back.
You also didn't state on how the business date column needs to be sorted.
You also haven't answer my question regarding the reason to order it by storenbr.
Regarding your requirement to get the date format correct: I think that's the easy part you can help us help you: please have a look at BOL (SQL OnlineHelp system installed together with your SQL Server).
Two more notes:
a)
But if i can get data in correct sort order, I can avoid doing a dataview in my .NET program. Not important.
That really means something to me (someone who's trying to help you!)! Sounds like "It's not important at all. Just want to see if you folks in the forum can solve it."
b)
I will ultimately have to convert this over to an Oracle procedure which has different syntax but T-SQL will work for now.
Maybe you should ask your question on a Oracle forum!
Final statement from my side:
Since it is
a) unclear, what the OP want,
b) obviously, not important to the OP and
c) for Oracle instead of SQL Server
I'm outta here. Have fun, whoever stays in here...
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply