fork(2) download 
  1. declare @header table (sort int, description varchar(50));
  2. declare @dates table (date datetime, sort int);
  3. declare @services table (code varchar(50), sort int);
  4. declare @glcodes table (id int, code varchar(50), name varchar(50), coef int, sort int);
  5. declare @locations table (id int, code varchar(10), name varchar(50), sort int);
  6. declare @orders table (
  7.   ID int,
  8.   Location int,
  9.   Sale money,
  10.   Discount money,
  11.   Tax money,
  12.   Date datetime
  13. );
  14.  
  15. declare @glcodesdiscounts table (glcodesort int, locationsort int, datesort int, Amount money);
  16. declare @glcodessales table (glcodesort int, locationsort int, datesort int, Amount money);
  17. declare @servicemoney table (servicesort int, locationsort int, datesort int, Amount money);
  18. declare @ordermoney table (locationsort int, datesort int, Sale money, Discount money, Tax money);
  19.  
  20. select
  21.   ld.locationsort,
  22.   ld.datesort,
  23.   columnsort = h.sort + case h.sort
  24.     when 400 then ss.sort
  25.     when 500 then ggs.sort
  26.     when 600 then ggd.sort
  27.     else 0
  28.   end,
  29.   Amount =  
  30.   case h.sort
  31.     when 300 then isnull(o.Sale, 0)
  32.     when 400 then isnull(s.Amount, 0)
  33.     when 500 then isnull(gs.Amount, 0)
  34.     when 600 then -isnull(gd.Amount, 0)
  35.     when 700 then -isnull(o.Discount, 0)
  36.     when 800 then isnull(o.Tax, 0)
  37.     when 999 then isnull(o.Sale, 0) + isnull(s.Amount, 0) + isnull(gs.Amount, 0) - isnull(gd.Amount, 0) - isnull(o.Discount, 0) + isnull(o.Tax, 0)
  38.   end,
  39.   Caption =  
  40.   case h.sort
  41.     when 100 then case ll.sort when 0 then h.description else case dd.sort when 99999 then 'Total' else ll.code end end
  42.     when 200 then case ll.sort when 0 then h.description else convert(varchar, dd.date, 101) end
  43.     when 400 then ss.code
  44.     when 500 then ggs.code + ' ' + ggs.name
  45.     when 600 then ggd.code + ' (Discounts)'
  46.     else h.description
  47.   end
  48. into #ReportSalesAnalysis
  49. from (select distinct locationsort from @ordermoney) l
  50. inner join (select distinct locationsort, datesort from @ordermoney) ld on l.locationsort = ld.locationsort
  51. inner join @locations ll on l.locationsort = ll.sort
  52. inner join @dates dd on ld.datesort = dd.sort
  53. cross join @header h
  54. left join @ordermoney o on h.sort in (300, 700, 800, 999)
  55.   and ld.locationsort = o.locationsort
  56.   and ld.datesort = o.datesort
  57. left join (select distinct t.*, m.servicesort from @servicemoney m left join @services t on m.servicesort = t.sort) ss
  58.   on h.sort = ss.servicesort or (h.sort = 400 and ss.servicesort < 100)
  59. left join @servicemoney s on ss.servicesort = s.servicesort
  60.   and ld.locationsort = s.locationsort  -- this line produces the error
  61. -- the error is:
  62. -- The multi-part identifier "ld.locationsort" could not be bound
  63.   and ld.datesort = s.datesort
  64. left join (select distinct t.*, m.glcodesort from @glcodessales m left join @glcodes t on m.glcodesort = t.sort) ggs
  65.   on h.sort = ggs.glcodesort or (h.sort = 500 and ggs.glcodesort < 100)
  66. left join @glcodessales gs on ggs.glcodesort = gs.glcodesort
  67.   and ld.locationsort = gs.locationsort
  68.   and ld.datesort = gs.datesort
  69. left join (select distinct t.*, m.glcodesort from @glcodesdiscounts m left join @glcodes t on m.glcodesort = t.sort) ggd
  70.   on h.sort = ggd.glcodesort or (h.sort = 600 and ggd.glcodesort < 100)
  71. left join @glcodesdiscounts gd on ggd.glcodesort = gd.glcodesort
  72.   and ld.locationsort = gd.locationsort
  73.   and ld.datesort = gd.datesort;
  74.  
Runtime error #stdin #stdout 0s 2964KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
https://ideone.com/gv6djO
language:
SQLite (sqlite 3.27.2)
created:
12 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!