April 24, 2012 at 1:21 pm
Read about Tally Tables, fourth reference in my signature block below. Start there and as you have questions, ask.
April 25, 2012 at 12:55 am
How's about something like this:
DECLARE @abc TABLE (Date DATETIME, Amount INT, Category VARCHAR(10))
INSERT INTO @abc SELECT '2012-04-13',1000,'a'
UNION ALL SELECT '2012-04-12',500,'b' UNION ALL SELECT '2012-04-11',7000,'c'
UNION ALL SELECT '2012-04-10',650,'c' UNION ALL SELECT '2012-04-09',600,'d'
UNION ALL SELECT '2012-04-05',500,'a' UNION ALL SELECT '2012-04-04',550,'z'
;WITH Tally(n) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)
,AllDates AS (
SELECT Date, Amount, Category
,ROW_NUMBER() OVER (PARTITION BY (SELECT NULL) ORDER BY Date) As rk
FROM @abc)
SELECT DATEADD(day, n, a1.Date) As Date, a1.Amount, a1.Category
FROM AllDates a1
CROSS APPLY (SELECT n FROM Tally WHERE n < DATEDIFF(day, a1.Date,
ISNULL((SELECT Date FROM AllDates a2 WHERE a1.rk+1 = a2.rk),DATEADD(day,1,a1.Date)))) x
You would of course need the Tally CTE to include as many n's as you need to cover the expected holiday gap.
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
April 25, 2012 at 1:18 am
I hope you really are like yoda. I am in need of a SQL master to guide this noobie padawan learner
I feel the same way.
I somehow managed to get into SQL coding without really understand the fundamentals so I may know how to do something but I dont really know what I am doing
Take a look at this thread for advice:
http://qa.sqlservercentral.com/Forums/Topic1285281-1292-1.aspx
Case in point, I am trying right now to break down and understand the above solution
So am I.
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
April 25, 2012 at 8:18 am
I like it.
April 25, 2012 at 8:21 am
Hi,
I prepared the following steps of my solution, when i struggle to understand a query i break it down to the very fundation, then i add one thing at the time to see the results it produces.
Run this , step by step, and you will see better what the query is trying to do.
-- Step 1
;WITH dates
AS (
SELECT id = row_number() OVER (ORDER BY DATE),* FROM abc
)
-- The dates receives numeration
select * from dates
-- Step 2
;WITH dates
AS (
SELECT id = row_number() OVER (ORDER BY DATE),* FROM abc
)
-- Put dates in pairs according their numeration and see how many days are between them.
SELECT do.Date,
NexDate = ds.Date,
DaysBetween = datediff(day, do.DATE, ds.DATE),
amount = do.amount,
category = do.category
FROM dates do
LEFT JOIN dates ds ON do.id = ds.id - 1
-- Step 3
;WITH dates
AS (
SELECT id = row_number() OVER (ORDER BY DATE),* FROM abc
)
-- Replicate the rows that has more than 1 DaysBetween
SELECT do.Date,
NexDate = ds.Date,
DaysBetween = datediff(day, do.DATE, ds.DATE),
Number,
amount = do.amount,
category = do.category
FROM dates do
LEFT JOIN dates ds ON do.id = ds.id - 1
LEFT JOIN numbers n ON number BETWEEN 1 AND datediff(day, do.DATE, ds.DATE)
-- Step 4
;WITH dates
AS (
SELECT id = row_number() OVER (ORDER BY DATE),* FROM abc
)
-- Calculate the real date based on the replicated day number
SELECT [Date] = dateadd(day, number - 1, do.DATE),
amount = do.amount,
category = do.category
FROM dates do
LEFT JOIN dates ds ON do.id = ds.id - 1
LEFT JOIN numbers n ON number BETWEEN 1 AND datediff(day, do.DATE, ds.DATE)
-- Step 5
;WITH dates
AS (
SELECT id = row_number() OVER (ORDER BY DATE),* FROM abc
)
-- if number is null it means, we should add 0 to the bussines date
SELECT [Date] = dateadd(day, isnull(number - 1,0), do.DATE),
amount = do.amount,
category = do.category
FROM dates do
LEFT JOIN dates ds ON do.id = ds.id - 1
LEFT JOIN numbers n ON number BETWEEN 1 AND datediff(day, do.DATE, ds.DATE)
April 25, 2012 at 2:58 pm
adrian.facio (4/25/2012)
Hi,I prepared the following steps of my solution, when i struggle to understand a query i break it down to the very fundation, then i add one thing at the time to see the results it produces.
Run this , step by step, and you will see better what the query is trying to do.
Thanks everybody who took the time to comment either with a solution,explanation or gameplan
Big Shout out to Adrian and Lynn !!!!!!!!
ty all for not being SQL snobs 😉
April 30, 2012 at 3:17 am
---------U need to use a fetch row sintax
---------I did a quick script for u , u need to do something similar.
-- Declare the variables to store the values returned by FETCH.
DECLARE @date datetime, @amount varchar(50),@category int ;
DECLARE contact_cursor CURSOR FOR
SELECT date, amount,category FROM Table1
ORDER BY date asc;
OPEN contact_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
declare @datemin datetime
set @datemin=(select min(date) from Table1 )
---create a Table 2 and insert first row from Table 1
select * into Table2
from Table1 where date=@datemin
FETCH NEXT FROM contact_cursor
INTO @date, @amount,category;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
declare @datemax datetime
declare @date_to_insert datetime
set @datemax=(select max(date) from Table2 )
declare @amount_to_insert varchar(50)
declare @category_to_insert int
set @amount_to_insert=(select amount from Table2 where date = @datemax)
set @category_to_insert=(select category from Table2 where date = @datemax)
if @date > dateadd(day,1,@datamax)
begin --if
@date_to_insert= @date
while @date_to_insert> dateadd(day,1,@datamax)
begin--while
insert into Table2 values dateadd(day,1,@datamax),@amount_to_insert,@category_to_insert
@datamax=dateadd(day,1,@datamax)
end --while
end --if
else --
begin
delete from table2 where date=@date---- to get rid of the first row that we insert
insert into Table2
select * from Table1 where date=@date
end--
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM contact_cursor
INTO @date, @amount,category;
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
select * from Table2
order by date desc
drop Table2--if u dont need it
--- Thats all
April 30, 2012 at 6:33 am
tysonlibelula (4/30/2012)
---------U need to use a fetch row sintax
Actually, you don't. A Cursor or While Loop is hardly ever necessary and will almost always be much slower than its properly written setbased cousins. Of course, it'll blow the doors off of improperly written "setbased" code. 🙂
--Jeff Moden
April 30, 2012 at 6:49 am
dwain.c (4/25/2012)
How's about something like this:
DECLARE @abc TABLE (Date DATETIME, Amount INT, Category VARCHAR(10))
INSERT INTO @abc SELECT '2012-04-13',1000,'a'
UNION ALL SELECT '2012-04-12',500,'b' UNION ALL SELECT '2012-04-11',7000,'c'
UNION ALL SELECT '2012-04-10',650,'c' UNION ALL SELECT '2012-04-09',600,'d'
UNION ALL SELECT '2012-04-05',500,'a' UNION ALL SELECT '2012-04-04',550,'z'
;WITH Tally(n) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)
,AllDates AS (
SELECT Date, Amount, Category
,ROW_NUMBER() OVER (PARTITION BY (SELECT NULL) ORDER BY Date) As rk
FROM @abc)
SELECT DATEADD(day, n, a1.Date) As Date, a1.Amount, a1.Category
FROM AllDates a1
CROSS APPLY (SELECT n FROM Tally WHERE n < DATEDIFF(day, a1.Date,
ISNULL((SELECT Date FROM AllDates a2 WHERE a1.rk+1 = a2.rk),DATEADD(day,1,a1.Date)))) x
You would of course need the Tally CTE to include as many n's as you need to cover the expected holiday gap.
You might want to take a closer look at this. It does a full cross join between the Tally cte and the number of the rows in the table twice and doubles the number of those rows once. It won't take very many rows for this to explode into a hidden RBAR monster.
--Jeff Moden
April 30, 2012 at 7:15 pm
Thought I could slip one past old Eagle Eye! Guess I was wrong.
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
April 30, 2012 at 8:06 pm
dwain.c (4/30/2012)
Thought I could slip one past old Eagle Eye! Guess I was wrong.
Perhaps a more direct approach... pick your favorite zero-based "Tally Table" method.
WITH
cteCount AS
(
SELECT t1.Date,
t1.Amount,
t1.Category,
Days = ISNULL((SELECT TOP (1) DATEDIFF(dd,t1.Date,Date)-1 FROM @abc WHERE Date > t1.Date ORDER BY Date),0)
FROM @abc t1
)
SELECT Date = DATEADD(dd,t.N,cte.Date),
cte.Amount,
cte.Category
FROM cteCount cte
INNER JOIN dbo.Tally t ON t.N BETWEEN 0 AND Days
ORDER BY cte.Date,t.N
;
--Jeff Moden
April 30, 2012 at 8:08 pm
I'm beginning to remember why I tried to slip that one past you.
Surely you can't be suggesting something like this (using my simulated Calendar table). While it works, I'm having a hard time figuring out how it could be more efficient.
DECLARE @abc TABLE (Date DATETIME, Amount INT, Category VARCHAR(10))
INSERT INTO @abc SELECT '2012-04-13',1000,'a'
UNION ALL SELECT '2012-04-12',500,'b' UNION ALL SELECT '2012-04-11',7000,'c'
UNION ALL SELECT '2012-04-10',650,'c' UNION ALL SELECT '2012-04-09',600,'d'
UNION ALL SELECT '2012-04-05',500,'a' UNION ALL SELECT '2012-04-04',550,'z'
SELECT * FROM @abc
DECLARE @start DATETIME, @end DATETIME
SELECT @start = MIN(Date), @end = MAX(Date) FROM @abc
;WITH Nbrs_2 (n) AS (SELECT 1 UNION SELECT 0)
,Nbrs_1 (n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2)
,Nbrs_0 (n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2)
,Nbrs (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2)
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs)
,Calendar (d) As (SELECT DATEADD(dd, n-1, @start)
FROM Tally WHERE DATEADD(dd, n-1, @start) BETWEEN @start and @end)
SELECT d
,CASE WHEN Date IS NULL
THEN (SELECT TOP 1 Amount FROM @abc WHERE d>Date ORDER BY Date DES) ELSE Amount END As Amount
,CASE WHEN Date IS NULL
THEN (SELECT TOP 1 Category FROM @abc WHERE d>Date ORDER BY Date DES) ELSE Category END As Category
FROM Calendar
LEFT OUTER JOIN @abc a ON d=Date
Teach me o'Master so I shall learn. The Force was not with me today.
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
April 30, 2012 at 8:14 pm
Jeff Moden (4/30/2012)
dwain.c (4/30/2012)
Thought I could slip one past old Eagle Eye! Guess I was wrong.Perhaps a more direct approach... pick your favorite zero-based "Tally Table" method.
WITHcteCount AS
(
SELECT t1.Date,
t1.Amount,
t1.Category,
Days = ISNULL((SELECT TOP (1) DATEDIFF(dd,t1.Date,Date)-1 FROM @abc WHERE Date > t1.Date ORDER BY Date),0)
FROM @abc t1
)
SELECT Date = DATEADD(dd,t.N,cte.Date),
cte.Amount,
cte.Category
FROM cteCount cte
INNER JOIN dbo.Tally t ON t.N BETWEEN 0 AND Days
ORDER BY cte.Date,t.N
;
When I added a Tally table and ran this it only produced the 3 missing records.
Now the challenge. Execution plan cost is showing this to be the worst of the 3 options. Time to whip out that million row test harness.
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
April 30, 2012 at 8:26 pm
dwain.c (4/30/2012)
Jeff Moden (4/30/2012)
dwain.c (4/30/2012)
Thought I could slip one past old Eagle Eye! Guess I was wrong.Perhaps a more direct approach... pick your favorite zero-based "Tally Table" method.
WITHcteCount AS
(
SELECT t1.Date,
t1.Amount,
t1.Category,
Days = ISNULL((SELECT TOP (1) DATEDIFF(dd,t1.Date,Date)-1 FROM @abc WHERE Date > t1.Date ORDER BY Date),0)
FROM @abc t1
)
SELECT Date = DATEADD(dd,t.N,cte.Date),
cte.Amount,
cte.Category
FROM cteCount cte
INNER JOIN dbo.Tally t ON t.N BETWEEN 0 AND Days
ORDER BY cte.Date,t.N
;
When I added a Tally table and ran this it only produced the 3 missing records.
Now the challenge. Execution plan cost is showing this to be the worst of the 3 options. Time to whip out that million row test harness.
Here is the Tally Table I used.
--===================================================================
-- 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.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
Here is the test data I used... do note the index.
DECLARE @abc TABLE (Date DATETIME PRIMARY KEY, Amount INT, Category VARCHAR(10))
INSERT INTO @abc SELECT '2012-04-13',1000,'a'
UNION ALL SELECT '2012-04-12',500,'b' UNION ALL SELECT '2012-04-11',7000,'c'
UNION ALL SELECT '2012-04-10',650,'c' UNION ALL SELECT '2012-04-09',600,'d'
UNION ALL SELECT '2012-04-05',500,'a' UNION ALL SELECT '2012-04-04',550,'z'
;
Here's the solution I used...
WITH
cteCount AS
(
SELECT t1.Date,
t1.Amount,
t1.Category,
Days = ISNULL((SELECT TOP (1) DATEDIFF(dd,t1.Date,Date)-1 FROM @abc WHERE Date > t1.Date ORDER BY Date),0)
FROM @abc t1
)
SELECT Date = DATEADD(dd,t.N,cte.Date),
cte.Amount,
cte.Category
FROM cteCount cte
INNER JOIN dbo.Tally t ON t.N BETWEEN 0 AND Days
ORDER BY cte.Date,t.N
;
Here are the results I got...
Date Amount Category
----------------------- ----------- ----------
2012-04-04 00:00:00.000 550 z
2012-04-05 00:00:00.000 500 a
2012-04-06 00:00:00.000 500 a
2012-04-07 00:00:00.000 500 a
2012-04-08 00:00:00.000 500 a
2012-04-09 00:00:00.000 600 d
2012-04-10 00:00:00.000 650 c
2012-04-11 00:00:00.000 7000 c
2012-04-12 00:00:00.000 500 b
2012-04-13 00:00:00.000 1000 a
What did you do differently to get only 3 days of results?
--Jeff Moden
April 30, 2012 at 8:30 pm
Not sure what I did wrong but I ran this (same test data table @abc):
;WITH Nbrs_2 (n) AS (SELECT 1 UNION SELECT 0)
,Nbrs_1 (n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2)
,Nbrs_0 (n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2)
,Nbrs (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2)
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) As n FROM Nbrs)
,cteCount AS
(
SELECT t1.Date,
t1.Amount,
t1.Category,
Days = ISNULL((SELECT TOP (1) DATEDIFF(dd,t1.Date,Date)-1 FROM @abc WHERE Date > t1.Date ORDER BY Date),0)
FROM @abc t1
)
SELECT Date = DATEADD(dd,t.N,cte.Date),
cte.Amount,
cte.Category
FROM cteCount cte
INNER JOIN Tally t ON t.N BETWEEN 0 AND Days
ORDER BY cte.Date,t.N
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
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply