Get Day Wise Sale of the Week Using SQL Query or Store procedure, get Today Date, get Last Friday from SQL, Create Temporary table in SQL, Drop Temporary table From SQL
Create PROCEDURE [dbo].[GetDaysOfTheWeek]
AS
BEGIN
--Creating TEMP Table
Create Table #Days
(
TodayDate Varchar(12),
Sales decimal (18,2)
)
DECLARE @Today DATE;
DECLARE @LastFriday DATE;
DECLARE @sale decimal (18,2);
DECLARE @CurrentDate AS DATE;
/*Get Last Friday And Store into @LastFriday Variable */
SET @LastFriday=(SELECT
CASE WHEN (DATEPART(DW,GETDATE())< 7)
THEN
CONVERT(DATE,(DATEADD(dd,-(DATEPART(DW,GETDATE())+1),GETDATE())))
ELSE
CONVERT(DATE,(DATEADD(d,- 1,GETDATE())))
END AS [LastFriday])
/*Get Today And Store into @LastFriday Variable */
SET @Today=(SELECT CONVERT(DATE,GETDATE()) AS Today)
/*Set Starting Date And Store into @CurrentDate Variable */
SET @CurrentDate = @LastFriday
WHILE (@CurrentDate <= @Today)
BEGIN
SET @sale =(SELECT SUM(TotalSales) AS Sales FROM YOUR-SALES-TABLE WHERE SaleDate=@CurrentDate GROUP BY SaleDate )--SUM of the current day sales
/*Insert data to the temp table */
INSERT INTO #Days VALUES ((SELECT DATENAME(weekday,@CurrentDate)),isnull(@sale,'0.0'))-- Inserting into TEMP Table
/*Increments into by 1 in to @CurrentDate Variable */
SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101);
END
SELECT * FROM #Days --return Date of the Days
DROP TABLE #Days -- Drop temp table
END
Comments