This should work, the trick is to follow the WHERE clause:

SELECT COUNT(*)

FROM dbo.et_Summary_Income_Statement inc

RIGHT JOIN dbo.it_1_Revenue__Costs rev

ON rev.itemiid = inc.dimension_1_revenu

RIGHT JOIN dbo.it_2_LOB l

ON l.itemiid = inc.dimension_2_lob

RIGHT JOIN dbo.it_2_elist e

ON e.itemiid...

Try something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER NewAgentRegistration

ON dbo.Client_Agent

AFTER INSERT

AS

SET NOCOUNT ON;

BEGIN TRY

INSERT INTO dbo.Asset(_RGuid, AsNumber, ASName, ASRDate, ASEDate)

SELECT _RGuid

,''

,''

,'' -- Should this be a date???

,'' -- Should this be...

You have two problems:

1. Your trigger will not cope if multiple rows are inserted.

2. You have a multi-statement trigger with no error handling.

Also, ASNumber is just a formated copy of...

If myTable is actually a complicated view, try avoiding subqueries and use a derived table instead:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

LEFT JOIN

(

SELECT DISTINCT T1.ReferenceNumber

FROM myTable T1

WHERE T1.ReferenceNumber NOT LIKE 'TR%'

AND...

How does this do?

SELECT UID, ReferenceNumber, Type

FROM myTable

WHERE ReferenceNumber NOT LIKE 'TR%'

AND Type = 1

UNION ALL

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

WHERE T.ReferenceNumber NOT LIKE 'TR%'

...

Try:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

WHERE T.ReferenceNumber NOT LIKE 'TR%'

AND

(

T.Type = 1

OR NOT EXISTS

(

SELECT *

FROM myTable T1

WHERE T1.ReferenceNumber = T.ReferenceNumber

AND T1.Type = 1

)

)

ORDER BY UID

It will probably be more efficient to PIVOT tblCommon_tblSkills.

Something like:

;WITH Skills

AS

(

SELECT Common_ID, [1],[2],[3],[4],[5],[6],[7]

FROM

(

SELECT Common_ID, Skill_ID

FROM tblCommon_tblSkills

) P

PIVOT

(

COUNT(Skill_ID)

FOR Skill_ID IN ([1],[2],[3],[4],[5],[6],[7])

) A

)

SELECT C.*

,CASE WHEN S.[1] = 1 THEN 'Yes' ELSE...

There is no need for joins with deletes.

Try the standard ANSI syntax:

DELETE HouseFacility

WHERE EXISTS

(

SELECT *

FROM Facility F

WHERE F.[Id] = HouseFacility.FacilityId

AND F.FacilityGroupId = ?

)

AND EXISTS

(

SELECT *

FROM HouseFacilityGroup G

WHERE G.[Id] = HouseFacility.HouseFacilityGroupId

AND...

You need to UNPIVOT to get the attribute and then PIVOT to get the attribute values in the same row.

The only order in SQL is what is specified in the ORDER BY clause. This means that you will have to some of the work

in the front end. An UNPIVOT...

SELECT lfisno, Stockcode, quantity

,CAST(quantity * 1.0 / SUM(quantity) OVER (PARTITION BY lfisno) AS decimal(6,2))AS rate

FROM

(

SELECT lfisno, Stockcode

,SUM(quantity) AS quantity

FROM test

GROUP BY lfisno, Stockcode

) D

I would start by downloading, for free, the Express versions of SQL Server and Visual Web Developer from

http://msdn.microsoft.com

Then I would look at

http://www.asp.net

where there is information to get you started.

You could...

To get parallel processing to work, I think you will either have to combine all your queries into one with something like UNION ALL, or write an async CLR SP.

Also,...

Use the ROW_NUMBER() function to work out which row the information should be on.

Also, PIVOT tends to be more readable than lots of CASE statements.

Try something like:

SELECT SYear, SWeek

,[2] AS...

The problem may be that all 1.3 million rows are being sent to the MSSQL instance before the filter is applied.

This could take a long time on a slow link.

Try:

1....