|
The functionality you described is possible with SQL Server Compact Edition. It needs no installing and the database is included in to the project and deployed with it.
However in CE you have few restrictions including:
- only one user can use it (not suitable for multi-user environments)
- no locking is possible
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
I understand for performance reasons, that the section in bold can be rewritten using a join, can anyone help me with the syntax. I am trying to select the most recent employee record where effective_date <= payperiod_enddate.
Thanks
select
t1.department,
t2.status,
t3.date_key as paycheck_date_key,
t4.date_key as start_date_key,
t5.date_key as end_date_key,
(select top 1 employee_key from d_employee where (employee_id = EEID and employee_number = EmpNum) and
(CAST(FLOOR( CAST( effective_date as float ) )as datetime) <= PeriodEndDate) order by effective_date desc) as employee_key,
t6.payment_type_key as payment_type_key,
from tblDetail t1
inner join tblHeader t2
left join d_date t3 on isnull(t2.PayDate, '01/01/1900') = t3.the_date
left join d_date t4 on isnull(t2.PeriodStartDate, '01/01/1900') = t4.the_date
left join d_date t5 on isnull(t2.PeriodEndDate, '01/01/1900') = t5.the_date
on t1.checkID = t2.checkID
left join d_PAYMENT_TYPE t6 on isnull(t1.PehEarnCode, '-999') = t6.payment_type_code
|
|
|
|
|
Hi,
How would I be able to select all columns (using T-SQL) from the following table where 'id' is unique and 'Date Updated' is the latest date.
table: demo
id Date Updated Rest of table ...
_____________ ______________________ __________________
A/VBRA-100 19/06/2008 00:00:00 More Data
A/VBRA-100 26/06/2008 00:00:00 More Data
A/VBRA-100 04/07/2008 00:00:00 More Data
A/VBRA-101 24/01/2008 00:00:00 More Data
A/VBRA-101 13/02/2008 00:00:00 More Data
A/VBRA-101 03/03/2008 00:00:00 More Data
....
Should Output:
id Date Updated Rest of table ...
_____________ ______________________ __________________
A/VBRA-100 04/07/2008 00:00:00 More Data
A/VBRA-101 03/03/2008 00:00:00 More Data
...
Cheers
Fly
|
|
|
|
|
Start off with a query to get the distinct ID and latest date updated. something along the lines of
SELECT id, MAX(DateUpdated)
FROM MyTable
GROUP BY ID
then use the result in a subquery to get the rest of the datya from that row
SELECT id, DateUpdated,OtherColumns
FROM MyTable
INNER JOIN
(
SELECT id, MAX(DateUpdated) as MostRecentlyUpdated
FROM MyTable
GROUP BY ID
) MostRecentMyTable
ON MyTable.Id = MostRecentMyTable.id
AND MyTable.DateUpdated = MostRecentMyTable.MostRecentlyUpdated
|
|
|
|
|
Sweet Man, your a legend!
|
|
|
|
|
Another way:
<br />
select distinct id,<br />
(select top 1 t1.dateupdated from MyTable as t1 where t1.id = MyTable.id order by t1.dateupdated asc),<br />
restoftable from MyTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Except if "restoftable" varies among the rows you now have repeats of the same id.
|
|
|
|
|
hay, i have a school project on creating a dummy software,
the project requires me to make a software on an office,
the office has 4 departments
the hr, marketing, mis, and the accounting departments..
i need a database design for the departments..
so far i have come up with a table with employee details for the hr, and time in- time out for the accounting.. problem is i cannot think of what fields should be included for the mis and marketing..(on my proposal, the mis and marketing provides reports..)
anyone who can help me give the necessary database table?
thanks 
|
|
|
|
|
Go talk to your teacher, that's his job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Is it possible to get all the database names through a query(or any other way)those are present in SQL Server?
|
|
|
|
|
Get to know your sys? tables
Select DBID ,Name From sysdatabases Order By name
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Either use sp_helpdb pprocedure or query master..sysdatabases.
The need to optimize rises from a bad design
|
|
|
|
|
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
Thanks in advance.
|
|
|
|
|
dream_liner_7e7 wrote: i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
What have you tried so far? If you can post the query that you now have, and also tell us where you are stuck, people here will be more than willing to try and help you.
|
|
|
|
|
SELECT
[AIMS_Library.Reck].Reck_Code + ' ' + [AIMS_Library.Reck].Shelf_Code AS [Reck & Shelf],
[AIMS_Library.Category].Category_Name,
[AIMS_Library.BookDetails].Qty,
[AIMS_Library.BookDetails].ISBN,
[AIMS_Library.BookDetails].BookTitle,
[AIMS_Library.BookDetails].Author,
[AIMS_Library.BookDetails].Publisher,
[AIMS_Library.BookDetails].Edition,
[AIMS_Library.BookDetails].YearPublished
FROM
[AIMS_Library.BookDetails], [AIMS_Library.Category], [AIMS_Library.Reck]
where
[AIMS_Library.Reck].reck_id = [AIMS_Library.BookDetails].reck_id
and
[AIMS_Library.Category].category_id = [AIMS_Library.BookDetails].category_id
and
[AIMS_Library.BookDetails].author = 'abc' or [AIMS_Library.BookDetails].publisher = 'xyz'
the last line thats where i stuck, when from my c# form users will try to search books. Is there any 1 query solution or i have to write multiple queries?
Thanks in advance.
|
|
|
|
|
One way to solve this would be to dynamicallyl create the query based on user selection... keep the basic query (including fields needed and joins) a constant, and add to the query string for each selection made.
So, if user has selected author 'xyz' and publisher 'abc'
qry = [basic query here, including the joins]
if [author selected] then qry = constant + " and author=" + selected author
if [publisher selected] then qry = constant + " and publisher=" + selected publisher
... and so on
You might want to look into creating parameters for your SQL query, to prevent injection attacks.
HTH
|
|
|
|
|
thank you every1 thats a valuable solution
|
|
|
|
|
What ChandraRam suggested is a good way to go.
However, also notice that you may have a potential problem in your query. Since you have an OR in the middle of the conditions, but no parenthesis used the result may be unexpected and in your example lead to cartesian product.
The need to optimize rises from a bad design
|
|
|
|
|
|
I believe that sp_filestream_configure is not correct. Instead you should use sp_configure and option filestream_access_level (value 0-2).
This enables filestream accessing for the instance.
The need to optimize rises from a bad design
|
|
|
|
|
Hi Guys, I'm totally new to Crystal reports and this problem has me stumped.
Here it is, lets say i have a table of employees this table has the departments they work in too and i want to display the number of employees in each department at the end of the report(in the page footer)
Here is what i did so far
Added a new group and grouped the records according to the Department name
This gives me the total of the employees in that department but it appears within the table i created
I want these to move in to the page footer
If i do move it to the page footer i get the total of only one group
Been banging my head against this for the past couple of days now. I'm sure i'm missing something obvious. Do i need a formula Field???
Also if i still want the group totals but i need the records sorted on another field is that possible?
|
|
|
|
|
Colwin wrote: Hi Guys, I'm totally new to Crystal reports and this problem has me stumped.
Here it is, lets say i have a table of employees this table has the departments they work in too and i want to display the number of employees in each department at the end of the report(in the page footer)
Here is what i did so far
Added a new group and grouped the records according to the Department name
This gives me the total of the employees in that department but it appears within the table i created
I want these to move in to the page footer
If i do move it to the page footer i get the total of only one group
Been banging my head against this for the past couple of days now. I'm sure i'm missing something obvious. Do i need a formula Field???
Also if i still want the group totals but i need the records sorted on another field is that possible?
Quote Selected Text
A couple of things...
0) End of report != Page footer
1) Are you saying you want the information to appear only at the end of the report, or should it appear at the end of every page?
2) If at the end of every page, what if the detail section for a department requires more than one page? What should be printed at the end of the first page then?
3) If at the end of the report (Report footer), do you want just a list of the data grouped by departments? If yes, why don't you create a sub-report containing just the grouped information and stick it in the Report footer? This way, you can have your main report ordered any other way you need too...
HTH
|
|
|
|
|
Thank you for your response.
i need the information at the end of the report. The end of the report need the count of all the employees in each department
like this
Dept1 10
Dept2 14
.
.
.
.
like that. I don't know at the start how many departments are there
i will look at embedding a sub report at the end of the report
Thanks again
Colwin
|
|
|
|
|
The sub report works like a charm
Thanks
Colwin
|
|
|
|
|
You are welcome 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
