Hi Keith

I am now faced with a new error

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'newtable'.

even though that is the name of the table and not a...

Hi Again

The sum does not work because it is actually a nvarchar column. Is there a way around this

SELECT ProductName,

ProductVersion,

SoftwareManufacturer,

SUM( DISTINCT Installations),

Licensable

FROM newtable where productname...

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, Line...

ok thanks for the update, the query works but yes I agree, i will speak to the powers that be, thanks again all

hi there

this just comes up with an error saying incorrect syntax near the keyword from

Hi There

Thanks for your reply this looks more like it but I am receiving an error saying

Msg 10713, Level 15, State 1, Line 19

A MERGE statement must be terminated...

the softwaremanufacturer,productname and productversion do not match in the lookuptable so it wont update with unknown,

thats what I meant to say if there is not a match in...

No that does not work, it has updated all licensable columns to unknown and nulled the software manufacturer,productname and productversion

please advise

that last query returns results even if the category is null

in the datalookuptable there are 291406 rows

so when i search for licensable products I get

select n.*,d.category

from newtable n

join datalookuptable d

on n.softwaremanufacturer = d.amended_sw_manufacturer

and n.productname = d.amended_product_name

and n.productversion = d.amended_product_version

and d.category...

Hi Errland

I have just actually posted a query regarding this.

I have just done that but my query seems to return to many rows eg

select count(*) from dbo.newtable

returns 600,00...

hi there

i cant add an id column as the dbo.newtable is created dynamically from spreadsheets

please advise

ive looked into that and I have no idea what to do

Hi SSCrazy

Yes i know but this still does not answer my question on how I actually get this in the right output format, at present it is showing

,endeffectivedate

,starteffectivedate

...

Hi SSCrazy

yes i know but this still does not answer my question as to how to concatenate this together my query is showing

,endeffectivedate

,starteffectivedate

from newtable

select softwaremanufacturer,productname,productversion

which is invalid

it...