September 23, 2010 at 12:07 pm
Few days back as I mentioned in my post http://www.sqlservercentral.com/Forums/Topic988521-392-1.aspx that I need to add few more columns to produce billing for client: and Thanks to LutzM for great help. I have one more requirement - Here is the script and desired output is also explained in script below:
-- Create Contents Table Script
Use tempdb
GO
CREATE TABLE [dbo].[Contents](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](10) NULL,
[AddedDateTime] [datetime] NULL,
[UpdatedDateTime] [datetime] NULL,
[Size] [int] NULL,
[IsDisabled] [bit] NULL,
CONSTRAINT [PK_Contents] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Add Data to Contents Table Script
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('a','1-Nov-09','10-Dec-09',10,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('b','5-Nov-09','13-Feb-10',6,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('c','5-Dec-09','10-Dec-10',8,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('d','19-Dec-09','19-Dec-09',4,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('e','1-Jan-10','13-Jul-10',10,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('f','15-Jan-10','17-Jan-10',2,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('g','31-Jan-10','2-Feb-10',8,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('h','10-Feb-10','21-Feb-10',6,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('i','3-Mar-10','21-Apr-10',4,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('j','20-Apr-10','20-Apr-10',2,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('k','21-May-10','21-May-10',20,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('l','5-Jun-10','7-Aug-10',4,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('m','7-Aug-10','7-Aug-10',8,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('n','3-Sep-10','3-Sep-10',2,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('o','5-Sep-10','5-Sep-10',6,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('p','10-Sep-10','10-Sep-10',30,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('q','15-Sep-10','20-Sep-10',10,0)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('r','20-Sep-10','20-Sep-10',15,1)
INSERT INTO Contents (Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled) values('s','21-Sep-10','21-Sep-10',6,0)
select Title, AddedDateTime, UpdatedDateTime, Size, IsDisabled from Contents
GO
-- The following table is just for the required output from Contents Table:
-- Create Desired RequiredOutputFromContents Table
CREATE TABLE [dbo].[RequiredOutputFromContents](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BillingMonth] [varchar](10) NULL,
[SpaceOccupied] [int] NULL,
[Calculation] [varchar](30) NULL,
CONSTRAINT [PK_RequiredOutputFromContents] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Add Data to ResultOutput Table
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Dec-09', 28, ' = 10+6+8+4')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Jan-10', 38, ' = 28+20-4(Id:4)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Feb-10', 38, ' = 38+6-6(Id:2)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Mar-10', 42, ' = 38+4')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Apr-10', 44, ' = 42+2')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('May-10', 60, ' = 44+20-4(Id:9)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Jun-10', 64, ' = 60+4')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Jul-10', 54, ' = 64+0-10(Id:5)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Aug-10', 58, ' = 54+8-4(Id:12)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Sep-10', 127, ' = 58+69')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Oct-10', 112, ' = 127+0-15(Id:18)')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Nov-10', 112, ' = 112+0')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Dec-10', 112, ' = 112+0')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Jan-11', 112, ' = 112+0')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Feb-11', 112, ' = 112+0')
INSERT INTO RequiredOutputFromContents ([BillingMonth], [SpaceOccupied], [Calculation]) values('Mar-11', 112, ' = 112+0')
select [BillingMonth], [SpaceOccupied], [Calculation] [this col is only for understanding] from RequiredOutputFromContents;
-- BillingMonth and SpaceOccupied are the only required columns to produce the results the Calculation Col is only for help to understand the calculation/formula
As the post becomes older and seems no one is taking interest I have written this posted, but is not the same but similar one.
Appreaciate for your help, kindly reply with SQL help to get required output.
Shamshad Ali.
September 24, 2010 at 5:36 am
Please discard this post as i made the solution, I was making wrong calculation and due to which I was stuck in the query results, it all is about the time and dedication which i was not able to give at that time when i thought better to post here;
Here is the solution
Declare @BillingFrom datetime, @BillingTo datetime
set @BillingFrom = '1-Nov-2010 3:45 PM'
set @BillingTo = '25-Dec-2010 4:45 PM'
set @BillingFrom = left(convert (varchar, @BillingFrom, 112), 6 ) + '01';
WITH calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,@BillingFrom) START,DATEADD(mm,n+1,@BillingFrom) finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < DATEDIFF(mm, @BillingFrom, @BillingTo)
)x
), Content_Status AS -- cross join calendar on user, setting deleted flag and added flag for the related month
(
SELECT
CASE WHEN IsDisabled =1 AND updateddatetime < START THEN 1 ELSE 0 END AS deleted,
CASE WHEN addeddatetime < finish THEN 1 ELSE 0 END AS added,
CASE WHEN IsDisabled =1 AND updateddatetime < START THEN Size ELSE 0 END AS deletedSize,
CASE WHEN addeddatetime < finish THEN Size ELSE 0 END AS addedSize,
c.START,
c.finish
FROM Contents Cnts
CROSS APPLY calendar c
WHERE c.finish > Cnts.AddedDateTime --and IsImport = 0
)
-- select deleted, added, deletedSize, addedSize, CONVERT(varchar,start, 107) as start, CONVERT(varchar, finish, 107) as finish from Content_Status order by convert(datetime, START)
SELECT
replace (CONVERT(VARCHAR, start, 107), ' 01','') as [Billing Month], sum(addedsize) - sum(deletedSize) as TotalSize,
SUM(added)-SUM(deleted) as [TotalContents]
FROM Content_Status
GROUP BY START
ORDER BY START
sorry for Inconvenience and your previous time.
the result output would be:
Billing Month----------SpaceOccupied----------Calculation
Dec-09--------------------28----------=10+6+8+4
Jan-10--------------------44----------=28+20-4(Id:4)
Feb-10--------------------50----------=44+6
Mar-10--------------------48----------=50+4-6(Id:2)
Apr-10--------------------50----------=48+2
May-10--------------------66----------=50+20-4(Id:9)
Jun-10--------------------70----------=66+4
Jul-10--------------------70----------=70+0
Aug-10--------------------68----------=70+8-10(Id:5)
Sep-10--------------------133----------=68+69-4(Id:12)
Oct-10--------------------118----------=133+0-15(Id:18)
Nov-10--------------------118----------=118+0
Dec-10--------------------118----------=118+0
Jan-11--------------------118----------=118+0
Feb-11--------------------118----------=118+0
Mar-11--------------------118----------=118+0
Shamshad Ali.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply