locked
How to get Single column of data into two RRS feed

  • Question

  • User-736011398 posted

    Hello friend,

    I have following data,

    Date             Status

    2013-02-05    Blocked
    2013-02-05    Blocked
    2013-02-05    Blocked
    2013-02-05    Blocked
    2013-02-06    Blocked
    2013-02-06    Blocked
    2013-02-06    Blocked
    2013-02-06    Blocked
    2013-02-07    Blocked
    2013-02-07    Blocked
    2013-02-07    Blocked
    2013-02-07    Blocked
    2013-02-08    Blocked
    2013-02-08    Blocked
    2013-02-08    Blocked
    2013-02-08    Blocked
    2013-02-09    Blocked
    2013-02-09    Blocked
    2013-02-09    Blocked
    2013-02-09    Blocked
    2013-02-07    Confirm
    2013-02-07    Confirm
    2013-02-07    Blocked
    2013-02-07    Blocked
    2013-02-08    Confirm
    2013-02-08    Confirm
    2013-02-08    Blocked
    2013-02-08    Blocked

    Now I want count of "Blocked" and count of "Confirm"  status in saperate columns in group by dates..

    Thanks......

    Thursday, February 7, 2013 4:15 AM

Answers

  • User-782344923 posted

    Nop Its not working it is giving result like,

    2013-02-05 00:00:00.000    4    4
    2013-02-06 00:00:00.000    4    4
    2013-02-07 00:00:00.000    8    8
    2013-02-08 00:00:00.000    8    8
    2013-02-09 00:00:00.000    4    4

    Hi keval.trivedi,

    Modify your query like this,

    SELECT	   Date, COUNT(CASE WHEN Status = 'Confirm' THEN 1 END) AS Confirm,
               COUNT(CASE WHEN Status = 'Block' THEN 1 END) AS Block
    FROM	   Status
    GROUP BY   Date

    This will give you your desire output. Let me know if any query remains.

    Cheers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2013 5:24 AM

All replies

  • User179837873 posted

    Write 2 temp tables for having above Blocked and Confirm.  join the temp table it will work for you.

    Thursday, February 7, 2013 4:22 AM
  • User143067745 posted

    Select [DATE], Count( Case When Status='Blocked' Then 1 Else 0) as 'Blocked',

               Count( Case When Status='Confirm' Then 1 Else 0) as 'Confirm'  

    From TableName Group By [DATE]

    hope it helps.

    Thursday, February 7, 2013 4:26 AM
  • User-736011398 posted

    No its not working.....

    Can I use pivot for that?

    I dont know how to use but I find it in google.

    If yes than how?

    Thursday, February 7, 2013 4:32 AM
  • User143067745 posted

    No its not working.....

    Can I use pivot for that?

    I dont know how to use but I find it in google.

    If yes than how?

    Did you really tried my previous posted query?

    Select [DATE], Count( Case When Status='Blocked' Then 1 Else 0) as 'Blocked',

               Count( Case When Status='Confirm' Then 1 Else 0) as 'Confirm'  

    From TableName Group By [DATE]

    if yes Please tell me what is the problem then?

    Thank you.

    Thursday, February 7, 2013 4:49 AM
  • User-736011398 posted

    It is showing error like "incorrect syntax near ')'"

    I have tried this

    select [date],st.[Blocked],st.[Confirm] from room_transaction
    pivot (count(room_status) for room_status in ([Blocked],[Confirm]))
    as st

    but it is not work when i use groupby......

    Thursday, February 7, 2013 4:57 AM
  • User143067745 posted

    Sry It's my mistake  add 'End' and try again


    Select [DATE], Count( Case When Status='Blocked' Then 1 Else 0 End) as 'Blocked',

               Count( Case When Status='Confirm' Then 1 Else 0 End) as 'Confirm'  

    From TableName Group By [DATE]


    Thursday, February 7, 2013 5:00 AM
  • User-736011398 posted

    Nop Its not working it is giving result like,

    2013-02-05 00:00:00.000    4    4
    2013-02-06 00:00:00.000    4    4
    2013-02-07 00:00:00.000    8    8
    2013-02-08 00:00:00.000    8    8
    2013-02-09 00:00:00.000    4    4

    Thursday, February 7, 2013 5:08 AM
  • User143067745 posted

    Try

    Select  Convert(Date,[DATE]), Count( Case When Status='Blocked' Then 1 Else 0 End) as 'Blocked',

               Count( Case When Status='Confirm' Then 1 Else 0 End) as 'Confirm'  

    From TableName Group By [DATE]

    Thursday, February 7, 2013 5:16 AM
  • User-1130897084 posted

    pls give an example what u want

    Thursday, February 7, 2013 5:20 AM
  • User-736011398 posted

    Not date problem,

    its giving same value in both columns

    Thursday, February 7, 2013 5:20 AM
  • User-782344923 posted

    Nop Its not working it is giving result like,

    2013-02-05 00:00:00.000    4    4
    2013-02-06 00:00:00.000    4    4
    2013-02-07 00:00:00.000    8    8
    2013-02-08 00:00:00.000    8    8
    2013-02-09 00:00:00.000    4    4

    Hi keval.trivedi,

    Modify your query like this,

    SELECT	   Date, COUNT(CASE WHEN Status = 'Confirm' THEN 1 END) AS Confirm,
               COUNT(CASE WHEN Status = 'Block' THEN 1 END) AS Block
    FROM	   Status
    GROUP BY   Date

    This will give you your desire output. Let me know if any query remains.

    Cheers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 7, 2013 5:24 AM
  • User-782344923 posted

    Hi again, 

    I added dummy data in order to run the query, my above sql query return me output as

    DATE                    Confirm      Block
    --------------------------------------------
    2013-02-05 00:00:00.000	   4	      8
    2013-02-06 00:00:00.000	   5	      8

    It is same that you want.

    Cheers

    Thursday, February 7, 2013 5:27 AM
  • User-736011398 posted

    Its Working........

    Thanks a lot 

    matifnadeem

    Thursday, February 7, 2013 5:29 AM
  • User-736011398 posted

    It is Giving me dates i have inserted only can you plz try to give me some more help?

    Copy this query,

    Declare @Days Table ([Date] datetime)
    Declare @CurrentDate date
    Declare @EndDate date
    --SET @TotDays = dbo.GetNumDaysInMonth(GETDATE())

    SET @CurrentDate = DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())
    SET @EndDate = DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE()))
    While @CurrentDate <= @EndDate
    Begin
    Insert Into @Days Values(@CurrentDate)
    Set @CurrentDate = DateAdd(d,1,@CurrentDate)
    End
    Select d.[Date] From @Days as d

    Now is is giving me all dates of month, Now i want to show all dates if it is not having "confirm" or "blocked" status then should display zero(0)s

    Thursday, February 7, 2013 5:36 AM
  • User-782344923 posted

    Hi,

    This thread is over. Please create a new one for this, because it is right thing to do so other who will come here later able to see right answer.  Well after copying your query with my dummy data, I create a resulted query as

    DECLARE	@Days TABLE ([Date] DATETIME, [Status] VARCHAR(10))
    DECLARE @CurrentDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @Date DATETIME
    DECLARE @Status VARCHAR(10)
    
    SET	@CurrentDate = DATEADD(dd, -(DAY(GETDATE()) - 1), GETDATE())
    SET @EndDate = DATEADD(dd, -(DAY(DATEADD(mm, 1, GETDATE()))), 
    				DATEADD(mm, 1, GETDATE()))
    SET @Status = '0'
    
    WHILE @CurrentDate <= @EndDate
    BEGIN		
    	INSERT INTO @Days VALUES(CASE WHEN @Date IS NULL THEN @CurrentDate
    					ELSE @Date END, CASE WHEN @Status IS NULL THEN '0'
    					ELSE @Status END)
    
    	SET @Date = (SELECT Date FROM Status 
    					WHERE	YEAR(Date) = YEAR(@CurrentDate) AND
    						MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate))
    
    	SET @Status = (SELECT Status FROM Status 
    					WHERE	YEAR(Date) = YEAR(@CurrentDate) AND
    						MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate))
    
    	SET @CurrentDate = DATEADD(d, 1, @CurrentDate)		
    END
    
    SELECT *
    FROM   @Days AS d

    The output of above query is

           DATE             STATUS
    ---------------------------------------
    2013-02-01 16:40:13.977	0
    2013-02-01 00:00:00.000	Block
    2013-02-03 16:40:13.977	0
    2013-02-04 16:40:13.977	0
    2013-02-05 16:40:13.977	0
    2013-02-05 00:00:00.000	Block
    2013-02-07 16:40:13.977	0
    2013-02-08 16:40:13.977	0
    2013-02-09 16:40:13.977	0
    2013-02-10 16:40:13.977	0
    2013-02-10 00:00:00.000	Confirm
    2013-02-12 16:40:13.977	0
    2013-02-13 16:40:13.977	0
    2013-02-14 16:40:13.977	0
    2013-02-15 16:40:13.977	0
    2013-02-16 16:40:13.977	0
    2013-02-17 16:40:13.977	0
    2013-02-18 16:40:13.977	0
    2013-02-19 16:40:13.977	0
    2013-02-20 16:40:13.977	0
    2013-02-20 00:00:00.000	Confirm
    2013-02-22 16:40:13.977	0
    2013-02-23 16:40:13.977	0
    2013-02-24 16:40:13.977	0
    2013-02-25 16:40:13.977	0
    2013-02-26 16:40:13.977	0
    2013-02-27 16:40:13.977	0
    2013-02-28 16:40:13.977	0

    But there is repeatation of row, I hope you can get rid of this to modify my query. 

    Try to remove duplication like
    
    2013-02-01 16:40:13.977	0
    2013-02-01 00:00:00.000	Block

    If any query remains... Please create a new post to resolve your issue.

    Cheers



    Thursday, February 7, 2013 6:30 AM
  • User-736011398 posted

    As you said I have started another thread here it is,

    http://forums.asp.net/t/1880971.aspx/1?Join+two+queris+having+diffent+rows+and+having+group+by+in+one

    Thanks for giving your this much time but,

    did you see the the problem where status is there it is repeating the same date see in your output

    Thursday, February 7, 2013 9:19 AM