sql server - SQL Query (Sybase): Subtract 1 from the sum of one column depending on the value of another -


i have 2 sql queries need combine 1 due sheer size of database (most of retail store locations, fine, have much more transactions)

basically, need number of transactions per employee given day. this, counting distinct number of transaction numbers per employee (each item in transaction gets own row same transaction number, needs distinct). however, there column "txnvoidmod". goal if value of column 0, count transactions normally, if value 1, need subtract off 1 because transaction voided.

here sample data

+------ -+------------+-------------+ |transnum| txnvoidmod | salesprsnid | +--------+------------+-------------+ | 115568 | 0          | 1339        | | 115568 | 0          | 1339        | | 114566 | 0          | 1339        | | 114566 | 0          | 1339        | | 115504 | 0          | 2555        | | 105551 | 0          | 0452        | | 105551 | 0          | 0452        | | 105551 | 0          | 0452        | | 105552 | 1          | 0452        | | 105552 | 1          | 0452        | | 105552 | 1          | 0452        | +--------+------------+-------------+

i omitting fields unnecessary example.

here query using

 select txn_pos_transactions.cashiernum salesprsnid,      (count(distinct ms.transnum))as transcnt     txn_merchandise_sale ms       inner join txn_pos_transactions on ms.transnum=txn_pos_transactions.transnum      ms.modtime '2013-06-01 %' , ms.itemdatetime '2013-06-01 %'      , (txnvoidmod=0 or txnvoidmod=1)      group txn_pos_transactions.cashiernum order salesprsnid

i leaving out fact query querying netsales , units sold, must take account. sample results query (including omitted netsales , units portion quite verbose)

+------ -+------------+-------------+-------------+ |transcnt| unitssold  | salesprsnid | netsalesamt | +--------+------------+-------------+-------------+ | 2      | 5          | 1339        | 98.50       | | 1      | 2          | 2555        | 35.20       | | 2      | 1          | 0452        | 24.00       | +--------+------------+-------------+-------------+

from above, employee id "0452" has 2 transaction in results (transcnt), however, need build query reads 0 transactions because 1 transaction has txnvoidmod equal 1 , void of previous transaction txnvoidmod=0. if said "txnvoidmod=0" in clause, "0452" have 1 transaction, still need subtract off transaction txnvoidmod=1

i've tried various things such conditionals , subtracting off value of txnvoidmod, no avail trys group txnvoidmod gives me unnecessary rows. need there 1 row per salesprsnid.

any suggestions great, thanks.

by way, using sap sybase database.. seems part queries same exception of few items missing.

how this:

select    txn_pos_transactions.cashiernum salesprsnid   ,count(distinct case txnvoidmod when 1 ms.transnum end) transcnt txn_merchandise_sale ms     inner join txn_pos_transactions on ms.transnum = txn_pos_transactions.transnum  ms.modtime '2013-06-01 %'    , ms.itemdatetime '2013-06-01 %'    , txnvoidmod in(0, 1)  group txn_pos_transactions.cashiernum  order salesprsnid

Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more