November 25, 2014 at 7:06 am
Could someone help me with a SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
Thank you for looking at this query.
November 26, 2014 at 4:52 am
We will need more information than you have provided,
Can you post the DDL Create for the table and sample data, along with expected results.
EDIT : Ideally you should not be using a GUID (Unique identifier) for an ID column, as in larger schemas it makes looking up single data rows simpler.
eg
Apart from storage considerations, Which of the following is easier to understand?
SELECT * FROM aTable Where Id='9B91D096-2F5F-4C86-AFFE-A68019ADD89A';
--Or
SELECT * FROM aTable Where Id=14556';
You might not have control over the database or the schema but just for reference.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 27, 2014 at 3:04 am
I would create a peakhours table
Date,
Hour,
IsPeak
and then join to that to determine peak status. Populating this table is then the hardest part. You may also want to include some reference columns such as DayofWeek, IsPublicHoliday etc.
The other way to do it would be to have a table for days and a table for hours
DAYS:
Date,
Season [summer/winter]
IsWeekDay
IsPublicHoliday
HOURS:
Season
IsWeekDay
IsPublicHoliday
Hour
IsPeak
The FK on the hours table is Season,IsWeekDay,IsPublicHoliday
You join to the DAYS table to determine which set of data in the HOURS table you should be using.
December 1, 2014 at 1:33 pm
Hi,
Thank you very much for the your response.
The above table structure is where we store the hourly data for all the units. We dont have any other table or column to show the off Peak or on Peak values. At present I do the, the manual calculations for onPeak & offpeak in excel. Attached the snapshot of the report, all the data on the report were from SQL DB.
December 1, 2014 at 1:35 pm
Deries (12/1/2014)
Hi,Thank you very much for the your response.
The above table structure is where we store the hourly data for all the units. We dont have any other table or column to show the off Peak or on Peak values. At present I do the, the manual calculations for onPeak & offpeak in excel. Attached the snapshot of the report, all the data on the report were from SQL DB.
A picture is nothing like consumable ddl and sample data. We need something so we can work on the query to help. Please take a few minutes and read the article referenced in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2014 at 3:27 pm
Sean,
I am sorry,i get .cvs file from the client on monthly basis and i manipulate that in excel and give them the report. Sorry again.
December 1, 2014 at 8:20 pm
Deries (12/1/2014)
Sean,I am sorry,i get .cvs file from the client on monthly basis and i manipulate that in excel and give them the report. Sorry again.
Excellent! Post the CREATE TABLE statement and attach one of the files as a ZIP file. That way, we can also show you how to import the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2014 at 8:28 pm
P.S. Using a UNIQUEIDENTIFIER as the Clustered PK of a table is "Death by page splits" on steroids!. Also, using a "CustomerName" in that table is also pretty bad. It would be much better to have a customer table that contained unique, narrow, immutable, ever-increasing ID and the "CustomerName" and then use that id in your reading table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2014 at 8:34 pm
Jeff,
Could you show me some sample sql statement to create like you mentioned above.
The table structure which I gave stores the meter data for each customers on an hourly basis and the company is using this table since 8yrs don't know who created it and am new... Appreciate if you could guide me on how to proceed, would be of great help for my learning. Thank you for your reply.
December 1, 2014 at 8:51 pm
Deries (12/1/2014)
Jeff,Could you show me some sample sql statement to create like you mentioned above.
The table structure which I gave stores the meter data for each customers on an hourly basis and the company is using this table since 8yrs don't know who created it and am new... Appreciate if you could guide me on how to proceed, would be of great help for my learning. Thank you for your reply.
From SSMS, open the "Explorer" window if it's not already open. You can press the {f8} key to do that. Drill town to the table and right click on it. A menu will appear. Select the {Script Table as}, {Create to}, {New Query Editor Window}. You can copy and paste the code that appears to the forum.
As for the table itself, my comments stand even though they've been using that table for quite a while. Still, that's not going to deter us from trying to help you through this.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2014 at 9:02 pm
Here the code:
USE [METERS]
GO
/****** Object: Table [dbo].[IntegratedHour_MV90] Script Date: 12/1/2014 11:00:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IntegratedHour_MV90](
[IntegratedHourId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ReadingDate] [datetime] NOT NULL,
[IntegratedHour] [tinyint] NOT NULL,
[IntegratedUsage] [decimal](18, 4) NULL,
[IntegratedGeneration] [decimal](18, 4) NULL,
[ReadingsIncluded] [tinyint] NULL,
[LastModified] [datetime] NULL,
[ModifiedBy] [nvarchar](50) NULL,
[DataEntryComments] [nvarchar](200) NULL,
CONSTRAINT [PK_IntegratedHour_MV90] PRIMARY KEY NONCLUSTERED
(
[IntegratedHourId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedHourId] DEFAULT (newid()) FOR [IntegratedHourId]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedUsage] DEFAULT (0) FOR [IntegratedUsage]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedGeneration] DEFAULT (0) FOR [IntegratedGeneration]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_ReadingsIncluded] DEFAULT (0) FOR [ReadingsIncluded]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_LastModified] DEFAULT (getdate()) FOR [LastModified]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_ModifiedBy] DEFAULT (suser_sname()) FOR [ModifiedBy]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] WITH NOCHECK ADD CONSTRAINT [FK_IntegratedHour_MV90_Customers] FOREIGN KEY([CustomerName])
REFERENCES [dbo].[Customers] ([CustomerName])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] NOCHECK CONSTRAINT [FK_IntegratedHour_MV90_Customers]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of readings included in usage/gen' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IntegratedHour_MV90', @level2type=N'COLUMN',@level2name=N'ReadingsIncluded'
GO
December 1, 2014 at 9:06 pm
Here is the code:
USE [METERS]
GO
/****** Object: Table [dbo].[IntegratedHour_MV90] Script Date: 12/1/2014 11:00:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IntegratedHour_MV90](
[IntegratedHourId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ReadingDate] [datetime] NOT NULL,
[IntegratedHour] [tinyint] NOT NULL,
[IntegratedUsage] [decimal](18, 4) NULL,
[IntegratedGeneration] [decimal](18, 4) NULL,
[ReadingsIncluded] [tinyint] NULL,
[LastModified] [datetime] NULL,
[ModifiedBy] [nvarchar](50) NULL,
[DataEntryComments] [nvarchar](200) NULL,
CONSTRAINT [PK_IntegratedHour_MV90] PRIMARY KEY NONCLUSTERED
(
[IntegratedHourId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedHourId] DEFAULT (newid()) FOR [IntegratedHourId]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedUsage] DEFAULT (0) FOR [IntegratedUsage]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_IntegratedGeneration] DEFAULT (0) FOR [IntegratedGeneration]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_ReadingsIncluded] DEFAULT (0) FOR [ReadingsIncluded]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_LastModified] DEFAULT (getdate()) FOR [LastModified]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] ADD CONSTRAINT [DF_IntegratedHour_MV90_ModifiedBy] DEFAULT (suser_sname()) FOR [ModifiedBy]
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] WITH NOCHECK ADD CONSTRAINT [FK_IntegratedHour_MV90_Customers] FOREIGN KEY([CustomerName])
REFERENCES [dbo].[Customers] ([CustomerName])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[IntegratedHour_MV90] NOCHECK CONSTRAINT [FK_IntegratedHour_MV90_Customers]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of readings included in usage/gen' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IntegratedHour_MV90', @level2type=N'COLUMN',@level2name=N'ReadingsIncluded'
GO
also attached the data (.zip)
December 2, 2014 at 12:55 pm
Deries (12/1/2014)
. We dont have any other table or column to show the off Peak or on Peak values. At present I do the, the manual calculations for onPeak & offpeak in excel. Attached the snapshot of the report, all the data on the report were from SQL DB.
I was suggesting that you create TWO new tables which you will only need to populate once
CREATE TABLE dbo.BillingDay(
ID int identity(1,1),
CalendarDate Datetime NOT NULL, -- use DATE if on 2008 onwards
Season varchar(20) NOT NULL,
IsWeekDay int NOT NULL,
IsPublicHoliday int NOT NULL
)
Then populate it with the correct data:
INSERT INTO dbo.BillingDay (CalendarDate,Season,IsWeekDay,IsPublicHoliday) VALUES ('2014-01-01','Winter',0,1)
INSERT INTO dbo.BillingDay (CalendarDate,Season,IsWeekDay,IsPublicHoliday) VALUES ('2014-01-02','Winter',0,0)
INSERT INTO dbo.BillingDay (CalendarDate,Season,IsWeekDay,IsPublicHoliday) VALUES ('2014-01-03','Winter',1,0)
...
INSERT INTO dbo.BillingDay (CalendarDate,Season,IsWeekDay,IsPublicHoliday) VALUES ('2014-04-01','Summer',0,0)
...
Now create another table
CREATE TABLE dbo.BillingHour(
ID int identity(1,),
Hour int NOT NULL, -- this is INT because it is INT in your import table
Season varchar(20) NOT NULL,
IsWeekDay int NOT NULL,
IsPublicHoliday int NOT NULL,
IsPeak int NOT NULL
)
and populate with the relevant data
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (1,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (2,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (3,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (4,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (5,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (6,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (7,'Winter',1,0,0)
INSERT INTO dbo.BillingHour(Hour, Season, IsWeekDay, IsPublicHoliday, IsPeak) VALUES (8,'Winter',1,0,1)
...
You will need 24 (hours) X 2 (seasons) X 2 (isWeekDay) X 2 (IsPublicHoliday) = 192 records. This will cover all hourly points for all seasons and whether this is to be considered peak.
From your incoming table, use the reading date to find the correct dbo.BillingDay record. This will tell you the season and whether it is weekday/weekend or Public holiday. You can then use the billing hour from the source table and the season/weekday/holiday combination to find the correct dbo.BillingHour record and determine whether this is a PEAK hour or not.
December 3, 2014 at 12:44 pm
I created two tables as you mentioned above Billingday & BillingHour. Also inserted records for both the table.
Could you help me with a sample SQL query to display the data that shows the followings (All in one):
1. sum of all hourly data for the month, customer
2. sum of onPeak data for the month, customer
3. sum of off peak value for the month, customer
4. max of onpeak for the month, customer
5. min of off-peak for the selected month, customer.
Appreciate you doing the help so far. Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply