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...!
goltrekAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Here you go, might be overkill, but completely flexible - just nominate the @date, and really could be a stored procedure with @date as a parameter - will dynamically generate all the columns from that date...

if doing a stored procedure, just uncomment the lines and remove the declare and set of @date as indicated... then all you do is exec sp_pivot_totalsell '01 Sep 2008'


--create procedure sp_pivot_totalsell(@date datetime)      -- remove comment to make stored procedure
--as                                                       -- remove comment to make stored procedure
declare @date datetime                                     -- comment out if stored procedure
set @date = '01 Sep 2008'                                  -- comment out if stored procedure
 
declare @sql varchar(max)
declare @hdrdate varchar(max)
declare @pvtdate varchar(max)
 
select @hdrdate = isnull(@hdrdate,'') + ',['+convert(varchar,date,106)+'] AS '''+convert(varchar,date,106)+''' ' from is_vrec where date >= @date group by date
print @hdrdate
select @pvtdate = isnull(@pvtdate,'') + ',['+convert(varchar,date,106)+']' from is_vrec where date >= @date group by date
print @pvtdate
 
set @sql = '
SELECT id_item, desc_item '+@hdrdate+'
FROM 
( SELECT id_item, desc_item, convert(datetime,convert(varchar,date,106)) as date, totalsell_amount from is_vrec) as p
PIVOT
( SUM (totalsell_amount) FOR date IN ( '+right(@pvtdate,len(@pvtdate)-1)+' ) ) AS pvt
ORDER BY id_item, desc_item'
exec (@sql)
 
GO

Open in new window

0
 
DaProwlerCommented:
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

0
 
SharathData EngineerCommented:
Do you have fixed number of columns or is it a dynamic number?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
StanyslawCommented:
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
0
 
StanyslawCommented:
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

0
 
StanyslawConnect With a Mentor Commented:
Ops, DATEADD should be used instaed of date-1 and date-2

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

Open in new window

0
 
goltrekAuthor Commented:
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

0
 
goltrekAuthor Commented:
Thanks Sharath_123,

Yes, the numbers of columns is fixed.

Regards.
0
 
ErnariashCommented:
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

0
 
ErnariashCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.