locked
Join two queris having diffent rows and having group by in one RRS feed

  • Question

  • User-736011398 posted

    Hello,

    This are my two queris,

    1.  Declare @Days Table ([Date] datetime)
        Declare @CurrentDate date
        Declare @EndDate date
       
        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 * From @Days as d

    2. SELECT date,COUNT(CASE WHEN room_status = 'Blocked' THEN 1 END) AS Blocked,
        COUNT(CASE WHEN room_status = 'Confirm' THEN 1 END) AS Confirm
        FROM room_transaction r
        GROUP BY r.Date   

    I want to combine this queris....

    Thursday, February 7, 2013 6:41 AM

Answers

  • User-782344923 posted

    But The status shows in one column only as we discuss b4 we have saperated the status column into two

    So Resulted Should be

    Date       Blocked       Confirm

    where Date has to be all dates of months and Blocked and Confirm should be count as we discussed

    Hi keval.trivedi,

    Follow my sql query to get rid of your issue. It will display exact output that you want.

    DECLARE @Days TABLE ([Date] DATETIME, [Confirm] INT, [Block] INT)
    DECLARE @CurrentDate DATETIME
    DECLARE @EndDate DATETIME
    
    DECLARE @Date DATETIME
    DECLARE @Confirm INT
    DECLARE @Block INT
    
    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(
    		CASE WHEN @Date IS NULL THEN @CurrentDate ELSE @Date END, 
    		CASE WHEN @Confirm IS NULL THEN 0 ELSE @Confirm END,
    		CASE WHEN @Block IS NULL THEN 0 ELSE @Block END)
    
             SET @Date = (SELECT Date FROM Status WHERE YEAR(Date) = YEAR(@CurrentDate) AND 
    						MONTH(Date) = MONTH(@CurrentDate) AND DAY(Date) = DAY(@CurrentDate)
    						GROUP BY Date)
    
    		SET @Confirm = (SELECT COUNT(CASE WHEN Status = 'Confirm' THEN 1 END) AS Confirm FROM Status 
    						WHERE YEAR(Date) = YEAR(@CurrentDate) AND MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate) GROUP BY Date)
    
    		SET @Block = (SELECT COUNT(CASE WHEN Status = 'Block' THEN 1 END) AS Block FROM Status
    						WHERE YEAR(Date) = YEAR(@CurrentDate) AND MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate) GROUP BY Date)
    
            SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
    END
    
    SELECT *
    FROM   @Days AS d

    It will resolve your issue. Let me know if any issue still remains.

    Cheers

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

All replies

  • User179837873 posted

    USE below query

    SELECT d.date,COUNT(CASE WHEN room_status = 'Blocked' THEN 1 END) AS Blocked,
        COUNT(CASE WHEN room_status = 'Confirm' THEN 1 END) AS Confirm
        FROM room_transaction r  JOIN @Days d ON d.data = r.Date

        GROUP BY r.Date  

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

    NO its not working giving error like,

    "Column '@Days.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."



    Thursday, February 7, 2013 6:49 AM
  • User179837873 posted

    USE below one forgot to change the date selection table


    SELECT d.date,COUNT(CASE WHEN room_status = 'Blocked' THEN 1 END) AS Blocked,
        COUNT(CASE WHEN room_status = 'Confirm' THEN 1 END) AS Confirm
        FROM room_transaction r  JOIN @Days d ON d.data = r.Date

        GROUP BY d.date

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

    It will give me only number of records having in room_transaction

    But,

    I want all dates which my query 1 gives and if date matches it shows me count else zeroes

    Thursday, February 7, 2013 6:56 AM
  • User-782344923 posted

    Hi keval.trivedi,

    I merge both two queries 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

    But unfortunately the output show one bug. 

           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

    I hope so you can get rid of that bug. Issue occur when we find status value "Confirm" or "Block" in some date. Like

    2013-02-01 16:40:13.977 0
    2013-02-01 00:00:00.000 Block

    In above two rows, status --> "Block" is present at Feb, 01 but it insert two rows. You have to change something in While loop to get rid of this. 

    Let me know if any query remains.

    Cheers

    Friday, February 8, 2013 1:35 AM
  • User-736011398 posted

    But The status shows in one column only as we discuss b4 we have saperated the status column into two

    So Resulted Should be

    Date       Blocked       Confirm

    where Date has to be all dates of months and Blocked and Confirm should be count as we discussed

    Friday, February 8, 2013 4:24 AM
  • User-782344923 posted

    But The status shows in one column only as we discuss b4 we have saperated the status column into two

    So Resulted Should be

    Date       Blocked       Confirm

    where Date has to be all dates of months and Blocked and Confirm should be count as we discussed

    Hi keval.trivedi,

    Follow my sql query to get rid of your issue. It will display exact output that you want.

    DECLARE @Days TABLE ([Date] DATETIME, [Confirm] INT, [Block] INT)
    DECLARE @CurrentDate DATETIME
    DECLARE @EndDate DATETIME
    
    DECLARE @Date DATETIME
    DECLARE @Confirm INT
    DECLARE @Block INT
    
    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(
    		CASE WHEN @Date IS NULL THEN @CurrentDate ELSE @Date END, 
    		CASE WHEN @Confirm IS NULL THEN 0 ELSE @Confirm END,
    		CASE WHEN @Block IS NULL THEN 0 ELSE @Block END)
    
             SET @Date = (SELECT Date FROM Status WHERE YEAR(Date) = YEAR(@CurrentDate) AND 
    						MONTH(Date) = MONTH(@CurrentDate) AND DAY(Date) = DAY(@CurrentDate)
    						GROUP BY Date)
    
    		SET @Confirm = (SELECT COUNT(CASE WHEN Status = 'Confirm' THEN 1 END) AS Confirm FROM Status 
    						WHERE YEAR(Date) = YEAR(@CurrentDate) AND MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate) GROUP BY Date)
    
    		SET @Block = (SELECT COUNT(CASE WHEN Status = 'Block' THEN 1 END) AS Block FROM Status
    						WHERE YEAR(Date) = YEAR(@CurrentDate) AND MONTH(Date) = MONTH(@CurrentDate) AND
    						DAY(Date) = DAY(@CurrentDate) GROUP BY Date)
    
            SET @CurrentDate = DATEADD(d, 1, @CurrentDate)
    END
    
    SELECT *
    FROM   @Days AS d

    It will resolve your issue. Let me know if any issue still remains.

    Cheers

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2013 5:22 AM
  • User-736011398 posted

    Yup, It is working fine.....

    I am tring to solve that repeatation problem If you have some idea than tell me.....

    Thanks a lot

    Friday, February 8, 2013 11:06 PM
  • User-736011398 posted

    Your query gives me this output, 

    2013-02-01 09:47:11.457     0    0
    2013-02-02 09:47:11.457     0    0
    2013-02-03 09:47:11.457     0    0
    2013-02-04 09:47:11.457     0    0   
    2013-02-05 09:47:11.457     0    0
    2013-02-05 00:00:00.000     0    4
    2013-02-06 00:00:00.000     0    4
    2013-02-07 00:00:00.000     2    6
    2013-02-08 00:00:00.000     2    6
    2013-02-09 00:00:00.000     0    4
    2013-02-11 09:47:11.457     0    0
    2013-02-12 09:47:11.457     0    0
    2013-02-13 09:47:11.457     0    0
    2013-02-14 09:47:11.457     0    0
    2013-02-15 09:47:11.457     0    0   
    2013-02-16 09:47:11.457     0    0
    2013-02-17 09:47:11.457     0    0
    2013-02-18 09:47:11.457     0    0
    2013-02-19 09:47:11.457     0    0
    2013-02-20 09:47:11.457     0    0
    2013-02-21 09:47:11.457     0    0
    2013-02-22 09:47:11.457     0    0
    2013-02-23 09:47:11.457     0    0
    2013-02-24 09:47:11.457     0    0
    2013-02-25 09:47:11.457     0    0
    2013-02-26 09:47:11.457     0    0
    2013-02-27 09:47:11.457     0    0
    2013-02-28 09:47:11.457     0    0

    Friday, February 8, 2013 11:24 PM
  • User3866881 posted

    Hi keval,

    What actually do you wanna do?

    Can you elebrate this more?

    Tuesday, February 12, 2013 8:16 PM
  • User-736011398 posted

    As you show my queries first will gives me all dates of current month and second gives me rooms that is blocked or confirm in perticular day now i m tring to combine these two queries and want to display whole months dates and when if there is record in perticular day it will show me else it will show me zero(0).

    Query given by matifnadeem the out put of that query repeat the column when there is data.

    Please help me to solve this problem.

    Thanx

    Thursday, February 14, 2013 11:45 PM