|
Thanks a lot for ur reply.
CST
|
|
|
|
|
while(date1<>date2)
begin
if(datepart(dw, date1)=sunday)
begin
@sundaycount = @sundaycount +1
end
DATEADD ( dd , 1, date1 )
end
print @sundaycount
Gautham
|
|
|
|
|
I'm going to say a bunch of stuff here that ASSUMES SQL Server uses the same date system that Microsoft Offices uses. If that's wrong, then what I say here won't apply.
Microsoft Office uses date serial number zero to (sort of) represent Saturday, Dec 30, 1899 (actually, their system is broken below serial number 61 because they assume the year 1900 was a leap year, which it was not -- but that doesn't affect this discussion). Thus, the date serial number modulo 7 gives you the day of the week, counting Sunday as day #1. Taking the interval between two dates, and dividing by 7 gets the number of weeks (and most weeks have one Sunday), then you need to correct for the beginning and end dates in case you get a fraction of a week, or the beginning or end dates are Sunday.
numSundays( date1, date2 )
{
dayOfWeek1 = MOD(date1,7)
dayOfWeek2 = MOD(date2,7)
numSundays = (date2-date1)/7
If(dayOfWeek2 < dayOfWeek1) numSundays++
ElseIf(dayOfWeek2=1 OR dayOfWeek1=1) numSundays++
}
This should give you the number of Sundays INCLUSIVE (includes both start and end dates). BUT, like I said earlier: won't work if SQL Server doesn't use the same dating system as MS Office.
Hope this helps.
|
|
|
|
|
Thanks a lot for ur reply.
CST
|
|
|
|
|
I re-read your post, and realized I hadn't really answered your question in my first response. You asked how to do it in SQL, and you didn't specify which flavor. I answered in pseudo code; not SQL. Here is another shot at it, this time putting the previous pseudo code into SQL.
I looked thru the (XOpen) SQL standard, and couldn't find the functions I was looking for, so had to settle for generic functions that you will have to substitute for if your system doesn't support them. In both the procedures below I've assumed:
1) the way to get an integer divide is with the '\' operator. This works in Access, but is not universal across DBMs.
2) the whole part of a date interval is actually the number of days between the two dates. The standard does NOT specify this.
3) the existence of a "WEEKDAY" function.
4) that date2 >= date1
5) that you specify a DATE-LITERAL by delimiting it with # signs.
Note that Jan 2, 2000 was a Sunday.
First, a procedure that uses ISO SQL standard CASE notation:
CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS
SELECT CINT(date2 - date1) \ 7
+ CASE WEEKDAY(date2) < WEEKDAY(date1)
WHEN TRUE THEN 1 ELSE
CASE WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#)
OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#)
WHEN TRUE THEN 1 ELSE 0
END
END
;
And here is the same thing in MS Access notation.
CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS
SELECT CINT(date2 - date1) \ 7
+ IIF( WEEKDAY(date2) < WEEKDAY(date1), 1,
IIF( WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#)
OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#), 1, 0 ))
;
While more complicated than a looping approach to counting the number of Sundays, they are much more efficient as they are effectively only a simple formula.
Hope this helps
-- modified at 16:35 Tuesday 17th April, 2007
|
|
|
|
|
Thanks a lot for ur reply.
found it quite useful.
CST
|
|
|
|
|
hi,
According to my knowledge , when a column is an IDENTITY it increments by 1 , now even if a row has a value of , lets say 5 , and i delete it , the next row will have a value that increments the previous value , even if it was deleted.
So i need to figure the Next number that will get into the IDENTITY column or the highest number(including deletions) of that IDENTITY column.
I'm working with C# 2.0 in .NET 2.0 using SQL Server 2005.
Thanks so much
Gideon
|
|
|
|
|
Use SCOPE_IDENTITY() like this:
-- begin sql --
DECLARE @Id INT
INSERT INTO myTable (val1, val2, val3)
SET @Id = SCOPE_IDENTITY()
-- end sql --
|
|
|
|
|
hi Mark,
Thanks so much for your reply but i'm afraid i have to be a little more annoying.
How do i use the syntax you've mentioned??. I did search msdn and google but came up with some confusing results. In my little DataLayer , i have 2 small functions that [1] Execute a query and returns a DataTable and [2] execute a non-query sql statement.
So far , i've only used non-query sql statements to do all my edit/delete operations. I dont know much about anything else like stored procedures or triggers
I'm guessing i'm supposed to use stored procedures? Is this [^]a good article?
Thanks so much
Gideon
|
|
|
|
|
guess a little more searching and reading is all it took!
I put it all in a stored proc and It works a treat now!
Thanks so much.
|
|
|
|
|
I have a customer that is a one off because they have Vista on their computer so SQL Server 2000 is not compatible. Does VB.NET use a specific provider for 2000 and 2005 or can I just set the database up the same and it will work?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: I have a customer that is a one off because they have Vista on their computer so SQL Server 2000 is not compatible. Does VB.NET use a specific provider for 2000 and 2005 or can I just set the database up the same and it will work?
It is the same provider (in System.Data.SqlClient) for both SQL Server 2000 and 2005
|
|
|
|
|
Hi friends,
I'm trying to create a job in SQLServer 2005 and I' gettin this error:
"Unable to cast object of type Microsoft.SqlServer.Management.Smo.SimpleObjectKey into type Microsoft.SqlServer.Management.Smo.JobObjectKey"
Does anyone have had the same error?
Any help will be very appreciated!!
|
|
|
|
|
Hello,
I am new to t-sql programming (sql server 2005) and I have a small problem. The DB system is current deployed with case sensitivity on. What I need to accomplish is a simple query that finds a distinct list of names and return them in a case-insensitive order.
here is what I attempted:
SELECT DISTINCT Name
FROM MyTable
ORDER BY lower(Name)
This of course bombs on me because "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." I intend on keeping the returned case the same as it appears in my table. This prevents me from solving the issue with "SELECT DISTINCT lower(Name)" Any help is much appreciated!
~Adam
|
|
|
|
|
Try:
SELECT DISTINCT Name COLLATE Latin1_General_CI_AI
FROM MyTable
ORDER BY 1 The "collate" keyword transforms the string into a case-insensitve collation. The "order by 1" is a lazy way of saying sort by the first column.
|
|
|
|
|
Hi!
I have a function that returns a long quary, more then 4000 chars.
I am returning the answer as a where clause, the problem is that i am returning it to the main where and there i get an error that says that it is a CLOB and can not be handled.
can you please help me? do i need to convert it to string?
this is the function declare:
CREATE OR REPLACE FUNCTION getorgentitylistyos (company_name IN VARCHAR2) RETURN CLOB IS
This is the answer from the function:
1 PL/SQL block, executed in 0.563 sec.
(OWNER IN ('DEUTSCHE BANK SUISSE','PRODUCTION DIVISION','GLOBAL DEPARTMENT','RINAT DIVISION','RINATTEST_DEPARTMENT','TANYA DIVISION','TANYA DEPARTMENT','TEST DIVISION','RUBY DEP','SWIFT DEPARTMENT','TEST DEPARTMENT','WRDIV1','WRDEP1','WRDEP10','WRDEP100',
'WRDEP101','WRDEP102','WRDEP103','WRDEP104','WRDEP105','WRDEP106','WRDEP107','WRDEP108','WRDEP109','WRDEP11','WRDEP110','WRDEP111','WRDEP112','WRDEP113','WRDEP114','WRDEP115','WRDEP116','WRDEP117','WRDEP118','WRDEP119','WRDEP12','WRDEP120','WRDEP121','WRD
EP122','WRDEP123','WRDEP124','WRDEP125','WRDEP126','WRDEP127','WRDEP128','WRDEP129','WRDEP13','WRDEP130','WRDEP131','WRDEP132','WRDEP133','WRDEP134','WRDEP135','WRDEP136','WRDEP137','WRDEP138','WRDEP139','WRDEP14','WRDEP140','WRDEP141','WRDEP142','WRDEP14
3','WRDEP144','WRDEP145','WRDEP146','WRDEP147','WRDEP148','WRDEP149','WRDEP15','WRDEP150','WRDEP151','WRDEP152','WRDEP153','WRDEP154','WRDEP155','WRDEP156','WRDEP157','WRDEP158','WRDEP159','WRDEP16','WRDEP160','WRDEP161','WRDEP162','WRDEP163','WRDEP164','
WRDEP165','WRDEP166','WRDEP167','WRDEP168','WRDEP169','WRDEP17','WRDEP170','WRDEP171','WRDEP172','WRDEP173','WRDEP174','WRDEP175','WRDEP176','WRDEP177','WRDEP178','WRDEP179','WRDEP18','WRDEP180','WRDEP181','WRDEP182','WRDEP183','WRDEP184','WRDEP185','WRDE
P186','WRDEP187','WRDEP188','WRDEP189','WRDEP19','WRDEP190','WRDEP191','WRDEP192','WRDEP193','WRDEP194','WRDEP195','WRDEP196','WRDEP197','WRDEP198','WRDEP199','WRDEP2','WRDEP20','WRDEP200','WRDEP201','WRDEP202','WRDEP203','WRDEP204','WRDEP205','WRDEP206',
'WRDEP207','WRDEP208','WRDEP209','WRDEP21','WRDEP210','WRDEP211','WRDEP212','WRDEP213','WRDEP214','WRDEP215','WRDEP216','WRDEP217','WRDEP218','WRDEP219','WRDEP22','WRDEP220','WRDEP221','WRDEP222','WRDEP223','WRDEP224','WRDEP225','WRDEP226','WRDEP227','WRD
EP228','WRDEP229','WRDEP23','WRDEP230','WRDEP231','WRDEP232','WRDEP233','WRDEP234','WRDEP235','WRDEP236','WRDEP237','WRDEP238','WRDEP239','WRDEP24','WRDEP240','WRDEP241','WRDEP242','WRDEP243','WRDEP244','WRDEP245','WRDEP246','WRDEP247','WRDEP248','WRDEP24
9','WRDEP25','WRDEP250','WRDEP251','WRDEP252','WRDEP253','WRDEP254','WRDEP255','WRDEP256','WRDEP257','WRDEP258','WRDEP259','WRDEP26','WRDEP260','WRDEP261','WRDEP262','WRDEP263','WRDEP264','WRDEP265','WRDEP266','WRDEP267','WRDEP268','WRDEP269','WRDEP27','W
RDEP270','WRDEP271','WRDEP272','WRDEP273','WRDEP274','WRDEP275','WRDEP276','WRDEP277','WRDEP278','WRDEP279','WRDEP28','WRDEP280','WRDEP281','WRDEP282','WRDEP283','WRDEP284','WRDEP285','WRDEP286','WRDEP287','WRDEP288','WRDEP289','WRDEP29','WRDEP290','WRDEP
291','WRDEP292','WRDEP293','WRDEP294','WRDEP295','WRDEP296','WRDEP297','WRDEP298','WRDEP299','WRDEP3','WRDEP30','WRDEP300','WRDEP31','WRDEP32','WRDEP33','WRDEP34','WRDEP35','WRDEP36','WRDEP37','WRDEP38','WRDEP39','WRDEP4','WRDEP40','WRDEP41','WRDEP42','WR
DEP43','WRDEP44','WRDEP45','WRDEP46','WRDEP47','WRDEP48','WRDEP49','WRDEP5','WRDEP50','WRDEP51','WRDEP52','WRDEP53','WRDEP54','WRDEP55','WRDEP56','WRDEP57','WRDEP58','WRDEP59','WRDEP6','WRDEP60','WRDEP61','WRDEP62','WRDEP63','WRDEP64','WRDEP65','WRDEP66',
'WRDEP67','WRDEP68','WRDEP69','WRDEP7','WRDEP70','WRDEP71','WRDEP72','WRDEP73','WRDEP74','WRDEP75','WRDEP76','WRDEP77','WRDEP78','WRDEP79','WRDEP8','WRDEP80','WRDEP81','WRDEP82','WRDEP83','WRDEP84','WRDEP85','WRDEP86','WRDEP87','WRDEP88','WRDEP89','WRDEP9
','WRDEP90','WRDEP91','WRDEP92','WRDEP93','WRDEP94','WRDEP95','WRDEP96','WRDEP97','WRDEP98','WRDEP99','WRDIV10','WRDIV100','WRDIV101','WRDIV102','WRDIV103','WRDIV104','WRDIV105','WRDIV106','WRDIV107','WRDIV108','WRDIV109','WRDIV11','WRDIV110','WRDIV111','
WRDIV112','WRDIV113','WRDIV114','WRDIV115','WRDIV116','WRDIV117','WRDIV118','WRDIV119','WRDIV12','WRDIV120','WRDIV121','WRDIV122','WRDIV123','WRDIV124','WRDIV125','WRDIV126','WRDIV127','WRDIV128','WRDIV129','WRDIV13','WRDIV130','WRDIV131','WRDIV132','WRDI
V133','WRDIV134','WRDIV135','WRDIV136','WRDIV137','WRDIV138','WRDIV139','WRDIV14','WRDIV140','WRDIV141','WRDIV142','WRDIV143','WRDIV144','WRDIV145','WRDIV146','WRDIV147','WRDIV148','WRDIV149','WRDIV15','WRDIV150','WRDIV151','WRDIV152','WRDIV153','WRDIV154
','WRDIV155','WRDIV156','WRDIV157','WRDIV158','WRDIV159','WRDIV16','WRDIV160','WRDIV161','WRDIV162','WRDIV163','WRDIV164','WRDIV165','WRDIV166','WRDIV167','WRDIV168','WRDIV169','WRDIV17','WRDIV170','WRDIV171','WRDIV172','WRDIV173','WRDIV174','WRDIV175','W
RDIV176','WRDIV177','WRDIV178','WRDIV179','WRDIV18','WRDIV180','WRDIV181','WRDIV182','WRDIV183','WRDIV184','WRDIV185','WRDIV186','WRDIV187','WRDIV188','WRDIV189','WRDIV19','WRDIV190','WRDIV191','WRDIV192','WRDIV193','WRDIV194','WRDIV195','WRDIV196','WRDIV
197','WRDIV198','WRDIV199','WRDIV2','WRDIV20','WRDIV200','WRDIV201','WRDIV202','WRDIV203','WRDIV204','WRDIV205','WRDIV206','WRDIV207','WRDIV208','WRDIV209','WRDIV21','WRDIV210','WRDIV211','WRDIV212','WRDIV213','WRDIV214','WRDIV215','WRDIV216','WRDIV217','
WRDIV218','WRDIV219','WRDIV22','WRDIV220','WRDIV221','WRDIV222','WRDIV223','WRDIV224','WRDIV225','WRDIV226','WRDIV227','WRDIV228','WRDIV229','WRDIV23','WRDIV230','WRDIV231','WRDIV232','WRDIV233','WRDIV234','WRDIV235','WRDIV236','WRDIV237','WRDIV238','WRDI
V239','WRDIV24','WRDIV240','WRDIV241','WRDIV242','WRDIV243','WRDIV244','WRDIV245','WRDIV246','WRDIV247','WRDIV248','WRDIV249','WRDIV25','WRDIV250','WRDIV251','WRDIV252','WRDIV253','WRDIV254','WRDIV255','WRDIV256','WRDIV257','WRDIV258','WRDIV259','WRDIV26'
,'WRDIV260','WRDIV261','WRDIV262','WRDIV263','WRDIV264','WRDIV265','WRDIV266','WRDIV267','WRDIV268','WRDIV269','WRDIV27','WRDIV270','WRDIV271','WRDIV272','WRDIV273','WRDIV274','WRDIV275','WRDIV276','WRDIV277','WRDIV278','WRDIV279','WRDIV28','WRDIV280','WR
DIV281','WRDIV282','WRDIV283','WRDIV284','WRDIV285','WRDIV286','WRDIV287','WRDIV288','WRDIV289','WRDIV29','WRDIV290','WRDIV291','WRDIV292','WRDIV293','WRDIV294','WRDIV295','WRDIV296','WRDIV297','WRDIV298','WRDIV299','WRDIV3','WRDIV30','WRDIV300','WRDIV31'
,'WRDIV32','WRDIV33','WRDIV34','WRDIV35','WRDIV36','WRDIV37','WRDIV38','WRDIV39','WRDIV4','WRDIV40','WRDIV41','WRDIV42','WRDIV43','WRDIV44','WRDIV45','WRDIV46','WRDIV47','WRDIV48','WRDIV49','WRDIV5','WRDIV50','WRDIV51','WRDIV52','WRDIV53','WRDIV54','WRDIV
55','WRDIV56','WRDIV57','WRDIV58','WRDIV59','WRDIV6','WRDIV60','WRDIV61','WRDIV62','WRDIV63','WRDIV64','WRDIV65','WRDIV66','WRDIV67','WRDIV68','WRDIV69','WRDIV7','WRDIV70','WRDIV71','WRDIV72','WRDIV73','WRDIV74','WRDIV75','WRDIV76','WRDIV77','WRDIV78','WR
DIV79','WRDIV8','WRDIV80','WRDIV81','WRDIV82','WRDIV83','WRDIV84','WRDIV85','WRDIV86','WRDIV87','WRDIV88','WRDIV89','WRDIV9','WRDIV90','WRDIV91','WRDIV92','WRDIV93','WRDIV94','WRDIV95','WRDIV96','WRDIV97','WRDIV98','WRDIV99','anna division','sanitydiv','s
anitydep')) And this is the main SQL:
REATE OR REPLACE VIEW test (
msg_id
) AS
SELECT msg_id
FROM messages
WHERE getorgentitylistyos('DEUTSCHE BANK SUISSE')
WITH READ ONLY
i get this error:
ORA-00920: invalid relational operator
Why????
Thanks for your help
|
|
|
|
|
I'm not good in Oracle.
however what i feel is resultant of function is not a Boolean value (True/False) where as the condition in the WHERE Clause should result in a boolean value.
Request you to please check for this.
Regards
KP
|
|
|
|
|
I have developed a small desktop application using c# and Ms Access 2002. Database is password protected and contains sensitive data. As many password retrieval tools are available, What should I do to protect Ms-Access (.MDB ) file? Is there any way through which I can hide database file.
poly
|
|
|
|
|
testmail_123 wrote: As many password retrieval tools are available
Yep, and this is exactly why you don't store sensitive data in an Access database. There is simply no way to protect it reliably.
testmail_123 wrote: What should I do to protect Ms-Access (.MDB ) file? Is there any way through which I can hide database file.
No, there isn't. Since Access is a file-based database, the .MDB file must be visible to the user using your application. This means he'she must have Read and Write access to the .MDB file. You can turn on the Hidden file attribute, but this is pathetically easy to defeat.
You can try to encrypt some of the database in the database, but you have HUGE performance problems and usability issues.
The only way to secure sensitive data is to use a real database engine, like SQL Server or Oracle. The security is MUCH better and easier to control.
Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007
|
|
|
|
|
|
Try using MSDE (SQL 2005 Express Ed.) instead of MS Access. Even if the user can open the file by installing SQL Server Management Studio or Query Analyzer you can deny them permission to access the sensitive data.
|
|
|
|
|
Hello Friends,
I have never written a cursor before and I have a situation in which I need it. I have two days trying and still couldn't make it work...I need some help
I have three tables one for Items, one for Orders and one for OrderDetail. I want to check every X days that all the unpaid orders(which has items blocked on it, defined in OrderDetail) free their items and increase the amount on the Items table. Evey time a customer make an order, the items are reserved, so they need to be freed if the order is canceled or remains unpaid for a given time.
In this case I need a cursor that iterates through all the items belonging to the same unpaid order, then increase the amount in the Items table and delete the record in the OrderDetail table.
Please, could you help me in this problem??
I will appreciate any help
Thanks.
|
|
|
|
|
Most of the time, when I see somebody say they need a cursor I can't help thinking that they've missed out on the alternatives. This seems to be the case here. You probably don't need to use a cursor. What you could do is to create a temporary (or in memory) table that contains details of all of the unfilled orders. Then, once you have done this, you would write your update statements for the other tables based on this table.
What you will find is that this approach is more efficient because it allows the database engine to operate in a set based manner which is what DB engines are designed to do.
As an example (in pseudocode)
SELECT the missing records
FROM orderDetails
INTO a Temporary Table.
UPDATE order WITH THE itemTotals FROM Temporary Table
WHERE THE Item Id IN Temporary Table
MATCHES THE Id in my Item TABLE.
Do the other tables here... Anyway, that's what I would do.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks Pete,
I will be more specific, I tried your suggestion but still doesn't work...I'm not an SQL expert  )
I will rite here the involved tables and their main fields:
Orders(OrderId)
OrderDetail(OrderId, ProductSubCode, Quantity)
Products(ProductSubCode, Quantity)
When an order is canceled I want to get all the items on it and re-insert them in the Product table (summarizing with the amount already in that table)
If I do a select of all the items in a given order as you told me I will get something like this:
"SELECT * INTO #TMP FROM ORDERDETAIL WHERE ORDERID=30"
OrderId ProductSubCode Quantity
30 BD0002 10
30 BD0004 4
Now I need to re-insert those quantities into the Products table.
I can't figure out how I ca do that without a cursor since I need to iterate through the records in the temporary table.
Of course I can still be missing alternatives
Thanks a lot.
|
|
|
|
|
OK - here's a sample way to update the product table:
UPDATE Products
SET p.Quantity = p.Quantity + t.Quantity
FROM Products p, #Tmp t
WHERE p.ProductSubCode = t.ProductSubCode This doesn't involve any cursors and is completely set based.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
