Hello Guys,
Here I am going to explain one interesting thing about SQL WHILE loop, for date calculation with counter based either date wise or month wise.
Why to use?
One day one of my friend was doing some programming regarding the Amount used in daily spent voucher system in his application, but he was facing one issue. His record was in order i.e some voucher was submitted on day like 01/02/2010 and another was submitted on 05/02/2013. But in his graph he was supposed to show all the month (with day wise) submitted voucher.
I explained him how very easily he can do it..Here is an example:
DECLARE @fromDate as DateTime
DECLARE @toDate as DateTime
SET @fromDate = '02/01/2013'
SET @toDate = '06/16/2013'
DECLARE @Tmp TABLE(SubmitDate DateTime, Amt numeric(18,0))
WHILE (@fromDate<=@toDate)
BEGIN
IF EXISTS(SELECT Amount,ExpenseDate FROM dbo.Expenses WHERE ExpenseDate = @fromDate)
BEGIN
INSERT INTO @Tmp(SubmitDate, Amt)
SELECT ExpenseDate, Amount FROM dbo.Expenses WHERE ExpenseDate = @fromDate
--PRINT @fromDate
END
ELSE
BEGIN
INSERT INTO @Tmp(SubmitDate, Amt)
VALUES(@fromDate, 0)
END
SET @fromDate = DATEADD(dd,1,@fromDate)
END
SELECT * FROM @Tmp
here is grid result:
The same way we can do by month also, here how it is:
DECLARE @fromDate as DateTime
DECLARE @toDate as DateTime
SET @fromDate = '02/01/2013'
SET @toDate = '06/16/2013'
DECLARE @Tmp TABLE(SubmitMonth int, SubmitYear int , Amt numeric(18,0))
WHILE (@fromDate<=@toDate)
BEGIN
IF EXISTS(SELECT Amount,ExpenseDate FROM dbo.Expenses
WHERE MONTH(ExpenseDate) = MONTH(@fromDate) AND YEAR(ExpenseDate) = YEAR(@fromDate) )
BEGIN
INSERT INTO @Tmp(SubmitMonth, SubmitYear, Amt)
SELECT MONTH(ExpenseDate), YEAR(ExpenseDate), sum(Amount) FROM dbo.Expenses
WHERE MONTH(ExpenseDate) = MONTH(@fromDate) AND YEAR(ExpenseDate) = YEAR(@fromDate)
GROUP BY MONTH(ExpenseDate), YEAR(ExpenseDate)
--PRINT @fromDate
END
ELSE
BEGIN
INSERT INTO @Tmp(SubmitMonth, SubmitYear, Amt)
VALUES(MONTH(@fromDate), YEAR(@fromDate), 0)
END
SET @fromDate = DATEADD(MM,1,@fromDate)
--SET @fromDate = DATEADD(MM,1,@fromDate)
END
SELECT * FROM @Tmp
here is grid result:
Here I am going to explain one interesting thing about SQL WHILE loop, for date calculation with counter based either date wise or month wise.
Why to use?
One day one of my friend was doing some programming regarding the Amount used in daily spent voucher system in his application, but he was facing one issue. His record was in order i.e some voucher was submitted on day like 01/02/2010 and another was submitted on 05/02/2013. But in his graph he was supposed to show all the month (with day wise) submitted voucher.
I explained him how very easily he can do it..Here is an example:
DECLARE @fromDate as DateTime
DECLARE @toDate as DateTime
SET @fromDate = '02/01/2013'
SET @toDate = '06/16/2013'
DECLARE @Tmp TABLE(SubmitDate DateTime, Amt numeric(18,0))
WHILE (@fromDate<=@toDate)
BEGIN
IF EXISTS(SELECT Amount,ExpenseDate FROM dbo.Expenses WHERE ExpenseDate = @fromDate)
BEGIN
INSERT INTO @Tmp(SubmitDate, Amt)
SELECT ExpenseDate, Amount FROM dbo.Expenses WHERE ExpenseDate = @fromDate
--PRINT @fromDate
END
ELSE
BEGIN
INSERT INTO @Tmp(SubmitDate, Amt)
VALUES(@fromDate, 0)
END
SET @fromDate = DATEADD(dd,1,@fromDate)
END
SELECT * FROM @Tmp
here is grid result:
| 02/01/13 | 0 |
| 02/02/13 | 0 |
| 02/03/13 | 0 |
| 02/04/13 | 0 |
| 02/05/13 | 0 |
| 02/06/13 | 0 |
| 02/07/13 | 70 |
| 02/07/13 | 420 |
| 02/07/13 | 30 |
| 02/07/13 | 100 |
| 02/07/13 | 220 |
| 02/07/13 | 2953 |
| 02/07/13 | 140 |
| 02/07/13 | 40 |
| 02/07/13 | 90 |
| 02/07/13 | 35 |
| 02/07/13 | 95 |
| 02/07/13 | 60 |
The same way we can do by month also, here how it is:
DECLARE @fromDate as DateTime
DECLARE @toDate as DateTime
SET @fromDate = '02/01/2013'
SET @toDate = '06/16/2013'
DECLARE @Tmp TABLE(SubmitMonth int, SubmitYear int , Amt numeric(18,0))
WHILE (@fromDate<=@toDate)
BEGIN
IF EXISTS(SELECT Amount,ExpenseDate FROM dbo.Expenses
WHERE MONTH(ExpenseDate) = MONTH(@fromDate) AND YEAR(ExpenseDate) = YEAR(@fromDate) )
BEGIN
INSERT INTO @Tmp(SubmitMonth, SubmitYear, Amt)
SELECT MONTH(ExpenseDate), YEAR(ExpenseDate), sum(Amount) FROM dbo.Expenses
WHERE MONTH(ExpenseDate) = MONTH(@fromDate) AND YEAR(ExpenseDate) = YEAR(@fromDate)
GROUP BY MONTH(ExpenseDate), YEAR(ExpenseDate)
--PRINT @fromDate
END
ELSE
BEGIN
INSERT INTO @Tmp(SubmitMonth, SubmitYear, Amt)
VALUES(MONTH(@fromDate), YEAR(@fromDate), 0)
END
SET @fromDate = DATEADD(MM,1,@fromDate)
--SET @fromDate = DATEADD(MM,1,@fromDate)
END
SELECT * FROM @Tmp
here is grid result:
| SubmitMonth | SubmitYear | Amt |
| 2 | 2013 | 5750 |
| 3 | 2013 | 12705 |
| 4 | 2013 | 11336 |
| 5 | 2013 | 7222 |
| 6 | 2013 | 8725 |
I hope you enjoy the topic, please feel free to let me know in case you need any clarifications.
Cheers,
Comments