I've been working hard to drag my family and the family business into the modern life, took me long enough to get then off a 'coal-powered' Siemens Nixdorf miniframe. Anyway, they went for a package system, on a PC ntwork. Now I've been gradually migrating them again, teasing them (as sometimes you do) with the benefits that could be supplied by my software, written on an SQL 2k back-end.
So anyway, we now have a system that ties into FoxPro as its original system (the bought package), plus SQL 2K..... I'm now upgrading to SQL 2005, and Express for starters....
Is it going to be possible to easily to run queries against both SQL2k and FoxPro, or should I redo my current fix from 2k to 2005 and work from there... at the end of the day, it will all hopefully become a single system, as my new system takes over more and more of the other packages functionality. And on the flip side, if i remake my current SQL2k hook-in, am i going to hit problems when i remake/convert it to SQL Express (and yes, i know Express has limitations, but i need to persuade them to move before they'll pay up the cost of the full version)
Hi every one, I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview. How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
The company for which I work did not have a DBA until I started a few weeks ago. Whoever installed SQL2K used the wrong CD so they have been running Personal Edition on their servers. I have installed a new SQL2K standard instance and have restored everything except the jobs and DTS packages. Can the msdb from the Personal edition be restored to the standard instance?
Is it possible to downgrade SQL from Enterprise to Standard Edition, or do you have to remove the previous installation (uninstall) and reinstall. Meaning you would also have to restore all user databases? Thanks.
The following SQL statement returns the correct totals except that the total value is shifted on decimal place to the right. i.e. a real total of 955.68 is displayed as 9,556.80. The total_ar field is a money type. Any help would be appreciated.
Mark
/* AR report Total Greater than 365 days sorted by Dept */
select
a.dept as `Department `, ` A/R 365+ `=sum(case when datediff(day,c.bill_date, getdate()) > 365 then b.total_ar else 0 end)
from
hbm_persnl a, blt_billm b, blt_bill c,hbl_dept d,hbm_matter e
where e.matter_uno = b.matter_uno and a.empl_uno = e.bill_empl_uno and b.bill_tran_uno = c.tran_uno and b.ar_status = `O` and e.status_code=`OPEN`
Is there a problem with stability when one uses too many text (memo) fields?I'm having a problem with data from one record occasionally ending up inanother record, though apparently not through any user interaction.I'm using SQL 7 and accessing the tables through the ODBC driver. There aretwo tables with a one-to-one relationship -- TableA and TableB. TableB isthe one that's having this occasional problem. TableB has about 30 textfields out of a total of about 50 fields in the table. The problem isusually with one or two text fields containing data from a different record,one that was created close to when the problem record was created.Example: TableB has fields 1-9, say. In one record, field 1 has A, field 2as B, field 3 has C, and so on.In another record (created shortly after the other one), field 1 has AA,field 2 has BB, field 3 has CC, and so on.The user works with the records, everything's fine. Then one day the usernotices that in the second record, field 1 has AA, field 2 has BB, but field3 has C instead of CC. In other words, all data's fine, except for one,maybe two fields, that have data from a previously-created record.At first glance this seems to be a user-interaction thing, that somehow theuser inadvertantly placed data from the older record into the newer one,either through a shortcut, or by having that data on the clipboard, orwhatever. But a recent incident opposes that theory.I have two forms in the front end for editing records (the forms are boundto the ODBC table links). Form1 is bound only to TableA (the one thatdoesn't have the problem); Form2 is bound to a query that is TableA joinedwith TableB.In the recent incident where data shifted, both the record that was affectedand the record from which the data came were both only edited with Form1. Inother words, TableB never came into play; yet its data was somehow affected.When a record is created, the user completes a few fields in a form, andthen a stored procedure creates a record in TableA and then a sister recordin TableB (using the TableA record's autonumber primary key as its primarykey). A couple of user entered values are entered into the TableB record.But if the user is using Form1, they never see the TableB record.In this case, the TableB record's two fields got changed to fields from anearlier record (one which was created a little earlier the same day), eventhough both records were only edited in Form1 (according to the historylog), which doesn't touch TableB.Thus, I'm wondering if there's a possibility that either the SQL database orthe ODBC driver somehow shifted the data from one record into another. Thatseems far-fetched. But, at this point, since a table that the user didn'ttouch somehow had its data changed to data from a different record, I'mtrying to explore all possibilities.Thanks for any insight!Neil
I'm a quantitative securities analyst working with Compustat data(company fiscal reports and pricing feeds).My coworker came across a problem that we fixed, but I'd like tounderstand 'why' it was happening and just don't get it yet.Here's the starting query (reduced to simple prefixes):----INITIAL-----declare @mthDate datetimeset @mthDate = (select max(datadate) from t)declare @wkDate datetimeset @wkDate = (select max(datadate) from z)Select...fromzleft join a on a.idA = z.idA and a.idB = z.idBand a.datadate = z.datadateleft join b on b.idA = z.idA and b.idB = z.idBand b.datadate = @mthDateleft join c on c.idA = z.idA and c.idB = z.idBand c.datadate = @mthDateleft join d on d.idA = z.idA and d.idB = z.idBand d.datadate = z.datadateleft join e on e.idA = z.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = z.idAleft join k on k.ticker = z.tickerleft join m on m.idA = z.idA and m.idB=z.idBwherez.datadate = @wkDate<..some other expression filters...>and k.ticker is null----END INITIAL-----------As you can see 'z' is the main table that things are linked to viaouter joins (our security master). Table 'k' has a list of securitiesthat we wish not to have results for.There are 77 entries in table k and 4933 in table z for that giventime. We'd expect 4856 to be in this, but no. it's 4400, and then thenext time you run it (no changes whatsover) it's 2312, and so on.Every time you execute you get a different record count.My thought/and fix was to move the (k.ticker) predicate out of thewhere clause and get a differenced set from z using NOT EXISTS:-----AMENDED---------------from(z where not exists(select * from k where k.ticker=y.ticker)) yleft join a on a.idA = y.idA and a.idB = y.idBand a.datadate = y.datadateleft join b on b.idA = y.idA and b.idB = y.idBand b.datadate = @mthDateleft join c on c.idA = y.idA and c.idB = y.idBand c.datadate = @mthDateleft join d on d.idA = y.idA and d.idB = y.idBand d.datadate = y.datadateleft join e on e.idA = y.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = y.idAleft join k on k.ticker = y.tickerleft join m on m.idA = y.idA and m.idB=y.idBwherey.datadate = @wkDate<..some other expression filters...>------------------------And this works. It's stable now.I'm hoping someone here can help me up the wisdom curve by explainingto me 'why' the recordset kept changing before.My guess is that the cost-based optimizer was resorting the outer joinsand handing back different sets as a result, but i want to understand,and thought i'd come to this group for help.I appreciate your time and look forward to replies.Greg McIntire
I have implemented a application on SQL Server 2005, this application runs huge queries and some stored procedures. Now that client is shifting on SQL Server 64bit edition, what should i need to worry about. What all changes am i expected to handle.
I would also like to know if there are any other issues to be considered.
What I'm after is to create a new [dtP] column where the [dtP] is the [P] value at least 5 seconds later. I would assume I should use the dateadd() function but I've tried [Timestamp] = dateadd(ss,5,[Timestamp]) in my WHERE clause and to no avail. Any suggestions on the best way to proceed?
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersOctober2002 and ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24 run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
I encounter a bug while exporting to excel a matrix. one cell is shifted to right and so i have wrong numbers and empty cell in the middle of the matrix.
instead of :
ABS Recent College Graduate (C)
Male (M) Total Female (F) Male (M) Total
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col %
232 34 54 56 56 5 24 56 56 56 34 23 43 54 56
I get:
ABS Recent College Graduate (C)
Male (M) Total Female (F) Male (M) Total
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col %
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row %
232 34 54 56 56 5 24 56 56 56 34 23 43 54 56
does anyone familiar with a solution to this issue? Thanks. Yuval.
Hello All Our Collection team has a report that has an error that I'm tring to fix. The Person who created this report just hard coded the current month due as =MonthName(Month(Fields!CurrentDue.value) &" " &year((Fields!CurrentDue.Value). For the Year 2007 it works fine. But now that the new year has started the report Current Month Due read Dec 2008, but it should be Dec 2007. I thought Of putting in some IIF statements but there has to be a easier way. Is there a function that check the past due and automatically increases the year by one. Say the bill in sent in Nov 2007 past due should be 30 days, Dec 2007. 60 Day should be Jan 2008 and so one.
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
If I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause? WHERE tblInvoices.InvoiceDate ???
I have a database of the table payments and I want to create another table with the olds payments (18 months past) a have a field with the payment date date_pay. How I can do that?
I am trying to design a graph to shows out build status for past 7-8 weeks. I have to write the SQL query first.
After spending a good chunk of time, I could get some basic chart going. Problem is that I could give it a starting time till now (static) but I am not sure how to get the results for past 7-8 wekks.
1) What is a good approach for this since time is not static and it is changing.
any example or piece of sql syntax is greately appreciated.
I have some set of variables that I want to add in all my packages. Like other components, is there any way to copy & paste the variables from one package to another package?
I have made a database and need to duplicate the tables to represent new catogaries. The definitions in the tables will stay the same , just the table names will change.
Is there a way to copy and past / duplicate tables without retyping all the definitions each time.
Hello,I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007 resultA01/28/2007 resultB01/29/2007 .....02/26/2007 resultC02/27/2007 resultD Thanks for your helpArnold
I would like to show a datagrid where the columns are the last 12 months from whatever today is. I am drawing from what could be a rather large tagle in the DB. The Table looks like this:LogID LogTally LogDate Item 1 2 3/28/03 apples 2 1 3/29/03 apples 3 2 4/01/03 oranges 4 3 4/01/03 apples 5 1 4/01/03 grapes
.......
293 1 3/17/04 oranges 294 1 3/17/04 apples And if someone wants to see a report on apples, they would see something like:3/03 4/03 5/03..................3/04 3 3 - 3I can select one month (this one) like this:SELECT SUM(LogTally) AS Expr1 FROM TABLE WHERE (MONTH(GETDATE()) = MONTH(LogDate)) AND (YEAR(GETDATE()) = YEAR(LogDate)) AND (item = 'apples') GROUP BY itembut, I just don't know how to do it for the past 12 months. Do I play with the dates and do 12 seperate SELECTS? THANK YOU!
What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?
I am trying to get the date of Tuesday from now of the past x Years
The below Query works fine for weeks
DECLARE @x INT SET x= 53 SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
The issue is with below query for any passed Number of Years
SET x = 7 CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation
I've a table which contains the nest following data:
Date Sales Price
01-01-07 5,00
31-03-07 6,00
16-04-07 5.75
26-04-07 6.25
For example, today is 18-04-07. In my report I want to show the most recent Sales Price, but not the price of next week. I want to see the SalesPrice of 5.75.
