|
Thank for that; it gived me two errors one on line 11 and one on 23 the last.
1st error on 11 is a syntax error near ")"
2nd error is on line 23 the last is error near "closed"
Heere it is revised a bit...this one is tuff for me thanks for the help!!!
Select total.Client,TotalTicketsAssigned,
TotalClosedForRangeFrom select count(*) as
'TotalTicketsAssigned' , location_name
'Client'from job_ticket jinner join priority_type p
on p.priority_type_id = j.priority_type_idinner join
tech t on t.client_id = j.assigned_tech_idinner join
location l on l.location_id = j.location_id WHERE Report_Date
>= DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
AND Report_Date
< DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
group by l.location_name)
total.client
Left Join (select count(*)
as 'TotalClosedforRange', location_name 'Client'from
job_ticket jinner join priority_type p on p.priority_type_id =
j.priority_type_idinner join tech t on t.client_id =
j.assigned_tech_idinner join location l on l.location_id =
j.location_id where (last_status_update_time >=
DATEADD(dd,-7,CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),112)))
AND Report_Date < DATEADD(dd,1,CONVERT(DATETIME,CONVERT(CHAR(8),
GETDATE(),112))) ) and status_type_id ='3'
group by l.location_name)
Closed on total.Client = closed.Client
Regards,
Hulicat
|
|
|
|
|
I copied and pasted the two queries you supplied into the join sub-query syntax, so the line 11 one is something from the query you supplied (and I'm guessing the second one is resulting from the first error).
To try to clear things up for you, here's a simple structure of a sub-query join:
Select
<select list>
From
(
<query 1>
) q1
Join
(
<query 2>
) q2 on
<join list>
<other clauses (probably will not have any here)>
The select must use the names assigned to the columns, so if you have Select Name, Date as OpenDate; then you would need to use Name for name (because the name was not changed) and OpenDate for the original column Date.
If you think of the two queries as tables, it might make the concept easier to grasp. If the above query was written from tables, it would be Select * From Table1 q1 Join Table2 q2 on ...
Hope that helps.
|
|
|
|
|
Thanks for that!!!
You made it crystal clear and I have it working now!!
Omce again thanks for taking the time to teach and give an example of perspective...
That will really help me moving forward.
Thanks and Regards,
Dennis
Regards,
Hulicat
|
|
|
|
|
need help on something i have this
Dim SQLIinsert As String = "UPDATE httpuser SET credits= " + -1 + "' WHERE username='" + My.Settings.Username + "'"
what i need is to decrease the column credits by 1 but it this i get -1 in the database.
can someone explain whats wrong? ive searched but im almost ready to quit.
thanks in advance
ive posted the same on vb.net because i guess this question envolves both parts
|
|
|
|
|
"UPDATE httpuser SET credits=credits -1 WHERE username='" + My.Settings.Username + "'"
But be aware from SQL Injections, take a read about it.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks 
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
hi Guys,
I have been working with Oracle till now and recently moved to SQL Server.
In oracle we have packages, which is an effective way of organizing our stored procs.
In sql server I am seeing all procedures under one folder at same level.
Is there any way to organize or group together procedures in SQL server?
Thanks
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
|
Hi...im a newbie in MySQL...hope you guys can help me...
Im going to upload an image or file to MySQL database,
the question is..what data type am I going to set for my image field?
thanks..
|
|
|
|
|
Blob, which stands for Binary large object.
|
|
|
|
|
Hi!
I have worked with a DB programming, but this is the first time that I actually build a database on my own.
Here is a short description of the task: I need to store the list of small businesses, each of them has some fields, like "Company name", "Address", "Phone", etc. However, I also need to store the working schedule for these businesses and that is something that I don't know how to implement.
The problem is that businesses in my country often have different working hours and lunch breaks, for example.
Monday - Friday: 10.00 - 19.00 , lunch break 13.00-14.00
Saturday: 10.00-16.00, lunch break 12.30-13.30;
Sunday: OFF
Because of the variety of the companies, I'd like to be able to take care of such instances.
So far I came up with idea of using a table, which stores the schedule in 28 different columns (time of work start + time of work end + time of lunch start + time of lunch end) * 7 days = 28 columns.
I was wondering if someone has done something similar before and can give me an advice on this.
Thanks for help
|
|
|
|
|
I would suggest to accomplish this by using relational tables.
Have a table with the business info, and then give the business an Id number.
Create a table that has BusinessId,Day, TimeWorkStart, TimeWorkEnd, TimeLunchStart, TimeLunchEnd (or whatever you want to call them).
The BusinessID would be the foreign key that would refer to the Business info table. To get the schedule, you would need to join up on the BusinessId of both tables.
The way you listed above could be one approach (and I've seen similar things done in program), but you might come to a point where the design restricts you from adding additional schedules without storing a bunch of duplicate data.
Also less data is being stored. If your table has 28 columns, but only has, say Monday and Tuesday workdays, then there are 20 unused columns.
|
|
|
|
|
Thanks a lot for your help! I really appreciate it.
I liked your idea, but once I started to implement it, I've come up with a "joined" solution, that combines the advantages of yours and mine approaches. Here is the idea.
Although, there is a big variety of businesses in my city, many of them share the same schedule. Therefore, I think it would be better to create a table "Schedules", which will contain the list of all schedules, that the businesses use. This way, each of 24/7 shops in my database will contain only an ID for appropriate 24/7 schedule, therefore, I will avoid lots of duplication. And, if I meet some odd schedule, I can just add it into the table with the schedules.
If you find any faults in this idea, or have some suggestions - I'll appreciate that.
Once again, thanks for time&effort
|
|
|
|
|
Something that might happen in the future is that a business might need to have 2 types of schedules, causing duplicate data in the business table on every column but ScheduleId.
You could "map" BusinessId's to SchedulesId's in a view, so you might have tables like:
BusinessID, Name, ...
ScheduleId, Day, StartTime, EndTime
Mapping Table: BusinessId, ScheduleId (with a unique index on BusinessId, ScheduleID so you don't dupe data)
Then have a view
Select ...<br />
From MappingTable m<br />
Join ...
But if you have a business rule similar to one business can have one schedule per day, then I think your Joined solution will work fine.
|
|
|
|
|
I have a doubt on Foreign key column. I understand that a Foregin key column can hold NULL values (meaning it need not be required always), but can it hold invalid data in it?
That is, it is holding the ROW_ID of another table where that record is not available. Will it throw an error or it will accept the invalid entry?
|
|
|
|
|
Foreign key is used to ensure data integrity. If you want to allow invalid entries, I cannot see the point in using foreign key.
|
|
|
|
|
Very new to SQL and I learn by example but cannot find anything like this after tons of searching.
I have an Excel workbook of software application names and versions. Many have the same 'root' name but different version number. e.g. root = "McAfee VirusScan"
sheet columns...
APP NAME -- VERSION # -- MANUF -- INSTALL COUNT
I think a View might be best(?) but no idea how to code it.
The View needs to SUM the INSTALL COUNTs for all similar APP NAMEs & VERSIONs(similar determined by using first few characters or something - options here?)
example:
McAfee VirusScan v1.01 21259
McAfee VirusScan v3.20 14234
... There are thousands of APPs. I will not know all the possible versions.
Anyone do this before and have some example code or could help?
Thx in advance!
|
|
|
|
|
How can I understand you your query should be something like this:
Select sum(Installcount)<br />
from Tablename<br />
group by version
If this don't help you then post your data how is stored on table and post result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Select AppName, Version, Sum(InstallCount)
From table
Group By AppName, Version
OrderBy AppName
|
|
|
|
|
Hello sir I am Convert Access Databse to sql server 2000 please tell me how to convert it
saurabh bhardwaj
|
|
|
|
|
Hi Saurabh,
Please follow these link. That may be helpfull.
Link 1[^]
Link 2[^]
Regards
|
|
|
|
|
|
I am using Rank() function in my VIEW.
it working in sql2005 but not in sql2000.
Is there any alternative function for Rank() or Row_Number() in sql server 2000?
|
|
|
|
|
No
You can simulate it using a cursor and identity field
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
