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.
keval.trived...
Member
158 Points
87 Posts
Join two queris having diffent rows and having group by in one
Feb 07, 2013 10:41 AM|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....
rambhopalred...
Member
671 Points
214 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 10:47 AM|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.
keval.trived...
Member
158 Points
87 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 10:49 AM|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."
rambhopalred...
Member
671 Points
214 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 10:52 AM|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.
keval.trived...
Member
158 Points
87 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 07, 2013 10:56 AM|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
matifnadeem
Contributor
4700 Points
1111 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 05:35 AM|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
M Atif Nadeem
Mark as Answer if you got right thing
Read my blog | Follow me on LinkedIn
keval.trived...
Member
158 Points
87 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 08:24 AM|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
matifnadeem
Contributor
4700 Points
1111 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 08, 2013 09:22 AM|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
M Atif Nadeem
Mark as Answer if you got right thing
Read my blog | Follow me on LinkedIn
keval.trived...
Member
158 Points
87 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 09, 2013 03:06 AM|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
keval.trived...
Member
158 Points
87 Posts
Re: Join two queris having diffent rows and having group by in one
Feb 09, 2013 03:24 AM|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