SQL File Tracking Query

• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  May 17, 2018 at 10:51 am

  #344649

  Hi
  Everyday some vendor files will be loaded into db and we are in process to create control report in place.  I need small help

  create table dbo.test
(
Vendor_Name CHAR(100),
File_Count INT
)
INSERT INTO dbo.test values ('V1',10),('V2',0),('V3',10),('V4',30),('V5',50)
  1) We will consider V1 as one vendor and remaining all as one  vendor
  2) if data is not loaded then we need to derive indictor as 0 or 1 

  Output
  Vendor Name     FileRecevied
  V1file                    1
  Allother                 0 (as we are not received data for v2 -- all vendor's are combined as one except V1- if one file data is not then we need to show as 0)

  I hope this is clear, if any questions, Please let me know thanks!
  

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  May 17, 2018 at 11:35 am

  #1990899

  DECLARE @vendors table( Id int IDENTITY, VendorName varchar(100) );
INSERT @vendors ( VendorName )
VALUES          ( 'V1' )
     ,          ( 'V2' )
     ,          ( 'V3' )
     ,          ( 'V4' )
     ,          ( 'V5' )
     ,          ( 'V6' );
DECLARE @test-2 table ( VendorId int, FileCount INT );
INSERT INTO @test-2
VALUES (1,10)
     , (3,10)
     , (4,30)
     , (5,50);
SELECT VendorName, Coalesce(t.FileCount, 0) FileCount
FROM @vendors v
LEFT JOIN @test-2 t ON v.Id = t.VendorId;
  

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  May 17, 2018 at 11:38 am

  #1990900

  koti.raavi - Thursday, May 17, 2018 10:51 AM

  Hi
  Everyday some vendor files will be loaded into db and we are in process to create control report in place.  I need small help

  create table dbo.test
(
Vendor_Name CHAR(100),
File_Count INT
)
INSERT INTO dbo.test values ('V1',10),('V2',0),('V3',10),('V4',30),('V5',50)
  1) We will consider V1 as one vendor and remaining all as one  vendor
  2) if data is not loaded then we need to derive indictor as 0 or 1 

  Output
  Vendor Name     FileRecevied
  V1file                    1
  Allother                 0 (as we are not received data for v2 -- all vendor's are combined as one except V1- if one file data is not then we need to show as 0)

  I hope this is clear, if any questions, Please let me know thanks!
  

  What do you need help with? You have not asked a question!

  Where do the vendor names come from?
  What is the link between a vendor and their files?

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel