Hi Adamedge, Thanks for asking. There are many good tutorials available for SSIS. One of them shared by...

Agree with you, both scenarios are valid and need to be handled. I just wanted to share an approach that can be used as a sample for real problem. But...

• This reply was modified 4 years, 4 months ago by  Anand929.

Hi,

Please refer below code snippet -

;WITH DateTable AS (
SELECT * FROM (VALUES
('2020-07-01',0,0),
('2020-07-02',0,0),
('2020-07-03',0,1),
('2020-07-04',1,0),
('2020-07-05',1,0),
('2020-07-06',0,0),
('2020-07-07',0,0),
('2020-07-08',0,0)
) AS t(DateValue,isWeekEnd,isHoliday)
),
TicketTable AS (
SELECT * FROM (VALUES
('Ticket1','2020-07-01','2020-07-06'),
('Ticket2','2020-07-07','2020-07-08'),
('Ticket3','2020-07-07',NULL)
) AS t(Ticket,CreateDate,ResolvedDate)
)
SELECT t.Ticket,t.CreateDate,t.ResolvedDate,
DATEDIFF(DAY,t.CreateDate,t.ResolvedDate) - SUM(d.isWeekEnd)-SUM(d.isHoliday) AS TicketAge 
FROM...

I use below query to get the list of all reports deployed in report server -

SELECT Name from ReportServer.dbo.Catalog WHERE Type=2

Type 2 represents report object in table.

Assuming, this question is regarding SSIS, you can follow below steps -

1.      Right click file connection Manager and select Properties. Alternatively, you can select file connection manager and press F4.

2.     ...

Below query will delete TradeCodeId 5 and 6, since these records does not have parent available in same table. Please check -

;WITH CTE AS (
SELECT tc.TradeCodeId,tc.CodeValue
,COALESCE(mcv.ParentCodeValue,tc.CodeValue) AS...