Forum Replies Created

Viewing 15 posts - 376 through 390 (of 541 total)

  • RE: Nested SELECT

    This one is now closed, many thanks for posting Garadin.

    kind regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 13, 2008 at 2:46 pm

    #884331

  • RE: Nested SELECT

    Update AGAIN. OK now amended further.....

    (Select SUM(SalesOrderItems.CurItemValue) - (ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') <> 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,

    This appears to return what I am after.....will need...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 13, 2008 at 10:11 am

    #884256

  • RE: Nested SELECT

    I have been having a play. I updated line:

    (Select SUM(SalesOrderItems.CurItemValue) - SUM(ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') = 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,

    Nearly but not quite (well I...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 13, 2008 at 10:07 am

    #884252

  • RE: Remove Schema / Remove Database User

    Thanks for that. It returned 0 rows so I guess I am safe to remove (as I thought). Best to check.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 8, 2008 at 9:37 am

    #882403

  • RE: Using row_number()

    Thanks for posting guys. I will get the desired output via reports.

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 4, 2008 at 5:08 am

    #880810

  • RE: Using row_number()

    Madhu not sure if you are looking at the most recent query?

    Here it is:

    SELECT

    case when rn = 1 then CustomerName else...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 3, 2008 at 3:22 am

    #880356

  • RE: Using row_number()

    Hi Dugi. I really appreciate the efforts to all of those who have posted.

    I am unable to do as requested as I would be in breach of the contractual...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 3, 2008 at 3:09 am

    #880352

  • RE: Using row_number()

    Guys I will test now, I have man flu....:)

    Watch this space......6.42am someone pin a medal on me.....

    Many Thanks,

    Phil.

    Update:

    row_number() over(

    PARTITION BY CUSTOMER,SalesOrders.SalesOrderId

    ORDER BY CUSTOMER,SalesOrders.SalesOrderId

    ) AS rn

    This returned error:

    Msg 209,...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 11:43 pm

    #880292

  • RE: Using row_number()

    Jacob that is perfect, what did you change?

    Finally was my approach valid in constructing a query to use in a report?

    Many Thanks,

    Phil.

    Update:

    Jacob in answer to my own question my approach...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 12:24 pm

    #880146

  • RE: Using row_number()

    Here is the amended code that returns error:

    Msg 8120, Level 16, State 1, Line 1

    Column 'r.rn' is invalid in the select list because it is not contained in either an...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 8:18 am

    #879972

  • RE: Using row_number()

    Hi guys, yes it was spilt correctly 🙂

    New error after changing InvoiceValue to PaymentMade in the outer query

    Msg 8120, Level 16, State 1, Line 1

    Column 'r.rn' is invalid in the...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 6:45 am

    #879917

  • RE: Using row_number()

    Hi Jacob, I appreciate it is difficult working blind. I made the change but now receive:

    Msg 207, Level 16, State 1, Line 5

    Invalid column name 'InvoiceValue'.

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 5:31 am

    #879878

  • RE: Using row_number()

    Hi Jacob, many thanks for taking time to reply.

    I have changed code but now receive different error:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "Payments.PaymentDate" could not be...

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 2, 2008 at 5:03 am

    #879872

  • RE: SQL Agent Log

    Thanks Glen.

    Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 1, 2008 at 2:27 pm

    #879732

  • RE: SQL Agent Log

    Hi Glen, yes I checked alerts.

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

    October 1, 2008 at 2:00 pm

    #879726

Viewing 15 posts - 376 through 390 (of 541 total)