7 day sales analysis query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

7 day sales analysis query

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-31 : 12:23:39

Anyone can give sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun.Thus start day is last sun.

now the time is 22:00 & group by shop plus underline +24 hour format

Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
-------------------------------------------------------------------
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-31 : 22:59:34

You have to at least show us how your table looks like.

Please do post your table DDL, sample data in DML format

KH
[spoiler]Time is always against us[/spoiler]

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-01 : 08:39:45

this is sales table sturcture and createdatetime format is 20140831 23:38:33:xxx

shop_id,createdatetime,sku,qty,amount

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-01 : 13:53:02

What we need id:

1. CREATE TABLE statements for each table
2. INSERT INTO statement to populate each table with test data
3. Your query so far.
4. The results of running your query on the test tables from steps 1 and 2
5. The desired results of a complete, correct query that meets your requirements.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 03:12:42

looking at your other thread, i guess this is what you want


select	shop_id,
	hr	= convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
		+ '-'
		+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
	[SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT]	
from
(
	select	shop_id, 
		weekdy	= upper(left(datename(weekday, createdatetime), 3),)
		hr	= datepart(hour, createdatetime),
		amount
	from	sales_table
        where   ....  -- place any where condition here
) d
pivot
(
	sum(amount)
	for weekdy in ([SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT])
) p

KH
[spoiler]Time is always against us[/spoiler]

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 07:03:49

I am compile this query have prompt error message " incorrect syntax error " , where is the wrong coding ?

select xsodetail.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT]
from
(
select xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3),)
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime = Convert(varchar, GETDATE(),112)
) d
pivot
(
sum(xsodetail.actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU],[FRI], [SAT])
) p

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 09:39:50

[code]
select
xsodetail.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
xsodetail.shopcode,
weekdy = upper(left(datename(weekday, xsodetail.ccreatedatetime), 3))
hr = datepart(hour, xsodetail.createdatetime),
xsodetail.actualsalesamt
from xsodetail
where xsodetail.createdatetime >= dateaddday(day, datediff(day, 0, getdate()), 0)
and xsodetail.createdatetime < dateaddday(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(xsodetail.actualsalesamt)
for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 21:51:21

our sql server 2008. I am copy your query run our server then prompt error in below :
incorrect syntax near 'hr'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-02 : 22:15:45

sorry typo error

select 
	xsodetail.shopcode,
	hr 	= convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
		+ '-'
		+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
	[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
	(
		select 
			xsodetail.shopcode,
			weekdy 	= upper(left(datename(weekday, xsodetail.ccreatedatetime), 3)),
			hr 	= datepart(hour, xsodetail.createdatetime),
			xsodetail.actualsalesamt
		from 	xsodetail
		where 	xsodetail.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
		and	xsodetail.createdatetime <  dateadd(day, datediff(day, 0, getdate()), 1)
	) d
	pivot
	(
		sum(xsodetail.actualsalesamt)
		for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
	) p

KH
[spoiler]Time is always against us[/spoiler]

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-02 : 23:55:25

error . The column prefix 'xsodetail' does not match with a table name or alias name used in a query, error code 107. Please look at it and help to solve. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-03 : 04:30:58

PIVOT operator does not like alias

select 
	shopcode,
	hr 	= convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
		+ '-'
		+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
	[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
	(
		select 
			xsodetail.shopcode,
			weekdy 	= upper(left(datename(weekday, xsodetail.ccreatedatetime), 3)),
			hr 	= datepart(hour, xsodetail.createdatetime),
			xsodetail.actualsalesamt
		from 	xsodetail
		where 	xsodetail.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
		and	xsodetail.createdatetime <  dateadd(day, datediff(day, 0, getdate()), 1)
	) d
	pivot
	(
		sum(actualsalesamt)
		for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
	) p

KH
[spoiler]Time is always against us[/spoiler]

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-03 : 08:29:10

I don't understanding what is your mean "pivot opertor does not like alias", you mean what is action to do ? it is delete the pivot this command and run again in sql studio ?

usafelix
Posting Yak Master

165 Posts

Posted - 2014-09-03 : 08:36:05

I have update this query again and run a time but still prompt The column prefix 'xsodetail' does not match with a table name or alias name used in a query, error code 107
select
a.shopcode,
hr = convert(varchar(5), dateadd(hour, hr, '00:00'), 108)
+ '-'
+ convert(varchar(5), dateadd(hour, hr + 1, '00:00'), 108),
[SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT]
from
(
select
a.shopcode,
weekdy = upper(left(datename(weekday, a.createdatetime), 3)),
hr = datepart(hour, a.createdatetime),
a.actualsalesamt
from xsodetail a
where a.createdatetime >= dateadd(day, datediff(day, 0, getdate()), 0)
and a.createdatetime < dateadd(day, datediff(day, 0, getdate()), 1)
) d
pivot
(
sum(a.actualsalesamt) for weekdy in ([SUN], [MON], [TUE], [WED], [THU], [FRI], [SAT])
) p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources