|
Then you sir deserve 5, I was responding to his too much code comment  without actually registering the details of your response otherwise I'd have given credit where it was due!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why, thank you 
|
|
|
|
|
Filip Dossche wrote: but I wanted to do it with less code if it was possible
Just to be clear (it wasn;t explicit from my original post), that I had shown the code for a User Defined Function (UDF), and therefore this code would be written once, and not repeated every time you needed this logic.
Thereafter, the usage would be as simple as
SELECT * FROM Whatever WHERE date>dbo.FloorDate('DD',GetDate())
or perhaps
INSERT INTO somewhere (someInfo,someDate) VALUES ('abc',dbo.FloorDate('DD',GetDate()))
|
|
|
|
|
CREATE FUNCTION [Lib].[DateTruncate] (@Subject DateTime , @Granularity varchar(4))
RETURNS DateTime AS
BEGIN
DECLARE @DW int
DECLARE @DF int
SET @DW = DatePart(dw,@Subject) - 1
IF @Granularity = 'YY'
BEGIN
SET @Subject = dateadd(mm,-datepart(mm,@Subject)+1,@Subject)
SET @Granularity = 'MM'
END
IF @Granularity = 'MM'
BEGIN
SET @Subject = dateadd(dd,-datepart(dd,@Subject)+1,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'SU'
BEGIN
SET @DF = 7-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'MO'
BEGIN
SET @DF = 1-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'TU'
BEGIN
SET @DF = 2-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'WE'
BEGIN
SET @DF = 3-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'TH'
BEGIN
SET @DF = 4-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'FR'
BEGIN
SET @DF = 5-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'SA'
BEGIN
SET @DF = 6-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'DD'
BEGIN
SET @Subject = dateadd(hh,-datepart(hh,@Subject),@Subject)
SET @Granularity = 'HH'
END
IF @Granularity = 'HH'
BEGIN
SET @Subject = dateadd(mi,-datepart(mi,@Subject),@Subject)
SET @Granularity = 'MI'
END
IF @Granularity = 'MI'
BEGIN
SET @Subject = dateadd(ss,-datepart(ss,@Subject),@Subject)
SET @Granularity = 'SS'
END
IF @Granularity = 'SS'
BEGIN
SET @Subject = dateadd(ms,-datepart(ms,@Subject),@Subject)
END
RETURN @Subject
END
(Hmmm... I thought it did half-hours and quarter-hours too. That must be in the C# version.)
|
|
|
|
|
The OP was looking for less code. 
|
|
|
|
|
I don't know that language.
|
|
|
|
|
You can pass in with a text literal and then use dynamic SQL to invoke it.
Probably less performant, perhaps less safe, but with less code.
|
|
|
|
|
Hi
Is it possible to use one table more than once when creating a SQL VIEW? For example, the tables I`m using store data about houses and it's features (e.g. bathrooms, bedrooms, etc), and what I want to accomplish is to display the number of bathrooms as well as the number of bedrooms in one view. To do this I`m using the COUNT function to count the number of FeatureId's where the FeatureName = 'Bathroom' etc. as shown below:
SELECT DISTINCT dbo.Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.MandateType.MandateType, dbo.Mandate.ErfSize
FROM dbo.Mandate INNER JOIN
dbo.MandateType ON dbo.Mandate.MandateTypeId = dbo.MandateType.Id LEFT OUTER JOIN
dbo.MandateListing ON dbo.Mandate.Id = dbo.MandateListing.MandateId LEFT OUTER JOIN
dbo.MandateFeature AS BedroomFeature ON dbo.Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.Feature AS Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.Feature ON BedroomFeature.FeatureId = dbo.Feature.Id
GROUP BY dbo.Mandate.Id, dbo.MandateType.MandateType, dbo.Mandate.ErfSize
This displays perfectly with the number of bedrooms. How can I show an extra column for the number of bathrooms? I tried adding the features table again and giving it a different alias, but somehow that didn't give me the desired results
|
|
|
|
|
One method i can think of is using a store procedure to get your count separately.
Or a sub query in the select parameter.
But either ways aren't really efficient ways.
P.S. first time answering questions here. Hoped I helped
|
|
|
|
|
Could you give a bit more detail on the structure of the database, as it seems to me this is a really difficult way to find out the number of bathrooms and bedrooms for a particular house, especially for a database that profiles houses.
...and I have extensive experience writing computer code, including OIC, BTW, BRB, IMHO, LMAO, ROFL, TTYL.....
|
|
|
|
|
I have a MandateFeature table that contains two Id's , MandateId and FeatureId. Each of these are foreign keys from two different tables (Feature and Mandate). If a mandate (house) contains 3 bedrooms and the Id for bedroom is 5, then the entries in the MandateFeature table will look something like this:
MandateId | FeatureId
----------------------
2, 5
2, 5
2, 5
If a mandate (house) has 2 bathrooms and the Id for bathroom is 6, then the table would look something like this:
MandateId | FeatureId
----------------------
2, 5
2, 5
2, 5
2, 6
2, 6
What I want to do, is to count the number of bedrooms as well as the number of bathrooms and then display it as follows:
Bedrooms: 3
Bathroom: 2
So I get it working using this:
SELECT DISTINCT dbo.__Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
FROM dbo.__Mandate INNER JOIN
dbo.__MandateType ON dbo.__Mandate.MandateTypeId = dbo.__MandateType.Id LEFT OUTER JOIN
dbo.__MandateListing ON dbo.__Mandate.Id = dbo.__MandateListing.MandateId LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
GROUP BY dbo.__Mandate.Id, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
..but this of course only displays the number of bedrooms. As soon as I duplicate this part:
LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
and modify it to this:
LEFT OUTER JOIN
dbo.__MandateFeature AS BathroomFeature ON dbo.__Mandate.Id = BathroomFeature.MandateId AND BathroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_2
WHERE (Feature = 'Bathroom')) LEFT OUTER JOIN
dbo.__Feature AS __Feature_2 ON BathroomFeature.FeatureId = dbo.__Feature.Id
..and add COUNT(BathroomFeature.FeatureId) AS Bathrooms to the SELECT, it displays the bedrooms and bathrooms, but the values are duplicated across these 2 columns (and it's not the correct values either)
modified on Wednesday, April 20, 2011 5:49 AM
|
|
|
|
|
Move your sub query from the join to the select and add the mandateID to the where clause. This can be repeated for different feature types.
Caveat this type of sub select can be very slow and expensive.
Another way would be to left join out to the feature table multiple times (1 for each feature type) using a case statement on the if field (case featureid when is null then 0 else 1 end), group by your mandate fields and sum each feature field.
Another way is to use the same multiple joins and then pivot the rows so each feature is in it's own column.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT ID, PRODUCT, PRICE, QTY, PRICE*QTY AS TOTAL FROM PRODUCTS
I added the expression PRICE*QTY in the adapter select statement in the design time, now the values can't be updated when running the bound datagridview ie. loads but doesn't save changes.
Please help.
|
|
|
|
|
Not really a surprise: if you were to edit TOTAL (so it no longer equals PRICE*QTY), what should the database do???
I would start by making the TOTAL column read-only. Not sure that will be sufficient though.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Thanks Luc,
Of course I'm not editing the totals and they're set to read only, I just need the user to see it as the values are entered.. The funny thing is that, I've done it before and I just don't seem to remember how..
Thanks mate!
|
|
|
|
|
Mate you need to look into a proper data access layer. You are using the built in adaptors and running up against their limitations. The reason I didn't follow up on the last problem was I don't know, I and all good developers use and understand the DAL.
It's a learning thing but you obviously need to move to the next stage and get to know how your data is delivered, get to know stored procedures as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes, you're absolutely right, actually, I never used the designer when it comes to database programming and this's exactly why I'm asking now.. Guess I'll just have to forget about the designer.. I thought if it will make my life easier, why not to go for it.
|
|
|
|
|
I kicked off a Delete where value > 2 statement. I didn't expect it take take more than a couple of minutes, which has been the case for similar deletes on similar tables. But, it has been executing for 34 minutes, and when I run this statement, the status is suspended.
SELECT start_time, status, command FROM sys.dm_exec_requests
Any ideas as to how to check if or why the initial query is suspended or seemingly not running anymore.
Edit:
Using SQL '05, have seen some answers that suggest upgrading the SP2, but that is not an option right now.
Could this be related to lack of space on the drive where the tempdb and masterdb are stored and the OS is running?
Craigslist Troll: litaly@comcast.net
"I have a theory that the truth is never told during the nine-to-five hours. "
— Hunter S. Thompson
|
|
|
|
|
Ok, it is a pageiolatch issue, lack of disk space.
Craigslist Troll: litaly@comcast.net
"I have a theory that the truth is never told during the nine-to-five hours. "
— Hunter S. Thompson
|
|
|
|
|
Only a computer programmer would make a delete operation fail because there is not enough space. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Ugh, I had too much faith in the admin. I moved the files for my Database to the E drive with enough space. He had a C drive as the OS drive with only 12 gig, and a D drive that was, from my understanding, supposed to be for programs, including SQL. The clown admin installed SQL to the system C drive with very limited space. It turns out the temp table and log files were getting full, jamming up the OS/SQL D: drive. I should say that this definitely is not my fault, as I don't remote to the machine ever, just use SQL Server Management Studio. So there was no way for me to be worried about it, or know about it even.
Craigslist Troll: litaly@comcast.net
"I have a theory that the truth is never told during the nine-to-five hours. "
— Hunter S. Thompson
|
|
|
|
|
If you have a SP where you (among other things) need to extract the number of a certain rowtype, and you do this:
SELECT a, b, c, d, (SELECT COUNT(*) FROM table1 WHERE my_condition1=1) AS FooBarCount, e, f FROM table2 WHERE my_condition2=1;
it seems to me that the performance ought to be remarkably slower than if you do:
SELECT a, b, c, d, (SELECT COUNT(table1s_indexed_column) FROM table1 WHERE my_condition1=1) AS FooBarCount, e, f FROM table2 WHERE my_condition2=1;
At least according to everything I've learnt until now.
Can anybody confirm if I'm right or not?
Why can't I be applicable like John? - Me, April 2011
-----
Beidh ceol, caint agus craic againn - Seán Bán Breathnach
-----
Da mihi sis crustum Etruscum cum omnibus in eo!
-----
Everybody is ignorant, only on different subjects - Will Rogers, 1924
|
|
|
|
|
It depends. But in general, I wouldn't expect so. But that's some weird code, there must be a better way.
|
|
|
|
|
I can't answer your question with 100% certainty, but you should explore the SQL Profiler and run your queries through them to see what execution plan is choosen. Remember that just because an index exists, the optimizer may choose not to use it; resulting in a table scan.
Experiment with SQL Profiler and you can learn alot.
Good luck.
|
|
|
|
|
I read somewhere[^] that the difference between count(*) and count(1) or count(indexed_column) is not nearly as relevant now as it used to be in the past. Basically, once the writers of the database engines learned of the problem, they quietly fixed it. The habit of writing count(1), however, outlived the bug. In the late nineties, using count(indexed_column) or count(1) would make queries a lot faster on many commercial engines (e.g. Oracle-7). At this point, however, most modern DB engines with the "non-toy" status should prefer count(*) to count(1).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
