Date columns of the month matrix report

Question

Date columns of the month matrix report

joshtheflame

Default port

Points: 1499
More actions
November 4, 2010 at 11:39 pm

#231300

Hi All,
I have an application in which i have following 2 tables.
Tasks Master
-------------
Task_ID
Task_Name
Task_Details
------------
Task_ID
Task_Date
Task_Count (can be any number like 2 or 3 or 4 or 40)
the Input Form is like that which the staff will fill at the end of the day.
Date Task Name Task_Count
---------------------------------------------------------------
24/01/2010 How many cheque books issued today : 12
24/01/2010 How many ATM Issued today : 7
Now I want a matrix report showing all tasks suppose 28 tasks in vertical row and on given month it should show all the dates of the particular month horizontal direction like from 1 to 31 days with the task_count.
what can be the stored procedure based on it.
thanks
November 5, 2010 at 3:35 am

This was removed by the editor as SPAM
November 5, 2010 at 3:49 am

This was removed by the editor as SPAM
November 5, 2010 at 4:05 am

This was removed by the editor as SPAM
November 5, 2010 at 5:12 am

This was removed by the editor as SPAM
November 5, 2010 at 5:59 am

This was removed by the editor as SPAM
November 5, 2010 at 6:11 am

This was removed by the editor as SPAM
November 5, 2010 at 7:12 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

joshtheflame Default port Points: 1499 More actions · Answer 1

Thanks for your answer I am quite new to SQL world 🙂 dont know how to create dates out of the given month in matrix style. or perhaps a view can do?

joshtheflame Default port Points: 1499 More actions · Answer 2

thanks how to create upto number of days column in select statement according to the given month?

joshtheflame Default port Points: 1499 More actions · Answer 3

do i need to issue 31 select statements to create 31 columns?

joshtheflame Default port Points: 1499 More actions · Answer 4

its working in Oracle but dont know how to convert it into SQL.

WITHstart_dateAS

(

SELECTTO_DATE ( '01-Jan-2010'

, 'DD-Mon-YYYY'

)AS start_date

FROMdual

)

SELECT m.task_name

, COUNT (CASE WHEN TO_CHAR (d.task_date, 'DD') = '01' THEN 1 END)AS Day_1

, COUNT (CASE WHEN TO_CHAR (d.task_date, 'DD') = '02' THEN 1 END)AS Day_2

, COUNT (CASE WHEN TO_CHAR (d.task_date, 'DD') = '03' THEN 1 END)AS Day_3

...

, COUNT (CASE WHEN TO_CHAR (d.task_date, 'DD') = '31' THEN 1 END)AS Day_31

FROM task_masterm

JOIN task_detaildONm.task_id= d.task_id

JOIN start_datesONd.task_date>= s.start_date

ANDd.task_date< ADD_MONTHS (s.start_date, 1)

GROUP BY m.task_name

;

joshtheflame Default port Points: 1499 More actions · Answer 5

Stewart thanks for the file but I cannot open it as I am using Visual Studio 2010 🙁

joshtheflame Default port Points: 1499 More actions · Answer 6

is this which i need to install where my VS 2010 is installed ..cause on my database server we do have sqlserver 2008 enterprise R2 and BIDS is there.

joshtheflame Default port Points: 1499 More actions · Answer 7

I am almost there as I've got this solution from web searching and results are coming as I was expecting.

CREATE PROCEDURE crosstab

@select varchar(8000),

@sumfunc varchar(100),

@pivot varchar(100),

@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivotcol INTO ##pivot FROM ' + @table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '

+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivotcol'

SELECT @sql=@sql + '''' + convert(varchar(100), pivotcol) + ''' = ' +

stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '

+ @delim + convert(varchar(100), pivotcol) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)

and executing the procedure like this.

EXECUTE crosstab 'select name as Task from Competitors inner join CompetitorDetail on (CompetitorDetail.CompetitorId=Competitors.CompetitorId)

group by Name', 'sum(Price)','convert(varchar, oDate, 105)','CompetitorDetail'

the output is coming like 01-01-2010....02-01-2010...but i want like

01-Apr...02-Apr....03-Apr....etc etc...tried datepart inplace of convert(varchar, oDate, 105) but no luck. what can be done?