|
Look into DATEPART that will give you the week number of the date within the year. Count the issues and group by the week datepart
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sum up want ever you want and make group by with datepart.
|
|
|
|
|
Thank you guys i solved the issue, thank you for your support
|
|
|
|
|
|
SELECT DATEPART(YEAR,trans_date) AS 'Year',DATEPART(wk,trans_date) AS 'Week #',MIN(DATEADD(wk, DATEDIFF(wk,0,trans_date), 0)) AS 'Week date', SUM(COALESCE(debit,0)) AS 'Debits', SUM(COALESCE(credit,0)) AS 'Credits' FROM trans GROUP BY DATEPART(YEAR,trans_date),DATEPART(wk,trans_date)ORDER BY 1,2;
|
|
|
|
|
Friends,
Need one big help Getting error while creating function in SQL Server. Pls help to resolve the issue.
FYI, the inputs as follows. -
Function name: func_check_seat_availability
Description: The function will check for the availability of seats in a particular bus on a given day.
Input Parameters: in_bus_No, in_DateOfJourney, in_NoOfAdults, in_NoOfChildren.
Return Values: Return 1 if the seats are available, else return 0.
Table details:
Column Data Type Constraints
-----------------------------------------------------------
BookingId Varchar(4) P_Key, Starts with B100
-----------------------------------------------------------
BusNo Int (REFERENCES tbl_busdetails)
-----------------------------------------------------------
CustomerId Int (REFERENCES tbl_Customer)
-----------------------------------------------------------
DateOfBooking DateTime
-----------------------------------------------------------
DateOfJourney DateTime DateOfJourney>=DateOfBooking
-----------------------------------------------------------
NoOfAdults Int Greater than zero
-----------------------------------------------------------
NoOfChildren Int Greater than or = to zero
-----------------------------------------------------------
|
|
|
|
|
HARI JJ wrote: Getting error
We have no way of knowing what the error is unless you let us know.
|
|
|
|
|
|
What have you tried? What is the error that you get? If you expect someone to write the function for you, then you're in the wrong place.
|
|
|
|
|
What precisely are you expecting us to do with this information? You need to supply the script for the function and the error you are getting for us to help you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
What I have nothing better to do than write your code for you tsk tsk not going to happen I'm afraid. How can you expect to learn if you don't research and try and do the coding, getting help when you stumble.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try something like
CREATE FUNCTION func_check_seat_availability
(
@in_bUS_No int,
@in_DateOfJourney datetime,
@in_NoOfAdults int,
@in_NoOfChildren int
)
RETURNS decimal(36,30)
AS
BEGIN
--considering 64 is the total count of bus
declare @value decimal(36,30)
select
@value=case when sum(@in_NoOfAdults+@in_NoOfChildren) >= 34 then 0
when sum(@in_NoOfAdults+@in_NoOfChildren) < 14 then 1
end
from Details where BusNo=@in_bus_No and DateOfJourney=@in_DateOfJourney
return @value
END
|
|
|
|
|
Good Day
i have been hit by this error this night.
I have a UDF defined like this
CREATE FUNCTION [dbo].[funcFormatPercentages_Extended]
(
@parPercentageToBeFormatted nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
RETURN CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(4, 1)) AS nvarchar(5)) + N'%'
END
so i will call the UDF with the value like this
select [dbo].[funcFormatPercentages_Extended_numeric]('-43.4703076923077')
OR
select [dbo].[funcFormatPercentages_Extended_numeric]('36.403813624481')
i get an Error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
I tried to change the datatypes and Precision around , but still the error.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
A guess only, try removing the N'%' and see if it makes a difference. % is an operator and the interpretor may be making a mistake on the operation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
decimal(4, 1) means a number with three digits before the decimal point and one digit after:
http://msdn.microsoft.com/en-gb/library/ms187746.aspx[^]
Based on your provided inputs: 36.403 * 100 = 3640.3
Therefore, your result requires four digits before the decimal point, so the precision should be 5:
RETURN CAST(CAST(CONVERT(float, @parPercentageToBeFormatted) * 100 AS decimal(5, 1)) AS nvarchar(5)) + N'%'
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
<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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
