Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get data from Previous record (Cumulative Data)-- Urgent help required


Get data from Previous record (Cumulative Data)-- Urgent help required

Author
Message
Motz
Motz
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 148
yes thats correct 24 months from current month i.e., till April13
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
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/

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 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)
Motz
Motz
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 148
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
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
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

Motz
Motz
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 148
Its not necessary to have cumulative data at first, we can do it in one go as well.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
play area...see if this makes any sense and can be worked upon



SELECT TOP 1000000
TranDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2014'), '2011'),
ProdID = 1 + CAST(Abs(Checksum(Newid()) % 50000 ) AS INT),
Qty = CAST(Rand(Checksum(Newid())) * 99 + 1 AS INT)
INTO Transdata
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
GO

CREATE CLUSTERED INDEX [CIX] ON [dbo].[TransData]
( [ProdID] ASC, [TranDate] ASC )
GO

SELECT DATEADD ( MONTH , -1 * N , DATEADD ( mm , DATEDIFF ( mm , 0 , GETDATE ( )) , 0 )) AS TranDate
INTO jlsdates
FROM Tally t
WHERE N >= 1
AND N <= 24;

CREATE CLUSTERED INDEX [CIX] ON [dbo].[jlsdates]
([TranDate] ASC)

;with jlscte as
(
SELECT jlsdates.TranDate
, TransData.ProdID
, SUM ( TransData.qty ) AS cumtot
FROM
jlsdates LEFT OUTER JOIN TransData
ON jlsdates.TranDate > TransData.TranDate
GROUP BY TransData.ProdID
, jlsdates.TranDate
)

SELECT ProdID, TranDate, cumtot
FROM jlscte
WHERE
(ProdId =1500)
order by tranDate





________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search