asked on

Help to build a query to select data from the previous n days

Hi to all.

I'm new with SQL. I need to build a query that reports data from the previous two (2) or five (5) or n days.

I think is better explain the scenario with an example:

Database Name: db_rec_ss
View Name: is_vrec
Tables associated in views: is_reg, is_bill, is_items

The view (is_vrec) groups data in this format (simplified as I can):

id_item | desc_item | totalsell_ammount | date
01 | Blue book | 21.00 | 10/2/2008
02 | Red book | 34.00 | 10/2/2008
01 | Blue book | 10.00 | 10/3/2008
02 | Red book | 32.00 | 10/3/2008
01 | Blue book | 25.00 | 10/4/2008
02 | Red book | 15.00 | 10/4/2008
01 | Blue book | 12.00 | 10/5/2008
02 | Red book | 10.00 | 10/5/2008

Ok.. you got it..!

I need to build a SQL query that retrieves the data from the previous 2 days, in this format: (For the example today is 10/5/2008)

id_item | desc_item | 10/4/2008 | 10/3/2008
01 | Blue book | 25.00 | 10.00
02 | Red book | 15.00 | 32.00

An important tip is that non-work days, doesn't generate data. I think this simplified the query, because I only need that the query retrieves the last n days. Doesn't important to calculate if is work or non-work day.

I don't want to import data on an Excel worksheet.

With the information supplied, can U help me?

Thanks a lot in advance for your help...!

DaProwler

If I understand you correctly, you are trying to select data from a view in a SQL database in which you want to get data that is only within the past few days. If so, you can do this with a select statement such as this:

SELECT * FROM [is_vrec] WHERE [date] BETWEEN DATEADD(d,-5,GETDATE()) AND GETDATE()

Open in new window

Sharath S

Do you have fixed number of columns or is it a dynamic number?

Stanyslaw

Try something like this:

SELECT id_item, desc_item,
Sum( CASE WHEN date BETWEEEN GetDate()-1 AND GetDate() THEN totalsell_ammount ELSE 0 END ) as "date1",
Sum( CASE WHEN date BETWEEEN GetDate()-2 AND GetDate()-1 THEN totalsell_ammount ELSE 0 END ) as "date2"
WHERE date > GetDate()-2

Stanyslaw

FROM missed in prev. answer:
Try something like this:

SELECT id_item, desc_item,
Sum( CASE WHEN date BETWEEEN GetDate()-1 AND GetDate() THEN totalsell_ammount ELSE 0 END ) as "date1",
Sum( CASE WHEN date BETWEEEN GetDate()-2 AND GetDate()-1 THEN totalsell_ammount ELSE 0 END ) as "date2"
FROM is_vrec
WHERE date > GetDate()-2

Open in new window

SOLUTION

Stanyslaw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

goltrek

ASKER

Hi Stanyslaw, thank you very much.

Your last message was applied in my case:

Only two questions:
1.- I need to reflect the corresponding date for data in the query. What sentence must I add to the query to show the date for the day reported?

2.- What about adding three or more days previous? Must I add the following code to retrieve data for three days?

Sum(CASE WHEN hasta BETWEEN DATEADD(d,-3,GetDate()) AND DATEADD(d,-1,GetDate()) THEN total ELSE 0 END) as "date3"

Open in new window

goltrek

ASKER

Thanks Sharath_123,

Yes, the numbers of columns is fixed.

Regards.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ernariash

Using dynamic query will solve your case...@N = 5 , 2 or any number...

DECLARE @N AS INT
DECLARE @STARTDATE AS datetime
DECLARE @DYNAMICCASE AS NVARCHAR(4000)
 
SET  @STARTDATE = Getdate() 
SET @DYNAMICCASE =  ' '
 
SET @N = 5
 
WHILE @N > 0
BEGIN
SET @DYNAMICCASE =    @DYNAMICCASE +', '
SET @DYNAMICCASE = @DYNAMICCASE+'SUM(CASE WHEN Convert(nvarchar(8),DATE,112) =''' + Convert(nvarchar(8),dateadd(day,-@N,@STARTDATE),112)+ ''' THEN totalsell_ammount ELSE 0 END) AS '''+Convert(nvarchar(8),dateadd(day,-@N,@STARTDATE),112) +''''
SET @N = @N-1
END
 
SET @DYNAMICCASE = N'select id_item, desc_item '+ @DYNAMICCASE + N'from  db_rec_ss.dbo.is_vrec group by id_item, desc_item'
 
execute sp_executesql @DYNAMICCASE, N'@N AS INT, @STARTDATE AS datetime', @N, @STARTDATE

Open in new window

Ernariash

Ok, in case you only need that the query retrieves the last n days with data from your table here is the dynamic query to do the job.
Please notice if you change my previous @DYNAMICCASE AS NVARCHAR(MAX) it will allow you to have a very big N number of days. Thanks...


DECLARE @N AS INT
DECLARE @STARTDATE AS datetime
DECLARE @DYNAMICCASE AS NVARCHAR(MAX)
SET  @STARTDATE = Getdate() 
SET @DYNAMICCASE =  ' '
 
SET @N = 20
DECLARE @is_vrec TABLE (
	ID INT IDENTITY(1,1) NOT NULL,
	DATE DATETIME
)
SET ROWCOUNT @N
INSERT INTO @is_vrec (DATE)
select DATE   from  is_vrec
where date < @STARTDATE
order by date desc
--in case @N is less than your ROWCOUNT
SELECT  @N = MAX(ID) FROM @is_vrec
 
WHILE @N > 0
BEGIN
 
SET @DYNAMICCASE =@DYNAMICCASE+ ', ' +  (SELECT 'SUM(CASE WHEN Convert(nvarchar(8),DATE,112) =''' + Convert(nvarchar(8),DATE,112)+ ''' THEN totalsell_ammount ELSE 0 END) AS '''+Convert(nvarchar(8),DATE,112) +''''
FROM @is_vrec
WHERE ID = @N)
 
SET @N = @N-1
END
 
Set @DYNAMICCASE = N'select id_item, desc_item '+ @DYNAMICCASE + N'from is_vrec group by id_item, desc_item'
eXecute sp_executesql @DYNAMICCASE, N'@N AS INT, @STARTDATE AS datetime', @N, @STARTDATE

Open in new window