January 8, 2011 at 10:01 am
I have following tables
Hub_Details (Master)
----------------------
Branch_ID
Branch_Name
VTRCheckList (Master)
--------------------
CLid
CLName
VTRCheckListDetails (Detail)
----------------------------
CLid
Branch_ID
vtrvalue
vtrRespDate
Wanted the data to be populated with all the branches exists in Hub_Details and show the checklist names alongwith the branches with the sum of vtrvalue field.
i have created this query
select r.CLName, p.BranchName, ISNULL(sum(cast(s.vtrvalue as int)),0)
from VTRCheckList r
cross join Hub_Details p
left outer join VTRCheckListDetails s on s.CLid = r.CLid
and s.branchid = p.BranchID
--WHERE Convert(date,s.vtrRespDate, 105) >= convert(date,'01-01-2011',105) and Convert(date, s.vtrRespDate, 105) <= convert(date,'29-01-2011',105)
group by r.CLName, p.BranchName
order by r.CLName, p.BranchName
END
that shows e.g. total vtrvalues by CheckListName and BranchName. If no inputs were made in CheckListName in any Branch then we want to see a row with a zero, rather than just not showing a row.
This query is working great but when put the where clause to check all the branches position in between dates it shows only the branch which meets the criteria. I want to show all the branches no matter which criteria i put it in. If i remove "and s.branchid = p.branchID" it comes with all branches but the vtrvalue repeats their values in all branches.
Any help?
thanks
January 8, 2011 at 2:05 pm
ive solved my problem 🙂
January 8, 2011 at 4:46 pm
joshtheflame (1/8/2011)
ive solved my problem 🙂
Cool... What did you do to fix it?
--Jeff Moden
January 9, 2011 at 1:50 am
this is what i did. the WHERE condition was the problem which was filtering right after grouping so removed where thats it:)
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
SELECT r.CLName, p.BranchName, ISNULL(sum(cast(s.vtrvalue as int)),0) FROM VTRCheckList r
CROSS JOIN Hub_Details p
LEFT OUTER JOIN VTRCheckListDetails s ON s.CLid = r.CLid AND s.branchid = p.BranchID
AND Convert(date,s.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, s.vtrRespDate, 105) <= convert(date,@endDate,105)
GROUP BY r.CLName, p.BranchName
ORDER BY r.CLName, p.BranchName
January 9, 2011 at 4:14 am
A few things to notice:
When running queries against datetime values stored in your db you should not convert those values to varchar in order to do any comparison. This will prevent SQL Server from using any index so you'll end with a table scan that migt cause a major performance decrease.
Even if you need to convert it, don't use a format that doesn't maintain the order of dates.
You convert it to a dd-mm-yy format. This will return wrong results as soon as you're dealing with a time span of outside a single month.
Maybe the following will be more efficient:
AND s.vtrRespDate>= CAST(@Year+@Month+'01' AS DATE) AND s.vtrRespDate< DATEADD(mm,1,CAST(@Year+@Month+'01'))
January 9, 2011 at 3:00 pm
joshtheflame (1/9/2011)
this is what i did. the WHERE condition was the problem which was filtering right after grouping so removed where thats it:)
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
SELECT r.CLName, p.BranchName, ISNULL(sum(cast(s.vtrvalue as int)),0) FROM VTRCheckList r
CROSS JOIN Hub_Details p
LEFT OUTER JOIN VTRCheckListDetails s ON s.CLid = r.CLid AND s.branchid = p.BranchID
AND Convert(date,s.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, s.vtrRespDate, 105) <= convert(date,@endDate,105)
GROUP BY r.CLName, p.BranchName
ORDER BY r.CLName, p.BranchName
Lutz is correct.... you're going to not only have performance problems because of the date conversions you've done but you're also going to get incorrect answers because you've selected a non-sortable date format.
--Jeff Moden
January 15, 2011 at 7:55 am
Lutz and Jeff,
I have only date datatype not time as I dont need time stored in table. Although i always find a problem regarding date formats. I want to keep dd/mm/yyyy so even though if i take datetime datatype what is the correct way of passing date string in procedure and compare against the start date and end date if time is also included (I have no concern with the time).
thanks for guiding me in the right direction guys. you guys rock.
January 15, 2011 at 8:17 am
SQL Server will recognize a lot of date formatted strings. If you declare a date variable you can simply assign to it from one of those correctly formatted string formats. For this reason you should keep your datetime columns and variables declared as dates. You should, however, stick to formats that are not ambiguous just for your own safety. eg:
create table T(some_date datetime)
declare @my_date datetime
set @my_date = '20110116' -- ISO format always safe, ymd implied. Sorts correctly as varchar
set @my_date = '110116' -- ISO format always safe, ymd implied. Sorts correctly as varchar
set @my_date = '2011-01-16' -- surprisingly not safe unless set dateformat ymd! Sorts correctly as varchar
set @my_date = '16 jan 2011' -- alphabetic months are always safe. Not always going to sort correctly as varchar
set @my_date = '16 jan 11' -- as above
set @my_date = '2011 jan 16' -- as above
set @my_date = '11/1/16' -- unsafe and ambiguous and probably won't sort correctly as varchar. DO NOT USE
set @my_date = '1/11/16' -- as above
set @my_date = '2011-01-16T20:15:00' -- ISO 8601. THE safest format, everything explicit, sorts correctly as varchar
select @my_date
insert T select @my_date
-- in the following line the strings will be implicitly converted to datetime before sql server does the comparison
select * from T where some_date >= '1 jan 2011' and some_date < '1 feb 2011'
January 15, 2011 at 8:40 am
@allmhuran:
I disagree with your statement "-- alphabetic months are always safe."
Alphabetic months depend on the language setting. Therefore, I won't consider this format as being safe by any means.
Simply run the following code:
SET LANGUAGE german
DECLARE @my_date DATETIME
SET @my_date = '16 oct 2011'
January 15, 2011 at 8:47 am
HAH! Quite true I suppose. Not a problem you're likely ever to run into though, unless for some reason you decide to develop in a different language for the lulz. I mean, a German programmer using set language german would probably use a German string, neh? 🙂
(edit: yeah yeah, portability, blah blah blah 😉 )
January 15, 2011 at 8:54 am
allmhuran (1/15/2011)
HAH! Quite true I suppose. Not a problem you're likely ever to run into though, unless for some reason you decide to develop in a different language for the lulz. I mean, a German programmer using set language german would probably use a German string, neh? 🙂(edit: yeah yeah, portability, blah blah blah 😉 )
I'm not only talking about portablility. It's enough to add a user with a different language setting. Think about English and French in Canada or English and Spanish in some other regions.
And that's nothing you would be made aware of as a developer... You'd just be asked to deal with the consequences. 😉
January 15, 2011 at 9:03 am
Good point. That would cause problems with check constraints, defaults, etc too....
So the lesson is stick with an ISO format! Well, I always have anyway.
January 15, 2011 at 2:37 pm
joshtheflame (1/15/2011)
Lutz and Jeff,I have only date datatype not time as I dont need time stored in table. Although i always find a problem regarding date formats. I want to keep dd/mm/yyyy so even though if i take datetime datatype what is the correct way of passing date string in procedure and compare against the start date and end date if time is also included (I have no concern with the time).
thanks for guiding me in the right direction guys. you guys rock.
Understood. Just be aware that sorting will always be a bitch as will any type of date math. I've had lot's of folks do the same as you for the same reasons and they always end up suffering with code and performance problems further on down the line.
--Jeff Moden
January 15, 2011 at 3:28 pm
joshtheflame (1/9/2011)
this is what i did. the WHERE condition was the problem which was filtering right after grouping so removed where thats it:)
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
SELECT r.CLName, p.BranchName, ISNULL(sum(cast(s.vtrvalue as int)),0) FROM VTRCheckList r
CROSS JOIN Hub_Details p
LEFT OUTER JOIN VTRCheckListDetails s ON s.CLid = r.CLid AND s.branchid = p.BranchID
AND Convert(date,s.vtrRespDate, 105) >= convert(date,@startDate,105) and Convert(date, s.vtrRespDate, 105) <= convert(date,@endDate,105)
GROUP BY r.CLName, p.BranchName
ORDER BY r.CLName, p.BranchName
First comment: in SQL Server 2008 there have been improvements that allow for converting a column to a date and still able to use indexes.
Second comment: you don't need to worry about this, and the converts are just causing additional processing to be performed.
In your query, you are converting a column that should already be a date data type to a date data type. Then, you are converting your string parameter to a date where you should define that parameter as a date in the first place.
If you change this:
DECLARE @startDate varchar(20)
DECLARE @endDate varchar(20)
SELECT @startDate = Convert(varchar(10),DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)),105)
SELECT @endDate = Convert(varchar(10),DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0))),105)
To this:
DECLARE @startDate date = DATEADD(m, @Month - 1, DATEADD(yyyy, @Year - 1900, 0));
DECLARE @endDate date = DATEADD(d, -1, DATEADD(m, @Month, DATEADD(yyyy, @Year - 1900, 0)));
And then remove the converts in the where clause, you will get the same results and it will not only perform better - but be much easier to maintain.
Also, since you are on 2008 and using the DATE data type - you can use BETWEEN since your date parameters are from the beginning of the month to the end of the month. Or, you could change to using greater than or equal and less than and remove the @endDate parameter:
DECLARE @startDate date = dateadd(month, @month - 1, dateadd(year, @year - 1900, 0));
SELECT ...
FROM ...
WHERE s.vtrRespDate >= @startDate
AND s.vtrRespDate < dateadd(month, 1, @startDate);
And finally, if your colum 'vtrRespDate is not a DATE data type - then you can use the following instead and it should still use an index:
DECLARE @startDate date = dateadd(month, @month - 1, dateadd(year, @year - 1900, 0));
SELECT ...
FROM ...
WHERE cast(s.vtrRespDate As date) >= @startDate
AND cast(s.vtrRespDate As date) < dateadd(month, 1, @startDate);
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply