sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclose)) as avgtimeclose , sum(case when typeid=2 then amount else 0 end) as typename1 , sum(case when typeid=2 then 1 else 0 end) as typename1count , sum(case when typeid=3 then amount else 0 end) as typename2 , sum(case when typeid=3 then 1 else 0 end) as typename2count from table1 group by weeksago isn't there a different way to solve this without the using the case statements? maybe with a crosstab query?
|
0 |
|
Utf
2/12/2010 2:32:01 PM
sqlserver.programming
1873 articles. 
0 followers.
9 Replies
1227 Views
This is a good method to get the counts. In essence you are doing a cross tab query, this is what the CASE expressions do. And since you are pivoting on multiple columns using CASE expressions is better than using the PIVOT operator. -- Plamen Ratchev http://www.SQLStudio.com
|
|
0 |
|
Plamen
2/12/2010 3:06:29 PM
thanks for the help, rod. "Plamen Ratchev" wrote: > This is a good method to get the counts. In essence you are doing a cross tab query, this is what the CASE expressions > do. And since you are pivoting on multiple columns using CASE expressions is better than using the PIVOT operator. > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
|
|
0 |
|
Utf
2/12/2010 7:38:01 PM
Is there a way to solve this without having knowledge about the typeid, for instance if a new typeid got added I wouldn't have to go back and edit the sql to add the new type? "Plamen Ratchev" wrote: > This is a good method to get the counts. In essence you are doing a cross tab query, this is what the CASE expressions > do. And since you are pivoting on multiple columns using CASE expressions is better than using the PIVOT operator. > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
|
|
0 |
|
Utf
2/15/2010 3:29:01 PM
You would have to use some form of dynamic pivoting, one example here: http://pratchev.blogspot.com/2008/12/dynamic-pivoting.html -- Plamen Ratchev http://www.SQLStudio.com
|
|
0 |
|
Plamen
2/15/2010 3:55:17 PM
Thank you, Rod. "Plamen Ratchev" wrote: > You would have to use some form of dynamic pivoting, one example here: > http://pratchev.blogspot.com/2008/12/dynamic-pivoting.html > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
|
|
0 |
|
Utf
2/15/2010 4:54:01 PM
do you know how would show a bi-weekly result set instead of a weekly one? "rodchar" wrote: > Hi All, > > Given: > > table1 > --------- > id, cdate, weeksago, amount, typeid, timeclose > 496, 2010-02-11, 0, 450, 3, 1 > 490, 2010-02-01, 1, 200, 4, 1 > 491, 2010-02-01, 1, 350, 2, 2 > 493, 2010-02-01, 1, 500, 5, 1 > 489, 2010-01-21, 3, 150, 2, 1 > 136, 2010-01-12, 4, 500, 3, 2 > 137, 2010-01-12, 4, 100, 3, 1 > 138, 2010-01-12, 4, 500, 2, 1 > > 1. i'm trying to create a sql that will give me a summary resultset grouped > by weeksago. > 2. i want to sum the columns and get counts based on typeid. > 3. avg of timeclose > > here's the sql i have: > > select weeksago, > , (sum(timeClose)/(count(timeclose)) as avgtimeclose > , sum(case when typeid=2 then amount else 0 end) as typename1 > , sum(case when typeid=2 then 1 else 0 end) as typename1count > , sum(case when typeid=3 then amount else 0 end) as typename2 > , sum(case when typeid=3 then 1 else 0 end) as typename2count > from table1 > group by weeksago > > > isn't there a different way to solve this without the using the case > statements? > maybe with a crosstab query?
|
|
0 |
|
Utf
2/15/2010 7:18:17 PM
rodchar wrote: > do you know how would show a bi-weekly result set instead of a weekly one? > Simply use this expression to group by: weeksago / 2 -- Plamen Ratchev http://www.SQLStudio.com
|
|
0 |
|
Plamen
2/15/2010 7:31:31 PM
would you please give me an example of what that might look like: date, amount 12/27/2009, $10 12/28/2009, $10 12/1/2009, $5 11/10/2009, $5 11/9/2009, $10 11/8/2009, $10 if these were grouped by bi-weekly and summed amounts? "Plamen Ratchev" wrote: > rodchar wrote: > > do you know how would show a bi-weekly result set instead of a weekly one? > > > > Simply use this expression to group by: > > weeksago / 2 > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
|
|
0 |
|
Utf
2/16/2010 2:39:02 AM
Sorry guys (Plamen) didn't mean to beat this dead horse to 2nd death. "rodchar" wrote: > Hi All, > > Given: > > table1 > --------- > id, cdate, weeksago, amount, typeid, timeclose > 496, 2010-02-11, 0, 450, 3, 1 > 490, 2010-02-01, 1, 200, 4, 1 > 491, 2010-02-01, 1, 350, 2, 2 > 493, 2010-02-01, 1, 500, 5, 1 > 489, 2010-01-21, 3, 150, 2, 1 > 136, 2010-01-12, 4, 500, 3, 2 > 137, 2010-01-12, 4, 100, 3, 1 > 138, 2010-01-12, 4, 500, 2, 1 > > 1. i'm trying to create a sql that will give me a summary resultset grouped > by weeksago. > 2. i want to sum the columns and get counts based on typeid. > 3. avg of timeclose > > here's the sql i have: > > select weeksago, > , (sum(timeClose)/(count(timeclose)) as avgtimeclose > , sum(case when typeid=2 then amount else 0 end) as typename1 > , sum(case when typeid=2 then 1 else 0 end) as typename1count > , sum(case when typeid=3 then amount else 0 end) as typename2 > , sum(case when typeid=3 then 1 else 0 end) as typename2count > from table1 > group by weeksago > > > isn't there a different way to solve this without the using the case > statements? > maybe with a crosstab query?
|
|
0 |
|
Utf
2/16/2010 4:28:01 PM
Similar Artilces:
Receipt Printer Help!!
Can a Verifone 250 credit card receipt printer be used with RMS as a cash receipt printer? If so, what cable connectors would be needed to do this. The only cable connector we have goes to the credit card terminal. Kelly Why would you want to? Very slow printer!!!! Get a Samsung erp-350 or a 270. Probibly have to have a custom cable. >-----Original Message----- >Can a Verifone 250 credit card receipt printer be used >with RMS as a cash receipt printer? If so, what cable >connectors would be needed to do this. The only cable >connector we have goes to the credit card ...
Suspicion of CListCtrl memory leak, Help !
I have a testing MFC DialogBase project which has only one control -- ListCtrl. When the app exit , i found that the memory is not free by the system by watch the System Performance dialog page. All that i do is within CListCDlg::OnTimer() func, show as bellow: < code snippet> void CListCDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); //{{AFX_DATA_MAP(CListCDlg) DDX_Control(pDX, IDC_LIST1, m_MyList); //}}AFX_DATA_MAP } void CListCDlg::OnTimer(UINT nIDEvent) { TCHAR sContent[ 128 ]; //I have not alloc any data from heap, all the data added to CListCtl is ...
Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...
is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...
VLOOKUP help. Im an Excel Newbie!!1
I have an Invoice worksheet which I must use VLOOKUP function to import data such as customer name, customer code, customer address, etc. My study sheets didn't explain how to use VLOOKUP for obtaining data from different files. Basically on the Invoice worksheet, in the cell under customer code use the VLOOKUP function to enter the customers name (from the table on the customers worksheet). In the cell under customers name use VLOOKUP to enter customers address, in the cell under customers address use VLOOKUP to enter suburb,state and postcode (from customer worksheet). Then I need to...
Help with database
I need to create a database that lists companies, with dates items done by, money paid, notes etc. Thats okay, but I need to retain the previous years records and start a new year off fresh. How would I do that? Jenni, It sounds like you have never created a database so I'm going to suggest you start here... Jeff Conrad's resources page... http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page... http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP)... http://allenbrowne.com/casu...
Aarrgghh!!! Can send external mail but not receive it
My customer has one Exchange Server with 2 domains - I'll call them 'companyA.co.uk' and 'companyB.net'. The users for CompanyA are the same people as the users for CompanyB. There is a requirement for these users to send and receive email (they use Outlook) from both their 'companyA.co.uk' and their 'companyB.net' addresses. I acheived this by configuring 2 user accounts per user in Active Directory and then giving one user full control of the other, and by giving one user permissions to view/send as etc on behalf of the other. This worked fine for over...
i need help
i signed into windows live again. i used to have this email several months ago without a problem and one day i tried to log in and it kept telling me i had the wrong password i tried to reset it with out any luck. what can i do to log back into POP3 again? Live.com Settings: Server names and ports: POP3 = pop3.live.com Port = 995 SMTP = smtp.live.com Port = 587 Checkboxes you need to check include: Server Tab: "My server requires authentication" (Click "Settings" button also and fill in "Log on Using" email address - *** bu...
HTML Help Context Sens. Help in Dialogs
Hey folks, I've been able to add context-sensitive HTML Help to an MFC dialog box, but there's an annoying behavior that I'm wondering if there's a workaround for (or if it's a bug). Version is VS2008 (not SP1 beta). If I click the "Help Tool" on a particular button that I've got mapped to an HTML file, the wrong control help ID gets sent to the OnHelpInfo(HELPINFO* pHelpInfo) handler. Visually, when running the dialog, there appears to be a "cursor" of sorts that highlights one button at a time (focus?), and this cursor seems to respond to...
Major help needed!!!!
Hello, Just came across this problem today. I have a spreadsheet I've been using for over 2 years that was originally created in excel 97. We are now working with excel 2003. The error that occurs is when I copy a cell in the existing spreadsheet to a cell in a new spreadsheet, the date changes by 4 years and 1 day. ex: 10/21/04 becomes 10/20/00. However, If I open a new spreadsheet and place a date in a cell, copy that date, open another new spreadsheet and paste the cell into it then the date does not change. I'm taking it that it's because of my original spreadsheet being d...
Formulas
I have a huge database that I need to add fields and build a couple of formulas for those fields. Right now I have 3 months worth of data that I need to be able to report trending on. Within those 3 months, I have loan data breaking out how many loans have closed for a multitude of reasons. I need to trend how many each month close for those reasons and what they are as a percentage of the overall total of loans for that month. Any suggestions? To see how many closed in each month with each reason, you could just do a query that summarizes the data. I don't know what yo...
Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...
Help! #2
Hello, i have to program an application which should do the following 1. Receive the binary data on 2 channels. 2. each channel will store their corresponding data to a common file (july4.text) (each day 1 file will be created) 3. Each day the data received will be close to 30 MB of raw data (thought SQL will not be appropriate for this) 4. The data which is stored in a file will have around 5000 parameters.(ie: name,age,temperature,etc,etc) 5. MFC application to read/parse the specified data(queried like june20th to july2nd) and display it in graphical manner.(MS Chart?) 6. The parameters ...
Subquery help
I am trying to set a value in a variable and am getting the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." The set line it fails on is below. How can I work around this? Thanks. SET @FedWHAllow = (SELECT FedWHAllow FROM PayFreq WHERE PayFrequency = (SELECT PayFrequency FROM BranchPayrollGroups WHERE PayrollGroup = @PayrollGroup)); -- David You can change the WHERE clause predicate in the subquery to guarantee only a single row is returne...
Free & Busy Data messed up.. Plz Help!!
We have exchange v5.5. Recently one of the exchange admins was in the public folders messing around with permissions. He said he didn't change anything but something had to have changed because now all free and busy data, public address book, tasks, notes, etc are not accessible to new users. Any time we create a new email account they can't view there calendar (says it doesn't exist), nothing shows up in the global address list. Anytime they receive an email to accept a meeting it says unable to open item. I've done the usual things like reset the server. I loo...
Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...
CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...
SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...
Vlookup help #2
I have two worksheets. Sheet 1 contains a number in column A and a name in column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N...
SFO help
I have a quick question. My client's SFO button's wont install, and generally, their SFO installation is unstable. Given that we're hosting Exchange and CRM for the client and they're accessing our systems via VPN, does SFO work when their systems are not part of the domain that we've set up for them locally on our premises? Thanks!! Chris ...
sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...
amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...
Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...
Retrieve emails/addresses Help
I recently removed Windows XP and went back to Windows ME. Upon doing so I mistakingly lost all the emails and address updates made in Outlook Express made since I changed from Windows ME to XP (3 mths). Any ideas on how to retrieve those lost emails and addresses in Outlook Express? To go from XP to Me, you had to re-install the OS, which more than likely wiped out everything. Ray at work "phil horan" <philhoran@yahoo.com> wrote in message news:03ba01c3610e$904b3470$a401280a@phx.gbl... > I recently removed Windows XP and went back to Windows ME. > Upon doing so ...
Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...
Can a Verifone 250 credit card receipt printer be used with RMS as a cash receipt printer? If so, what cable connectors would be needed to do this. The only cable connector we have goes to the credit card terminal. Kelly Why would you want to? Very slow printer!!!! Get a Samsung erp-350 or a 270. Probibly have to have a custom cable. >-----Original Message----- >Can a Verifone 250 credit card receipt printer be used >with RMS as a cash receipt printer? If so, what cable >connectors would be needed to do this. The only cable >connector we have goes to the credit card ...
Suspicion of CListCtrl memory leak, Help !
I have a testing MFC DialogBase project which has only one control -- ListCtrl. When the app exit , i found that the memory is not free by the system by watch the System Performance dialog page. All that i do is within CListCDlg::OnTimer() func, show as bellow: < code snippet> void CListCDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); //{{AFX_DATA_MAP(CListCDlg) DDX_Control(pDX, IDC_LIST1, m_MyList); //}}AFX_DATA_MAP } void CListCDlg::OnTimer(UINT nIDEvent) { TCHAR sContent[ 128 ]; //I have not alloc any data from heap, all the data added to CListCtl is ...
Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...
is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...
VLOOKUP help. Im an Excel Newbie!!1
I have an Invoice worksheet which I must use VLOOKUP function to import data such as customer name, customer code, customer address, etc. My study sheets didn't explain how to use VLOOKUP for obtaining data from different files. Basically on the Invoice worksheet, in the cell under customer code use the VLOOKUP function to enter the customers name (from the table on the customers worksheet). In the cell under customers name use VLOOKUP to enter customers address, in the cell under customers address use VLOOKUP to enter suburb,state and postcode (from customer worksheet). Then I need to...
Help with database
I need to create a database that lists companies, with dates items done by, money paid, notes etc. Thats okay, but I need to retain the previous years records and start a new year off fresh. How would I do that? Jenni, It sounds like you have never created a database so I'm going to suggest you start here... Jeff Conrad's resources page... http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page... http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP)... http://allenbrowne.com/casu...
Aarrgghh!!! Can send external mail but not receive it
My customer has one Exchange Server with 2 domains - I'll call them 'companyA.co.uk' and 'companyB.net'. The users for CompanyA are the same people as the users for CompanyB. There is a requirement for these users to send and receive email (they use Outlook) from both their 'companyA.co.uk' and their 'companyB.net' addresses. I acheived this by configuring 2 user accounts per user in Active Directory and then giving one user full control of the other, and by giving one user permissions to view/send as etc on behalf of the other. This worked fine for over...
i need help
i signed into windows live again. i used to have this email several months ago without a problem and one day i tried to log in and it kept telling me i had the wrong password i tried to reset it with out any luck. what can i do to log back into POP3 again? Live.com Settings: Server names and ports: POP3 = pop3.live.com Port = 995 SMTP = smtp.live.com Port = 587 Checkboxes you need to check include: Server Tab: "My server requires authentication" (Click "Settings" button also and fill in "Log on Using" email address - *** bu...
HTML Help Context Sens. Help in Dialogs
Hey folks, I've been able to add context-sensitive HTML Help to an MFC dialog box, but there's an annoying behavior that I'm wondering if there's a workaround for (or if it's a bug). Version is VS2008 (not SP1 beta). If I click the "Help Tool" on a particular button that I've got mapped to an HTML file, the wrong control help ID gets sent to the OnHelpInfo(HELPINFO* pHelpInfo) handler. Visually, when running the dialog, there appears to be a "cursor" of sorts that highlights one button at a time (focus?), and this cursor seems to respond to...
Major help needed!!!!
Hello, Just came across this problem today. I have a spreadsheet I've been using for over 2 years that was originally created in excel 97. We are now working with excel 2003. The error that occurs is when I copy a cell in the existing spreadsheet to a cell in a new spreadsheet, the date changes by 4 years and 1 day. ex: 10/21/04 becomes 10/20/00. However, If I open a new spreadsheet and place a date in a cell, copy that date, open another new spreadsheet and paste the cell into it then the date does not change. I'm taking it that it's because of my original spreadsheet being d...
Formulas
I have a huge database that I need to add fields and build a couple of formulas for those fields. Right now I have 3 months worth of data that I need to be able to report trending on. Within those 3 months, I have loan data breaking out how many loans have closed for a multitude of reasons. I need to trend how many each month close for those reasons and what they are as a percentage of the overall total of loans for that month. Any suggestions? To see how many closed in each month with each reason, you could just do a query that summarizes the data. I don't know what yo...
Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...
Help! #2
Hello, i have to program an application which should do the following 1. Receive the binary data on 2 channels. 2. each channel will store their corresponding data to a common file (july4.text) (each day 1 file will be created) 3. Each day the data received will be close to 30 MB of raw data (thought SQL will not be appropriate for this) 4. The data which is stored in a file will have around 5000 parameters.(ie: name,age,temperature,etc,etc) 5. MFC application to read/parse the specified data(queried like june20th to july2nd) and display it in graphical manner.(MS Chart?) 6. The parameters ...
Subquery help
I am trying to set a value in a variable and am getting the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." The set line it fails on is below. How can I work around this? Thanks. SET @FedWHAllow = (SELECT FedWHAllow FROM PayFreq WHERE PayFrequency = (SELECT PayFrequency FROM BranchPayrollGroups WHERE PayrollGroup = @PayrollGroup)); -- David You can change the WHERE clause predicate in the subquery to guarantee only a single row is returne...
Free & Busy Data messed up.. Plz Help!!
We have exchange v5.5. Recently one of the exchange admins was in the public folders messing around with permissions. He said he didn't change anything but something had to have changed because now all free and busy data, public address book, tasks, notes, etc are not accessible to new users. Any time we create a new email account they can't view there calendar (says it doesn't exist), nothing shows up in the global address list. Anytime they receive an email to accept a meeting it says unable to open item. I've done the usual things like reset the server. I loo...
Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...
CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...
SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...
Vlookup help #2
I have two worksheets. Sheet 1 contains a number in column A and a name in column C. On Sheet 2 I put my data from a csv file. What I would like to do is vlookup Sheet 1 column C from the information on Sheet 2 and return the number in column A that is associated with the name in column C. Ex. Sheet 1 Sheet 2 A B C A B 33 N6-270-15 N6-270-75 35 34 N6-270-30 N...
SFO help
I have a quick question. My client's SFO button's wont install, and generally, their SFO installation is unstable. Given that we're hosting Exchange and CRM for the client and they're accessing our systems via VPN, does SFO work when their systems are not part of the domain that we've set up for them locally on our premises? Thanks!! Chris ...
sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...
amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...
Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...
Retrieve emails/addresses Help
I recently removed Windows XP and went back to Windows ME. Upon doing so I mistakingly lost all the emails and address updates made in Outlook Express made since I changed from Windows ME to XP (3 mths). Any ideas on how to retrieve those lost emails and addresses in Outlook Express? To go from XP to Me, you had to re-install the OS, which more than likely wiped out everything. Ray at work "phil horan" <philhoran@yahoo.com> wrote in message news:03ba01c3610e$904b3470$a401280a@phx.gbl... > I recently removed Windows XP and went back to Windows ME. > Upon doing so ...
Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...