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 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
yes thats correct 24 months from current month i.e., till April13
Post #1449327
Posted Friday, May 3, 2013 2:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,282, Visits: 12,116
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)
Post #1449357
Posted Saturday, May 4, 2013 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
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
Post #1449407
Posted Saturday, May 4, 2013 7:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 1,899, Visits: 18,920
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 !
__________________________________________________________________
Post #1449421
Posted Saturday, May 4, 2013 1:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 5:30 AM
Points: 24, Visits: 146
Its not necessary to have cumulative data at first, we can do it in one go as well.
Post #1449455
Posted Saturday, May 4, 2013 2:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 1,899, Visits: 18,920
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 !
__________________________________________________________________
Post #1449464
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse