Table data in tabular form

Question

Post reply

Table data in tabular form

freecoder

SSC Eights!

Points: 951
More actions
August 6, 2013 at 5:20 am

#296472

CREATE TABLE [dbo].[LeaveEntry](
[LeaveId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[LeaveTypeName] [int] NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
)
INSERT INTO LeaveEntry VALUES
(1, 'A','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'),
(2, 'B','LON','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'),
(3, 'C','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000');
I want to do a report in ssis where I have to send an email i a tabular form for each week
something like
Week 1
<table border="1px">
<tr>
<td></td>
<td>A</td>
<td>B</td>
<td>C</td>
</tr>
<td>Monday</td>
<td>OUT</td>
<td>LON</td>
<td></td>
</tr>
<tr>
<td>Tuesday</td>
<td></td>
<td></td>
<td>LON</td>
</tr>
<tr>
<td>Wednesday</td>
<td>OUt</td>
<td></td>
<td>LON</td>
</tr>
<tr>
<td>Thurday</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Friday</td>
<td></td>
<td>OUT</td>
<td></td>
</tr>
</table>
Week 2
<table border="1px">
<tr>
<td></td>
<td>A</td>
<td>B</td>
<td>C</td>
</tr>
<td>Monday</td>
<td>OUT</td>
<td>LON</td>
<td></td>
</tr>
<tr>
<td>Tuesday</td>
<td></td>
<td></td>
<td>LON</td>
</tr>
<tr>
<td>Wednesday</td>
<td>OUt</td>
<td></td>
<td>LON</td>
</tr>
<tr>
<td>Thurday</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Friday</td>
<td></td>
<td>OUT</td>
<td></td>
</tr>
</table>

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

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

freecoder SSC Eights! Points: 951 More actions · Answer 1

freecoder

SSC Eights!

Points: 951

August 6, 2013 at 5:21 am

#1638856

http://htmledit.squarefree.com/

for the html table

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Not many folks will want to click on the link you posted. Can you post up a picture of the desired layout instead?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 3

There you go

Just an example how the data should look like

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Your table and the sample data don't seem to line up very well. First you were inserting the identity but when I removed that there are datatype mismatches. Is LeaveTypeName supposed to be char(3) instead of int?

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

freecoder SSC Eights! Points: 951 More actions · Answer 5

Sorry

CREATE TABLE [dbo].[LeaveEntry](

[LeaveId] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[LeaveTypeName] [varchar](100) NOT NULL,

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL,

)

INSERT INTO LeaveEntry VALUES

(1, 'A','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'),

(2, 'B','LON','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'),

(3, 'C','OUT','2013-08-29 00:00:

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Maybe I am missing something but your sample data does not seem to make sense compared to the picture you posted for desired output. You have 3 rows and they all three have the same start and end dates but your desired output has all sorts of other stuff.

Why only week1 and week2? Your sample data is a whole month.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

freecoder SSC Eights! Points: 951 More actions · Answer 7

You are right

The sample data is very different.

I just wanted to give a hint ..how the table should look like..its not actual representation of the data..

hope it makes sense? Sorry about the confusion.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

freecoder (8/6/2013)
You are right
The sample data is very different.
I just wanted to give a hint ..how the table should look like..its not actual representation of the data..
hope it makes sense? Sorry about the confusion.

Not really. It is a LOT easier if we have sample data and desired output based on the sample data. Otherwise we don't really know what we are doing. Keep in mind that we don't know your project or your system. The only thing we know is a very simplified table with three rows of meaningless (to us) data. Help paint the picture of the whole problem and we give you the whole solution.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

freecoder SSC Eights! Points: 951 More actions · Answer 9

cool..

Will update the sample data tomoro as I am finishing work now.

Cheers.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

freecoder (8/6/2013)
cool..
Will update the sample data tomoro as I am finishing work now.
Cheers.

Sounds good. I am sure that if I am not able to help you that some of the other fine folks around here will be happy to roll up their sleeves and help out.

I suspect that you will want to examine either a calendar and/or tally table for this but the details from you will determine the best approach.

Wish I was getting done with work now, I am just getting started for the day. 😛

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

freecoder SSC Eights! Points: 951 More actions · Answer 11

I hope this is right

CREATE TABLE [dbo].[LeaveEntry](

[LeaveId] [int] NOT NULL,

[Name] [varchar](50) NOT NULL,

[LeaveTypeName] [varchar](100) NOT NULL,

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL,

)

INSERT INTO LeaveEntry VALUES

(1, 'A','OUT','2013-08-12 00:00:00.000','2013-08-14 00:00:00.000'),

(2, 'B','LON','2013-08-13 00:00:00.000','2013-08-14 00:00:00.000'),

(3, 'C','OUT','2013-08-15 00:00:00.000','2013-08-16 00:00:00.000'),

(4, 'A','LON','2013-08-20 00:00:00.000','2013-08-22 00:00:00.000'),

(5, 'B','LON','2013-08-22 00:00:00.000','2013-08-23 00:00:00.000'),

(6, 'C','OUT','2013-08-22 00:00:00.000','2013-08-23 00:00:00.000');

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

-- sample data

DROP TABLE #LeaveEntry

CREATE TABLE #LeaveEntry(

[LeaveId] [int] NOT NULL,

[Name] [varchar](50) NOT NULL,

[LeaveTypeName] [varchar](100) NOT NULL,

[StartDate] [datetime] NULL,

[EndDate] [datetime] NULL

)

SET DATEFORMAT YMD

INSERT INTO #LeaveEntry VALUES

(1, 'A','OUT','2013-08-12 00:00:00.000','2013-08-14 00:00:00.000'),

(2, 'B','LON','2013-08-13 00:00:00.000','2013-08-14 00:00:00.000'),

(3, 'C','OUT','2013-08-15 00:00:00.000','2013-08-16 00:00:00.000'),

(4, 'A','LON','2013-08-20 00:00:00.000','2013-08-22 00:00:00.000'),

(5, 'B','LON','2013-08-22 00:00:00.000','2013-08-23 00:00:00.000'),

(6, 'C','OUT','2013-08-22 00:00:00.000','2013-08-23 00:00:00.000');

-- solution

SELECT c.DateRange, c.[DayName], c.AbsWeekno,

'A' = MAX(CASE WHEN Name = 'A' THEN LeaveTypeName ELSE '' END),

'B' = MAX(CASE WHEN Name = 'B' THEN LeaveTypeName ELSE '' END),

'C' = MAX(CASE WHEN Name = 'C' THEN LeaveTypeName ELSE '' END)

FROM (SELECT ReportStartDate = MIN(StartDate), ReportEndDate = MAX(EndDate) FROM #LeaveEntry) r

CROSS APPLY dbo.IF_Calendar(ReportStartDate, ReportEndDate, 'monday') c

LEFT JOIN #LeaveEntry l

ON c.DateRange BETWEEN l.StartDate AND l.EndDate

WHERE NOT c.[DayName] IN ('saturday','sunday')

GROUP BY c.DateRange, c.[DayName], c.AbsWeekno

ORDER BY c.DateRange, c.[DayName], c.AbsWeekno

-- calendar inline function definition

CREATE FUNCTION [dbo].[IF_Calendar]

(

@StartDate DATE,

@EndDate DATE,

@FirstWeekDay VARCHAR(10)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

(

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows

E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max

iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive

SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))

rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

FROM E3

)

-- Do some date arithmetic

SELECT

a.DateRange,

c.[Year],

c.[Month],

c.[DayOfMonth],

c.AbsWeekno,

c.[DayName],

d.Holiday

FROM iTally

CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a

CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)

) b (FirstWeekDay, FirstWeekdayOffset)

CROSS APPLY (

SELECT

[Year] = YEAR(a.DateRange),

[Month] = MONTH(a.DateRange),

[DayOfMonth] = DAY(a.DateRange),

AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,

[DayName]= DATENAME(weekday,a.DateRange)

) c

CROSS APPLY (

SELECT Holiday = CASE

WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'

WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'

WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'

WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'

WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'

WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'

ELSE NULL END

) d

WHERE b.FirstWeekDay = @FirstWeekDay

AND @EndDate IS NOT NULL

)

GO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

freecoder SSC Eights! Points: 951 More actions · Answer 13

freecoder

SSC Eights!

Points: 951

August 7, 2013 at 7:25 am

#1639362

Thanks 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

freecoder (8/7/2013)
Thanks 🙂

It works?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden