Join two queris having diffent rows and having group by in one

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....

Answer 1

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  

Answer 2

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."

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

User3866881 posted

Hi keval,

What actually do you wanna do?

Can you elebrate this more?

Answer 11

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