December 10, 2008 at 12:04 pm
I HAVE GOT DATA AS
DATE SOURCE TYPE COUNT
12/04 A IN 21
12/05 A OUT 29
12/05 B IN 12
12/06 A OUT 13
12/06 B IN 31
I NEED THE OUTPUT TO BE
SOURCE TYPE DATE(12/04) DATE(12/05) DATE(12/06)
A IN 21 0 0
A OUT 0 29 13
B IN 0 12 31
COULD YOU PLEASE LET ME KNOW HOW TO DO THAT ...TO GET THE FIRST QUERY I WROTE
SELECT DATE,SOURCE,TYPE,COUNT(*) FROM TABLE1
GROUP BY DATE,SOURCE,TYPE
ORDER BY 1,2,3
pLEASE DO REPLY...THANKS IN ADVANCE
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 12:28 pm
DATE(12/04) DATE(12/05) DATE(12/06)
Are these the only dates in the DATE column in the table
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 10, 2008 at 12:55 pm
No there will be so many dates in this way but ishall query jus past 7 days data...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 2:15 pm
You can do this by constructing a dynamic query using the PIVOT operator. You can take help from the article in the below link in doing it.
Dynamic PIVOT in SQL Server 2005
I am providing you the query to generate the required result for the three dates( '12/04', '12/05', '12/06'). You need to modify the query by passing it a list of dates that you need (past 7 days).
Let me know if you need help. if not then please post the query when you are able to modify it succesfully.
CREATE TABLE #TABLE1
(DATE smalldatetime, SOURCE char(1),TYPE VARCHAR(3))
INSERT INTO #TABLE1 VALUES ('2008-12-04','A', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-04','A', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-04','A', 'OUT')
INSERT INTO #TABLE1 VALUES ('2008-12-04','A', 'OUT')
INSERT INTO #TABLE1 VALUES ('2008-12-04','A', 'OUT')
INSERT INTO #TABLE1 VALUES ('2008-12-05','B', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-05','B', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-06','A', 'OUT')
INSERT INTO #TABLE1 VALUES ('2008-12-06','A', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-06','A', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-06','A', 'IN')
INSERT INTO #TABLE1 VALUES ('2008-12-06','A', 'IN')
SELECT SOURCE, TYPE, [2008-12-04] AS 'Date 04/12/2008', [2008-12-05] AS 'Date 05/12/2008',
[2008-12-06] AS 'Date 06/12/2008'
FROM
(SELECT DATE,SOURCE,TYPE,COUNT(*) CNT FROM #TABLE1
GROUP BY DATE,SOURCE,TYPE) A
PIVOT (SUM(CNT)
FOR DATE IN ([2008-12-04], [2008-12-05], [2008-12-06])) AS PVT
ORDER BY SOURCE
SOURCE TYPE Date 04/12/2008 Date 05/12/2008 Date 06/12/2008
A IN 2 NULL 4
A OUT 3 NULL 1
B IN NULL 2 NULL
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 10, 2008 at 2:28 pm
Hi eshan this is not a one time process i need to do it evryday so it should take the date itself....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 11, 2008 at 3:22 pm
Chris (12/10/2008)
Hi eshan this is not a one time process i need to do it evryday so it should take the date itself....
YesChris, you told this earlier. Here is the final thing for you, though I wanted you to do it yourself. This procedure will create the columns dynamically (consisting of the past 7 days)
Note: I have used normal table TABLE1 in this procedure (with the same structure and data as the temp table #TABLE1 that I used earlier)
CREATE procedure [dynamic_pivot]
as
declare @pivotCols varchar(max), @sql varchar(max)
SELECT @pivotCols = COALESCE(@pivotCols,'') + '['+ (SUBSTRING((CONVERT(varchar, A.DATE, 120)),1,10)) + '],'
FROM (SELECT DISTINCT DATE FROM TABLE1 WHERE DATE >= (DATE-7)) A
SELECT @pivotCols = SUBSTRING(@pivotCols,1,(LEN(@pivotCols)-1))
select @sql= 'SELECT SOURCE, TYPE,'+ @pivotCols
select @sql = @sql +
'
FROM
(SELECT DATE,SOURCE,TYPE,COUNT(*) CNT FROM TABLE1
GROUP BY DATE,SOURCE,TYPE) A
PIVOT (SUM(CNT)
FOR DATE IN (
'
select @sql = @sql + @pivotCols + ')) AS PVT ORDER BY SOURCE'
exec(@sql)
EXECUTE dynamic_pivot
SOURCE TYPE 2008-12-04 2008-12-05 2008-12-06
A IN 2 NULL 4
A OUT 3 NULL 1
B IN NULL 2 NULL
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply