Why are you storing productions as varchar? Do you expect non numeric values?

How do you compare results for previous month and previous year?

You should review your requirements for the report,...

I haven't take my morning coffee but this should do it. Maybe someone else can come with a better solution.

SELECT OrderNo

FROM #MatAlloc

WHERE OrderNo IN(

SELECT OrderNo

FROM #MatAlloc m

WHERE NOT EXISTS( SELECT...

There are a couple of things that can happen here. To be sure you need to provide DDL of the tables and indexes and execution plan.

These are the ideas that...

Why on earth would someone use an nvarchar(max) to store integer values? :w00t:

There's a way around this, just change the SUM for

SUM( DISTINCT CAST( Installations AS int))

Oracle765 (11/7/2013)

---

Sorry but that doesn't work

I put

SELECT softwaremanufacturer,productname, productversion, count(installations) as TotalInstalls, Licensable

FROM

(SELECT DISTINCT softwaremanufacturer,productname, productversion,Licensable from newtable)

GROUP BY softwaremanufacturer,productname, productversion, Licensable

and it says

Msg 156, Level 15, State 1,...

There's no way to avoid gaps with an identity column. I'm not sure if there's a secure method to achieve it that will perform correctly.

Gaps shouldn't be a concern especially...

You're not new to SQL Server, you've been here long enough to keep posting simple questions.

Please read about data types and Comparison operators.

A SUM( DISTINCT...) should do the job.

WITH Products(ProductName, ProductVersion, SoftwareManufacturer, Type, Category, Installation, Licensable) AS( SELECT

'Project Standard 2007', '12.x', 'Microsoft', 'Commercial', 'Office Productivity', 47, 'Licensable' UNION ALL SELECT

'Project Standard...

Do you provide sponsorship for mexicans?

Check the following link http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#OtherTables

Just to add on what Sean said, you could change those functions with a LEFT function.

WITH CTE(String) AS(

SELECT 'Some test string')

SELECT REVERSE(STUFF(REVERSE(String), 1, 3, '')) FROM CTE

UNION ALL

SELECT LEFT(String, LEN(String)...

You could do something like this:

WITH Sampledata AS(

SELECT *

FROM (VALUES

('8:00'),

('8:00 am'),

('8:00 AM'),

('8:00 A.M'),

('08:00'),

('Yoda'),

('5:00 p.m.'),

('17:00'))x(charTime)

)

SELECT charTime,

CASE WHEN charTime LIKE '[0-2][0-9]:[0-5][0-9]%' OR charTime LIKE '[0-9]:[0-5][0-9]%'

THEN CAST(REPLACE( charTime, '.', '') AS time)...

You're welcome. Those functions would have helped me a lot in my previous job but we didn't have 2012 🙁

They're not equal if StartDateTime is datetime. The correct way to go is up to you to decide.

where StartDateTime>='2013-10-01' and StartDateTime<'2013-10-31'

will go from '2013-10-01 00:00:00.000' to '2013-10-30 23:59:59.997'

where StartDateTime>='2013-10-01' and...

Have you tried TRY_CAST() or TRY_CONVERT()?

You're posting on a SQL Server 2012 forum, so I expect that you're working on it.