sql server - Changing Stored Procedure Structure -

-

i have stored procedure gets information multiple tables , displays data. need no longer @ order_detail_serials table , @ order_serial table. not sure how change structure so. assistance in matter appreciated.

alter procedure [dbo].[status] (   @begin_date smalldatetime,   @end_date smalldatetime ) begin  -- set nocount on added prevent result sets -- interfering select statements. set nocount on;  -- gather orders date range product codes  select distinct orders.ordernumber internal_id,          orders.ponumber client_id,          orders.altordernumber altonumber,         orders.shipid shipping_method,          orders.shipdate ship_date,         order_details.productid productid,          products.productcode parent_item_code,          orders.orderstatus order_status     #orders_header_info     orders     inner join order_details         on orders.ordernumber = order_details.ordernumber     inner join products         on order_details.productid = products.productid     orders.shipdate between @begin_date , @end_date     order internal_id   -- attach serial numbers orders select o.internal_id,         o.client_id,         o.altonumber,         o.shipping_method,         o.ship_date,         o.parent_item_code,         os.status item_status,          isnull(s.serial,'') sn,         o.order_status     #orders_detail_serials              #orders_header_info o     join order_detail_serials s         on o.internal_id = s.ordernumber             , o.productid = s.productid     join order_serial os         on s.productid = os.productid             , s.serial '%' + ltrim(rtrim(os.sn)) + '%'   -- concatenate tracking numbers select ordernumber, [1] tracknum1,          [2] tracknum2,          [3] tracknum3,          [4] tracknum4,          [5] tracknum5,          [6] tracknum6,          [7] tracknum7,         [8] tracknum8,         [9] tracknum9,         [10] tracknum10     #tracking             (             select ordernumber, trackingnumber,                     row_number() on (partition ordernumber order ordernumber)                     rownum                 order_tracking                 ordernumber in                         (                             select distinct internal_id                                 #orders_detail_serials                         )                     , isnull(publishedrate,0) <> 0          ) tracknumbers     pivot (max(trackingnumber) rownum in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt       -- retrieve return tracking numbers     -- dl 6-15-2015 commented, changed concatenating return tracking number     --select ordernumber,      --      trackingnumber return_trackingnumber     --  #return_tracking     --  order_tracking     --  ordernumber in      --          (     --              select distinct internal_id     --                  #orders_detail_serials     --          )     --      , isnull(publishedrate,0) = 0   -- dl 6-12-2015  -- concatenate return tracking numbers select ordernumber, [1] tracknum1,          [2] tracknum2,          [3] tracknum3,          [4] tracknum4,          [5] tracknum5,          [6] tracknum6,          [7] tracknum7,         [8] tracknum8,         [9] tracknum9,         [10] tracknum10     #return_tracking             (             select ordernumber, trackingnumber,                     row_number() on (partition ordernumber order ordernumber)                     rownum                 order_tracking                 ordernumber in                         (                             select distinct internal_id                                 #orders_detail_serials                         )                     , isnull(publishedrate,0) = 0          ) tracknumbers     pivot (max(trackingnumber) rownum in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt    -- attached tracking , return tracking numbers orders   select os.internal_id,         os.client_id,         os.altonumber,         os.shipping_method,         os.ship_date,         os.parent_item_code,         os.item_status,         os.sn,         os.order_status,         isnull(t.tracknum1,'') +              case when isnull(t.tracknum2,'') <> ''                 '|' + t.tracknum2                 else ''             end +             case when isnull(t.tracknum3,'') <> ''                 '|' + t.tracknum3                 else ''             end +             case when isnull(t.tracknum4,'') <> ''                 '|' + t.tracknum4                 else ''             end +             case when isnull(t.tracknum5,'') <> ''                 '|' + t.tracknum5                 else ''             end +             case when isnull(t.tracknum6,'') <> ''                 '|' + t.tracknum6                 else ''             end +             case when isnull(t.tracknum7,'') <> ''                 '|' + t.tracknum7                 else ''             end +             case when isnull(t.tracknum8,'') <> ''                 '|' + t.tracknum8                 else ''             end +             case when isnull(t.tracknum9,'') <> ''                 '|' + t.tracknum9                 else ''             end +             case when isnull(t.tracknum10,'') <> ''                 '|' + t.tracknum10                 else ''             end trackingnumbers,         -- dl 6-12-2015 concatenating return_trackingnumber         isnull(rt.tracknum1,'') +              case when isnull(rt.tracknum2,'') <> ''                 '|' + rt.tracknum2                 else ''             end +             case when isnull(rt.tracknum3,'') <> ''                 '|' + rt.tracknum3                 else ''             end +             case when isnull(rt.tracknum4,'') <> ''                 '|' + rt.tracknum4                 else ''             end +             case when isnull(rt.tracknum5,'') <> ''                 '|' + rt.tracknum5                 else ''             end +             case when isnull(rt.tracknum6,'') <> ''                 '|' + rt.tracknum6                 else ''             end +             case when isnull(rt.tracknum7,'') <> ''                 '|' + rt.tracknum7                 else ''             end +             case when isnull(rt.tracknum8,'') <> ''                 '|' + rt.tracknum8                 else ''             end +             case when isnull(rt.tracknum9,'') <> ''                 '|' + rt.tracknum9                 else ''             end +             case when isnull(rt.tracknum10,'') <> ''                 '|' + rt.tracknum10                 else ''             end return_trackingnumber     #orders_detail_serials os     left join #tracking t         on os.internal_id = t.ordernumber     left join #return_tracking rt         on os.internal_id = rt.ordernumber     order os.ship_date, os.internal_id, os.client_id, os.altonumber, os.parent_item_code, os.sn    -- clean drop table #orders_header_info drop table #orders_detail_serials drop table #tracking drop table #return_tracking   end


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more