Click here to Skip to main content
15,668,923 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900