|
|
|
SO I have two tables - one of "tasks" and another to log the time they are carried out each day - viz:
table "tasks"
ID - autoincrement PK
sJob - varchar task name
table "tasks_log"
ID - autoincrement PK
JobID - mapped to tasks.ID
dDateTime - date and time task is performed
Now, what I want to do is produce in one SQL statement a result set in which each row consists of the task (sJob), followed by the time it was carried out on each day within a given range
So... the result set will have variable columns depending on the date range, and the SQL sdtatement will need to be built dynamically.
A (probably not the best!) partial solution is as follows
Suppose we want the results for three days, Dec 1 to Dec 3
SELECT T.sJob, L1.dDateTime AS Dec_1, L2.dDateTime AS Dec_2, L2.dDateTime AS Dec_3
FROM tasks AS T
LEFT JOIN tasks_log AS L1 ON L1.JobID = T.ID
LEFT JOIN tasks_log AS L2 ON L2.JobID = T.ID
LEFT JOIN tasks_log AS L3 ON L3.JobID = T.ID
WHERE
DATE(L1.dDate) = '2013-12-01'
AND DATE(L2.dDate) = '2013-12-02'
AND DATE(L3.dDate) = '2013-12-03'
ORDER BY T.sJob
One obvious drawback of this is that it will be limited to a relatively small date range, but worse is that there is something I haven't told you! That is, that not all the tasks are carried out on all days.
Given this, the result set will only consist of those tasks that were, in fact, carried out on each day within the given date range.
What we need, if it's not asking the impossible, is to list them all, with NULLs showing on days any particular task was not performed.
Obviously there are other approaches, but it wuild be nice if there was simple (...) SQL statement that could do it all in one go...
|
|
|
|
|
What you need is a pivot query. This article [^]may help.
The ides is to prepare your data before pivoting. Create a table with the dates you want to report, and in your pivot join those date to the data table and then pivot the result on the dates.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
hi,
I need select query to select number of records(1 to 50 then 51 to 100 from 500 rows)from n rows of selection. not from id I think row id.
thanks in advance.
Have A Nice Day!
Murali.M
Blog
|
|
|
|
|
You mean like SELECT TOP 50 ... ?
|
|
|
|
|
no any 50 records.
Have A Nice Day!
Murali.M
Blog
|
|
|
|
|
|
Hi dear friends
I have a database (sql server) that every username should Enter some information in system & his (Admin,Admin User )then his (Admin) should give those Update Information to his (Users).
Now, I have some Problems to solve this & need your help
1) This program offline & there are no network!
2) The (Users) shouldn’t have all database & they have just information about themselves
This like organization chart that each user can just access to his information.
I want to know, how can I do backup & restore this information? In which way?
Do you Know any software that can Restore?
Backup this information the best possible way?
Image of this Message[]
|
|
|
|
|
This is not a backup/restore issue. You need to do some research into replication and distributed database strategies. This is not a trivial subject.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello there guys,
i trying to perform some right join to keep all records from tbl custumers, it works fine but when i do the "where" condition i noticed it killed my right join
how can i solve this?
i have a way to get the output i want but is very messy, so i guessed u guys could help me here, i sure is very easy
select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc
(my aim is to show total value of purchaces per client in some store, but i need to include all client even those that didn´t shop (zero values))
thanks
nelsonpaixao @yahoo.com.br
trying to help & get help
modified 7-Dec-13 15:11pm.
|
|
|
|
|
Caveat I'm a SQL Server user but would assume the syntax is similar.
Put the where clause into the join
left outer join Stores on (Stores.SID = Purchases.Stores and Stores.City = 'Porto')
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi ! having problem to use undedicated(local)server that's why i want to make own query analyzer using text box in asp.net. please help me.
example picture
|
|
|
|
|
I wonder just what your expectations are from this post?
You have not asked a question but simply stated a rather vague requirement to reproduce a reasonably complex application in ASP.net. What do you expect us to do to help?
Have you looked at existing offerings[^] and do you think you have something better to offer, or a cheaper alternative!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
in C# got error while executing update query. error is "syntax error in update statement".
my connection string is
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
Directory.GetCurrentDirectory() + "\\album.accdb;Persist Security Info=False";
query statement
m_thisCommand = m_thisConnection.CreateCommand();
strTemp = "update users set password='admin' where username='admin'";
m_thisCommand.CommandText = strTemp;
m_thisCommand.ExecuteNonQuery();
here
m_thisCommand is OleDbCommand
m_thisConnection is OleDbConnection
Thanks in advance
Have A Nice Day!
Murali.M
Blog
modified 6-Dec-13 4:20am.
|
|
|
|
|
"Password" is a reserved keyword[^] and hence, not a good name for a column.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i think u r missing a statement...
m_thisCommand.CommandType = CommandType.Text;
please try this,,,
DineshT
|
|
|
|
|
Commandtype.text is default and does not need to be specified.
Oh, and Txtspeak is considered immature and shouldn't be used in professional context.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
For the sake of this question, I've massively reduced everything. I know there are alternate solutions, but I want to understand SQL better and solve this specific problem. (So please refrain from suggesting alternate schemas--I already have those; I really want to understand INTERSECT vs SELF INNER JOIN.)
Given two tables with two columns each:
Table: Words
Id Name
1 One
2 Two
3 Three
Table: Letters
Id Letter
1 A
1 B
1 C
2 A
2 D
3 A
3 C
The task is to find all the Ids in Words which match two letters from Letters. For example, I want to find all the rows in Words which match the letters A AND C from letters. Again, massive oversimplification.
I can do the following (in SQLite):
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='A'
INTERSECT
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='C'
ORDER BY name COLLATE NOCASE
This returns One and Three. I'm happy. Except, it bugs me. I should be able to do the same thing with a series of INNER JOINS, but I've yet to come up with a solution which actually runs. Even with an intermediate table, I'm still not sure how to solve it and thus want to first solve it with this schema. Is there a SQL wizard in the house?
EDIT: Just after posting this, I came up with the following. It works, but is it optimal? Is there a better way? (It seems that if Words is really big, we end up with two huge joins which are then reduced or with the optimizer figure this out?)
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id
INNER JOIN Letters AS L2 ON Words.Id=L2.Id
WHERE L1.Letter='A' AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
I aslso got the following suggestion:
SELECT DISTINCT Words.Name FROM Words
INNER JOIN Letters AS L1 ON Words.Id=L1.Id AND L1.Letter='A'
INNER JOIN Letters AS L2 ON Words.Id=L2.Id AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE
|
|
|
|
|
Personally I use the self join, mainly because the intersect is a new fangled concept that has not penetrated my inertia. Some of the new stuff is just repackaging the the old stuff to be more palatable, linq to sql comes to mind.
My TSQL seems to have ossified about a decade ago as that gets 95% of my work done. I'd hate to have to set up a distributed database these days.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use joins whenever it's possible. The DB will then be able to use indexes for the joining.
Intersect is meant to be used when you have two resultsets with the same columns but different origins and no common indexing.
Instead of a self join you can try this query:
SELECT W.name
FROM Words w JOIN Letters l ON w.ID = l.id
WHERE l.letter IN ('A','C')
GROUP BY W.name
HAVING Count(w.name) = 2 A group by is generally faster than a distinct and it will never need more than one index seek, your self join might need two depending on what the optimizer comes up with. But it's a bit quirky as you need to have the letter count available.
But you should consider it if you have large resultsets because it can be pipelined and therefore need much less memory.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
How about using Exists ?
SELECT
Id,
Name
FROM
Words As W
WHERE
Exists
(
SELECT 1
FROM Letters As L
WHERE L.Id = W.Id
And L.Letter = 'A'
)
And
Exists
(
SELECT 1
FROM Letters As L
WHERE L.Id = W.Id
And L.Letter = 'C'
)
The query optimizer will probably give you the same plan for this as the INNER JOIN option, but this makes your intention slightly clearer IMO.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am getting this error:
FUNCTION mydatabase.sp_add_new_job_order does not exists
I checked on google and all were talking about spaces causing the problem but I am still unable to identify where is the problem.
Kindly help..
here is my code:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11,6), IN param_created_user int, OUT param_record_identity int)
BEGIN
DECLARE param_job_order_id int;
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, start_time, end_time, total_hours, job_order_note, is_contract, contract_id, created_date, created_user) VALUES (param_customer_id, param_cleaner_id, param_job_order_date, param_start_time, param_end_time, param_total_hours, param_job_order_note, param_is_contract, param_contract_id, NOW(), param_created_user);
SET param_job_order_id = LAST_INSERT_ID();
SET param_record_identity = param_job_order_id;
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;
IF (param_is_contract = TRUE) THEN
BEGIN
UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_job_order_id;
UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;
CALL sp_add_event_log("JOBORDER", param_job_order_id, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
I'm having trouble with this query, use to work in SQL Server 2008, but in 2012, I'm getting rows returned from various dates
So I'm just trying to get all the orders from Dec 3, 2013, I'm passing integer values into the function
12
3
2013
and using parameters Day, Month, Year
My Original SQL
"SELECT * From CompletedOrders " & _
"UNION " & _
"SELECT * FROM CompletedOrdersHistory " & _
"WHERE Day(OrderDate)=<a href="/Members/theday">@TheDay</a> " & _
"AND Month(OrderDate)=@TheMonth " & _
"AND Year(OrderDate)=<a href="/Members/theyear">@TheYear</a>"
Not really sure how to write this or what to search on. I have spent about an hour on this searching the internet.
[update]
I got this to work, but I have to use the greater than, so now I have to back date the date, which will get me trouble at the start of the month, guess I'll try looking up removing a day in SQL
DECLARE @TheDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 2;
set <a href="/Members/Year">@year</a> = 2013;
set @TheDate = DateFromParts(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>);
SELECT * From CompletedOrders
WHERE OrderDate > @TheDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @TheDate;
Well this is what I ended up with. It works, not sure of efficient it is
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 3;
set <a href="/Members/Year">@year</a> = 2013;
set @startDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 0, 0, 0, 0);
set @stopDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 23, 59, 59, 999);
SELECT * From CompletedOrders
WHERE OrderDate > @startDate AND OrderDate < @stopDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @startDate AND OrderDate < @stopDate;
modified 4-Dec-13 22:19pm.
|
|
|
|
|