Post reply

If ... select ...else then ??

  • March 1, 2012 at 2:27 pm

    #1453887

    Oh, i use this SQL Server Management Studio Express 2008 R2 on win7

    Microsoft SQL Server Management Studio10.0.1600.22 ((SQL_PreRelease).080709-1414 )

    Microsoft Data Access Components (MDAC)6.1.7601.17514 (win7sp1_rtm.101119-1850)

    Microsoft MSXML3.0 5.0 6.0

    Microsoft Internet Explorer8.0.7601.17514

    Microsoft .NET Framework2.0.50727.5448

    Operating System6.1.7601

  • March 1, 2012 at 2:38 pm

    #1453895

    What is the result of this query?

    select @@version

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 1, 2012 at 3:21 pm

    #1453922

    Do you mean SQL Server 10.0.4064 ?

  • March 1, 2012 at 3:28 pm

    #1453926

    Well what J Livingston wants to know is what version of SQL server are you connecting to. It doesn't matter what version of SSMS you have. What matters is the engine that SQL is using.

    Just to explain we have 3 servers here with different versions of sql. 2205, 2008, and 2008 R2. I can connect to any of those with the same SSMS.

    So depending on the server I am connected to i get these as results.

    Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )

    Microsoft SQL Server 2008 (SP1) - 10.0.2714.0 (X64) May 14 2009 16:08:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    The capabilities of each of these are a little different and he wants to know so he knows what he can use in the query.

    Just run the query and copy and paste the results.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • March 1, 2012 at 3:47 pm

    #1453935

    Finally I made it works, here are my query

    declare @dow int

    declare @2SundaysAgo datetime

    declare @lastSaturday datetime

    select @dow = datepart(dw, getdate())

    select @2SundaysAgo = getdate() - (7 + (@dow - 1))

    select @lastSaturday = getdate() - (@dow) + 1

    select @2SundaysAgo = CONVERT(datetime, CONVERT(varchar(10), @2SundaysAgo, 101))

    select @lastSaturday = DATEADD(s, -1, CONVERT(datetime, CONVERT(varchar(10), @lastSaturday , 101)))

    SELECT [System].[dbo].[Machine_Control_History].parent_id,

    '[System].[dbo].[Machine_Control_History].start_datetime' = CASE

    WHEN [System].[dbo].[Machine_Control_History].start_datetime < @2SundaysAgo THEN (@2SundaysAgo)

    ELSE ([System].[dbo].[Machine_Control_History].start_datetime)

    END

    ,[System].[dbo].[Machine_Control_History].end_datetime

    ,[System].[dbo].[Machine_Control_History].machine_status

    ,[System].[dbo].[Machine_Control_History].active_user

    ,[System].[dbo].[Machine_Control_History].notes

    FROM [System].[dbo].[Machine_Control_History]

    WHERE [System].[dbo].[Machine_Control_History].end_datetime BETWEEN @2SundaysAgo AND @lastSaturday

    ORDER BY [System].[dbo].[Machine_Control_History].start_datetime

    Sorry to confuse all of you with my questions.

    This is what I need and it works now.

    Thanks,

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply