November 1, 2012 at 5:09 am
Hi,
I've got quite an unusual question.
I'm running this simple query:
SELECT SourceHub, SnapshotDate, NetworkName, QOSGroup, CountOfRemotes, SumOfTX, SumOfRX
FROM ConfigurationSnapshot
this is the some of the data I get:
SourceHub SnapshotDate NetworkName QOSGroup
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network Advantage
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network Content Oasis Optimize
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network IT telecom
you can see that the snapshotdate is a round date (in this case 2012-08-29).
I want the data to be represented every minute. I mean that the first row in this example will appear 1440 times in this timestamp: 2012-08-29 00:00:00 the next after that is 2012-08-29 00:01:00 and than 2012-08-29 00:02:00 and so on...
is it possible?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 1, 2012 at 5:17 am
Is it what you want
declare @temptable table
(
datecol date
)
insert into @temptable
select * from
(
values
('10/28/2012'),
('10/28/2012'),
('10/28/2012'),
('10/28/2012'),
('10/28/2012'),
('10/28/2012')
)a (datecol)
select
dateadd(MI,ROW_NUMBER() over (order by datecol)-1,CONVERT(Datetime,datecol)) as DateTimecol
from
@temptable
November 1, 2012 at 5:38 am
Thanks Bri,
can u plz explain why you put -1 in query?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 1, 2012 at 5:41 am
ohhhh I am clear with that now... 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 1, 2012 at 5:43 am
Just Becuase Row_Number() starts with 1 and in 1st row you want to start with 0 min
so -1 in each row
November 1, 2012 at 6:44 pm
Perhaps it is something like this that you seek:
;WITH Dates AS (
SELECT MyDate
FROM (
VALUES ('2012-10-28'),('2012-10-29'),('2012-10-30')) a (MyDate)),
Tally (n) AS (
SELECT TOP (1440) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1 (n1)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2 (n2)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t3 (n3)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t4 (n4))
SELECT MyDate, DateWithMinute=DATEADD(minute, n, MyDate)
FROM Dates
CROSS APPLY Tally
ORDER BY DATEADD(minute, n, MyDate)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 1, 2012 at 8:01 pm
kapil_kk (11/1/2012)
Hi,I've got quite an unusual question.
I'm running this simple query:
SELECT SourceHub, SnapshotDate, NetworkName, QOSGroup, CountOfRemotes, SumOfTX, SumOfRX
FROM ConfigurationSnapshot
this is the some of the data I get:
SourceHub SnapshotDate NetworkName QOSGroup
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network Advantage
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network Content Oasis Optimize
nms-Crawford-nss10-T11 2012-08-29 Evolution_AMC12_Network IT telecom
you can see that the snapshotdate is a round date (in this case 2012-08-29).
I want the data to be represented every minute. I mean that the first row in this example will appear 1440 times in this timestamp: 2012-08-29 00:00:00 the next after that is 2012-08-29 00:01:00 and than 2012-08-29 00:02:00 and so on...
is it possible?
Life can get real simple if you have the correct tools. The tool I'm using to make this particular query so easy is called a "Tally Table". (of course, untested because I don't have your tables on my machine)
SELECT SourceHub,
SnapshotMinute = DATEADD(mi,t.N,SnapshotDate),
NetworkName,
QOSGroup,
CountOfRemotes,
SumOfTX,
SumOfRX
FROM ConfigurationSnapshot cs
CROSS JOIN dbo.Tally0 t
WHERE t.Number < 1440
;
The Tallly Table used above is a "zero based" Tally Table running from 0 to 11,000 (total). Here's how to build one...
--===== Do this in a nice safe place that everyone has
USE TempDB;
IF OBJECT_ID('TempDB..Tally0','U') IS NOT NULL
DROP TABLE Tally0;
GO
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally0
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally0
ADD CONSTRAINT PK_Tally0_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally0 TO PUBLIC
;
GO
... and here's (at the following URL) what it is and how it can be used to easily replace certain types of WHILE loops in a big hurry.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Understanding how a Tally Table (or the equivalent like what Dwain built above) will change your professional database programming life. They make difficult code mere childs play and they blow the doors off of any loop or form of recursion you may care to try in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply