May 3, 2013 at 9:41 am
Hi,
I need an urgent help to get the data from previous record. I am able to calculate Cumulative data but data is not getting populated for missing months .Below is the table data
Date, Prj_ID, Amount
Jan12, 1, 1000
Feb12, 1, 1500
Apr12, 1, 1800
July12, 1, 1200
Jan12, 2, 500
Apr12, 2, 1000
The output i want is
Date, Prj_ID, Amount
Jan12, 1, 1000
Feb12, 1, 1500
Mar12, 1, 1500
Apr12, 1 , 1800
May12, 1, 1800
Jun12 , 1 , 1800
July12, 1 , 1200
Au12 , 1 , 1200
Sep12, 1 , 1200
Jan12, 2, 500
Feb12, 2, 500
Mar12, 2, 500
Apr12, 2, 1000
May12, 2, 1000
Jun12, 2, 1000
July12, 2, 1000
Au12, 2 , 1000
Sep12, 2, 1000
Thanks
May 3, 2013 at 10:00 am
You are going to need another table in order to do this. Probably a tally table will do the job but it is difficult to know for sure based on your post. Can you post ddl and sample data for your table?
_______________________________________________________________
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/
May 3, 2013 at 10:08 am
The first records set is the table data which is cumulative data, but i need to populate the data for the missing dates as well and insert new record set in another table.
May 3, 2013 at 10:11 am
Motz (5/3/2013)
The first records set is the table data which is cumulative data, but i need to populate the data for the missing dates as well and insert new record set in another table.
Unless you need this data to persist you don't need to insert this to another table. You can use a tally table.
I am willing and able to help but I need you to first help me by posting ddl (create table scripts) and some sample data (insert statements). Once you post that I can show you how you can easily do this with a tally table.
_______________________________________________________________
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/
May 3, 2013 at 10:12 am
By DDL and sample data, we mean you need to post something like this so people helping can have a table on their system to work with.
create table #test
(
mydatevarchar(6),
prj_idint,
amountnumeric(5)
)
insert #test
select 'Jan12', 1, 1000
union all select 'Feb12', 1, 1500
union all select 'Apr12', 1, 1800
union all select 'July12', 1, 1200
union all select 'Jan12', 2, 500
union all select 'Apr12', 2, 1000
Besides being potentially tedious work for the helper, they may not be able to give you correct help if they guess incorrectly as to what data types your columns are.
May 3, 2013 at 10:13 am
Thanks, for the reply, give me 5 - 10 mins I will give post the insert script
May 3, 2013 at 10:25 am
Below is the Create table script, and insert
CREATE TABLE Rev
(
PrjDate Datetime,
PRJ_ID varchar(7),
AMOUNT decimal(26,2)
)
INSERT INTO Rev
SELECT '2012-01-31', '1', 1000
UNION ALL
SELECT '2012-02-29', '1', 1500
UNION ALL
SELECT '2012-04-30', '1', 1800
UNION ALL
SELECT '2012-07-31', '1', 1200
UNION ALL
SELECT '2012-01-31', '2', 500
UNION ALL
SELECT '2012-04-30', '2', 1000
May 3, 2013 at 12:19 pm
Excellent job posting ddl and sample data. I am a little uncertain what you want for output. In your original post this is what you had for PRJ_ID 1
The output i want is
Date, Prj_ID, Amount
Jan12, 1, 1000
Feb12, 1, 1500
Mar12, 1, 1500
Apr12, 1 , 1800
May12, 1, 1800
Jun12 , 1 , 1800
July12, 1 , 1200
Au12 , 1 , 1200
Sep12, 1 , 1200
Is the data supposed to split by month? What defines the start and end months? In your example here you go to September but the data ends in July.
_______________________________________________________________
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/
May 3, 2013 at 12:35 pm
Thanks for the reply, the output u have copies is half.. please check thr is also PRJ_ID = 2 and have data till sept12
the data I want is for last 24 months. and PRJDATE will column always have last day of the month
May 3, 2013 at 12:41 pm
Motz (5/3/2013)
Thanks for the reply, the output u have copies is half.. please check thr is also PRJ_ID = 2 and have data till sept12the data I want is for last 24 months. and PRJDATE will column always have last day of the month
Yeah I only quoted part of it. Just trying to understand the requirements. So you want the previous 24 months including the current month at the time it is run or the end of the previous month. So if I ran it today it would end with April 2013?
_______________________________________________________________
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/
May 3, 2013 at 12:49 pm
yes thats correct 24 months from current month i.e., till April13
May 3, 2013 at 2:30 pm
I think this is right. I am kinda tight on time so I didn't have time to look super close but this should at least get you close.
You can find the tally table here. http://www.sqlservercentral.com/articles/62867/[/url]
with MyDates as
(
select dateadd(MONTH, -1 * N, dateadd(mm, datediff(mm, 0, getdate()), 0)) as PrjDate, x.PRJ_ID
from Tally t
cross join (select PRJ_ID from Rev group by PRJ_ID) x
where N >= 1
and N <= 24
)
select d.PrjDate, d.PRJ_ID, x.AMOUNT
from MyDates d
outer apply (select top 1 PRJ_ID, Amount from Rev where Rev.PrjDate <= d.PrjDate and Rev.PRJ_ID = d.PRJ_ID order by Rev.PrjDate desc) x
order by d.PRJ_ID, d.PrjDate
_______________________________________________________________
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/
May 4, 2013 at 5:15 am
Thanks, its working but taking lot of time. I have almost 30000 prj_id's. Any suggestion on how to improve the query performance or any change in query that makes performance better
May 4, 2013 at 7:27 am
Motz (5/4/2013)
Thanks, its working but taking lot of time. I have almost 30000 prj_id's. Any suggestion on how to improve the query performance or any change in query that makes performance better
how much time?
what indexes have you got in place.
The first records set is the table data which is cumulative data, but i need to populate the data for the missing dates as well and insert new record set in another table.
just a thought...but is it necessary to do the cumulative query first?...is it possible that we could wrap this up in one hit?
seems to me that you have transactional data > you are then summarising this into a table> you then want to add missing months and populate another table
what are you intending to do with the resultant table?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 4, 2013 at 1:12 pm
Its not necessary to have cumulative data at first, we can do it in one go as well.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply