May 15, 2012 at 1:33 am
Hi All,
Suppose that a have a table in sql server that store sales information ( Product; Date, Qty, Region).
It is possible to write a sql staments to retrivea table with daily comulative qty like the table bellow? Note: each day have a comulative qtys of the previous day including qty sold on that day.
Region A Region B Region C
1/1/2012 10 ...
2/1/2012 15
3/1/2012 30
4/1/2012 40
5/1/2012 53
5/1/2012 60
May 15, 2012 at 1:38 am
Hi, Welcome to SSC, please take a moment to read through the second link in my signature block on how to post code on the forum. It will help us to help you out quicker and get a more suitable solution to your problem.
But from the details you have given, yes it will be possible, but we need the information from the link to give you a solution.
May 15, 2012 at 3:23 am
based on what I think you are asking for its a pivot/cross tab query, I have knocked up some sample data which does what I think your asking for below, but you might want to take a look at the two links in my signature block on Cross-Tabs for other ways of doing this task
declare @tab table (product char(1),[date] date, qty int, region char(1))
insert into @tab values
('a','2012-05-01',1,'a'),
('b','2012-05-01',100,'a'),
('c','2012-05-01',64,'a'),
('d','2012-05-01',57,'a'),
('e','2012-05-01',14,'a'),
('a','2012-05-01',143,'b'),
('b','2012-05-01',1234,'b'),
('c','2012-05-01',6567,'b'),
('d','2012-05-01',577876,'b'),
('e','2012-05-01',1445,'b'),
('a','2012-05-01',0,'c'),
('b','2012-05-01',10,'c'),
('c','2012-05-01',6,'c'),
('d','2012-05-01',5,'c'),
('e','2012-05-01',1,'c'),
('a','2012-05-02',134536,'a'),
('b','2012-05-02',1854,'a'),
('c','2012-05-02',876,'a'),
('d','2012-05-02',765,'a'),
('e','2012-05-02',675,'a'),
('a','2012-05-02',1,'b'),
('b','2012-05-02',10,'b'),
('c','2012-05-02',6,'b'),
('d','2012-05-02',7,'b'),
('e','2012-05-02',4,'b'),
('a','2012-05-02',166,'c'),
('b','2012-05-02',10,'c'),
('c','2012-05-02',6776,'c'),
('d','2012-05-02',534,'c'),
('e','2012-05-02',1655,'c')
select
[date] AS [Date],
a AS RegionA,
b AS RegionB,
c AS RegionC
from
(select [date],qty,region from @tab) as sourcetable
pivot
(
SUM(QTY)
FOR region in ([a],,[c])
) As pivottable
Will give results as this
Date | RegionA | RegionB | RegionC
2012-05-01 | 236 | 587265 | 22
2012-05-02 | 138706 | 28 | 9141
But as I say this is just guessing what you need. Will be able to give you something more detailed once we have the information I requested.
May 15, 2012 at 6:42 am
That's a very nice solution by Anthony. 🙂
But, if you are not comfortable with PIVOT then you can use a simple CASE Statement like this:
--Declaring Temporary Table
declare @tab table (product char(1),[date] date, qty int, region char(1))
--Inserting Sample Data
insert into @tab values
('a','2012-05-01',1,'a'),
('b','2012-05-01',100,'a'),
('c','2012-05-01',64,'a'),
('d','2012-05-01',57,'a'),
('e','2012-05-01',14,'a'),
('a','2012-05-01',143,'b'),
('b','2012-05-01',1234,'b'),
('c','2012-05-01',6567,'b'),
('d','2012-05-01',577876,'b'),
('e','2012-05-01',1445,'b'),
('a','2012-05-01',0,'c'),
('b','2012-05-01',10,'c'),
('c','2012-05-01',6,'c'),
('d','2012-05-01',5,'c'),
('e','2012-05-01',1,'c'),
('a','2012-05-02',134536,'a'),
('b','2012-05-02',1854,'a'),
('c','2012-05-02',876,'a'),
('d','2012-05-02',765,'a'),
('e','2012-05-02',675,'a'),
('a','2012-05-02',1,'b'),
('b','2012-05-02',10,'b'),
('c','2012-05-02',6,'b'),
('d','2012-05-02',7,'b'),
('e','2012-05-02',4,'b'),
('a','2012-05-02',166,'c'),
('b','2012-05-02',10,'c'),
('c','2012-05-02',6776,'c'),
('d','2012-05-02',534,'c'),
('e','2012-05-02',1655,'c')
--Query For your Requirement
Select date,
SUM(Case When region = 'a' Then qty Else 0 End) As Region_A,
SUM(Case When region = 'b' Then qty Else 0 End) As Region_B,
SUM(Case When region = 'c' Then qty Else 0 End) As Region_C
From @tab
Group By date
If this doesn't satisfy your requirement then please check out the link in my Signature to get to how to get fast and good solutions to your requirements.
May 15, 2012 at 6:44 am
Yep, thats also another good solution Vinu. We just need to wait and see now what the actual requirements are from the OP as we might be heading in totally the wrong direction with this.
May 15, 2012 at 6:54 am
Yes, Anthony. You are right.
Just a few days back someone(a developer) was telling me that one can be a good developer if and only if he feels "the excitement" in coding.
Providing the OP with many solutions to choose from definitely shows the excitement of the people here.
Great Job guyz. 🙂
😛
Its outta context, but I thought I should tell you. :-D:-P
May 15, 2012 at 8:37 am
abdul.badru (5/15/2012)
Hi All,Suppose that a have a table in sql server that store sales information ( Product; Date, Qty, Region).
It is possible to write a sql staments to retrivea table with daily comulative qty like the table bellow? Note: each day have a comulative qtys of the previous day including qty sold on that day.
Region A Region B Region C
1/1/2012 10 ...
2/1/2012 15
3/1/2012 30
4/1/2012 40
5/1/2012 53
5/1/2012 60
I believe you're asking for a "Running Total" much like someone would have in a check book. Please see the following article for how to pull off that minor miracle in a timely fashion before SQL Server 2012 came out. The method in the article still beats the 2012 method for performance if you really need the performance.
http://www.sqlservercentral.com/articles/T-SQL/68467/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2012 at 4:06 am
Thanks all for your contribution. I Think we are almost there. I am able to do what you a suggesting. But remember Need the resultset as explained bellow:
Date | RegionA | RegionB | RegionC
2012-05-01 | 236 | 587265 | 22
2012-05-02 | 138706 | 28 | 9141
For e.g, on regionA the fist day is OK. but from Second day to the end, the value must be the qty sold on that day + qty sown on previuos day.
E.g. The result my look like this:
Date | RegionA | RegionB | RegionC
2012-05-01 | 236 | 587265 | 22
2012-05-02 | 138942| 587293| 9163
How can I will accomplish this in sql statements?
Regards
May 16, 2012 at 4:12 am
then you need to look at the running totals method which Jeff has detailed in his post.
May 16, 2012 at 4:20 am
Thanks anthony.green,
I will check the article.
Regards
May 16, 2012 at 7:26 am
Hi Jeff Moden,
Thank you. Your article helped me alot. I have accomplished my tasks.
Again, many thanks
May 16, 2012 at 7:27 am
Thanks,
The Jeff Moden Article helped me alot.
Regards
May 25, 2012 at 5:35 pm
Sorry for the late post. Thank you for the feedback, Abdul.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply