Date Query

,
1

I want to take sales from start if month till yesterdays date automatic ,
now suppose if yesterday is Sunday then sales should come from 1 august till 02 August

select  																				
round(sum(case when Date=FORMAT(GetDate()-1, 'yyyy-MM-dd')  then Amount else 0 end),0) as Todaystotal

This query is perfect now I want that if holiday comes then it should take sales before holiday from start of month

Suppose today is 10 August , my query will fetch sales from 1 to 9 august (let 9 August be Sunday ) then sales should take 1 to 8 August

2

hi

i tried to do this

hope it helps
:slight_smile: :slight_smile:

drop create data ... 
/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
 -- create tables 
 -- INT VARCHAR(100)  DATE DATETIME DECIMAL(10,2) 

 create table #tablename
 (
 sales int,
 sales_date datetime
 )
 go 

 /***************************************************************************/

 -- insert data into tables

 insert into #tablename select 10,'2019-08-01'
 insert into #tablename select 8,'2019-08-02'
 insert into #tablename select 5,'2019-08-03'
 insert into #tablename select 10,'2019-08-04'
 insert into #tablename select 20,'2019-08-05'
 insert into #tablename select 5,'2019-08-06'
 insert into #tablename select 7,'2019-08-07'
 insert into #tablename select 7,'2019-08-08'
 insert into #tablename select 12,'2019-08-09'
 insert into #tablename select 21,'2019-08-10'
 insert into #tablename select 4,'2019-08-11'
 insert into #tablename select 34,'2019-08-12'
 insert into #tablename select 23,'2019-08-13'
 insert into #tablename select 10,'2019-08-14'
 insert into #tablename select 17,'2019-08-15'
 go 


/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/
SQL .... 
DECLARE @today DATETIME = '2019-08-11' 

SELECT Sum(sales) 
FROM   #tablename 
WHERE  sales_date <= CASE 
                       WHEN Datepart(dw, Dateadd(dd, -1, @today)) = 7 THEN 
                       Dateadd(dd, -2, @today) 
                       ELSE Dateadd(dd, -1, @today) 
                     END

image
image.png253×546 3.92 KB

3

Please define your holiday . Sunday meaning weekend or Sundsy was a holiday where you live?

4

Sunday will be holiday for us. I have Date table also where all Sunday are marked as Holiday,
My current query is this -

select
round(sum(case when Date=FORMAT(GetDate()-1, 'yyyy-MM-dd') then Amount else 0 end),0) as Todaystotal

So This query is counting Sunday also then I am getting zero output but in actual if Sunday comes then instead of Sunday it should count till Saturday

5

sum ( case when sunday 0 else 1 ) end

hope this helps :slight_smile: :slight_smile:

6

No this query is giving me result 1 august to 13 August
select round(sum(case when Date=FORMAT(GetDate()-1, 'yyyy-MM-dd') then Amount else 0 end),0) as Todaystotal
How your query I will use

7

suppose if yesterday is Sunday,
Suppose today is 10 August
let 9 August be Sunday

All of these statements are totally confusing. please restate your question with real actual dates not suppositions.

8

hi

i tried ... hope this is what you are looking for :slight_smile: :slight_smile:

drop create data ... 
/* ------------------------------------------------------------   */
-- select database tempdb 

USE TEMPDB
GO 
/* ------------------------------------------------------------   */


/******************************************************************/
-- drop all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' Drop table ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/******************************************************************/


/* ------------------------------------------------------------   */
-- create tables tempdb 

/*
-- int varchar(100) date datetime decimal(10,2) 
create table #data 
(

)
go
*/

create table #tablename
 (
 sales int,
 sales_date datetime
 )
 go 

/* ------------------------------------------------------------   */


/*****************************************************************/
-- insert data tables tempdb 

-- insert into #data select 1 
insert into #tablename select 10,'2019-08-01'
 insert into #tablename select 8,'2019-08-02'
 insert into #tablename select 5,'2019-08-03'
 insert into #tablename select 10,'2019-08-04'
 insert into #tablename select 20,'2019-08-05'
 insert into #tablename select 5,'2019-08-06'
 insert into #tablename select 7,'2019-08-07'
 insert into #tablename select 7,'2019-08-08'
 insert into #tablename select 12,'2019-08-09'
 insert into #tablename select 21,'2019-08-10'
 insert into #tablename select 4,'2019-08-11'
 insert into #tablename select 34,'2019-08-12'
 insert into #tablename select 23,'2019-08-13'
 insert into #tablename select 10,'2019-08-14'
 insert into #tablename select 17,'2019-08-15'
 go 
go
/*****************************************************************/


/* ------------------------------------------------------------   */
-- select all tables tempdb 

DECLARE @sql NVARCHAR(max)='';SELECT @sql += ' SELECT * FROM  ' +  QUOTENAME(TABLE_NAME) + '; ' FROM   INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'tempdb'; Exec sp_executesql @sql

/* ------------------------------------------------------------   */
SQL ..... 
/******************************************************************/
-- Query Solution 

SELECT Sum(CASE 
             WHEN Datename(ww, sales_date) = 7 THEN 0 
             ELSE 1 
           END) 
FROM   #tablename 

/******************************************************************/

image
image.png364×593 6.83 KB