Sub Query writing

• dopydb

  SSCertifiable

  Points: 6687

  More actions

  October 9, 2013 at 3:05 am

  #278836

  hi all

  i have 2 DB's that i need to report on transact data..

  first sub query returns:

  CODE CREATEDON Syspro_GRN

  AL721552013-10-08 10:33:07.357-550.00

  AL721552013-10-08 10:33:07.3572320.00

  sub query 2 returns:

  CODE CREATEDON PLATO_VOUCHER

  AL721552013-10-08 10:33:07.357-550

  AL721552013-10-08 10:33:07.3571

  AL721552013-10-08 10:33:07.3572319

  together i get:

  CODE CREATEDON PLATO_VOUCHER Syspro_GRN

  AL721552013-10-08 10:33:07.357-550-550.00

  AL721552013-10-08 10:33:07.357-5502320.00

  AL721552013-10-08 10:33:07.3571-550.00

  AL721552013-10-08 10:33:07.35712320.00

  AL721552013-10-08 10:33:07.3572319-550.00

  AL721552013-10-08 10:33:07.35723192320.00

  so now if i try and calculate the fields, my data is wrong..

  how should i go about querying this efficiently?

  SELECT distinct TR.CODE,TR.CREATEDON,isnull(PLATO_VOUCHER,0) as PLATO_VOUCHER

  ,isnull(Syspro_GRN,0)as Syspro_GRN

  FROM TRANSPORTREQUEST TR

  LEFT JOIN

  (select TRANSPORTREQUESTID,(AMOUNT)AS PLATO_VOUCHER from TRANSPORTVOUCHER TV

  LEFT JOIN TRANSPORTVOUCHERENTRY TVE ON TVE.TRANSPORTVOUCHERID =TV.TRANSPORTVOUCHERID

  WHERE TVE.TOPOST = 'T'

  and TV.ISACTIVE = 'T'

  ) TV ON TV.TRANSPORTREQUESTID =TR.TRANSPORTREQUESTID

  left join

  (select rtrim(StockCode)as StockCode, (CurGrnValue) as Syspro_GRN from SysproCompanyW.dbo.GrnDetails

  )GRN on GRN.StockCode collate Latin1_General_CI_AS = TR.Code

  WHERE

  TR.CREATEDON >'2013-07-01'

  and TR.CODE = 'AL72155'

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  October 9, 2013 at 7:20 am

  #1657131

  In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/