|
<pre>hi all below is my Store Proc
SELECT vb.Sno,
vb.Vehicle_Number,
vb.Vehicle_Booking_Date,
vb.Time_From,
vb.Time_To,
vb.Vehicle_Used_By,
vb.Cost_Code,
vb.Budget_Line,
lb.Vehicle_Number,
lb.Vehicle_Booking_Date,
lb.Time_From,
lb.Time_To,
lb.KM_Start,
lb.KM_End,
lb.Vehicle_Used_By,
lb.Cost_Code,
lb.Budget_Line
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno
where vb.Vehicle_Number = @Vehicle_Number
AND DATEPART(MONTH,vb.Vehicle_Booking_Date) = MONTH(getdate())
it is working fine. But here I need to put a condition, it should give priority to mtblLog_Book means if mtblLog_Book having data then it should show from mtblLog_Book table otherwise it should shows data from mtblVehicle_Booking for a particular Sno.
Actually firstly on the behalf of mtblVehicle_Booking I am binding a Gridview after updating the data it should show the data from mtblLog_Book.
So let me know please how to put a condition in my sp.
Thanks</pre>
|
|
|
|
|
Use a Case When or IsNull condition like this:
SELECT isnull(lb.somefield,vb.anotherfield) as columnname
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno If lb.somefield is null it will be replaced by vb.anotherfield
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
your query may be right, let me complete my sentence. according to you query it will show the not null columns, Yes I need that,
but suppose I update vehicle_no in LogBook, now both Vehcile_Booking table and LogBook tabel both have Different Vehicle_no, but after updation I just want to show it from LogBook table instead of Vehicle_Booking table.
|
|
|
|
|
Did you actually try what Jorgen wrote, the isnull test does exactly what you are asking for:
select ISNULL(LogBook, Vehcile_Booking) from .. .
Will test logbook and return a non null result, if it is null then it will return vehicle_booking.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote:
if it is null then it will return vehicle_booking
and if it is not null then, it will show value from LogBook?
|
|
|
|
|
No, if LogBook is not null then the not null value will be returned from logbook
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
<pre lang="SQL">SELECT vb.Sno,
ISNULL(lb.Vehicle_Number,vb.Vehicle_Number) as Vehicle_Number,
ISNULL(lb.Vehicle_Booking_Date,vb.Vehicle_Booking_Date) as Vehicle_Booking_Date,
ISNULL(lb.Time_From,vb.Time_From) as vb.Time_To,
ISNULL(lb.Time_To,vb.Time_To) as vb.Time_To,
ISNULL(lb.Vehicle_Used_By,vb.Vehicle_Used_By) as Vehicle_Used_By,
ISNULL(lb.Cost_Code,vb.Cost_Code) as Cost_Code,
ISNULL(lb.Budget_Line,vb.Budget_Line) as Budget_Line,
lb.KM_Start,
lb.KM_End
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno
where vb.Vehicle_Number = @Vehicle_Number
AND DATEPART(MONTH,vb.Vehicle_Booking_Date) = MONTH(getdate())</pre>
showing error
Incorrect syntax near '.'.
|
|
|
|
|
Hello Experts,
As per Difference between Delete and truncate.
Everybody knows that We Can Recover the Data from if the Delete Query is fire and we can't Recover the Data if truncate is execute.
SO I need to know that How can we recover the Data when Delete is executed.
|
|
|
|
|
My google foo got me this article[^], don't know if it helps.
I consider a restore the only reliable way of getting the data back but then I have never attempted the log recovery method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's Really Superb... thanks RAH.
|
|
|
|
|
database design for a self food ordering system
|
|
|
|
|
It does not work like this here.
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.
Try them and tell if you face issues.
Members will be more than happy to help like this.
|
|
|
|
|
declare @i int
set @i=1
select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
i am trying to retrieve holidays list from db.but i am unable to retrieve ....can anyone tell me pls what is the error in my query
|
|
|
|
|
Assuming you are using SQL Server - take your code to SSMS and test it, then tell us that the error is.
If there is no error then check there is a record to get.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i don't know how to use if...exists
i am new to asp.net & database as well
my requirement is to get 3 consecutive working days
declare @i int
declare @count int
set @i=0
set @count=0
while(@count <= 3)
begin
1) select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
the above statement is to check whether holiday exists
2) IF DATEPART(day, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
the 2nd statement is to check whether weekend or not
set @i = @i + 1
if there no holiday and not weekend then only the following code is executed
set @count = @count + 1
here i have to retrieve the date...i don't know how to get the date...because i vil get 3 dates...
end
actually i no need to select holiday name in 1)statement...there i am checking for holiday..
|
|
|
|
|
Hi,
I am using the below bcp command to export the binary files from the filestream db and all other files are seems to be exported fine (i.e. txt, pdf, rtf, image files & etc) except docx, xlsx & pptx files. I am able to export these files (i.e. docx, xlsx & pptx) but I am getting a warning/error message when opening those files and then it opens the file properly.
BCP "SELECT content FROM [dbo].[Contents] WHERE ID=1" queryout "C:\Temp\" -T -S (local) -f C:\Temp\files.fmt
I am getting the below message when opening docx, xlsx & pptx files:
XLSX - Excel found unreadable content and Do you want to recover the contents of this document? DOCX - The file test.docx cannot be opened bcos there are problems with the content and then Excel found unreadable content and Do you want to recover the contents of this document?
Also, I have this in my files.fmt file:
10.0
1
1 SQLBINARY 0 0 "\t" 1 content ""
Any help will be much appreciated.
|
|
|
|
|
Hi i am working for a E-Commerce website, i want to know about what are all the table that are required to achieve the task
|
|
|
|
|
|
thanks for your reply..
i have a requirement doc but i am unable to understand that one thats y can u tell me the basic tables which are required to achieve the e-commerce project..
as per my understand
1)user may signup using facebook,twitter,google+..
2)user interests and habits are need to be imported from social networking sites while signing up..
3)user may refer the friends, and stored in different groups
4)search, results will be in sorted as user wish(interest, buying habit, friends recommendations...)
5)reviews, user may write the reviews, like the others reviews.
these reviews are need to keep
6)rating..
7)all like wish list, shopping cart,payment, delivery..
Thanks
subbarao
|
|
|
|
|
Hi Subbarao,
I will give you some sample DB design Document by Monday....
|
|
|
|
|
|
subbaraogaddam wrote: but i am unable to understand
subbaraogaddam wrote: as per my understand
Your project is in deep trouble.
|
|
|
|
|
I don't know how you managed to get this job, but I hope your boss does not belong to CodeProject. I would suggest you spend the rest of the weekend using Google to research "e-commerce solutions".
Use the best guess
|
|
|
|
|
You are in trouble.
You need a basic understanding of the following skills
- Reading and understanding requirements.
- Creating designs
- Creating data models
- Creating databases
And I expect that you are going to also need the following skills
- Internet Security
- Web design
- Basic SQL
- Intermediate Database management and SQL
- Basic user security
- Basic business retail knowledge
You could probably start by learning how to create databases, create tables and use SQL to access them. That would probably take a week.
Then learn about business retail and improve your requirement reading skills. No idea how long that will take you.
The two above together can then be used to create some tables that are at least something like what is needed for the project.
|
|
|
|
|
OK, so as I understand it the db_accessadmin role got merged into db_securityadmin in recent versions of SQL server? This is fine, it's better this way.
My question is, when did it happen? I try to cater for all reasonable flavours of SQL server in my system, I recently took great joy in removing support for SQL 7 (The last client using it paid me to migrate their data to 2008r2 via 2005).
Now I'm trying to set up a login with both db_accessadmin & db_securityadmin rights. Or just db_securityadmin, if appropriate. So I need to know which release they merge in. Google is useless on this, so is MSDN.
I could just try to grant the right and suppress the error if it fails, On Error resume next anyone 
One day we'll look back on this and plow into a parked car.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
