Newbie Ist time query 1

[shibuyauk]

Hi, sory if this is a very basic question. I am not a programer/developer by trade, but have found a great need to use SQL reporting services. I simply want to extract all this data on just 1 day, what is wrong with my transact code?

Select *
FROM applyTracking
WHERE (hitDate <= '01-Apr-2005') AND (deal = 'true')

Its a long time since i did any of this, i dont understand why it wont work.
My hitdate table also uses seconds in the field, so when i just use '01-Apr-2005' it only finds records with a date stamp of '01-Apr-2005 00:00:00' no others
iS there a between comand i can put in there? If so what is the syntax

Also it doesnt like AND (deal = 'true') at all, is the syntax wrong there?

Thanks for any help for a N00b!

 

[jmr23]

You are dealing with typing in both instances.
What is the datatype of column "deal"
If it is a bit then you must use 1/0 not "true" or "false"
If it is an int, same story.
If it is a varchar column then 'true' should work fine.
As for the date comparison, here are some ideas:

DECLARE @date datetime
SELECT @date = getdate()
print convert(varchar, @date, 1)
print convert(varchar, @date, 2)
print convert(varchar, @date, 3)
print convert(varchar, @date, 4)
print convert(varchar, @date, 5)
print convert(varchar, @date, 6)
print convert(varchar, @date, 7)
print convert(varchar, @date, 8)
print convert(varchar, @date, 9)
print convert(varchar, @date, 10)
print convert(varchar, @date, 11)
print convert(varchar, @date, 12)
print convert(varchar, @date, 13)
print convert(varchar, @date, 14)
print convert(varchar, @date, 101)
print convert(varchar, @date, 102)
print convert(varchar, @date, 103)
print convert(varchar, @date, 104)
print convert(varchar, @date, 105)
print convert(varchar, @date, 106)
print convert(varchar, @date, 107)
print convert(varchar, @date, 108)
print convert(varchar, @date, 109)
print convert(varchar, @date, 110)
print convert(varchar, @date, 111)
print convert(varchar, @date, 112)
print convert(varchar, @date, 113)
print convert(varchar, @date, 114)

 

[Welshbird]

I would try doing something like this for the date part of your query:

Select *
FROM      applyTracking
WHERE     hitDate 
between   '2005-04-01' and '2005-04-02'
AND       deal like 'true%'

This should give you just things from the first of april 2005.

In terms of the 'deal' criteria I think you need to check the datatype for this column
Just in case you don't know the easiest way to do this is probably to type

sp_help applyTracking

and then it will list you all of the information about the table.

The other option is to do:

select top 10 * from applyTracking

and then you'll see what is in the field to check that your criteria is correct.

Let me know if this helps / works or if you need some more!


Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[shibuyauk]

Thanks willif and jmr23, using both your advice i got it down to
SELECT prodTypeID, prodID, productName, applyURL, cookieFrom, hitDate
FROM applyTracking
WHERE (hitDate BETWEEN '18-may-2005' AND '19-may-2005') and deal ='1'


Thanks again, now for the tricky part of grouping all the data into seperate totals. it should be fun finding out though

 

[Catadmin]

Grouping isn't so tricky. Check out the following in Books Online:

Rollup Operator
Cube Operator
Compute or Compute By

One of these should help you. BTW, With Cube can return results that you might not expect, so make sure to read what it does VERY CAREFULLY.

Hope this helps!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Welshbird]

It might also be useful to just use the 'group by' claseu.

Post me what you are trying to achieve and I'll help if I can!

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Catadmin]

BTW, you should be warned that use of the BETWEEN keyword sometimes doesn't find records on the date you're specifying. I don't know why, but SQL gets confused and omits records that should normally be found.

If you see this happening, try using >= and <= for your two dates. This should be more inclusive for you.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shibuyauk]

Sorry about the delay in replying, i have been away.

What i am trying to do is achive a report that groups the cookieFrom values together so instead of 600 different entries for xxxxx i instead get a total of how many XXXXX values there are for each individual value in cookieFrom.

Does that make sense?

 

[shibuyauk]

That last message was very confusing, aplogies all round!
Here is what i have to report on

We have 9 different afiliates who provide us with clicks, they are represented in the field "cookieFrom" also we have a feild "ipAddress" so we can (somehow) work out unique clicks.

What i need, is to generate a report that calculates how many unique clicks we have per affiliate per day.

So say NHB - 1890 clicks
MKJ - 1530 clicks


and so on

I am using the code

SELECT ipAddress, productName, cookieFrom, hitDate, deal
FROM applyTracking
WHERE (hitDate BETWEEN '18-may-2005' AND '19-may-2005') and deal ='1'

to get the data, but how to manipulate it?

I hope someone can help!
I think i am finally understanding the problem

Thanks again

 

[JamesLean]

SELECT cookiefrom, COUNT(DISTINCT ipaddress)
FROM applytracking
WHERE hitdate >= '20050518'
  AND hitdate < '20050519'
  AND deal = 1
GROUP BY cookiefrom

Note the change in the date filter - the way you had it, it would have included any clicks that were made on 19th May at midnight.

--James

 

[shibuyauk]

That is great JamesLean, i can see the logic of how this is done, which is really usefull for the future, if only i had a few weeks to sit down with transact SQL......
Thanks again

 

[shibuyauk]

I am furthus refing my search to just "apples" and "oranges" withing the prodTypeID field. My best guess doesnt work

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks
FROM applytracking
WHERE hitdate >= '20050518'
AND hitdate < '20050519'
AND deal = 1
AND prodTypeID = 'apples' OR 'oranges'

GROUP BY cookiefrom, prodTypeID


Thanks for any pointers

And i wish this to be a continuous monitoring of every 24 hours of activity, is it possible to sepcify 00:00 to 23:59 of each day, instead of changing the date every morning?

Thanks again!

 

[shibuyauk]

Sory , i hope people dont think i'm spamming the board, i just find it a lot more helpfull to discuss things rather that let is fester, also it may help other newbies to see how i progress.

This works

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks
FROM applytracking
WHERE hitdate >= '20050518'
AND hitdate < '20050519'
AND deal = 1
AND prodTypeID = 'Loans'
OR prodTypeID = 'Credit Cards'
OR prodTypeID = 'savings'
OR prodTypeID = 'Current Accounts'
GROUP BY cookiefrom, prodTypeID


But the question of 0:00 - 23:59 daily remains!

 

[JamesLean]

AND prodtypeid IN ('apples', 'oranges')

--James

 

[shibuyauk]

I am having a little trouble grouping the report by cookieFrom, at the moment they are grouped in a strange order

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks
FROM applytracking
WHERE hitdate >= '20050607'
  AND hitdate < '20050608'
  AND deal = 1
AND prodtypeid IN ('apples', 'oranges', 'bananas' , 'grapes')


GROUP BY cookiefrom, prodTypeID

Instead of

Vendor 1 - Oranges - 6
Vendor 1 - apples - 6
Vendor 1 - grapes - 6
Vendor 1 - bannas - 6
Vendor 2 - Oranges - 6
Vendor 2 - apples - 6

i get

Vendor 1 - Oranges - 6
Vendor 1 - apples - 6
Vendor 2 - Oranges - 6
Vendor 2 - Apples - 6
Vendor 3 - Oranges - 6
Vendor 3 - apples - 6


etc

anyway to GROUP BY cookiefrom primarilly, THEN by prodTypeID ?


Thanks

 

[shibuyauk]

ORDER BY cookiefrom!

 

[JamesLean]

Sorry, not sure of the problem? The results you say you are getting look fine to me. Can you elaborate on what results you want?

--James

 

[shibuyauk]

Thanks, but the SORT BY command put everything in the correct order

Thanks for your help

 

[shibuyauk]

Once again i get stuck, i am trying to add a field of the sum of totalclicks

my best guess is

SELECT cookiefrom, prodTypeID, COUNT(DISTINCT ipaddress) as indivclicks, COUNT(SUM indivclicks) as totalclicks

but no.

Thanks again for any pointers on this, i apreciate any help

SUM (Feilds!indivclicks) as totalclicks

isnt there either

 

[JamesLean]

COUNT(*) AS totalclicks

--James