how to write 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

how to write query?

Author

Topic

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-06-19 : 12:32:06

Hi all,

I have one query

i have 3 tables

1.statemaster
2.countrymaster
3.tbl_sales

in statemaster columns are stateid,statename,countryid

in countrymaster columns are countryid,countryname

in tbl_sales columns are date,stateid,sales.

but the output result is :

count(sales),countryname where date is between getdate()-7 and getdate()-1 -----(means past 7 days)
data

how to write query for this.

if anybody knows please help me

Regards
Rama

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-19 : 13:06:31

[code]SELECT c.countryname,COUNT(sales)
FROM countrymaster c
JOIN statemaster s
ON s.countryid=c.countryid
JOIN tbl_sales ts
ON ts.stateid=s.stateid
WHERE ts.date > = DATEADD(dd,DATEDIFF(dd,0,getdate())-7,0)
AND ts.date <=DATEADD(dd,DATEDIFF(dd,0,getdate())-1,0)
GROUP BY c.countryname
[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 13:13:26

SELECT
count(sales) -- Do you mean sum(sales)?
,cn.CountryName
FROM
table_Sales s
INNER JOIN
statemaster sm
ON
s.stateid = sm.stateid
INNER JOIN
countrymaster cm
ON
sm.countryid = cm.countryid

WHERE s.Date >= DATEADD(day,datediff(day,8,getdate()),0)
and s.Date < DATEADD(day,datediff(day,0,getdate()),0)
GROUP BY cn.CountryName

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources