Home A unique key to use in query operations for the view
Reply: 2

A unique key to use in query operations for the view

Waheed Alzekry
1#
Waheed Alzekry Published in 2017-12-05 20:12:56Z

I have this view table in sql server and I want to make it a unique key for each record in a separate column.

how I can make it a unique key for each record in a separate column? like index for example

This is the code created the view

SELECT     'order' AS type, id AS id, id_customer, amount AS debit, 0 AS credit, order_date AS date, '....' AS description
FROM         dbo.Orders
UNION ALL
SELECT     'receipt' AS type, id AS id, id_customer, 0 AS debit, amount AS credit, receipt_date AS date, 'cash' AS description
FROM         dbo.receipts

I am asking this presentation using this code But I have a problem when there is an invoice and receipt similar id number is repeated each one of them twice so I want a unique key to do the comparison in the following code

declare @id_customer int 
;with initial as(
  select *
  from result
  where id_customer= @id_customer
),report as(
  select r.id,[balance]=isnull((select sum(b.debit-b.credit)
               from initial b
               where b.[date]<r.[date]) + r.debit - r.credit ,r.debit-r.credit)
  from initial r
)

 select [Operation type] = type,
        reference_no = r.id,
        [description],
        [Debit] = debit,
        [Credit] = credit,
        [Balance] = b.balance
 from result r
 inner join report b on b.id = r.id
 where r.id_customer = @id_customer
 order by r.[date]
MohamedHamza
2#
MohamedHamza Reply to 2017-12-06 12:30:06Z

If you want to distinguish your records with a new unique key (as I understand) then you shall use the NEWID for your selection in order to have a unique key for the UnionAll query:

declare @id_customer int 
;with initial as(
  select *
  from result
  where id_customer= @id_customer
),report as(
  select r.id,[balance]=isnull((select sum(b.debit-b.credit)
               from initial b
               where b.[date]<r.[date]) + r.debit - r.credit ,r.debit-r.credit)
  from initial r
)

 select NEWID(), -- this is a new generated unique key
        [Operation type] = type,
        reference_no = r.id,
        [description],
        [Debit] = debit,
        [Credit] = credit,
        [Balance] = b.balance
 from result r
 inner join report b on b.id = r.id
 where r.id_customer = @id_customer
 order by r.[date]
Waheed Alzekry
3#
Waheed Alzekry Reply to 2017-12-07 19:01:10Z

You add a query that gives automatic numbering to the records and you do a comparison job on that field

  declare @id_customer int 
    ;with
     result3 as ( select ROW_NUMBER() OVER (ORDER BY date ASC) AS Row , * from result ),
     initial as( select   * from result3 where id_customer= @id_customer),
    report as( select r.row,[balance]=isnull((select sum(b.debit-b.credit)
                   from initial b
                   where b.[date]<r.[date]) + r.debit - r.credit ,r.debit-r.credit)
      from initial r
    )

     select  
             row=r.row,
            [Operation type] = type,
            reference_no = r.id,
            [description],
            [Debit] = debit,
            [Credit] = credit,
            [Balance] = b.balance,
            [date] = [date]
     from result3 r
     inner join report b on b.row = r.row
     where r.id_customer = @id_customer
     order by r.[date]
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.331675 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO