April 20, 2012 at 1:58 pm
Hi guys...... please treat me as a SQL noobie
I have a question.
Lets take a simple query
select date
,amount
,category
from abc
order by date desc
The desired output should look like this
Date Amount Category
4/13/2012 1000 a
4/12/2012 500 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/05/2012 500 a
Simple right....... Lets treat these days as business days. I need to now include non business days as well. (weekends and holidays)
The requirement is now that if the date does not exist, take the last known business day and copy that record and date it with the non business day
So using the information provided and using the date 4/06/2012 as an example, I would need to copy the record with 4/05/2012 and insert it as 4/06/2012. The same would apply to 04/07/2012 and 04/08/2012 that would need to "copy" the last known business date being 04/05/2012 and dating it with 04/07/2012 and 04/08/2012 respectively.
Summary:
I am creating a report in desc order by date
Possible Solutions:
Do i need to insert my result set into a temp table then use a cursor to step through each record and check if datediff(day,date,cursor date) > 1 ?? ( sorry my poor coding its been really long) If it is greater then 1 then I know it satisfies the condition of a "non business day"
Any sample code would be appreciated..... please be simple for i am a noobie and forgive me if i accidently insulted you SQL gurus out there with my ignorant question. 😀
April 20, 2012 at 2:53 pm
Have no fear, we don't bite. 😀 It is hard to know all the details from your description but it seems that you could use what is called a "calendar" table quite effectively for your requirements.
There is great article http://qa.sqlservercentral.com/articles/T-SQL/70482/[/url] that explains what a calendar table is and how to use it. After reading that article if you still have questions don't hesitate to ask.
Before posting questions you might want to take a look at the first link in my signature for best practices about how to post questions in a format that will generate the most response.
_______________________________________________________________
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/
April 23, 2012 at 7:42 am
Ty for your reply.
I think you are confused with what I am asking.
Basically all i want to do is copy a record(s).
How do I copy a row and change the date of that row?
Current Results
Date Amount Category
4/13/2012 1000 a
4/12/2012 500 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/05/2012 500 a
Take the above table or result set. I want this to display the missing dates of 4/6,4/7,4/8 with the last known business date being 4/05/2012.
How do I accomplish this?
Desired Results
Date Amount Category
4/13/2012 1000 a
4/12/2012 500 b
4/11/2012 700 c
4/10/2012 650 c
4/09/2012 600 d
4/08/2012 500 a
4/07/2012 500 a
4/06/2012 500 a
4/05/2012 500 a
April 23, 2012 at 8:03 am
You are correct that I am a bit confused by what you are trying to do. We need to see ddl (create table statements), sample data (insert statements), desired output based on your sample data.
Based on what you posted I still say a calendar table would be an easy way to accomplish what you are trying to do.
_______________________________________________________________
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/
April 23, 2012 at 11:06 am
As I said before you should look at the first link in my signature. There is no ddl and your sample data is not easily consumable.
The point is we are all volunteers around here and time spent setting up your question is time taken away from working on your solution.
_______________________________________________________________
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/
April 23, 2012 at 3:15 pm
I was interested in this problem so I made up some ddl. (I'm allowed to do this as I'm not a volunteer 😉 )
CREATE TABLE ABC
(
Date DATETIME,
Amount INT,
Category VARCHAR(10)
)
INSERT INTO ABC
SELECT '2012-04-13',1000,'a' UNION
SELECT '2012-04-12',500,'b' UNION
SELECT '2012-04-11',7000,'c' UNION
SELECT '2012-04-10',650,'c' UNION
SELECT '2012-04-09',600,'d' UNION
SELECT '2012-04-05',500,'a' UNION
SELECT '2012-04-04',550,'z'
CREATE TABLE DATETABLE
(
Date DATETIME
)
INSERT INTO DATETABLE
SELECT '2012-04-13' UNION
SELECT '2012-04-12' UNION
SELECT '2012-04-11' UNION
SELECT '2012-04-10' UNION
SELECT '2012-04-09' UNION
SELECT '2012-04-08' UNION
SELECT '2012-04-07' UNION
SELECT '2012-04-06' UNION
SELECT '2012-04-05' UNION
SELECT '2012-04-04'
Notice I added one more record for 4-04 as its needed to properly cover getting the latest value available (otherwise a right looking answer could fail when presented with multiple prior values to a blanked date)
Also note the cheezy date table.
So this is my take, I'm simply going to create a select statement for missing dates that include data for the latest date less than or equal to the missing date which could be then used as a source for an insert statement.
SELECT DATEIDX, AMOUNT, CATEGORY FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY DATEIDX ORDER BY DATEPART DESC) ROWID,
DATEIDX, AMOUNT, CATEGORY
FROM
(
SELECT
DATETABLE.DATE DATEIDX,
TEST.AMOUNT AMOUNT_TEST,
AGAIN.DATE DATEPART,
AGAIN.AMOUNT,
AGAIN.CATEGORY FROM
DATETABLE
LEFT JOIN
ABC TEST
ON
DATETABLE.DATE = TEST.DATE
JOIN
ABC AGAIN
ON
DATETABLE.DATE >= AGAIN.DATE
WHERE TEST.AMOUNT IS NULL
) TEMP_CALC
) COPIED_RECS
WHERE ROWID = 1
It seems to do the trick but it pretty much involves a triangular join. Is there any other way to effect this without the triangular join?
April 24, 2012 at 8:25 am
Hi, what do you think about this one?
-- A tally table is needed.
CREATE TABLE Numbers (Number INT)
INSERT INTO numbers
SELECT TOP 1000 row_number() OVER (
ORDER BY column_id
)
FROM sys.columns;
-- Number the rows
WITH dates
AS (
SELECT id = row_number() OVER (
ORDER BY DATE
),
*
FROM abc
)
-- Pair up bussines days and fill the gaps between them.
SELECT Date = isnull(dateadd(day, number - 1, do.DATE), 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 24, 2012 at 8:43 am
@adrian.facio
nice solution!
April 24, 2012 at 8:45 am
adrian.facio (4/24/2012)
Hi, what do you think about this one?-- A tally table is needed.
CREATE TABLE Numbers (Number INT)
INSERT INTO numbers
SELECT TOP 1000 row_number() OVER (
ORDER BY column_id
)
FROM sys.columns;
-- Number the rows
WITH dates
AS (
SELECT id = row_number() OVER (
ORDER BY DATE
),
*
FROM abc
)
-- Pair up bussines days and fill the gaps between them.
SELECT Date = isnull(dateadd(day, number - 1, do.DATE), 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)
+1
Good stuff.
_______________________________________________________________
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/
April 24, 2012 at 8:48 am
thanks man.
April 24, 2012 at 9:02 am
thanks man
April 24, 2012 at 12:55 pm
adrian.facio (4/24/2012)
Hi, what do you think about this one?-- A tally table is needed.
CREATE TABLE Numbers (Number INT)
INSERT INTO numbers
SELECT TOP 1000 row_number() OVER (
ORDER BY column_id
)
FROM sys.columns;
-- Number the rows
WITH dates
AS (
SELECT id = row_number() OVER (
ORDER BY DATE
),
*
FROM abc
)
-- Pair up bussines days and fill the gaps between them.
SELECT Date = isnull(dateadd(day, number - 1, do.DATE), 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)
Thank you so much for taking time and looking at my problem. This will help my problem.
I am just happy that people take time to respond to questions. I will be using this site from now on actively
p.s. how do I accept this quote as the right answer?
April 24, 2012 at 12:58 pm
wutang (4/24/2012)
adrian.facio (4/24/2012)
Hi, what do you think about this one?-- A tally table is needed.
CREATE TABLE Numbers (Number INT)
INSERT INTO numbers
SELECT TOP 1000 row_number() OVER (
ORDER BY column_id
)
FROM sys.columns;
-- Number the rows
WITH dates
AS (
SELECT id = row_number() OVER (
ORDER BY DATE
),
*
FROM abc
)
-- Pair up bussines days and fill the gaps between them.
SELECT Date = isnull(dateadd(day, number - 1, do.DATE), 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)
Thank you so much for taking time and looking at my problem. This will help my problem.
I am just happy that people take time to respond to questions. I will be using this site from now on actively
p.s. how do I accept this quote as the right answer?
Actually, you don't. SSC doesn't work that way. It is possible that someone else may come along, look at the problem and solution and use it, or they may have another way to solve it. These forums are pretty much open in that regard.
April 24, 2012 at 12:59 pm
wutang (4/24/2012)
Thank you so much for taking time and looking at my problem. This will help my problem.
I am just happy that people take time to respond to questions. I will be using this site from now on actively
p.s. how do I accept this quote as the right answer?
We operate a little differently around here. We don't mark answers as "correct". One of the main reasons for that is there may be something that is not quite right in your "answer" and if it goes unmentioned, others may decide it is fine for them and perpetuate the issue. The other advantage is that somebody may come look at this thread in a few days or even a few years and have a completely new take on how to solve the issue. As this community is always looking for new ways to tackle problems we don't "resolve" them.
We all welcome newcomers so I will say that we look forward to your upcoming questions. Keep 'em coming!!!
_______________________________________________________________
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/
April 24, 2012 at 1:14 pm
Lynn Pettis (4/24/2012)
wutang (4/24/2012)
adrian.facio (4/24/2012)
Hi, what do you think about this one?-- A tally table is needed.
CREATE TABLE Numbers (Number INT)
INSERT INTO numbers
SELECT TOP 1000 row_number() OVER (
ORDER BY column_id
)
FROM sys.columns;
-- Number the rows
WITH dates
AS (
SELECT id = row_number() OVER (
ORDER BY DATE
),
*
FROM abc
)
-- Pair up bussines days and fill the gaps between them.
SELECT Date = isnull(dateadd(day, number - 1, do.DATE), 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)
Thank you so much for taking time and looking at my problem. This will help my problem.
I am just happy that people take time to respond to questions. I will be using this site from now on actively
p.s. how do I accept this quote as the right answer?
Actually, you don't. SSC doesn't work that way. It is possible that someone else may come along, look at the problem and solution and use it, or they may have another way to solve it. These forums are pretty much open in that regard.
Linn,
I hope you really are like yoda. I am in need of a SQL master to guide this noobie padawan learner
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 :w00t:
Case in point, I am trying right now to break down and understand the above solution
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply