Skip to main content



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

Popular posts from this blog

Get Current Month Total Sales Using SQL Query or Store procedure, get Today Date from SQL , Get first Day of the Month
Sales Report (SQL Procedure, Query) by Hourly, using while loop in SQL, Get Today Date from SQL, Temporary Table in SQL   CREATE PROCEDURE [GetSaleByHours] (   @Date datetime ) AS BEGIN Create Table #Day  -- Create primary Table to store hourly based sales (    hour bigint ) Declare @Count int = 0; WHILE @Count <= 23    -- Adding values to temp table using while loop  BEGIN       INSERT INTO #Day VALUES (@Count)     SET @Count = @Count + 1; END SELECT  @Date AS DAYDATE, dh.hour AS DAYHOUR ,ISNULL(SUM(ST.sales),0) AS Sales FROM #Day dh LEFT OUTER JOIN SalesTable ST  ON dh.hour = DATEPART(hh,ST.TimeStamp) GROUP BY dh.hour DROP TABLE #Day   -- Droping temp table END