June 26, 2015 at 8:30 am
Hi Experts
I want to write a query on hour basis.
My time always starts from 0:00 hrs and end on 24:00 hrs.
1. I want to subtract the data on hourly basis 00:00 hrs ( - Minus) 1:00 hr then 2:00 hr subtract 1:00 hr data like in attached sheet column F.
2. I need to subtract all hours data from 0:00 hrs., like 02:00 hrs. data subtract 00:00 hrs. , 03:00 hrs. data subtract 00:00 hrs data as like in attached workbook (sheet) column G.
Your help appreciated
June 26, 2015 at 8:53 am
farrukhhameed786 (6/26/2015)
Hi ExpertsI want to write a query on hour basis.
My time always starts from 0:00 hrs and end on 24:00 hrs.
1. I want to subtract the data on hourly basis 00:00 hrs ( - Minus) 1:00 hr then 2:00 hr subtract 1:00 hr data like in attached sheet column F.
2. I need to subtract all hours data from 0:00 hrs., like 02:00 hrs. data subtract 00:00 hrs. , 03:00 hrs. data subtract 00:00 hrs data as like in attached workbook (sheet) column G.
Your help appreciated
can you provide the spreadsheet data as a SQL create table / insert data script.....that way people can easily paste into SSMS to start work on a solution for you.
your spreadsheet shows date and time as separate columns...is this correct or just how you have shown the data?
you have posted in SQL 2008 forum...please confirm this is the SQL version you are using.....reason being running sums such as this are much improve in later versions
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2015 at 9:43 am
Hi
You mean the below table script?
In spread sheet the data and time is separate, yes I want to show it separately. The date and time is together in database.
INSERT INTO [TEST1].[dbo].[ITEM_VOL]
([EVENT_GROUP_ID]
,[ITEM_ID]
,[EVENT_TYPE]
,[PERIOD]
,[START_DATETIME]
,[END_DATETIME]
,[VAL1] ---- spread sheet column C ( sum of VAL2 and VAL 3)
,[VAL2] ---- spread sheet column D
,[VAL3]) ---- spread sheet column E
VALUES
(<EVENT_GROUP_ID, char(32),>
,<ITEM_ID, char(32),>
,<EVENT_TYPE, varchar(14),>
,<PERIOD, varchar(20),>
,<START_DATETIME, datetime,>
,<END_DATETIME, datetime,>
,<VAL1, numeric(28,12),>
,<VAL2, numeric(28,12),>
,<VAL3, numeric(28,12),>)
June 26, 2015 at 9:51 am
farrukhhameed786 (6/26/2015)
HiYou mean the below table script?
In spread sheet the data and time is separate, yes I want to show it separately. The date and time is together in database.
INSERT INTO [TEST1].[dbo].[ITEM_VOL]
([EVENT_GROUP_ID]
,[ITEM_ID]
,[EVENT_TYPE]
,[PERIOD]
,[START_DATETIME]
,[END_DATETIME]
,[VAL1] ---- spread sheet column C ( sum of VAL2 and VAL 3)
,[VAL2] ---- spread sheet column D
,[VAL3]) ---- spread sheet column E
VALUES
(<EVENT_GROUP_ID, char(32),>
,<ITEM_ID, char(32),>
,<EVENT_TYPE, varchar(14),>
,<PERIOD, varchar(20),>
,<START_DATETIME, datetime,>
,<END_DATETIME, datetime,>
,<VAL1, numeric(28,12),>
,<VAL2, numeric(28,12),>
,<VAL3, numeric(28,12),>)
not exactly......where is the data to insert?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2015 at 10:15 am
farrukhhameed786 (6/26/2015)
HiYou mean the below table script?
In spread sheet the data and time is separate, yes I want to show it separately. The date and time is together in database.
INSERT INTO [TEST1].[dbo].[ITEM_VOL]
([EVENT_GROUP_ID]
,[ITEM_ID]
,[EVENT_TYPE]
,[PERIOD]
,[START_DATETIME]
,[END_DATETIME]
,[VAL1] ---- spread sheet column C ( sum of VAL2 and VAL 3)
,[VAL2] ---- spread sheet column D
,[VAL3]) ---- spread sheet column E
VALUES
(<EVENT_GROUP_ID, char(32),>
,<ITEM_ID, char(32),>
,<EVENT_TYPE, varchar(14),>
,<PERIOD, varchar(20),>
,<START_DATETIME, datetime,>
,<END_DATETIME, datetime,>
,<VAL1, numeric(28,12),>
,<VAL2, numeric(28,12),>
,<VAL3, numeric(28,12),>)
The idea here is to provide code so we can load this into our test database so we can work on your issue. Take a look at this article. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url] or take a look at the first link 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/
June 26, 2015 at 11:04 am
J Livingston SQL (6/26/2015)
farrukhhameed786 (6/26/2015)
HiYou mean the below table script?
In spread sheet the data and time is separate, yes I want to show it separately. The date and time is together in database.
INSERT INTO [TEST1].[dbo].[ITEM_VOL]
([EVENT_GROUP_ID]
,[ITEM_ID]
,[EVENT_TYPE]
,[PERIOD]
,[START_DATETIME]
,[END_DATETIME]
,[VAL1] ---- spread sheet column C ( sum of VAL2 and VAL 3)
,[VAL2] ---- spread sheet column D
,[VAL3]) ---- spread sheet column E
VALUES
(<EVENT_GROUP_ID, char(32),>
,<ITEM_ID, char(32),>
,<EVENT_TYPE, varchar(14),>
,<PERIOD, varchar(20),>
,<START_DATETIME, datetime,>
,<END_DATETIME, datetime,>
,<VAL1, numeric(28,12),>
,<VAL2, numeric(28,12),>
,<VAL3, numeric(28,12),>)
not exactly......where is the data to insert?
as a guide...here is one way.....amend accordingly for you data
USE [tempdb]
GO
CREATE TABLE [dbo].[ITEM_VOL](
[EVENT_GROUP_ID] [char](32) NULL,
[ITEM_ID] [char](32) NULL,
[EVENT_TYPE] [varchar](14) NULL,
[PERIOD] [varchar](20) NULL,
[START_DATETIME] [datetime] NULL,
[END_DATETIME] [datetime] NULL,
[VAL_1] [numeric](28, 12) NULL,
[VAL_2] [numeric](28, 12) NULL,
[VAL_3] [numeric](28, 12) NULL
)
GO
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD], [START_DATETIME], [END_DATETIME], [VAL_1], [VAL_2], [VAL_3]) VALUES ('11', '22', 'ab', '55', '2015-05-01 00:00', '2015-05-01 00:59', 10, 20, 30)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD], [START_DATETIME], [END_DATETIME], [VAL_1], [VAL_2], [VAL_3]) VALUES ('11', '22', 'ab', '55', '2015-05-01 01:00', '2015-05-01 01:59' , 10, 20, 30)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 26, 2015 at 12:33 pm
Hi Experts !
Actually the data inserted in attached screenshot , I need to make a query to use this data to get Column F & G results ( attached on previous post spreadsheet) on hourly based. I am sorry if I cannot post correctly.
June 26, 2015 at 12:39 pm
farrukhhameed786 (6/26/2015)
Hi Experts !Actually the data inserted in attached screenshot , I need to make a query to use this data to get Column F & G results ( attached on previous post spreadsheet) on hourly based. I am sorry if I cannot post correctly.
You need to post this so that we don't have to type in your data. We are volunteers around here and I have no interest in spending 30 minutes creating what should take you about 5 so that I can donate my time helping you with solution so that you can get paid.
_______________________________________________________________
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/
June 26, 2015 at 12:54 pm
Hi Sean
First of all I apologized may be my way of writing not correct !
Actually data is already inside database I want to write the sql query to get the results as in spreadsheet column F & G .
I am extremely sorry you consume lot of time (:
Kindly close this thread.
June 26, 2015 at 2:32 pm
farrukhhameed786 (6/26/2015)
Hi SeanFirst of all I apologized may be my way of writing not correct !
Actually data is already inside database I want to write the sql query to get the results as in spreadsheet column F & G .
I am extremely sorry you consume lot of time (:
Kindly close this thread.
Yes we know the data is in your database. That is the point. We need the data in OUR database so we have something to test with. I didn't waste any time here because I was not going to recreate guids and such by hand. I will be happy to help you if you can post ddl and sample in a consumable format as suggested previously and in both of the articles I referenced.
_______________________________________________________________
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/
June 27, 2015 at 6:19 am
Hi Sean,
Thanks
Please find in the attachment Create Table.sql,Insert Script.sql and sample new.xlsx ( contains results).
I hope not miss any thing.
June 29, 2015 at 7:18 am
farrukhhameed786 (6/27/2015)
Hi Sean,Thanks
Please find in the attachment Create Table.sql,Insert Script.sql and sample new.xlsx ( contains results).
I hope not miss any thing.
OK we are getting closer. The file contains a nice clean script to create the table and populate it with data. I don't typically download and open excel documents from somebody I don't know. Call me paranoid but those things are scary. As a result I have no idea what you want as output.
_______________________________________________________________
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/
June 29, 2015 at 8:05 am
Hi Sean,
Do not be scary some time knowing is not important but trust !
For future about output column F & G in excel having calculation results.
June 29, 2015 at 9:55 am
That's weird Sean, I would be more scared of rar files.
About the solution, I'm not sure if you got confused with the dates because the values with zero hours don't seem to follow the logic of the other rows.
WITH CTE AS(
SELECT a.[EVENT_GROUP_ID]
,a.[ITEM_ID]
,a.[EVENT_TYPE]
,a.[PERIOD]
,a.[ITEM_NAME]
,a.[START_DATETIME]
,a.[END_DATETIME]
,a.[VAL1]
,a.[VAL2]
,a.[VAL3]
,ROW_NUMBER() OVER (ORDER BY START_DATETIME) rn
FROM [dbo].[ITEM_VOL] a
)
SELECT a.[EVENT_GROUP_ID]
,a.[ITEM_ID]
,a.[EVENT_TYPE]
,a.[PERIOD]
,a.[ITEM_NAME]
,a.[START_DATETIME]
,a.[END_DATETIME]
,a.[VAL1]
,a.[VAL2]
,a.[VAL3]
,a.VAL1 - b.VAL1
,a.VAL1 - MIN(a.VAL1) OVER()
FROM CTE a
LEFT
JOIN CTE b ON a.rn = b.rn + 1
June 29, 2015 at 10:00 am
Luis Cazares (6/29/2015)
That's weird Sean, I would be more scared of rar files.
Heh, well I have a VM with winrar on it but it doesn't have office. I was able to open the rar file with no issue but can't open the excel file from there. 😀
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply