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
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
Regards
Rambhopal Reddy E
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as
Answer” if a marked post does not actually answer your question.
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
Regards
Rambhopal Reddy E
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as
Answer” if a marked post does not actually answer your question.
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 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.
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.
Member
116 Points
113 Posts
Join two queris having diffent rows and having group by in one
Feb 07, 2013 06:41 AM|keval.trivedi|LINK
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....
Member
367 Points
206 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 06:47 AM|rambhopalreddy|LINK
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
Rambhopal Reddy E
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as
Answer” if a marked post does not actually answer your question.
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 06:49 AM|keval.trivedi|LINK
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."
Member
367 Points
206 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 06:52 AM|rambhopalreddy|LINK
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
Rambhopal Reddy E
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as
Answer” if a marked post does not actually answer your question.
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 06:56 AM|keval.trivedi|LINK
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
Contributor
3829 Points
1281 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 01:35 AM|matifnadeem|LINK
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 dBut unfortunately the output show one bug.
I hope so you can get rid of that bug. Issue occur when we find status value "Confirm" or "Block" in some date. Like
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
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 04:24 AM|keval.trivedi|LINK
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
Contributor
3829 Points
1281 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 05:22 AM|matifnadeem|LINK
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 dIt will resolve your issue. Let me know if any issue still remains.
Cheers
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 11:06 PM|keval.trivedi|LINK
Yup, It is working fine.....
I am tring to solve that repeatation problem If you have some idea than tell me.....
Thanks a lot
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 11:24 PM|keval.trivedi|LINK
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
All-Star
94130 Points
18109 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 12, 2013 08:16 PM|Decker Dong - MSFT|LINK
Hi keval,
What actually do you wanna do?
Can you elebrate this more?
Member
116 Points
113 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 14, 2013 11:45 PM|keval.trivedi|LINK
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