Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Procedure Questions

Posted on 2007-09-28
14
Medium Priority
?
336 Views
Last Modified: 2010-03-19
Hey everyone, I have one question, and again I'm very sorry for my poor knowledge of this system, I know I've been opening numours cases but I'm very new to Experts-Exchange.  Thank you very much Adraughn for your help getting me this far.

Question .
on MIN(ChangeDTTM) i'm looking to get the MIN in the PREVIOUS full month, so for example today is September 28, I'm looking to get the MIN from the full month of August. Does that make sence?  

CREATE PROCEDURE dbo.rptMinimumAuditDiagnosticResultDTTMs
AS
SELECT v.AuditEventType,v.InternalExamID, MIN(ChangeDTTM)
FROM vusrAuditDiagnosticReport v
WHERE (v.AuditEventType = 'DICTATE') OR (v.AuditEventType = 'TRANSCRIBE') OR (v.AuditEventType = 'AUTHENT')
GROUP BY v.InternalExamID,v.AuditEventType
0
Comment
Question by:dbguy2626
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19981148
CREATE PROCEDURE dbo.rptMinimumAuditDiagnosticResultDTTMs
AS
SELECT v.AuditEventType,v.InternalExamID,
MIN(ChangeDTTM) MINVALUE
FROM vusrAuditDiagnosticReport v
WHERE ((datepart(MM, ChangeDTTM) = Month(GetDate())-1)) AND
(v.AuditEventType = 'DICTATE') OR (v.AuditEventType = 'TRANSCRIBE') OR (v.AuditEventType = 'AUTHENT')
GROUP BY v.InternalExamID,v.AuditEventType
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 19981176
Hi,

so you are wanting rows where the date is between 1st of last month and first of this month.

The between clause includes both end values, so you are wanting something like this:
and v.DateColumn >= @FirstOfLastMonth and v.DateColumn < @FirstOfThisMonth.

CREATE PROCEDURE dbo.rptMinimumAuditDiagnosticResultDTTMs
AS
    -- This works by find the difference in months from datetime 0,
    -- which is 1 Jan 1900, and then adding that number of months
    -- back to datetime 0.
    -- Works for most datetime periods except seconds.
    declare @FirstOfThisMonth datetime
    set @FirstOfThisMonth = dateadd( month, datediff( month, 0, getdate() ), 0 )
    SELECT
        v.AuditEventType
        ,v.InternalExamID
        , MIN(ChangeDTTM)
    FROM dbo.vusrAuditDiagnosticReport v
    WHERE
        -- note the extra parenthasis around all the or's
        ((v.AuditEventType = 'DICTATE') OR (v.AuditEventType = 'TRANSCRIBE') OR (v.AuditEventType = 'AUTHENT'))
        -- First of Last Month is this month less one month
        and v.DateColumn >= dateadd( month, -1, @FirstOfThisMonth )
        and v.DateColumn < @FirstOfThisMonth
    GROUP BY v.InternalExamID,v.AuditEventType

go

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 19981189
Hi,

With Regards to SQL_SERVER_DBA's answer, you will also get multiple years. Ie August (or September) 2006 and 2005 and ...

Regards
  David
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dbguy2626
ID: 19981226
Thank you SQL Server DBA, but the dates returned were not even from this year, I'm just looking to get the previous month.
0
 

Author Comment

by:dbguy2626
ID: 19981234
Hi David, thanks for picking that up! I apprecaite that.
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19981257
AND Year(<datecolumn>) = year(getdate())
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19981427
Unfortunately, another problem with SQL_SERVER_DBA's code is it will produce errors if run in January. :-(
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19981462
Oh well, that's what I get for shooting my guns off,
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19981505
Don't worry about it. If I can borrow you and about five-hundred other people, we could count on our fingers how many times I've opened mouth, inserted foot. I've started washing my feet extra-carefully in the morning now. :-)
0
 

Author Comment

by:dbguy2626
ID: 19981516
HI David EVERYTHING worked but The Column Nameis blank on the date field when the rows return in SQL, how can I put the field/Column name back in?  Also how can I format the date to be YYYY,MM,DD,hh,mm,ss?  You are the man!! Thank you very very  much for your help.. You really helped me out
0
 

Author Comment

by:dbguy2626
ID: 19981521
Also thank you very much SQL_SERVER_DBA: for your prompt resonse and help, you guys are great.  This is an amazing service
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19981544
, MIN(ChangeDTTM) AS MinimumDate
0
 

Author Comment

by:dbguy2626
ID: 19981666
David,

Any idea on the date/time formatting in my last question?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 19982178
Hi,

        , convert( varchar( 20 ), MIN(ChangeDTTM), 120 ) as MinimumDateTime

This is close, but not exactly what you want.

Look up "Cast and Convert" in BOL. The third parameter to convert is the style. This is generally used for datetime formatting as you requested.

Otherwise to get what you requested is something like:
            , convert( char( 4 ), year( MIN(ChangeDTTM) ) + ',' +
                  right ( '0' + convert( char( 2 ), month( MIN(ChangeDTTM) ), 2 ) + ',' +
                  right ( '0' + convert( char( 2 ), day( MIN(ChangeDTTM) ), 2 ) + ',' +
                  right ( '0' + convert( char( 2 ), datepart( hour( MIN(ChangeDTTM) )), 2 ) + ',' +
                  right ( '0' + convert( char( 2 ), datepart( minute( MIN(ChangeDTTM) )), 2 ) + ',' +
                  right ( '0' + convert( char( 2 ), datepart( second( MIN(ChangeDTTM) )), 2 )

But would recommend doing this in the presentation layer, rather than SQL.

Regards
  David
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question