Basic Query help - 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

Basic Query help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 05:10:21

HI guys and girls

I need some help with two particular queries

The First query which i am trying to work out is how many accounts there are on total for the rolling year by rm code

the Second query is that i need to know how many accounts over the value of £10 have traded on a rolling year , and i need to group that by rm code also

These are the two tables and columns with the tables which you need to use

FROM [FDMS].[dbo].[Fact_Financial_History]
[hst_sales_amt]
[hst_merchnum]
[hst_date_processed] = is layed out as year, month, day

FROM [FDMS].[dbo].[Dim_Outlet]
[FDMSAccountNo_First9] = Same as [hst_merchnum]
[RM_Code]
[ParentID]

I have built one query which is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] f inner join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @date
group by rm_code

But i am not sure if its even correct

any help would be appreciated

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-22 : 07:11:34

try the following code

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[hst_merchnum] = [FDMS].[dbo].[FDMSAccountNo_First9]
Where hst_date_processed > @date
group by rm_code

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date
AND SUM([FDMS].[dbo].[Dim_Outlet].[hst_sales_amt] >10
group by rm_code

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 07:39:15

Hi

AM i suppose to run the two queries together or separately ?

if i run them separately i get the following msg

query1 - Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "FDMS.dbo.hst_merchnum" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "FDMS.dbo.FDMSAccountNo_First9" could not be bound.

and should query two be

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())
SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date
AND SUM([FDMS].[dbo].[Dim_Outlet].[hst_sales_amt] >10
group by rm_code

Or how you listed above ?
If its how you listed above i get the following error msg -Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@date".

vtxvtx
Starting Member

18 Posts

Posted - 2012-08-22 : 07:49:07

Sorry as two seperate queries:

Query 1:

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date
group by rm_code

Query 2:

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date
AND SUM([FDMS].[dbo].[Dim_Outlet].[hst_sales_amt] >10
group by rm_code

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 07:52:37

for query 2 : im getting the following msg

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '>'.

any ideas ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-22 : 07:59:29

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date

group by rm_code
HAVING SUM([FDMS].[dbo].[Dim_Outlet].[hst_sales_amt]) >10

N 56°04'39.26"
E 12°55'05.63"

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 08:02:01

Hi swepeso

i assume that the query actually needs to be

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date

group by rm_code
HAVING SUM([FDMS].[dbo].[Dim_Outlet].[hst_sales_amt]) >10

if thats the case, i am still reciving an msg statin 'Msg 207, Level 16, State 1, Line 10
Invalid column name 'hst_sales_amt'.'

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 08:06:59

PLEASE INGORE PREVIOUS POST
THE QUERY SHOULD BE

Declare @date datetime
set @Date = dateadd(MM,-12,getdate())

SELECT count (distinct ParentID), RM_Code
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet
on [FDMS].[dbo].[Fact_Financial_History].[hst_merchnum] = [FDMS].[dbo].[Dim_Outlet].[FDMSAccountNo_First9]
Where hst_date_processed > @date

group by rm_code
HAVING SUM([FDMS].[dbo].[Fact_Financial_History].[hst_sales_amt]) >10

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-22 : 08:36:27

HI swepeso

Unfort query 2 is still incorrect

i belive i need to group parentid, aswell.

because at the moment i have a feeling that sum on hst_sales_am is looking at every month,

i need it to group all the transactiosn together for the last 12months, via the parent id

If one of the parentId is under the value of £10, then give me a unique count of parent id per rm account

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources