Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: How to show a balance for all denominations even when a denomination has no record in

    I know this is got be a simple solution, but for some reason it eludes me. Thanks for taking the time to read this. I need to create a report that shows the balance for each denomination for a pawn shop as an example.
    I need to pull all the data from the Denomination table and give them a specific denomination code using a case statement, join it with the Cash table and multiply the denomination value by the number of counted denominations from the cash table.
    My results should be 'Denomination Code', 'Denomination Balance'. Finally, if there were no denominations for that day, I still want to show the denomination with a $0 balance.

    Tables and Data:
    Denomination Table
    CREATE TABLE [DENOMINATION]
    [LINK] [varchar](31) NOT NULL,
    [NAME] [varchar](64) NOT NULL,
    [VALUE] [numeric](18, 6) NOT NULL,
    CONSTRAINT [DENOMINATION_LINK_PK] PRIMARY KEY

    Denomination Table Data
    Insert into DENOMINATION (LINK, NAME) Values('0015000012_2011-07-13_23:21:27', '1 cent', 0.010000)
    Insert into DENOMINATION (LINK, NAME) Values('0015000008_2011-07-13_23:20:08', '5 cent', 0.050000)
    Insert into DENOMINATION (LINK, NAME) Values('0015000004_2011-07-13_23:17:29', '10 cent', 0.100000)
    Insert into DENOMINATION (LINK, NAME) Values('0015000002_2011-07-13_23:16:50', '25 cent', 0.250000)
    Insert into DENOMINATION (LINK, NAME) Values('0015000000_2011-07-13_23:15:59', '50 cent', 0.500000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999998_2011-07-13_23:14:50', '1 dollar coin', 1.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0050999952_2011-07-14_01:59:01', '$1', 1.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0205999797_2011-07-14_13:02:03', '$2', 2.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999996_2011-07-13_23:06:30', '$5', 5.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999994_2011-07-13_23:05:45', '$10', 10.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999992_2011-07-13_23:05:16', '$20', 20.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999990_2011-07-13_23:04:54', '$50', 50.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0014999988_2011-07-13_23:04:35', '$100', 100.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0508999494_2012-01-21_15:43:21', '$500', 500.000000)
    Insert into DENOMINATION (LINK, NAME) Values('0508999496_2012-01-21_15:44:52' '$1000', 1000.000000)

    Cash Table
    CREATE TABLE [CASH]
    [LINK] [varchar](31) NOT NULL,
    [NBDENOM] [numeric](10, 0) NOT NULL,
    [LDENOM] [varchar](31) NOT NULL,
    CONSTRAINT [CASH_LINK_PK] PRIMARY KEY CLUSTERED
    [LDENOM] ASC, ([DENOMINATION].[LINK])
    --Not exactly sure how to link the LDENOM to the [DENOMINATION].[LINK] so I included it here.

    Cash Table Data
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:01', 10, '0015000012_2011-07-13_23:21:27')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:02', 20, '0015000008_2011-07-13_23:20:08')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:03', 30, '0015000004_2011-07-13_23:17:29')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:04', 40, '0015000002_2011-07-13_23:16:50')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:05', 50, '0015000000_2011-07-13_23:15:59')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:06', 60, '0014999998_2011-07-13_23:14:50')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:07', 70, '0050999952_2011-07-14_01:59:01')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:08', 80, '0205999797_2011-07-14_13:02:03')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:09', 90, '0014999996_2011-07-13_23:06:30')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:10', 11, '0014999994_2011-07-13_23:05:45')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:11', 22, '0014999992_2011-07-13_23:05:16')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:12', 33, '0014999990_2011-07-13_23:04:54')
    Insert into CASH (LINK, NBDENOM, LDENOM) Values('0015000012_2011-07-13_23:21:13', 44, '0014999988_2011-07-13_23:04:35')

    --Notice there are no values for the $500 or $1000 bills in the CASH table.

    Attempted Query:
    Select
    (Case "DENOMINATION"."NAME"
    When '1 cent' Then 1
    When '5 cent' Then 5
    When '10 cent' Then 10
    When '25 cent' Then 25
    When '50 cent' Then 50
    When '1 dollar' Then 101
    When '$1' Then 100
    When '$2' Then 200
    When '$5' Then 500
    When '$10' Then 1000
    When '$20' Then 2000
    When '$50' Then 5000
    When '$100' Then 10000
    When '$500' Then 50000
    When '$1000' Then 100000
    Else ''
    End) AS "Denomination Code",
    (Case
    When ("CASH"."LDENOM" = '0015000012_2011-07-13_23:21:27') --1 cent
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0015000008_2011-07-13_23:20:08') --5 cent
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0015000004_2011-07-13_23:17:29') --10 cent
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0015000002_2011-07-13_23:16:50') --25 cent
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0015000000_2011-07-13_23:15:59') --50 cent
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999998_2011-07-13_23:14:50') --1 dollar coin
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0050999952_2011-07-14_01:59:01') --$1
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0205999797_2011-07-14_13:02:03') --$2
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999996_2011-07-13_23:06:30') --$5
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999994_2011-07-13_23:05:45') --$10
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999992_2011-07-13_23:05:16') --$20
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999990_2011-07-13_23:04:54') --$50
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0014999988_2011-07-13_23:04:35') --$100
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0508999494_2012-01-21_15:43:21') --$500
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    When ("CASH"."LDENOM" = '0508999496_2012-01-21_15:44:52') --$1000
    Then (Replace(CAST((("CASH"."NBDENOM" * "DENOMINATION"."VALUE")) AS NUMERIC(18,2)),'.',''))
    Else (Replace(CAST(((0)) AS NUMERIC(18,2)),'.',''))
    End) As "Denomination Balance"
    FROM
    "CASH" "CASH"
    LEFT JOIN "DENOMINATION" "DENOMINATION" ON "CASH"."LDENOM" = "DENOMINATION"."LINK"

    My desired results:
    Denomination Code Denomination Balance
    1 010
    5 100
    10 300
    25 1000
    50 2500
    101 6000
    100 7000
    200 16000
    500 45000
    1000 11000
    2000 44000
    5000 165000
    10000 440000
    50000 000
    100000 000

    Results I get:
    Denomination Code Denomination Balance
    1 010
    5 100
    10 300
    25 1000
    50 2500
    101 6000
    100 7000
    200 16000
    500 45000
    1000 11000
    2000 44000
    5000 165000
    10000 440000

    How far off am I?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Look up LEFT JOIN.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I may be over simplifying things given the large amount of code you posted, but the following query does get you very close to the results you asked for.

    Code:
    select d.NAME, coalesce(c.NBDENOM * d.value * 100,0) as daMoney
    from CASH c
    right join DENOMINATION d on d.LINK = c.LDENOM
    order by d.value
    Note the use of the right join to get the desired output. If you want to use the left join, you need to switch your tables around

    Code:
    select d.NAME, coalesce(c.NBDENOM * d.value * 100,0) as daMoney
    from DENOMINATION d
    left join CASH c on d.LINK = c.LDENOM
    order by d.value
    You may want to read up on the differences between right & left joins.

    The only hitch is the $1 coin. You are using a mystical code (101) to differentiate this from a $1 bill. I would suggest you create a new column in your DENOMINATION table for a reporting purposes. Copy code 101 and the other existing values (value * 100) into this new column.
    If this new code were in the table, the output would match exactly what you are looking for without the huge and nasty case statements. Then, with a minor modification, you can use the query I posted.
    Just think of the maintenance difference when you revisit this in 6 months after a $5 coin is created or the penny is taken out of circulation.

    HTH
    Last edited by LinksUp; 02-04-13 at 19:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •