Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get data from Previous record (Cumulative Data)-- Urgent help required Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 9:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
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




Post #1449269
Posted Friday, May 3, 2013 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449276
Posted Friday, May 3, 2013 10:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
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.
Post #1449280
Posted Friday, May 3, 2013 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449281
Posted Friday, May 3, 2013 10:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 11:25 AM
Points: 690, Visits: 5,922
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
(
mydate varchar(6),
prj_id int,
amount numeric(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.
Post #1449283
Posted Friday, May 3, 2013 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
Thanks, for the reply, give me 5 - 10 mins I will give post the insert script
Post #1449285
Posted Friday, May 3, 2013 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
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
Post #1449290
Posted Friday, May 3, 2013 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449321
Posted Friday, May 3, 2013 12:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
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
Post #1449324
Posted Friday, May 3, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 sept12
the 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449325
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse