I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. This is the query that i used
SELECT date(readingtime), max(energy)-min(energy)as total FROM lights_1 WHERE readingtime between 20140407000000 and 20140409235959 Group by date(readingtime) LIMIT 0,30;
This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!
Hi, I'm using scalar UDFs in SQL server to return computed values. I've been trying to use the same udfs to perform the same computations but from different tables, but I'm not sure how. Can someone please help???
Here's an example of a counter that I'm using to return the number of days.
CREATE FUNCTION [dbo].[MarketPulse_fn_Counter] (@date smalldatetime, @date2 smalldatetime, @osid int)
RETURNS int AS
BEGIN
return (select count(*) from MarketPulse_0ndqc where stockosid = @osid and createdate <= @date and createdate >=@date2 )
END
How can I use this same UDF to do the same computation but to SELECT from another table and return that value?
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query: SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount, T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid, T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount, T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid, T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled, T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student FROM T_Receipt INNER JOIN T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount, T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount, T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo, T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo. 30429 Cheque 250 Deposit 250
30429 679.98 250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result. So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
I have a report where in I have a combination of matrix ,table data regions.
The problem what I am facing is that the data tables don't remain fixed in their position and they tend to move down.
E.g. table 1 and table 2 are on the same page in design time side by side (right and left)however during the runtime the table1 is pushed down and table2 is at its position .
Now how can I keep them all fixed in their same position. Most of the tables have fixed size rows and some who have high size of rows have been put at the end . What settings we can set?
I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?
So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.
Please Excuse my lame example but can anyone please help?
In one report I have 2 tables. Both tables are related to a different data set. Now I want to create a calculating between a field of the table 1 and a field of table 2.
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it? i think; it should be implemented through transaction statements but not much aware about these
hi. can anyone help me, please. i am using vwd2005 express edition and sql server 2005 express. i want to insert data in multiple tables at once. the tales are linked to each other through primary key and foreign keys. for example i have one table with a primary key. when i add data to it, i have to retrieve the id of the newly inserted record and then introduce this id in another table as a foreign key. i have 10 tables linked in this way, and only one form to add data to the database. can you help me, please? i'd be very greatful. thanks.
Hi all, I am a newbie to .NET and would appreciate all your valuble suggestions. I have and issue were I am trying to import data from a few selected columns MS Access and a couple of columns in SQL Server Table Y and trying to populate another table X . Both tables X and Y are in the same Database . I am wondering if I could design a custom package for this task.
I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.Thanks!Vic
Hi there!!! Trying to figure something out, I have searched this forum but no answer to my dilemma.
I have three tables on a database that I have to insert new data and update the old one. The structure of the tables is like this:
Table1 custid int primary key,identity fname varchar(30) lname varchar(30)
Table2 fileid int primary key, identity ssn varchar(11) custid int foreign key
Table3 statusid int primary key,identity title varchar(18) fileid int foreign key
This is very general but that's the idea. Now I receive a text file with the necessary info, I've already parse and break down the file into the correct fields.
Now for my question, how I insert the relevant data onto the tables from this one parsed file? and also how I go about inserting the primary keys from the different tables onto the foreign keys of each tables?
I tried relationships and key indexes, but it just spew a bunch of errors, that I'm investigating.
Hi there,I have tables with such structuretransaction_YYMM(idx,date,company_id,value)where YYMM stands for 2digits year and monthI want to define query (maybe view, procedure):select * from [???] where date>='2007-01-01' and date<='2007-04-30'which will grab data fromtransaction_0701transaction_0702transaction_0703transaction_0704and return all as onebest regardsRafal
Hi, I have a number of related tables: RGData is related to RGCrossReference RCPPositionData is related to RCPCrossReference RGCrossReference is also related to RCPCrossReference. The data is returned correctly from these tables. However, I also want to return data from another table - RComments. How do I do this? RComments is related to either RGData or RCPPositionData only. Thanks.
Code Snippet SELECT cm.CommentImage AS ViewComment, gd.PositionID AS GPositionID, cd.UniquePositionID AS CPPositionID FROM RGData gd INNER JOIN RGCrossReference g ON g.GPositionID = gd.PositionID INNER JOIN RCPCrossReference c ON c.GMatchID = g.GMatchID INNER JOIN RCPPositionData cd ON cd.UniquePositionID = c.CPPositionID left outer JOIN RComments cm ON ((cm.CPPositionID = cd.UniquePositionID) or (cm.GPositionID = gd.PositionID)) AND cm.CommentsDate = (SELECT MAX(CommentsDate) AS Expr1 FROM RComments WHERE (GPositionID = g.GPositionID)) WHERE (cd.Quantity != gd.Quantity OR cd.Currency != gd.Currency) AND g.ForcedMatch = 'no';
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Can someone show me the command string required to write data out to 2 or 3 tables at once? How about how to write 2 entries at once? Write now my solution writes 1 record to a table and then I write somewhere around 40-200 records to a second child . Write now I'm writing like this:Open --> Add 1 record in table #1 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeand I'm just wondering if there is a better approach, such as writing all the data at once. Something like:Open --> Add 1 record in table #1 --> Add 5 records in table #2 --> close
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
I have 3 tables: CUSTOMER, SALES_HEADER, SALES_DETAIL and there are no relationships / keys between these tables.
I want to INSERT into SALES_HEADER from CUSTOMER & SALES_DETAIL. Here is the query I used.
insert into sales_header (SALES_ID, CUST_ID, SALES_AMOUNT) select SALES_DETAIL.sales_id, SUM(SALES_DETAIL.prod_price) as sales_amount, CUSTOMER.CUST_ID from SALES_DETAIL, CUSTOMER where SALES_HEADER.sales_id = CUSTOMER.cust_id group by sales_detail.SALES_ID, CUSTOMER.cust_id;
It shows parsed correctly, but giving error: The multi-part identifier "SALES_HEADER.CUST_ID" could not be bound. How to insert from multiple tables when there are no primary / foreign keys & relationships.
All, i am looking for a query which fetches data from column 'managerid' which spread across 20 tables,
Managerid colums is spread across 20 tables and i need only managerid data from all the tables available, data for managerid column will be different in each table.
I've been struggling with this for about 2 weeks now and can't seem to get any further.I have two tables: orders and orders_extended. They can be joined by the common orderid field (example SELECT * FROM orders JOIN orders_extended ON orders.order = orders_extended.orderid WHERE 1=1)I need to create a report that sums the following fields:
SELECT
CONVERT(VARCHAR(12), orderdate, 101) As orderdate , COALESCE ( CASE WHEN orders_extended.productprice < 0 THEN 'DISCOUNT' ELSE orders_extended.productnumber END , CASE WHEN orders_extended.productnumber LIKE '%AB%' THEN 'PRODUCTGROUPAB' ELSE orders_extended.productnumber END , CASE WHEN orders_extended.productnumber LIKE '%CD%' THEN 'PRODUCTGROUPCD' ELSE NULL END
[code]...
What I'm trying to accomplish is to get the total dolloar amount of sales for each day for each payment type on one line per productgroup.
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
i didnt think my sql qeury was that complicated that it would crash my webapp. all im trying to do is filter data between two tables. heres my query<cfquery name="GetResults" datasource="#datasource#">SELECT *FROM Content, Content_SitesWHERE Content.ContentID <> Content_Sites.ContentIDORDER BY Content.ContentID DESC</cfquery>equals works, but when i try not equals, it all goes haywire. any ideas?TIA
I have 3 tables with the follwing schema Table <Category> {
UniqueID, LastDate DateTime }
Assume the follwing tables with data following the above schema
Table Cat1 {
1, D1 2, D2 3, D3 } Table Cat2 {
2, D4 3,D5 4, D6 } Table Cat3 {
1, D7 3,D8 5,D9 }
I have a Master and the schema is as follows Table master {
UniqueId, Cat1 DateTime, -- This is same as the Table name Cat2 DateTime, -- This is same as the Table name Cat3 DateTime -- This is same as the Table name }
After inserting the data from all these 3 tables, I want the my master table to look like this Table Master {
Make a few simple pages to enter data into multiple tables in my SQL DB.
I have the DB designed and IIS setup but I am trying to wrap my head around the php connection to the DB and then relating the tables/columns in the "Input" page.
After this is done I will use SRS to use these entries to show reports hourly.
My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:
Code Snippet CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy AS SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_ UNION ALL SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_ UNION ALL SELECT clmSectors, clmLeft, clmRight FROM tblSectors_ UNION ALL SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_
Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.
I have a text file which contains the data that has to be inserted into multiple tables.The columnames of table 1 form the H1 follwed by Details D1,D1,D1... The column names of table two form the H2 followed by details D2,D2,D2 so on and similarly for Table 3. Am using a link server to the file directory and schema.ini which defines the column names fofr the text file
Is there any way of defining column names for more than one table through the schema.ini? or is there any other way through I can parse the text file contents to multiple tables?
Sample text file: H1,JobDate,JobNumber,FileName, D1,13/02/2008,asdf123,text1.txt D1,13/02/2008,asdf123,text2.txt D1,13/02/2008,asdf123,text3.txt
