Try this

ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 WHEN BOOKED_BY_FLG = 'Y' THEN 2 ELSE 3 END ASC) AS [ROWID]

it worked for me 🙂

Yes you are correct

the line 'ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN 1 ELSE 2 END ASC) AS [ROWID]'

puts the most desirable name at row 1 ie BK first

You...

LEFT JOIN (

SELECT BOOKING_ID,

CASE WHEN COMPANY_ROLE_CD = 'BK' OR BOOKED_BY_FLG = 'Y'

THEN PARTY_NAME

ELSE NULL

END AS [BOOKING PARTY],

ROW_NUMBER() OVER (

PARTITION BY BOOKING_ID

ORDER BY CASE WHEN COMPANY_ROLE_CD = 'BK' THEN...

Can there be more than one row in MiniGapp..MG_BOOKING_PARTY table for a BOOKING_ID?

If so how do you determine which one?

If not then you do not need a sub query.

However max(rate) over( partition by product) will not work if the rate varies +/- and the latest value by date is needed

Nice 🙂

I did not think of that :blush:

Use case to pivot the data, ie

WITH cte (ACCOUNT,CODE,ColNo) AS (

SELECT ACCOUNT,CODE,

ROW_NUMBER() OVER (PARTITION BY ACCOUNT ORDER BY CODE)

FROM )

SELECT ACCOUNT,

MAX(CASE WHEN ColNo=1 THEN CODE END) AS [CODE1],

MAX(CASE WHEN ColNo=2...

SELECTDATENAME(month,DATEADD(month,MONTH(t.Bill_date),0)) AS [Month],

t.product,

SUM(t.QTY) AS [QTY],

r.rate,

SUM(t.total_val) AS [T_val]

FROMtest t

CROSS APPLY (SELECT TOP 1 rate from test ORDER BY Bill_date DESC,rate DESC) r

GROUPBY MONTH(t.Bill_date),t.product,r.rate

ORDERBY MONTH(t.Bill_date)

Just a guess, try using a LEFT JOIN?

SELECT table1.column1, table2.column1 as SomeRandomColumnName, CAST(CASE WHEN table2.column1 IS NULL THEN 1 ELSE 0 END AS bit)

AS BitValueColumnName

FROM table2 LEFT JOIN

...

See answer

If the parameters are integers

DECLARE @Month int = 2

DECLARE @Year int = 2014

SELECT

DATEADD(month,(@Year*12-22800)+(@Month-1),0) AS [SatrtDate],

DATEADD(daY,-1,DATEADD(month,(@Year*12-22800)+@Month,0)) AS [EndDate]

Previous replies notwithstanding

STUFF(CONVERT(varchar(12),DATEADD(month,2,dd.date),107),1,3,DATENAME(MM,DATEADD(month,2,dd.date)))

SELECTj.name AS JobName

, c.name

, c.path

, c.description

, rs.SubscriptionID

, laststatus

, eventtype

, LastRunTime

, date_created

, date_modified

FROMReportServer.dbo.[Catalog] c

JOIN ReportServer.dbo.Subscriptions s

ON s.report_oid = c.itemid

JOIN ReportServer.dbo.ReportSchedule rs

ON rs.SubscriptionID...

Ordinal Date? 😀

My answer, FWIW, would be to remove the year filter from the dataset and add it to the column expression instead.

e.g.

Sum(IIf(Year = Year(Now)-1,Fields!SentLeads.Value),0) for last year

Sum(IIf(Year = Year(Now),Fields!SentLeads.Value),0) for this...