SQL problem is epic proportions

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL problem is epic proportions

Author

Topic

Knives85
Starting Member

6 Posts

Posted - 2010-02-03 : 03:29:39

hi guys

I need some help with a SQL problem of epic proportions. Basically I have two sets of completely different data. EPoS transaction data and footfall data.

The idea is to combine this data to all users to view the total amount of people in the shop, to what has been sold in the shop.

The problem I’m having is that the data is so different I cant find a common identifier. I’m abit new to working in SQL Server environment (used to Access Sql). I think i will have to clean and change the data so there both in a similar format; however I’m unsure exactly how to do this in Enterprise Manager (or if in can even be done).

My first try at this query has not gone well. I haven’t cleaned the data but tried to format it together however I believe the record ID from the footfall data is just duplicating loads of records.

If anyone can help me that would be great. I know what I'm asking might not be explained in the best way but please comment and I'll answer any questions you guys ask

Thanks you for you help

Thanks Boys(and Girls)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 03:44:20

can you give some sample of people as well as sale data and then explain what you want out of them?

Knives85
Starting Member

6 Posts

Posted - 2010-02-03 : 03:56:04

Hi Thanks for replying

Footfall Data.

The basic structure of the data is at the highest level you have site, within each site there is multiple counters that count the people who pass through it. Each counter stores data on a Half-hour basis so the data is structed to half hour intervals with the total count for each counter recieved every 30 minutes.

EPoS

The data structure is standard to most EPoS data. This been sales oritentated. the data is structure by Site as well (however the site codes are different), basically i created a function that groups the data here like it is group in the footfall data (30 minute bands).

I also created a link in the site table of the footfall data that having the siteID for footfall with the siteID for EPOS.

The intended output for this query (or queries depending) on how hard it is is for the information to have the site with the half hour people counter data and next to that there would be a total number of passes issued by site in half hour bands.

Is that better?

Thanks Boys(and Girls)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 04:20:46

quote:
Originally posted by Knives85

Hi Thanks for replying

Footfall Data.

The basic structure of the data is at the highest level you have site, within each site there is multiple counters that count the people who pass through it. Each counter stores data on a Half-hour basis so the data is structed to half hour intervals with the total count for each counter recieved every 30 minutes.

EPoS

The data structure is standard to most EPoS data. This been sales oritentated. the data is structure by Site as well (however the site codes are different), basically i created a function that groups the data here like it is group in the footfall data (30 minute bands).

I also created a link in the site table of the footfall data that having the siteID for footfall with the siteID for EPOS.

The intended output for this query (or queries depending) on how hard it is is for the information to have the site with the half hour people counter data and next to that there would be a total number of passes issued by site in half hour bands.

Is that better?

Thanks Boys(and Girls)

please give data in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2010-02-03 : 04:22:52

Something like this perhaps?


SELECT DateAndTime,
       Count(FootFall) AS FootFall,
       Count(Orders) AS OrderNos,
       Sum(ValueOfOrders) AS OrderValue
FROM ... Various Tables ...
GROUP BY ... Time-in-Hours or half-hours ...

Knives85
Starting Member

6 Posts

Posted - 2010-02-03 : 09:27:23

This is the best I could do in regarding to creating what you wanted to see, this does not work though just returns nothing at all its to do with the link between the two database the data is very different, I have created the site ID in the footfall database is the linking fields between the two databases

CREATE PROCEDURE
dbo.Proc1
AS

SELECT
Footfallcol1
, CAST(Footfallcol2 AS VARCHAR (10))
, Footfallcol3
, Footfallcol4
, Footfallcol5
, Footfallcol6
, Footfallcol7
, dbo.fn_TimeGroup(Footfallcol8)
, Footfallcol9
, Footfallcol10
, EPOSCol1

FROM
( SELECT
EPOSCol1
, CAST(CONVERT(varchar,EPOSCol2,103) AS DATETIME)
, dbo.fn_TimeGroup(EPOSCol3)
, COUNT(EPOSCol4)

FROM
(SELECT
res.EPOSCol1

FROM
EPOSTable1
LEFT OUTER JOIN
CRMTable1
ON
EPOSCol1 = CRMTable1 COLLATE Latin1_General_CI_AS

WHERE
EPOSCol5 NOT LIKE 1

GROUP BY
EPOSCol1) AS Qdef
, EPOSTable2
, EPOSTable3

WHERE
Qdef.EPOSCol1 = plu.EPOSCol1
AND plu.EPOSCol1 = head.EPOSCol6

GROUP BY
plu.EPOSCol7
, head.EPOSCol2
, head.EPOSCol3 ) AS qry

,FOOTFALLTable1
,FOOTFALLTable2
,FOOTFALLTable3

WHERE
qry.EPOSCol1 = FOOTFALLCol1
AND qry.EPOSCol1 = FOOTFALLCol2
AND FOOTFALLCol3 = FOOTFALLCol4
AND FOOTFALLCol5 = FOOTFALLCol6
AND FOOTFALLCol7 = FOOTFALLCol8

GO

Thanks Boys(and Girls)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:23:12

if you need more help, please provide sample data in the requested format

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources