sql server - T-SQL Return Records that do not Exist -


i'm new sql , need way return either null or 0 years , quarters no records exist each surgeon. there 2 product families should return (knee , shoulder) , need missing year/quarter if surgeon has ever used product in family. example if smith, john ever had knee not need nulls shoulder.

here query have far, haven't been able figure out how include years , quarters have no values.

select  q.surgeon_name ,         q.product_family ,         first_date ,         q.year ,         q.quarter ,         cast(round(q.monthly_average, 2) money) 'monthly average'    ( select distinct                     rtrim(confirmed_to) 'surgeon_name' ,                     product_family ,                     year(rev_ship_date) 'year' ,                     datename(quarter, cast(rev_ship_date datetime)) 'quarter' ,                     ( sum(amount) / 3 ) 'monthly_average'                vsoicl                     left join product_line_desc on vsoicl.part_id = product_line_desc.part_id               vsoicl.product_line <> 'mirr'                     , product_family not null                     , product_family <> 'null'                     , confirmed_to <> ''                     , rev_ship_date >= '2013-01-01'           group  confirmed_to ,                     product_family ,                     year(rev_ship_date) ,                     datename(quarter, cast(rev_ship_date datetime))         ) q         left join ( select distinct                             rtrim(confirmed_to) 'surgeon_name' ,                             product_family ,                             cast(min(rev_ship_date) date) 'first_date'                        vsoicl                             left join product_line_desc on vsoicl.part_id = product_line_desc.part_id                       vsoicl.product_line <> 'mirr'                             , product_family not null                             , product_family <> 'null'                     group confirmed_to ,                             product_family                   ) f on q.surgeon_name = f.surgeon_name                             , q.product_family = f.product_family order surgeon_name ,         product_family ,         year ,         quarter

any assistance appreciated.

i figured out late last night.

select  snyq.surgeon_name, snyq.product_family, f.first_date, snyq.year, snyq.quarter, sn_sales.[monthly_average] ( select *      (select distinct rtrim(confirmed_to) 'surgeon_name'     ,product_family      vsoicl     left join product_line_desc      on vsoicl.part_id = product_line_desc.part_id       vsoicl.product_line <> 'mirr'     , product_family not null     , product_family <> 'null'     , rev_ship_date between '2013-01-01' , getdate()      group confirmed_to, product_family) sn,   ( select distinct year(rev_ship_date) year, datepart(quarter,rev_ship_date) quarter     vsoicl     rev_ship_date between '2013-01-01' , getdate()) yq ) snyq left join  (     select q.surgeon_name ,q.product_family   ,q.year  ,q.quarter  ,cast(round(q.monthly_average,2) money) 'monthly_average'  (select distinct rtrim(confirmed_to) 'surgeon_name' ,product_family ,year(rev_ship_date) 'year' ,datename(quarter, cast(rev_ship_date datetime)) 'quarter' ,(sum(amount) / 3) 'monthly_average'  vsoicl left join product_line_desc  on vsoicl.part_id = product_line_desc.part_id   vsoicl.product_line <> 'mirr' , product_family not null , product_family <> 'null' , confirmed_to <> '' , rev_ship_date >= '2013-01-01'  group confirmed_to, product_family, year(rev_ship_date), datename(quarter, cast(rev_ship_date datetime))) q  ) sn_sales on snyq.surgeon_name=sn_sales.surgeon_name , snyq.product_family=sn_sales.product_family , snyq.year=sn_sales.year , snyq.quarter=sn_sales.quarter  left join (select distinct rtrim(confirmed_to) 'surgeon_name' ,product_family ,cast(min(rev_ship_date) date) 'first_date'  vsoicl left join product_line_desc  on vsoicl.part_id = product_line_desc.part_id   vsoicl.product_line <> 'mirr' , product_family not null , product_family <> 'null' , (stores_code <> 'qu' , so_status <> 'x' , so_line_status <> 'x' , ship_type not in('o', 'c')) , (cust_po_id not '%demo%' , cust_po_id not '%expired%' , cust_po_id not '%literature%' , cust_po_id not '%toss%' , cust_po_id not '%replace%' , cust_po_id not '%swap%' , cust_po_id not '%exchange%' , cust_po_id not '%cancel%' , cust_po_id not '%duplicate%') , order_class <> 'r'  group confirmed_to, product_family) f on snyq.surgeon_name=f.surgeon_name , snyq.product_family=f.product_family  order surgeon_name,product_family,year,quarter

i had read sql in haste, , revised thought be

create mycalendar table (myyyyy, myqq) years/quarters of interest. small table, 10 years having 40 rows

then use cross join "multiply" surgeon/product each yyyyqq

select     just.surgeon_name ,     just.product_family ,      myyyyy ,     myqq ,      first_date ,     [monthly average]   (     (select  distinct                 rtrim(confirmed_to) 'surgeon_name' ,                 product_family ,            vsoicl           product_line <> 'mirr'             , product_family not null             , product_family <> 'null'             , confirmed_to <> ''             , rev_ship_date >= '2013-01-01'       )    ,  mycalendar        )  left join     (the above sql) myv      on    just.surgeon_name = myv.surgeon_name      ,   just.product_family= myv.product_family      ,   myyyyy = year      ,   myqq = quarter

Comments

Post a Comment

Popular posts from this blog

user interface - How to replace the Python logo in a Tkinter-based Python GUI app? -

is there way change default logo, python logo , appears in window's task bar? notice have replaced default tk logo used appear in application window. i using windows 7 , python 2.6 , developing gui of tkinter. you can using winico tk extension package. winico package can used add system tray icons tk programs. the following sample shows 1 way change runtime application icon. note need provide .ico file suitable sizes of icons in on command line , need use pythonw. not change taskbar icon console when running python script. test extracted winico0.6 package python\tcl\winico0.6 folder package require winico work , ran code below using pythonw winico_test.py path\to\some\ico\file.ico . import sys tkinter import * def main(argv): root = tk() root.update() root.tk.call('package','require','winico') id = root.tk.call('winico','createfrom',argv[1]) root.tk.call('winico','setwindow',root,id...
Read more

objective c - Greedy NSProgressIndicator Allocation -

i'm using bunch of nsprogressindicators @ once (32 exact) , looks when they're animating start allocating memory no one's business greedy_run http://i62.tinypic.com/15rgmso.png i've got reports app slowdown , crash after days of uptime, , looks culprit, it's hard pinpoint bugs take days appear. i comment out call start animating, memory behaves expected. result of multiple nsprogressindicators alloc'ed on same thread? or maybe sandboxing? of constant allocations coming core animation threads, don't know else going on. custom nsview progress indicator lives in @interface myview () @property (nonatomic, strong) nsimageview *imageview; @property (nonatomic, strong) nstextview *numbertxt; @property (nonatomic, strong) nsprogressindicator *progress; @end @implementation myview - (id)initwithframe:(nsrect)framerect { self = [super initwithframe:framerect]; if (self) { // image nsimage *image = [nsimage imagenamed:@"bg.png"]; c...
Read more

how to set an OCR language in Google Drive -

i need upload image google drive including ocr feature. the problem setocrlanguage method. need specify hebrew language , based on iso 639-1 codes should "heb". i'm getting response "code" : 400, "errors" : [ { "domain" : "global", "location" : "ocrlanguage", "locationtype" : "parameter", "message" : "invalid value", "reason" : "invalid" } ], any idea should code or can list of avaliable codes?
Read more