count distinct date problem

See more:

MS-Excel

server

data

extract

Dear All,

I am using excel to get the data from the server. Everything works fine until here until I decided to run count(distinct) values for the date of sales. Some of the customer are member since 2004. I just want to check their visit per year. Please bear in mind the customers might have multiple entries to different shops in a day. Therefore I need to count(distinct) of those date. The dates stores as number in the server rather then date format. That is a question that I don't know:)When I run my code, it does not give what I exactly want. Can you please let me know where I am doing wrong. FYI my code works fine if I am using different field rather than date, such as for shops etc.here is the sql part of my code:

"SELECT  custmast.cust_id, setup_sales.shop_name, " & _
"{fn YEAR(inhouse.transaction_date-2)},(SELECT COUNT(DISTINCT ((inhouse.transaction_date-2))) FROM sales.dbo.inhouse inhouse, sales.dbo.custmast custmast " & _
"WHERE inhouse.card_id = custmast.card_id AND custmast.cust_id='" & custid & "' AND inhouse.transaction_date>='" & qdate & "' AND inhouse.transaction_date<='" & qdate2 & "' )" & _
"FROM sales.dbo.custmast custmast , sales.dbo.transactions transactions , sales.dbo.inhouse inhouse , sales.dbo.setup_sales setup_sales  " & _
"WHERE custmast.cust_id='" & custid & "' AND inhouse.card_id = custmast.card_id AND  inhouse.table_id = setup_sales.table_id AND " & _
"transactions.transaction_type = setup_sales.transaction_type AND inhouse.table_id = setup_sales.table_id AND " & _
"inhouse.transaction_date>='" & qdate & "'And inhouse.transaction_date<='" & qdate2 & "' ORDER BY setup_sales.shop_name", Conn, , , adCmdText