November 13, 2010 at 12:29 pm
I have this pivot query and my background is Oracle not SQLServer...Need two things in the following query
1. Total At the end
2. Need to mark "Holiday" as per my holiday calendar entry in pivot output
Holiday Table Structure
holiday_date
holiday_description
this query generates the output like
task_id......task_name....1-Nov...2-Nov...3-Nov....31-Nov
1............ATM Issued....3.......7.......8........10
now I want to add
task_id.....task_name.....1-Nov...2-Nov...3-Nov....31-Nov...Total
1...........ATM Issued.....3.......7.......8........10......need sum of all month of task1
2...........Chequebooks ...7.......17......87.......19......need sum of all month of task2
declare @StartOfMonth datetime = '10/1/2010';
declare @counter datetime = @StartOfMonth;
declare @sql varchar(MAX) = '';
declare @columnnames varchar(MAX);
declare @columnfilter varchar(MAX);
declare @fieldname varchar(12);
declare @fieldalias varchar(MAX);
--First, create a string of dynamic columns, one for each day of the month.
WHILE (MONTH(@counter) = MONTH(@StartOfMonth))
BEGIN
SET @fieldname = '[' + CONVERT(varchar(10), @counter, 101) + ']';
SET @fieldalias = '[' + CONVERT(VARCHAR(20), datepart(day, @counter), 101) + '-'+ left(CONVERT(VARCHAR(20), datename(MONTH, @counter), 101),3) + ']' ;
--Wrap the columns in ISNULL(@,0) to avoid having null values for days without tasks.
SET @columnnames = ISNULL(@columnnames + ',', '') + 'ISNULL(' + @fieldname + ',0) AS ' + @fieldalias;
--Also create a dynamic list of the Task_Date values to include in the pivot.
SET @columnfilter = ISNULL(@columnfilter + ',', '') + @fieldname;
SET @counter = DATEADD(DAY,1,@counter);
END
select @columnnames
--Put it all together into a pivot query.
set @sql = 'SELECT Task_Name, ' + @columnnames + ' FROM (';
set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count '
set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';
exec (@sql)
November 13, 2010 at 1:06 pm
You can change your @sql part to something like below.I cannot test it since you didn't post your table structure and sample data.
Notice the new column name "TaskTotal"
--Put it all together into a pivot query.
set @sql = 'SELECT Task_Name, ' + @columnnames + ' TaskTotal, FROM (';
set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count,Sum(D.Task_Count)over(partition by M.Task_Name)TaskTotal'
set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';
exec (@sql)
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 8:59 am
Thanks for your reply. I did try this
--Put it all together into a pivot query.
set @sql = 'SELECT Task_Name, ' + @columnnames + ' , Total FROM (';
set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count, Sum(D.Task_Count) over(partition by M.Task_Name) AS "Total" '
set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';
I have date wise data in detail table and entries are of different month. The purpose of the query is to get the monthly data shows total days in a month wise data and a running total of each task of the particular month.
After running your query it is giving me the total of the task including other months also..I want the total of the particular month only.
Ok here are my tables
Task_Master
------------------------
Task_ID
Task_Name
Task_Details
------------------
Task_ID
Task_Date
Task_Count
Holiday Table
--------------
Holiday_ID
Holiday_Date
November 14, 2010 at 9:41 am
--Put it all together into a pivot query.
set @sql = 'SELECT Task_Name, ' + @columnnames + ' , Total FROM (';
set @sql = @sql + 'SELECT M.Task_Name, D.Task_Date, D.Task_Count, Sum(D.Task_Count) over(partition by M.Task_Name,YOUR MONTH COLUMN) AS "Total" '
set @sql = @sql + 'FROM Task_Details D JOIN Task_Master M ON D.Task_ID = M.Task_ID) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR Task_Date IN (' + @columnfilter + ')) AS PivotTable';
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 9:46 am
I dont have MONTH COLUMN in my table 🙂 as I am populating days of the month dynamically all i have is TASK_ID, TASK_DATE, TASK_COUNT ...thats it..now how to change query accordingly.
November 14, 2010 at 9:50 am
joshtheflame (11/14/2010)
I dont have MONTH COLUMN in my table 🙂 as I am populating days of the month dynamically all i have is TASK_ID, TASK_DATE, TASK_COUNT ...thats it..now how to change query accordingly.
Get the month from the task_date column using datepart function.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 10:00 am
do one thing plz...I have the following data in TASK_DETAIL...IN TASK_MASTER..u can create just two records with the following n test..results are strange..it should come as two lines with 31 columns from 1st OCtober to 31st OCtober with the values of task_count in their respective days and 0 if nulls in date and task_id number 1 running total should be "3" and task_id number 2 running total should be "4080"
task_id.......task_name
-----------------------
1 abcd
2 efgh
Task_idTask_DateTask_count
12010-10-01 10
12010-10-02 20
22010-10-01 1
12010-11-01 50
12010-11-02 40
22010-11-01 60
22010-11-02 40
22010-10-02 2
12010-10-03 450
12010-10-04 3600
November 14, 2010 at 10:06 am
joshtheflame (11/14/2010)
do one thing plz...I have the following data in TASK_DETAIL...IN TASK_MASTER..u can create just two records with the following n test..results are strange..task_id.......task_name
-----------------------
1 abcd
2 efgh
Task_idTask_DateTask_count
12010-10-01 10
12010-10-02 20
22010-10-01 1
12010-11-01 50
12010-11-02 40
22010-11-01 60
22010-11-02 40
22010-10-02 2
12010-10-03 450
12010-10-04 3600
I don't think I am quite following you.
Can you please post some sample data and the expected o/p ? It's like shooting in the dark right now.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 10:18 am
Sachin,
Just provided u the sample data. If you notice it has data of task 1 and 2 for the month of October and November.
In October the sum of task_count for task 2 is 3
In October the sum of task_count for task 1 is 4080
run the query against the data I provided u and check the total.
TASK_MASTER
--------------
task_id .....task_name
1 Abcd
2 EFGH
TASK_DETAIL
---------------
Task_id....Task_Date.....Task_count
12010-10-01 10
12010-10-02 20
12010-10-03 450
12010-10-04 3600
12010-11-01 50
12010-11-02 40
22010-10-01 1
22010-10-02 2
22010-11-01 60
22010-11-02 40
November 14, 2010 at 11:18 am
create table #tbl (Task_id int,Task_Date datetime,Task_count int)
insert into #tbl
select 1,'2010-10-01 00:00:00', 10 union
select 1,'2010-10-02 00:00:00', 20 union
select 2,'2010-10-01 00:00:00', 1 union
select 1,'2010-11-01 00:00:00', 50 union
select 1,'2010-11-02 00:00:00', 40 union
select 2,'2010-11-01 00:00:00', 60 union
select 2,'2010-11-02 00:00:00', 40 union
select 2,'2010-10-02 00:00:00', 2 union
select 1,'2010-10-03 00:00:00', 450 union
select 1,'2010-10-04 00:00:00', 3600
select * from #tbl order by Task_id,Task_Date
declare @sql varchar(MAX) = '';
declare @columnnames varchar(MAX)='';
select @columnnames=@columnnames + MM from(
select ',[' + convert(varchar(30),Datename(mm,Task_Date)) + ']'MM from #tbl
)t group by MM
print @columnnames
set @sql = 'SELECT * FROM (';
set @sql = @sql + 'SELECT Task_id,Datename(MM,Task_Date)MM,Task_Count '
set @sql = @sql + 'FROM #tbl M) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR MM IN (' + stuff(@columnnames,1,1,'') + ')) AS PivotTable';
exec (@sql)
drop table #tbl
From next time please post the sample data the way I did.
Gee I should get paid for this one 😉
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 1:02 pm
Sachin thank you for your efforts and I appologize that I didn't provide you the data the way you mentioned me Next time I will do it accordingly.
Well through your latest query I am only getting the sum of October and november of both task id's but again you might didnt understand my requirement.
My desired output is like the following and its only for the given month as date parameter.
output required.
Task_Name......1-Oct....2-Oct....3-Oct......31-Oct....Total
ABCD................40.........50.........10..........20........ 120
EFGH................10.........20..........30.........40.........100
The Total Column is the total of the task and sum of 1st Oct to 31-Oct ...I hope you understand me this time...your query is not making 31 columns of different days of the input month...and I want the total of the given month only..:) the cheque in the mail 🙂
November 14, 2010 at 1:26 pm
What about multiple months?How will o/p appear for October and November together in the sample data I have used?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 1:44 pm
Just one month at a time. User will pass the parameter for month like Jan or Feb or Mar and according to the month the procedure will get executed and create dynamic columns of the days in the month along with the TOTAL COLUMN at the end for each task and the last column need to add is TOTAL and it will sum up for all dates in the given month of the particular task and place it in TOTAL column. The query I've pasted in my first question it was coming out fine except the total at the end of the given month and the tasks and this is what i am looking for.
November 14, 2010 at 1:45 pm
It will work assuming the query will be executed against just one month .
create table #tbl (Task_id int,Task_Date datetime,Task_count int)
insert into #tbl
select 1,'2010-10-01 00:00:00', 10 union
select 1,'2010-10-02 00:00:00', 20 union
select 2,'2010-10-01 00:00:00', 1 union
select 1,'2010-11-01 00:00:00', 50 union
select 1,'2010-11-02 00:00:00', 40 union
select 2,'2010-11-01 00:00:00', 60 union
select 2,'2010-11-02 00:00:00', 40 union
select 2,'2010-10-02 00:00:00', 2 union
select 1,'2010-10-03 00:00:00', 450 union
select 1,'2010-10-04 00:00:00', 3600
select * from #tbl order by Task_id,Task_Date
declare @columnnames varchar(MAX)='';
declare @StartOfMonth datetime = '10/1/2010';
declare @counter datetime = @StartOfMonth;
declare @sql varchar(MAX) = '';
declare @columnfilter varchar(MAX);
declare @fieldname varchar(12);
declare @fieldalias varchar(MAX);
--First, create a string of dynamic columns, one for each day of the month.
WHILE (MONTH(@counter) = MONTH(@StartOfMonth))
BEGIN
SET @fieldname = '[' + CONVERT(varchar(10), @counter, 101) + ']';
SET @fieldalias = '[' + CONVERT(VARCHAR(20), datepart(day, @counter), 101) + '-'+ left(CONVERT(VARCHAR(20), datename(MONTH, @counter), 101),3) + ']' ;
--Wrap the columns in ISNULL(@,0) to avoid having null values for days without tasks.
SET @columnnames = ISNULL(@columnnames + ',', '') + '' + @fieldname --+ + @fieldalias;
--Also create a dynamic list of the Task_Date values to include in the pivot.
SET @columnfilter = ISNULL(@columnfilter + ',', '') + @fieldname;
SET @counter = DATEADD(DAY,1,@counter);
END
set @sql = 'SELECT * FROM (';
set @sql = @sql + 'SELECT Task_id,Task_Date MM,Task_Count,(select Sum(Task_Count) from #tbl t where datename(mm,Task_Date)=' + '''' + DATEname(mm,@StartOfMonth) +'''' + ' and t.Task_id=M.Task_id )TaskTotal '
set @sql = @sql + 'FROM #tbl M) as SourceTable ';
set @sql = @sql + 'PIVOT (SUM(Task_Count) FOR MM IN (' + stuff(@columnnames,1,1,'') + ')) AS PivotTable';
print @sql
exec (@sql)
drop table #tbl
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 14, 2010 at 2:03 pm
Sachin great work 🙂 just need four things and it will be perfect cause i am trying to change but it has error...not good with sql though.
1. I've lost my field Aliases (eg. instead of 2010/10/01 i need 1-Oct, 2-Oct ....31-Oct)
2. Order by Task_ID
3. Total At the end instead of begining..
4. Instead of showing NULL the field value should show "0" in column values
rest is pefect ..
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply