SQl query fro automatic data

Transact-SQL
1

Team I need a help

I sql server I have to daily extract the report from my table Grand_total
I have to get 3 column like this
imageOnly On 27th november 2017 1-22 novmber 2017 1 October to 27 october 2017
500 1000 9999

In first column I want data as on yesterday,2 column I want data as on 1st day of current month till yesterday and
3 column i want data from 1st day of previous month till previous day of previous month(Example suppose today is 28th November so 01-October to 27 October 2017)

I doesn't want to put date everyday and i want to make it schedule automatically

My orderdate is in Varchar(20)...I can change it in Date datatype also but I need in Varchar

How shloud I write the query for that
Note I have written query to split column into 3 part,I need again and again i don't have to pass date.
Any way to write procedure so that it automatically run and i get the data

As data is coming from single table so i used Case if to split in 3 column
SELECT xy as COMPANY,Channel as Channel,regionname ,
**sum(CASE WHEN op.orderdate = '2017-11-27' THEN inna ELSE '0' END) AS SalesToday, **
**sum(CASE WHEN op.orderdate between '2017-11-01' and '2017-11-27' THEN inna ELSE '0' END) AS till_1_to_yesterdaysales, **
**sum(CASE WHEN op.orderdate between '2017-10-01' and '2017-10-27' THEN inna ELSE '0' END) AS till_1_to_yesterdaysales_previousmnth **
from GRAND_TOTAL OP

CHANNEL='10-GENERAL TRADE ' group by xy, CHANNEL,regionname;

2

Absolutely store it as DATE datatype. You will never, reliably, get data manipulation on a VARCHAR.

When you display it you can convert the DATE datatype to text or, much better, use the application end to do the conversion/display (because that way the Application end ALSO has the date as a DATE datatype, and if you want to do Sort/Filter at the application end you have a real date, not a human-formatted text one). So "format as close to the display point as possible".

Don't use BETWEEN for date ranges - in particular if the datatype is DATETIME. Use MyCol >= @START AND MyCol < @END_PLUS_ONE_DAY

You will need to decide (and test) what the date range is for till_1_to_yesterdaysales_previousmnth on the 31-March - is it 28-Feb still? or something else? Clearly if you compare 01-Feb to 28-Feb with 01-Mar to 31-Mar the ratio is off by quite a bit.

This perhaps?

DECLARE	@Today	DATE
	, @ThisMonthStart	DATE
	, @LastStartMonth	DATE
	, @LastMonthEnd	DATE

SELECT	@Today = CONVERT(DATE, GetDate())
	, @ThisMonthStart = DATEADD(Month, DATEDIFF(Month, 0, @Today), 0)
	, @LastStartMonth = DATEADD(Month, DATEDIFF(Month, 0, @Today)-1, 0)
	, @LastMonthEnd = DATEADD(Month, -1, @Today)	-- For 31-Mar that will be 28-Feb (or 29-Feb)

-- Test the values:
SELECT	[@Today] = @Today
	, [@ThisMonthStart] = @ThisMonthStart
	, [@LastStartMonth] = @LastStartMonth
	, [@LastMonthEnd] = @LastMonthEnd

SELECT	xy as COMPANY,
	Channel as Channel,
	regionname ,
	sum(CASE WHEN op.orderdate = @Today THEN inna ELSE 0 END) AS SalesToday, 
	sum(CASE WHEN op.orderdate >= @ThisMonthStart AND op.orderdate < @Today THEN inna ELSE 0 END) AS till_1_to_yesterdaysales, 
	sum(CASE WHEN op.orderdate >= @LastStartMonth AND op.orderdate < @LastMonthEnd THEN inna ELSE 0 END) AS till_1_to_yesterdaysales_previousmnth 
from GRAND_TOTAL 
WHERE	OPCHANNEL='10-GENERAL TRADE ' 
group by xy, 
	CHANNEL,
	regionname

NOTE: WHEN op.orderdate = @Today will not work if orderdate is DATETIME datype, rather than DATE datatype

1 Like
3

kristen....for 3 column i did like this

this is giving me good results as the date i want

select [1st_day_of_prev_mth] = dateadd(month, datediff(month, 0, getdate()) - 1, 0),
[last_day_of_prev_mth]= dateadd(month, datediff(month, 0, getdate()), -1),
[1st_day_of_curr_mth] = dateadd(month, datediff(month, 0, getdate()), 0),
[yesterday] = dateadd(day, datediff(day, 0, getdate()), -1)

result is -

image1st_day_of_prev_mth last_day_of_prev_mth 1st_day_of_curr_mth yesterday
2017-10-01 00:00:00.000 2017-10-31 00:00:00.000 2017-11-01 00:00:00.000 2017-11-27 00:00:00.000

Now the procedure which you wrote i have to put all this and scheduled it ???

4

You can schedule it with SQL Agent.

However, what are you going to do with the results? Do you want them saved to a file, or something else?

We build this type of thing into our Application - any time a user wants to see the Report they just choose that "report" from the menu, and it runs the SQL and displays the result. We don't run it on a schedule, but "on demand"

5

Yes,,I need to fetch the report ...as soon as user select the Date ..Report should get downloaded for it..How you can you help me in that as i used pentaho report designer but I need java to call prpt and i don't know how to do that

6

Sorry, I have no knowledge about those tools

7

So what tool you are using ..You said that you will be giving a Menu and data will get downloaded

8

I need this part if we can suggest for this

9

That is a whole Application that we have built, so no help to you, sorry.

I don't know very much about Report Writers, so I cannot suggest what may be most suitable for you.

The "SQL Server Reporting Services" (SSRS) is part of SQL Server, so that might be something that you already have installed? and would do what you want, but you need to consider if that is best-fit for your requirements.

10

We are into a sales company ,In retail sales...We are searching for a report product only...if you can ahead with your application part

11

I suggest you look at SSRS first then, particularly if it is already installed in your company (its "free" with SQL Server)

12

Actually I need some button PROVIDED TO the user and they will download there report from a locahost server..By entering username and passwd ..they will click on reports and report will get downloaded..we have region wise reports..all those report used in retail industry.

Can you help me out

13

SSRS will do that.

When you say "downloaded" - does the user just "view" the report, on their screen, or do they want e.g. a PDF file instead?

SSRS is basically a "Report display engine". You, the developer, Design the report (both layout and also the SQL to select the data, the User can fill in a Form with the report parameters - e.g. Start Date / End Date - if the report allows that.

Once the report is displayed on screen the user can View it, Print it, Export to e.g. PDF

or you could just have a PDF file sent to the user every night.

But personally I dislike the "Email a PDF file" for reports. Better that the user logs in and then has a Menu of Reports, then choose one, optionally they put in the Parameters, and then they View the report.

14

This looks like a good Demo of what SSRS can do:

15

Can u send email id...I will send my all required..in hope to work together...I need it ....
Actually In pentaho also i developed the report that is .prpt file...But users are not developer...they are normal user they want the data as per there business requirement and we are developer,we will fetch the data for them..

For example...They go a link localhost:8090/report
It will ask for username and password......the department will download the report in excel and PDF format and even can scheduled also for Night or early in the morning.

I have deprtment also...1 department can't see the report of other and all

16

You can ask all your questions here, other people will answer too. People in USA are more likely to answer later :slight_smile: I'm in UK

17

I have send you my requirement details

18

SSRS is very similar. Go to "local host web page" and then you can run a report (which has already been Designed by a Developer)

I don't see any need to schedule the reports. They do not need to be pre-prepared.

If you want to EMail a PDF file - THEN you can do that using scheduled task in the middle of the night!

19

You said You developed your application..what about that..

20

I will be devloping 30 reports for excel for user........and in 10 user id's i have to assign reports to different diffrent department.can i do that,,,,,,,can i create 15 diffrent user id and passwd