previous month on first day

Newbie question. I am using a query that pulls month-to-date data that has the following where clause:

WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))

this works just fine but what I would like for it to do is give me the previous month of data if the if it's

the first day of the month and then any other day give me month to date. Is this possible?

Thanks in advance,

Marco

Could you just use GETDATE()-1?

Code Snippet

WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE()-1))

HTH!

|||

I'm sorry, I should have added that I want to accomplish this without having to modify the query every month.

|||

Sorry, i don't understand. Why would you have to modify the query every month?

|||

Well, throughout the entire month I use WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE())) to get the month to date data and then when the new month starts Ihave to change it to WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE())) to get the previous month and then change it back to continue the month-to-date data.

|||

But as you're only matching on month and year, you won't have to change it. GETDATE()-1 will take one day off the date, and so it just means that on the 1st day of the month, it will use the last day of the last month whereas any other day within that month, the month and year values are the same.

Test it out:

Code Snippet

declare @.date datetime

set @.date = '1 nov 2007'

select month(@.date-1), year(@.date-1)

declare @.date datetime

set @.date = '20 nov 2007'

select month(@.date-1), year(@.date-1)

|||

Code Snippet

SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, '8/29/07'), 0))

Lee Everest

Sr. Consultant - Sogeti USA LLC

www.texas2oo.com/sqlblog

|||

You can combine the 2 WHERE clause like this :

select datefield,month(datefield) from dbo.datefieldtable

WHERE (

MONTH(datefield)=

case when DATEPART(day, GETDATE())=1 then MONTH(GETDATE()-1)

else MONTH(GETDATE()) end )

AND

YEAR(datefield) = YEAR(GETDATE())

--with DATEPART(day, GETDATE())=1 you can obtain the first day of month

You have to see the performance!

|||

here it is,

Code Snippet

where

datefield >= case when day(getdate()) = 1 then

cast(convert(varchar,dateadd(mm,-1,getdate()),101) as datetime)

else

cast(convert(varchar,dateadd(dd,1-day(getdate()),getdate()),101) as datetime)

end

and

datefield < case when day(getdate()) = 1 then

cast(convert(varchar,getdate(),101) as datetime)

else

cast(convert(varchar,getdate()+1,101) as datetime)

end

|||

Perhaps the range can be obtained by something like:

Code Snippet

declare @.testDates table (dt datetime)
insert into @.testDates
select '7/20/7' union all
select '7/31/7' union all
select '8/1/7' union all
select '8/2/7' union all
select '8/5/7' union all
select '8/31/7' union all
select '9/1/7' union all
select '9/2/7'
--select * from @.testDates

select dt,
dateadd(mm, datediff(mm, 0, dt-1), 0) as rangeLow,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0) as rangeHi,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0)-1 as monthEnd
from @.testDates

/*
dt rangeLow rangeHi monthEnd
--
2007-07-20 2007-07-01 2007-08-01 2007-07-31
2007-07-31 2007-07-01 2007-08-01 2007-07-31
2007-08-01 2007-07-01 2007-08-01 2007-07-31
2007-08-02 2007-08-01 2007-09-01 2007-08-31
2007-08-05 2007-08-01 2007-09-01 2007-08-31
2007-08-31 2007-08-01 2007-09-01 2007-08-31
2007-09-01 2007-08-01 2007-09-01 2007-08-31
2007-09-02 2007-09-01 2007-10-01 2007-09-30
*/

|||

Got it. Thank you all.

Preventing GroupBy clause

I had a question similar to this before and can't seem to make this one
work.
I have the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
This works fine if I don't have the max function, but it can give me
multiple responses so I just want the last ApplicantID. This also works if
I put the password, email,firstName and LastName in a Groupby clause. Can I
change the max statement to prevent having to use the Groupby Clause.
Thanks,
TomIs there any special reason you'd rather not use a GROUP BY clause?
Any way, you can use:
SELECT TOP 1 <col_list>
FROM <table>
ORDER BY <sort_list>
BG, SQL Server MVP
www.SolidQualityLearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>I had a question similar to this before and can't seem to make this one
>work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
> if I put the password, email,firstName and LastName in a Groupby clause.
> Can I change the max statement to prevent having to use the Groupby
> Clause.
> Thanks,
> Tom
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O1mgWnkAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> Is there any special reason you'd rather not use a GROUP BY clause?
Mainly, because it is just something more for SQL to do, when all I want is
the Max Applicant.

> Any way, you can use:
> SELECT TOP 1 <col_list>
> FROM <table>
> ORDER BY <sort_list>
Same problem as above - would like to just say Max instead of having to sort
it.
It may not be much different, but I assume that doing a sort or group is a
little more of a hit to the engine that getting the max number.
Thanks,
Tom
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>|||> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
Well, did you test your theory? We have the ability to view the plans,
stress test our apps, and time our code. Why are we relying on assumptions
to make our decisions for us?|||<snip>
> Same problem as above - would like to just say Max instead of having to so
rt
> it.
> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
So how is SQL-Server supposed to determine the Max ApplicantID without
some way of comparing the different ApplicantIDs? Should SQL-Server just
guess it? In that case, you can leave out the ORDER BY clause, and you
will get 'any' ApplicantID.
Otherwise, just use the technology (i.e. use a GROUP BY clause) and
trust the product, or do some actual performance testing instead of
asking the impossible. Chances are that you would even see a significant
(measurable) performance difference between the different methods,
unless you're using a big table.
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41F567CA.CC06163B@.toomuchspamalready.nl...
> <snip>
> So how is SQL-Server supposed to determine the Max ApplicantID without
> some way of comparing the different ApplicantIDs? Should SQL-Server just
> guess it? In that case, you can leave out the ORDER BY clause, and you
> will get 'any' ApplicantID.
>
No, I was trying to use the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
which does work fine if I take all fields out. I want the max ApplicantID,
because it will be the last ApplicantID assigned for this email address.
Tom
> Otherwise, just use the technology (i.e. use a GROUP BY clause) and
> trust the product, or do some actual performance testing instead of
> asking the impossible. Chances are that you would even see a significant
> (measurable) performance difference between the different methods,
> unless you're using a big table.
> Gert-Jan|||> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
ApplicantID,
> because it will be the last ApplicantID assigned for this email address.
You need to get the MAX from a subquery. Also note that SQL Server will
have no idea if you want the MAX(ApplicantID) to have an e-mail address of
tfs@.dlink.com or not.|||--Down with JOINS, up with table variables:
DECLARE @.MyApplicant TABLE
(
email varchar(50),
pw varchar(50),
fname varchar(50),
lname varchar(50),
AppID int
)
--grab records of interest
INSERT INTO @.MyApplicant (email, pw, fname, lname)
SELECT l.email, l.password, l.firstName, l.LastName
FROM logon l
WHERE l.email = 'tfs@.dlink.com'
--grab details pertaining to the records of interest by key information
UPDATE @.MyApplicant
SET AppID =
(
SELECT Max(a.ApplicantID)
FROM Applicant a
WHERE a.email = myapp.email
)
FROM @.MyApplicant myapp
SELECT *
FROM @.MyApplicant
--assuming indexes on email field in all tables, it doesn't get much
faster than this.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
> I had a question similar to this before and can't seem to make this one
> work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
if
> I put the password, email,firstName and LastName in a Groupby clause. Can
I
> change the max statement to prevent having to use the Groupby Clause.
> Thanks,
> Tom
>|||This may be unreasonable of me, but when you say max(applicantID), is this
really the last one? Could you not assign an applicantId that had already
been used? Do you not have a date when the assignment was made that you can
use to get the last assignment?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41F567CA.CC06163B@.toomuchspamalready.nl...
> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
> ApplicantID, because it will be the last ApplicantID assigned for this
> email address.
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uwWf3EpAFHA.2112@.TK2MSFTNGP09.phx.gbl...
> This may be unreasonable of me, but when you say max(applicantID), is this
> really the last one? Could you not assign an applicantId that had already
> been used? Do you not have a date when the assignment was made that you
> can use to get the last assignment?
In this particular case the applicantID is just a numeric ID that is
sequentially assigned. It happens to be an identity field. The max
applicantID will always be the latest one assigned.
Tom
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
>

Prevent users from seeing Table structures or underlying data

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

Decryption is weak and can be cracked by searching on google for the specific algorithms, there was a thread sometime ago, which might be useful to you:

http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/34b309b76ba574b4

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens is right, the procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back.

Thanks
Laurentiu

|||

Thanks for your comments

and is there a tool in Sql Server 2005 in order to protect the code? What can I do? I need to install a project in the customer, and I would like to protect it.

Thanks again for your help.

Haydee

|||You could use third party components to accomplish this, there sure can be found some by searching in google for them.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

HI,

I am using MSDE 2000 and I will be deploying it with my software application. I have invested a good bit into my database schema and I don't want it to be viewed by others.

I can not see why some user can not take the .mdf (multiple mdf's actually) and sp_AttachDB or attach them to their instanced SQL server using EM. I of course do not want this.

Maybe someone can clear up the limitations and types of SQL security that can assure no one can simply attach the MDF to see the structure, let alone the data.

As far as I can see there is Network security as to authentication for a live/instanced SQL server and this has no ability to prevent an MDF from being re-attached and viewed/queried.

I also see EncryptByPassPhrase which I can use prior to executing a query (if I understand this process which is data remains in encrypted state until its about to be used, then decrypted in memory (I presume ? otherwise someone could grab a snapshot of the mdf while it's in decrypted state ? {or SQL server has a temp region when using encryption where it places the decrypted data I take it}) and then I have to encrypt it again after processing.

Neither of these look like they can obfuscate or lock the db schema information, such as table names, structures, fields, field types/attributes etc.

Sooooooo.....

How can I prevent a user from seeing the underlying table structures and does anyone know if column encryption will cost me 10 years off my life time wise on large data sets ?

Thanks

|||

See this recent thread for a discussion of this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu

|||

The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause that we discussed so far. It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution. This is a general problem and for any solution you consider, you should look at how easy it is for someone to find the encryption key.

This is basically a DRM solution, and I have talked about the difficulty of creating an unbreakable DRM solution on other threads, more recently in:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1

Thanks
Laurentiu