sql server - Restricting WHERE clause to specific SELECT statement in sql -

-

i have following sql code. use clause date interval. problem want date interval used on specific part of select statement.

i want restrict clause date select on qtyinperiod , profitinperiod , not other columns selected (lines.item, inventory.itemalternative, inventory.onhandphys, inventory.allocated, inventorry.costprice, brand & stockstatus).

do need fetch data in 2 seperate sql querys or can limit date clause part of select statement?

select lines.item,        inventory.itemalternative,        inventory.onhandphys,        inventory.allocated,        inventory.costprice,        sum(lines.invoiced)*-1 qtysoldinperiod,        (sum(lines.amountbase-lines.costamount))*-1 profitinperiod,        replace(vinventoryoptional2values.value, '/', '') brand,        inventory.optional3 stockstatus lines inner join inventory on lines.item = inventory.item inner join vinventoryoptional2values on inventory.optional2 = vinventoryoptional2values.recordid inventory.item = 'item#'   , lines.date >= 'date'   , lines.date <= 'date'   , lines.invoiced < 0   , inventory.status = 0   , inventory.optional3 <> 3 group lines.item,          inventory.optional3,          inventory.itemalternative,          inventory.onhandphys,          inventory.costprice,          vinventoryoptional2values.value,          inventory.allocated having sum(lines.amountbase-lines.costamount) < 0

edit: have tried sub-query, can't work:

select lines.item,        inventory.itemalternative,        inventory.onhandphys,        inventory.allocated,        inventory.costprice,        replace(vinventoryoptional2values.value, '/', '') brand,        inventory.optional3 stockstatus   (select sum(lines.invoiced)*-1 qtysoldinperiod    (lines.item          lines.date >= '2014-01-01'            , lines.date <= '2014-01-31' ) ) lines inner join inventory on lines.item = inventory.item inner join vinventoryoptional2values on inventory.optional2 = vinventoryoptional2values.recordid inventory.item = 'a158wa'   , lines.invoiced < 0   , inventory.status = 0   , inventory.optional3 <> 3 group lines.item,          inventory.optional3,          inventory.itemalternative,          inventory.onhandphys,          inventory.costprice,          vinventoryoptional2values.value,          inventory.allocated

you should use subquery , in updated question syntax wrong, try using,

select lines.item,        inventory.itemalternative,        inventory.onhandphys,        inventory.allocated,        inventory.costprice,        replace(vinventoryoptional2values.value, '/', '') brand,        inventory.optional3 stockstatus,        (select sum(l.invoiced) * -1          lines l         l.date >= '2014-01-01'            , l.date <= '2014-01-31'             , l.item = lines.item) qtysoldinperiod lines inner join inventory on lines.item = inventory.item inner join vinventoryoptional2values on inventory.optional2 = vinventoryoptional2values.recordid inventory.item = 'a158wa'   , lines.invoiced < 0   , inventory.status = 0   , inventory.optional3 <> 3 group lines.item,          inventory.optional3,          inventory.itemalternative,          inventory.onhandphys,          inventory.costprice,          vinventoryoptional2values.value,          inventory.allocated

Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

python - NameError: name 'subprocess' is not defined -

-
i'm trying make rng website: http://jamesdotcom.com/?p=417 . when run following code: captureimage = subprocess.popen( [ "fswebcam", "-r", "356x292", "-d", "/dev/video0", "static.jpg", "--skip", "10" ], stdout=devnull, stderr=devnull) captureimage.communicate() i following error message: nameerror: name 'subprocess' not defined what should do? thanks have imported first? import subprocess captureimage = subprocess.popen(["fswebcam", ...) subprocess included in python standard library since python 2.4. should not necessary install it.
Read more