erikd (6/13/2013)

---

I'm not sure how fashionable this method is around here, but you could create a look up table of zip codes, and match your columns to it.

If you're expecting...

SELECT

transactiondate = ISNULL(a.transactiondate,b.transactiondate),

a.TotalwebSales,

b.TotalInHouseSales

FROM (

select transactiondate, count(distinct transactionid) as TotalwebSales

from saletransactions

where transactionstationnumber = web -- ?

and transactiondate = <today> -- ?

group by transactiondate

) a

FULL OUTER JOIN (

select transactiondate,...

jw.lankhaar (6/13/2013)

---

For a limited number of rows (< 100) using a recursive common table expression is an alternative:

DECLARE @lowerbound int

DECLARE @increment int

DECLARE @upperbound int

SET @lowerbound = -5

SET @upperbound...

.Netter (6/13/2013)

---

Hi,

Im still confused on why negative numbers are used because from my eyes when u look at

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

if its 3...

You're welcome - thanks for the feedback 🙂

select top(1) oh.OrderType

from OrderHandsets oh

where oh.OrderId = co.OrderId

ORDER BY CASE

WHEN Ordertype = 3 THEN -2

WHEN Ordertype = 1 THEN -1

ELSE oh.OrderType END

-- sample data

;WITH students (ID_Student, Name, rate) AS (

SELECT 1, CAST('Chipper Jones' AS nchar(30)), CAST('678*9' AS nchar(5)) UNION ALL

SELECT 2, 'Mike Piazaa', '98***' UNION ALL

SELECT 3, 'Barry Bonds', '678**' UNION...

Amy.G (6/12/2013)

---

Chris, You've probably forgotten all about this, but after some other projects finished, I finally got back to this. I ran into a problem I hadn't thought of. There...

ssurekha2000 (6/12/2013)

---

the whole sp cannot be posted as there are many joins

to get the result & check i prepared 3 different SPs with conditions as stated above

SP1 contains...

Here's a pure tsql way which performs reasonably well:

SELECT *

FROM (

SELECT Postcode = 'BS16 1EJ' UNION ALL

SELECT 'OX8 1TP' UNION ALL

SELECT 'GL20 8LZ' UNION ALL

SELECT 'JUNK'

) d

CROSS APPLY (...

SELECT

CYYMMDD,

SQLDate1 = CONVERT(DATE,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112),

SQLDate2 = cast(cast(cast(CYYMMDD as int) + 19000000 as char(8)) as date),

SQLDate3 = CONVERT(DATE,CAST('1130531' + 19000000 AS CHAR(8)),112),

SQLDate4 =...

SELECT

CYYMMDD,

SQLDateTime = CONVERT(DATETIME,STUFF(CYYMMDD,1,1,CASE WHEN LEFT(CYYMMDD,1) = '0' THEN '19' ELSE '20' END),112)

FROM (SELECT CYYMMDD = '1130531') t

Krishna1 (6/10/2013)

---

Dear All

In procedure i am using Remark table to capture all the steps executed (instead of print). From another session when I try to

select * from...

The table's two constraints cannot have the same name. Change one of them.

Your query looks fine to me apart from the syntax errors. SQL Server is quite adept at optimising out query nesting.

SELECT

DistinctCompanyCount,

DenseGroup, CatName, CatId, CompanyId, CompanyName, PropertyId,

Status, Day,...