June 8, 2008 at 9:36 pm
I've have this following table,
Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
6/1/2005 | 194 | 155
7/1/2005 | 194 | 132
8/1/2005 | 194 | 199
5/1/2005 | 196 | 333
6/1/2005 | 196 | 544
7/1/2005 | 196 | 722
8/1/2005 | 196 | 845
...
...
*Combination of Date_Taken and Main_ID generate unique row
*Above data consist of 1 May 2005 to 1 AUG 2005 data (4 month data for each Main_ID), which is Date_Taken start from 1st day every month.
*Date_Taken | Main_ID | Daily_Rainfall
-------------------------------------------
5/1/2005 | 194 | 142
---> Mean, In May 2005 Main_ID=194 having 142 Daily_Rainfall
6/1/2005 | 196 | 544
---> Mean, In Jun 2005 Main_ID=196 having 544 Daily_Rainfall
Let's say, current month is 8
I stuck to do the query to display result like as follow
Date_Taken | Main_ID | CurrentMonth_DR | LastMonth_DR | Last2Month_DR| Last3Month_DR
--------------------------------------------------------------------------------------
8/1/2005 | 194 | 199 | 132 | 155 | 142
8/1/2005 | 196 | 845 | 722 | 544 | 333
...
...
*CurrentMonth_DR is 8/1/2005, LastMonth_DR is 7/1/2005, Last2Month_DR is 6/1/2005 and so on
Plz help me :sick:
June 9, 2008 at 12:55 am
I hope this will help you
OR At least will give a solid clue to your solution...
--I created the following structure table and inserted the rows as required...
CREATE TABLE [dbo].[tblDR](
[Date_t] [datetime] NULL,
[ID] [int] NULL,
[DR] [int] NULL,
[MID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/01/2007',1,100)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/02/2007',1,110)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/03/2007',1,120)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/04/2007',1,130)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/01/2007',2,200)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/02/2007',2,210)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/03/2007',2,220)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/01/2007',3,300)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/02/2007',3,310)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/03/2007',3,320)
INSERT INTO [dbs].[dbo].[tblDR] ([Date_t],[ID],[DR])
VALUES('01/04/2007',3,330)
Declare @aa as varchar(8000)
Declare @BB as varchar(8000)
Declare @DD varchar(100)
Set @bb = ''
Set @DD = ''
-- Create the column list for pivoting
-- This list depends upon the number of dates in your table
Declare scr cursor for Select Distinct Date_t from tblDR
open scr
Fetch Next From scr into @DD
while @@Fetch_Status = 0
begin
if @bb <> ''
Set @bb = '' + @bb + ',[' + @DD + ']'
else
Set @bb = '[' + @DD + ']'
Fetch Next From scr into @DD
end
close scr
deallocate scr
--Start Pivoting
Set @aa = 'SELECT ID, '
Set @aa = @aa + @bb
Set @aa = @aa + ' into tblDR2 From
( Select ID,Date_t from tblDR) p PIVOT
( Count(date_t) for DATE_T in (' + @bb + '))
AS pvt
ORDER BY 1
'
Print @aa
Exec (@aa)
-- Pivot table created as tblDR2
--- Now lets do the actual job...
Declare @id int
Declare @DDR int
Declare @obj bigint
Declare @cname varchar(100)
Declare @sql varchar(100)
Select @obj = object_id from sys.objects where name = 'tblDR2'
Declare sc Cursor for Select ID from tblDr2
Open sc
Fetch next from sc into @ID
while @@Fetch_Status = 0
begin
Declare sc1 Cursor for Select name from sys.columns where object_id= @obj and name <> 'ID'
Open sc1
Fetch next from sc1 into @cname
while @@Fetch_Status = 0
begin
Select @DDR = DR from tblDR where id = @id and date_t = convert(datetime,@cname)
Set @sql = 'update tblDR2 Set [' + @cname + '] = ' + cast(@ddr as varchar(10))+ ' where id = ' + cast(@id as varchar(10)) + ' and [' + @cname + '] = 1 '
print(@Sql)
Exec(@SQL)
Fetch next from sc1 into @cname
end
Close sc1
Deallocate sc1
Fetch next from sc into @ID
end
Close sc
Deallocate sc
-- The result is as required...
Select * from tblDr2
drop table tblDr2
drop table tblDr
Atif Sheikh 😀
June 9, 2008 at 7:46 am
As always, I'm going to recommend not doing pivoting in the database. Let a front-end application do it for you. They're better at it. SQL Server Reporting Services, MS Excel, Crystal Reports, MS Access, all of these are better at pivoting data than SQL is. Easier to do, and can do more with it at the user-end of things.
(Want to change from having a total in the field to having an average? Gotta write a whole new query in SQL, gotta right-click and change one selection in Excel.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2008 at 3:09 pm
For what's being asked for, how about the following:
DECLARE @DR TABLE (
Date_Taken DateTime,
Main_ID int,
Daily_RainFall int
)
INSERT INTO @DR VALUES('05/01/2005',194,142)
INSERT INTO @DR VALUES('06/01/2005',194,155)
INSERT INTO @DR VALUES('07/01/2005',194,132)
INSERT INTO @DR VALUES('08/01/2005',194,199)
INSERT INTO @DR VALUES('05/01/2005',196,333)
INSERT INTO @DR VALUES('06/01/2005',196,544)
INSERT INTO @DR VALUES('07/01/2005',196,722)
INSERT INTO @DR VALUES('08/01/2005',196,845);
WITH GET_IT_ALL AS (
SELECT Date_Taken, Main_ID, Daily_RainFall AS CurrentMonth_DR,
(SELECT Daily_RainFall
FROM @DR AS B
WHERE B.Date_Taken = DATEADD(m,-1,A.Date_Taken) AND A.Main_ID=B.Main_ID) AS LastMonth_DR,
(SELECT Daily_RainFall
FROM @DR AS C
WHERE C.Date_Taken = DATEADD(m,-2,A.Date_Taken) AND A.Main_ID=C.Main_ID) AS Last2Month_DR,
(SELECT Daily_RainFall
FROM @DR AS D
WHERE D.Date_Taken = DATEADD(m,-3,A.Date_Taken) AND A.Main_ID=D.Main_ID) AS Last3Month_DR
FROM @DR AS A
)
SELECT *
FROM GET_IT_ALL
WHERE CurrentMonth_DR + LastMonth_DR + Last2Month_DR + Last3Month_DR IS NOT NULL
Provided that the number of previous months is indeed limited to 3 as opposed to requiring every previous month, this should work just fine. Extending it to cover additional months is very little more than a rather simple copy, paste, and update to make unique. I've tested the code and the output results are as attached in Excel format.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2008 at 12:49 am
yes. it simple and effective. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply