none
Where with multiples columns RRS feed

  • Question

  • Hi guys,

    I need to compare if the current month has value 1 in respective column name month in the table. I have the table

    select jan,fev,mar,apr,may,jun,jul,ago,sep,oct,nov,dec,value,country from table A

    I need to compare if current month convert(char(3), GETDATE(), 0) has value 1 in respective month name column like December

    It's possible?

    Friday, December 13, 2019 5:41 PM

All replies

  • see if unpivot if you need just the current month info..

    DECLARE @Test TABLE (sname VARCHAR(200),[Dec] VARCHAR(20),[Nov] VARCHAR(20),[Oct] VARCHAR(20),[Sep] VARCHAR(20))
    
    INSERT INTO @test 
    VALUES ('1','1','0','0','0'),('1','0','0','0','0')
    
    ; WITH CTE AS (SELECT sname,[Month],[V1]
    FROM @test
    UNPIVOT
    (
    	[V1]
    	FOR [Month] in (Dec,Nov,Oct,Sep)
    ) AS SchoolUnpivot)
    
    SELECT * FROM CTE WHERE [Month]=CAST(DATENAME(Month,GETDATE()) AS VARCHAR(3)) AND V1=1
    

    If you full result set and do not have unique value to build it..try this.

    CREATE Table MyTest(sname VARCHAR(200),[Dec] VARCHAR(20),[Nov] VARCHAR(20),[Oct] VARCHAR(20),[Sep] VARCHAR(20))
    
    INSERT INTO  MyTest 
    VALUES ('1','1','0','0','0'),('1','0','0','0','0')
    
    DECLARE @SQL NVARCHAR(200)
    DECLARE @Col NVARCHAR(200)
    
    SELECT @Col = [Name] FROM sys.columns WHERE OBJECT_ID=OBJECT_ID('MyTest') AND Name = CAST(DATENAME(Month,GETDATE()) AS VARCHAR(3))
    SET @SQL = 'SELECT * FROM MyTest WHERE ['+@Col+']=''1'''
    
    EXECUTE sp_executesql @SQL
    
    DROP TABLE dbo.MyTest


    Hope it Helps!!

    • Proposed as answer by Lily Lii Tuesday, December 17, 2019 9:53 AM
    Friday, December 13, 2019 6:16 PM
  • If you provide your table structure and some sample data and your expected result, it will be helpful.

    Thanks.

    Friday, December 13, 2019 6:23 PM
    Moderator
  • If there are multiple rows and you want to check if there is at least one suitable row, then check this fragment too:

    declare @has_value as bit = 0
     
    if exists( select 0 from TableA 
                   where choose( DATEPART(m, GETDATE()), jan,fev,mar,apr,may,jun,jul,ago,sep,oct,nov,[dec]) = 1)
           set @has_value = 1 

     

    The result is in @has_value variable.




    • Edited by Viorel_MVP Friday, December 13, 2019 8:29 PM
    Friday, December 13, 2019 8:28 PM
  • Hi twister8,

    Do you need to check if the value exists in table?

    Please use DECLARE @local_variable.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 9:44 AM