API tools faq
paste
Advertisement
Guest User

sql loco

a guest
Jun 28th, 2017
66
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 17.38 KB | None | 0 0
raw download clone embed print report
  1. --Al parecer es algo asi como que la primer query trae lo
  2.  
  3. --For Subs Adjust. 
  4.     SELECT dbo.NotNumberedInvoicesItemsView.MsoName                 AS MSO
  5.          , dbo.NotNumberedInvoicesItemsView.DocumentType           
  6.          , dbo.NotNumberedInvoicesItemsView.NUMBER                  AS ContractNumber
  7.          , dbo.NotNumberedInvoicesItemsView.ProductBrandName        AS ProductBrand
  8.          , dbo.NotNumberedInvoicesItemsView.PeriodCategoryType      AS Category
  9.          , dbo.NotNumberedInvoicesItemsView.AntiquityClientType AS SubscriberAge
  10.          , dbo.NotNumberedInvoicesItemsView.Quantity                AS SubscribersQty
  11.          , dbo.NotNumberedInvoicesItemsView.ContractCurrencyDescription AS ContractCurrency
  12.          , dbo.NotNumberedInvoicesItemsView.ContractExchangeRate    AS ExRateToInvoice
  13.          , dbo.NotNumberedInvoicesItemsView.BillingCurrencyDescription AS BillingCurrency
  14.          , dbo.NotNumberedInvoicesItemsView.NetTaxBase              AS BillingItemTotal
  15.          , dbo.NotNumberedInvoicesItemsView.ExchangeRate            AS ExRateToBase
  16.          , dbo.NotNumberedInvoicesItemsView.BaseTotal
  17.        
  18. ------------------------------------------------------------------------------------------------------------------------------------------------     
  19. --PREVIO
  20.          , dbo.LasBilledInvoiceItemsForContractByMonthView.BillingMonth AS PreviousBillingMonth
  21.          , dbo.LasBilledInvoiceItemsForContractByMonthView.Quantity     AS PreviousQtySubscribers
  22.          , dbo.LasBilledInvoiceItemsForContractByMonthView.ExchangeRate AS PreviousExRateToBase
  23.          , dbo.LasBilledInvoiceItemsForContractByMonthView.NetTaxBase       AS PreviousBillingTotalItem
  24.          , dbo.LasBilledInvoiceItemsForContractByMonthView.BaseTotal        AS PreviousBaseTotal
  25.          , dbo.LasBilledInvoiceItemsForContractByMonthView.ContractExchangeRate AS PreviousExRateToInvoice
  26. ------------------------------------------------------------------------------------------------------------------------------------------------
  27.          , dbo.NotNumberedInvoicesItemsView.BillingDocumentId               AS ActualBillingDocumentId
  28.  
  29. ------------------------------------------------------------------------------------------------------------------------------------------------
  30. --PREVIO
  31.          , dbo.LasBilledInvoiceItemsForContractByMonthView.BillingDocumentId AS PreviousBillingDocumentId
  32. ------------------------------------------------------------------------------------------------------------------------------------------------
  33.  
  34.          , dbo.NotNumberedInvoicesItemsView.PeriodFrom
  35.  
  36. ------------------------------------------------------------------------------------------------------------------------------------------------
  37. --PREVIO
  38.          , dbo.LasBilledInvoiceItemsForContractByMonthView.PeriodFrom       AS PreviousPeriodFrom
  39.          , dbo.LasBilledInvoiceItemsForContractByMonthView.UnitPrice        AS PreviousRate
  40. ------------------------------------------------------------------------------------------------------------------------------------------------
  41.          , dbo.NotNumberedInvoicesItemsView.UnitPrice                       AS Rate
  42.          , dbo.NotNumberedInvoicesItemsView.ItemType
  43.          , dbo.NotNumberedInvoicesItemsView.BillingCurrencySymbol
  44.          , dbo.NotNumberedInvoicesItemsView.ContractCurrencySymbol
  45.  
  46.          , dbo.NotNumberedInvoicesItemsView.TransmissionMode    AS TransmissionModeValue
  47.          , dbo.NotNumberedInvoicesItemsView.TransmissionType
  48.          , dbo.NotNumberedInvoicesItemsView.TypeOfSubscribers   AS TypeOfSubscribersValue
  49.          , dbo.NotNumberedInvoicesItemsView.BillingDocumentNumber AS BillingDocumentNumber
  50.  
  51.         FROM dbo.NotNumberedInvoicesItemsView
  52.         LEFT OUTER JOIN dbo.LasBilledInvoiceItemsForContractByMonthView ON
  53.     dbo.NotNumberedInvoicesItemsView.ServiceType = dbo.LasBilledInvoiceItemsForContractByMonthView.ServiceType AND
  54.     dbo.NotNumberedInvoicesItemsView.NUMBER = dbo.LasBilledInvoiceItemsForContractByMonthView.NUMBER AND
  55.     dbo.LasBilledInvoiceItemsForContractByMonthView.MonthBilled = dbo.NotNumberedInvoicesItemsView.MonthBilled AND
  56.     dbo.LasBilledInvoiceItemsForContractByMonthView.RangeBilled = dbo.NotNumberedInvoicesItemsView.RangeBilled AND
  57.     dbo.LasBilledInvoiceItemsForContractByMonthView.ItemType = dbo.NotNumberedInvoicesItemsView.ItemType AND
  58.     dbo.LasBilledInvoiceItemsForContractByMonthView.BillingMonth = dbo.NotNumberedInvoicesItemsView.BillingMonth AND
  59.  
  60.     --SameProduct
  61.     dbo.NotNumberedInvoicesItemsView.ProductBrandId = dbo.LasBilledInvoiceItemsForContractByMonthView.ProductBrandId AND
  62.     dbo.NotNumberedInvoicesItemsView.PeriodCategoryType = dbo.LasBilledInvoiceItemsForContractByMonthView.PeriodCategoryType AND
  63.     dbo.NotNumberedInvoicesItemsView.AntiquityClientType = dbo.LasBilledInvoiceItemsForContractByMonthView.AntiquityClientType AND
  64.     dbo.NotNumberedInvoicesItemsView.BillingGroup = dbo.LasBilledInvoiceItemsForContractByMonthView.BillingGroup AND
  65.     ISNULL(NotNumberedInvoicesItemsView.TransmissionTypesId, -1) = ISNULL(LasBilledInvoiceItemsForContractByMonthView.TransmissionTypesId, -1) AND
  66.     ISNULL(NotNumberedInvoicesItemsView.FeedId, -1) = ISNULL(LasBilledInvoiceItemsForContractByMonthView.FeedId, -1) AND
  67.     ISNULL(NotNumberedInvoicesItemsView.TypeOfSubscribers, '') = ISNULL(LasBilledInvoiceItemsForContractByMonthView.TypeOfSubscribers, '')
  68.  
  69.    
  70.     UNION  
  71. --////////////////////////////////////////////////////////////////////////////////////////////////////
  72.  
  73.  
  74.  
  75. --For Subs Adjust. for delthas
  76.     (
  77.     SELECT dbo.NotNumberedCreditNoteDebitNoteItemsView.MsoName                          AS MSO
  78.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.DocumentType         
  79.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.NUMBER                        AS ContractNumber
  80.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ProductBrandName              AS ProductBrand
  81.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.PeriodCategoryType            AS Category
  82.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.AntiquityClientType           AS SubscriberAge
  83.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.Quantity                      AS SubscribersQty
  84.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractCurrencyDescription   AS ContractCurrency
  85.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractExchangeRate          AS ExRateToInvoice
  86.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingCurrencyDescription    AS BillingCurrency
  87.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.NetTaxBase * dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemSign AS BillingItemTotal
  88.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ExchangeRate                  AS ExRateToBase
  89.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.BaseTotal * dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemSign AS BaseTotal
  90.                  
  91. ------------------------------------------------------------------------------------------------------------------------------------------------     
  92. --PREVIO                 
  93.             , dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingMonth             AS PreviousBillingMonth
  94.             , SUM(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.Quantity)                    AS PreviousQtySubscribers
  95.             , (SELECT ExchangeRate FROM dbo.BillingDocument bd1 WHERE bd1.BillingDocumentId = MAX(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingDocumentId)) AS PreviousExRateToBase
  96.             , SUM(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.NetTaxBase)              AS PreviousBillingTotalItem
  97.             , SUM(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BaseTotal)               AS PreviousBaseTotal
  98.             , (SELECT ContractExchangeRate FROM dbo.BillingDocument bd1 WHERE bd1.BillingDocumentId = MAX(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingDocumentId)) AS PreviousExRateToInvoice
  99. ------------------------------------------------------------------------------------------------------------------------------------------------     
  100.  
  101.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingDocumentId         AS ActualBillingDocumentId
  102.  
  103. ------------------------------------------------------------------------------------------------------------------------------------------------     
  104. --PREVIO
  105.             , MIN(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingDocumentId)       AS PreviousBillingDocumentId
  106. ------------------------------------------------------------------------------------------------------------------------------------------------     
  107.  
  108.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.PeriodFrom
  109.  
  110. ------------------------------------------------------------------------------------------------------------------------------------------------     
  111. --PREVIO
  112.             , dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.PeriodFrom               AS PreviousPeriodFrom
  113.             , (SELECT InformedUnitPrice FROM dbo.AccountingDocumentItem ADI
  114.                 WHERE ADI.AccountingDocumentItemId = MAX(dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.AccountingDocumentItemId)
  115.                     ) AS PreviousRate
  116. ------------------------------------------------------------------------------------------------------------------------------------------------     
  117.  
  118.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.UnitPrice                 AS Rate
  119.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemType
  120.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingCurrencySymbol
  121.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractCurrencySymbol
  122.  
  123.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.TransmissionMode  AS TransmissionModeValue
  124.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.TransmissionType
  125.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.TypeOfSubscribers AS TypeOfSubscribersValue
  126.             , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingDocumentNumber AS BillingDocumentNumber
  127.  
  128.     FROM dbo.NotNumberedCreditNoteDebitNoteItemsView
  129.     LEFT OUTER JOIN dbo.NumberedInvoicesCreditNoteDebitNoteItemsView ON
  130.     dbo.NotNumberedCreditNoteDebitNoteItemsView.ServiceType = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.ServiceType AND
  131.     dbo.NotNumberedCreditNoteDebitNoteItemsView.NUMBER = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.NUMBER AND
  132.     dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.MonthBilled = dbo.NotNumberedCreditNoteDebitNoteItemsView.MonthBilled AND
  133.     dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.RangeBilled = dbo.NotNumberedCreditNoteDebitNoteItemsView.RangeBilled AND
  134.     dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.ItemType = dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemType AND
  135.     dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingMonth = dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingMonth AND
  136.  
  137.     --SameProduct
  138.     dbo.NotNumberedCreditNoteDebitNoteItemsView.ProductBrandid = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.ProductBrandid  AND
  139.     dbo.NotNumberedCreditNoteDebitNoteItemsView.PeriodCategoryType = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.PeriodCategoryType AND
  140.     dbo.NotNumberedCreditNoteDebitNoteItemsView.AntiquityClientType = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.AntiquityClientType AND
  141.     dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingGroup = dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingGroup AND
  142.     ISNULL(NotNumberedCreditNoteDebitNoteItemsView.TransmissionTypesId, -1) = ISNULL(NumberedInvoicesCreditNoteDebitNoteItemsView.TransmissionTypesId, -1) AND
  143.     ISNULL(NotNumberedCreditNoteDebitNoteItemsView.FeedId, -1) = ISNULL(NumberedInvoicesCreditNoteDebitNoteItemsView.FeedId, -1) AND
  144.     ISNULL(NotNumberedCreditNoteDebitNoteItemsView.TypeOfSubscribers, '') = ISNULL(NumberedInvoicesCreditNoteDebitNoteItemsView.TypeOfSubscribers, '')
  145.  
  146.     GROUP BY dbo.NotNumberedCreditNoteDebitNoteItemsView.MsoName
  147.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.DocumentType         
  148.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.NUMBER
  149.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ProductBrandName
  150.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.PeriodCategoryType
  151.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.AntiquityClientType
  152.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.Quantity
  153.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractCurrencyDescription
  154.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractExchangeRate
  155.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingCurrencyDescription
  156.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.NetTaxBase * dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemSign
  157.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ExchangeRate
  158.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.BaseTotal * dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemSign
  159.         , dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.BillingMonth
  160.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingDocumentId
  161.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.PeriodFrom
  162.         , dbo.NumberedInvoicesCreditNoteDebitNoteItemsView.PeriodFrom
  163.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.UnitPrice
  164.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ItemType
  165.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingCurrencySymbol
  166.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.ContractCurrencySymbol
  167.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.TransmissionMode
  168.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.TransmissionType
  169.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.TypeOfSubscribers
  170.         , dbo.NotNumberedCreditNoteDebitNoteItemsView.BillingDocumentNumber
  171.     )
  172.    
  173.    
  174.    
  175.     UNION
  176. --//////////    //////////////////////////////////////////////////////////////////////////////////////////
  177. --Previous billing items without current billing items for Bill Affiliates.                  
  178.     SELECT  PastBillingItemsByMonth.MsoName AS MSO,
  179.             ActualBillingItems.DocumentType,
  180.             PastBillingItemsByMonth.NUMBER AS ContractNumber,
  181.             PastBillingItemsByMonth.ProductBrandName AS ProductBrand,
  182.             PastBillingItemsByMonth.PeriodCategoryType AS Category,
  183.             PastBillingItemsByMonth.AntiquityClientType AS SubscriberAge,
  184.             ActualBillingItems.Quantity AS SubscribersQty,
  185.             PastBillingItemsByMonth.ContractCurrencyDescription AS ContractCurrency,
  186.             ActualBillingItems.ContractExchangeRate AS ExRateToInvoice,
  187.             PastBillingItemsByMonth.BillingCurrencyDescription AS BillingCurrency,
  188.             ActualBillingItems.NetTaxBase AS BillingItemTotal,
  189.             ActualBillingItems.ExchangeRate AS ExRateToBase,
  190.             ActualBillingItems.BaseTotal,
  191. ------------------------------------------------------------------------------------------------------------------------------------------------     
  192. --PREVIO           
  193.             PastBillingItemsByMonth.BillingMonth AS PreviousBillingMonth,
  194.             PastBillingItemsByMonth.Quantity AS PreviousQtySubscribers,
  195.             PastBillingItemsByMonth.ExchangeRate AS PreviousExRateToBase,
  196.             PastBillingItemsByMonth.NetTaxBase AS PreviousBillingTotalItem,
  197.             PastBillingItemsByMonth.BaseTotal AS PreviousBaseTotal,
  198.             PastBillingItemsByMonth.ContractExchangeRate AS PreviousExRateToInvoice,
  199. ------------------------------------------------------------------------------------------------------------------------------------------------     
  200.  
  201.             ActualBillingItems.BillingDocumentId AS ActualBillingDocumentId,
  202. ------------------------------------------------------------------------------------------------------------------------------------------------     
  203. --PREVIO
  204.             PastBillingItemsByMonth.BillingDocumentId AS PreviousBillingDocumentId,
  205. ------------------------------------------------------------------------------------------------------------------------------------------------     
  206.  
  207.             ActualBillingItems.PeriodFrom,
  208. ------------------------------------------------------------------------------------------------------------------------------------------------     
  209. --PREVIO           
  210.             PastBillingItemsByMonth.PeriodFrom AS PreviousPeriodFrom,
  211.             PastBillingItemsByMonth.UnitPrice AS PreviousRate,
  212. ------------------------------------------------------------------------------------------------------------------------------------------------     
  213.  
  214.             ActualBillingItems.UnitPrice AS Rate,
  215.             PastBillingItemsByMonth.ItemType,
  216.             COALESCE(ActualBillingItems.BillingCurrencySymbol,PastBillingItemsByMonth.BillingCurrencySymbol) AS BillingCurrencySymbol,
  217.             COALESCE(ActualBillingItems.ContractCurrencySymbol,PastBillingItemsByMonth.ContractCurrencySymbol) AS ContractCurrencySymbol,
  218.             ActualBillingItems.TransmissionMode AS TransmissionModeValue,
  219.             ActualBillingItems.TransmissionType,
  220.             ActualBillingItems.TypeOfSubscribers AS TypeOfSubscribersValue,
  221.             ActualBillingItems.BillingDocumentNumber AS BillingDocumentNumber
  222.            
  223.         FROM dbo.NotNumberedInvoicesItemsView AS ActualBillingItems
  224.     RIGHT OUTER JOIN dbo.LasBilledInvoiceItemsForContractByMonthView AS PastBillingItemsByMonth ON
  225.     ActualBillingItems.ServiceType = PastBillingItemsByMonth.ServiceType AND
  226.     ActualBillingItems.NUMBER = PastBillingItemsByMonth.NUMBER AND
  227.     PastBillingItemsByMonth.MonthBilled = ActualBillingItems.MonthBilled AND
  228.     PastBillingItemsByMonth.RangeBilled = ActualBillingItems.RangeBilled AND
  229.     PastBillingItemsByMonth.ItemType = ActualBillingItems.ItemType AND
  230.     PastBillingItemsByMonth.BillingMonth = ActualBillingItems.BillingMonth AND
  231.  
  232.     --SameProduct
  233.     ActualBillingItems.ProductBrandid = PastBillingItemsByMonth.ProductBrandId AND
  234.     ActualBillingItems.PeriodCategoryType = PastBillingItemsByMonth.PeriodCategoryType AND
  235.     ActualBillingItems.AntiquityClientType = PastBillingItemsByMonth.AntiquityClientType AND
  236.     ActualBillingItems.BillingGroup = PastBillingItemsByMonth.BillingGroup AND
  237.  
  238.     ISNULL(ActualBillingItems.TransmissionTypesId, -1) = ISNULL(PastBillingItemsByMonth.TransmissionTypesId, -1) AND
  239.     ISNULL(ActualBillingItems.FeedId, -1) = ISNULL(PastBillingItemsByMonth.FeedId, -1) AND
  240.     ISNULL(ActualBillingItems.TypeOfSubscribers, '') = ISNULL(PastBillingItemsByMonth.TypeOfSubscribers, '')
  241.                      
  242.     WHERE EXISTS (
  243.         SELECT '1'
  244.         FROM dbo.NotNumberedInvoicesItemsView
  245.         WHERE
  246.             dbo.NotNumberedInvoicesItemsView.NUMBER = PastBillingItemsByMonth.NUMBER
  247.         AND dbo.NotNumberedInvoicesItemsView.BillingGroup = PastBillingItemsByMonth.BillingGroup
  248.         AND dbo.NotNumberedInvoicesItemsView.PeriodFrom = PastBillingItemsByMonth.PeriodFrom)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!