Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. bradlee27514

    Using Current Date parameters in the SQL command in the DB Expert

    Thanks all, got the syntax and used datediff: where datediff(day,table.date,getdate()) between 0 and 3
  2. bradlee27514

    Using Current Date parameters in the SQL command in the DB Expert

    I have a query pulling some data in a SQL command something like select table.invoicenumber, table.invoicedate from table What I want to do is restrict the records in the SQL command based on the invoicedate field, something like where table.invoicedate =< currentdate and...
  3. bradlee27514

    Trouble getting a distinct count in SQL command

    select sop10100.soptype, sop10100.sopnumbe, sop10100.orignumb, sop10100. reqshipdate, sop10100.custnmbr, sop10100.custname, sop10100.cstponbr, sop10100.city, sop10100.state, sop10100.miscamnt, sop10100.docamnt, sop10100.creatddt, sop10200.sopnumbe, sop10200.itemnmbr...
  4. bradlee27514

    Trouble getting a distinct count in SQL command

    Thanks. Where in my SQL command do I put that? A lot of the things I try are yielding Syntax errors. It's a SQL Server 2005 database for our Microsoft Dynamics Great Plains.
  5. bradlee27514

    Trouble getting a distinct count in SQL command

    It is confusing I'm sorry. I should have been more clear. Certain customers will always be in one group, while the rest of the customers will either be in a "single" or "multiple" group. The logic would be this if ( {Command.custnmbr} startswith 'swc' or...
  6. bradlee27514

    Trouble getting a distinct count in SQL command

    I see what you're saying, unfortunately my grouping logic will be more complicated. there will be 3 groups group 1 will be a group of customers regardless of the count group 2 will be the rest of the customers with a count of 1 group 3 will be the rest of the customers with a count > 1 I need...
  7. bradlee27514

    Trouble getting a distinct count in SQL command

    How would I do that? If you mean using a formula or running total in CR, then that does not appear to work. That formula/running total is not available for selection in the grouping interface. If the count was calculated before the data got to CR (e.g. in the SQL command) then it would be...
  8. bradlee27514

    Trouble getting a distinct count in SQL command

    I have this query: select sop10100.soptype, sop10100.sopnumbe, sop10100.orignumb, sop10100. reqshipdate, sop10100.custnmbr, sop10100.custname, sop10100.cstponbr, sop10100.city, sop10100.state, sop10100.miscamnt, sop10100.docamnt, sop10100.creatddt, sop10200.sopnumbe...
  9. bradlee27514

    How can I find the week number of the year?

    I can't seem to figure this out with the available date functions. I want to take a date (e.g. currentdate) and have Crystal return what week of the year (e.g. 43) it is. Any ideas?
  10. bradlee27514

    Selecting a specific record value to input in header

    I am attempting something that I think should be rather simple. I think I can do it inside the report but perhaps I need to do something in my SQL command. Any help is appreciated. My command is pretty simple: select gl00100.actindx, gl00100.actnumbr_1, gl00100.actdescr...
  11. bradlee27514

    Some Strange Date Formulas

    oh wait, you are saying all of that would go in the select expert box right? my thinking was create a formula for start date and one for enddate and then use logic from there.
  12. bradlee27514

    Some Strange Date Formulas

    im not pulling the date from a db, i want to manipulate currentdate, basically want a formula for startdate and one for enddate, then just want to use them in the select expert
  13. bradlee27514

    Some Strange Date Formulas

    I'm sure this is possible, but can't seem to figure the coding out. I have a report I want to run any given day and select data based on a start date and an end date. I want the start date to be day 1 of 3 months ago. I want the end date to be the last day of the last month. For example, If...
  14. bradlee27514

    Trouble Restricting Smartlist Access to Financial

    GP 10. I've managed to remove access to all Financial smartlists except for 'Accounts' and 'Multidimensional Analysis' . I created a security role for a test user, displayed the financial series task IDs and un checked everything, yet they still these two folders in the smartlist. Any...
  15. bradlee27514

    Summing group footer data

    This worked: "Use a RT as a SUM but set the evaluate for section to once per group (group2) and reset at GROUP1 palced on GF1.
  16. bradlee27514

    Summing group footer data

    I need it in group footer 1 because that is how the report is organized, by customer locations. That running total would sum up each record just as it is doing currently for each group, correct? I think my initial post may have been mis-labeled. I am grouping by customer and then by invoice...
  17. bradlee27514

    Summing group footer data

    It is this field {Command.totalapplyamount} from my query: select * from (select aptodcnm, sum(actualapplytoamount) as totalapplyamount from rm30201 group by aptodcnm) as rm30201 inner join sop30200 on rm30201.aptodcnm=sop30200.sopnumbe left join sop30300 on...

Part and Inventory Search

Back
Top