Solved

RE : SQL Server query 2008

Posted on 2013-07-01
6
280 Views
Last Modified: 2013-07-09
Hi,

I am facing challenge in creating SQL query in optimized way.

Find the attached sample data in excel sheet. I would like the date as per Output 1 and Output 2 sheet.

Do let me know if any question regarding the same.

Data requirement is that i have the consumption data now i want to place the cancellation data against each consumption. Attached the transactional data in excel.

Facing to
0
Comment
Question by:vikalgupta
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Hi,

I think you have forgotten to attach the example data.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
and please post your existing SQL query (or include it in the spreadsheet)
0
 

Author Comment

by:vikalgupta
Comment Utility
find the attached sheet.
rpt.xlsx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Your not providing all the test data. You have not populated the shop master table. Although this does look like it will be a simple join.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
The following result is produced by either of the SQL options below. Please note the date format used is an arbitrary choice which you can amend.
MERCHANTID	VOUCHERNUMBER	CONSUMEDSHOPNAME	CONSUMEDSHOPCODE	CONSUMEDDATE	REQUESTJOBNO	CANCELLEDSHOPNAME	CANCELLEDSHOPCODE	CANCELLEDDATE
3		BV34145412	Karol Bagh		BR-GP001	2013-07-01 14:48:54	23		Green Park		BR-GP000	2013-07-01 14:49:48
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:49:24	78		Rohini			BR-GP002	2013-07-01 14:49:33
3		BV55625541	Green Park		BR-GP000	2013-07-01 14:48:32	12		Karol Bagh		BR-GP001	2013-07-01 14:49:16
3		BV61731976	Rohini			BR-GP002	2013-07-01 14:49:03	34		(null)			(null)		(null)
3		BV74939357	Green Park		BR-GP000	2013-07-01 12:57:33	ADMIN_EVOUCHER	Green Park		BR-GP000	2013-07-01 12:58:15

Open in new window

There does not appear to be a tidy method within the source transactions to relate a cancellation to a consumption and to overcome this I have used the row_number() function to produce sequence numbers to tie cancellation to consumption. This works in the sample provided but suggest you test this on a broader set of data.

These 2 SQL options are functionally the same, they just differ in technique (using CTEs or using nested subquery)
-- using CTEs
;WITH
consume AS (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
            ),
cancel  AS (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
            )
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM consume
LEFT JOIN cancel ON consume.VoucherNumber = cancel.VoucherNumber
                AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

-- using nested subqueries
SELECT
        consume.MERCHANTID
      , consume.VOUCHERNUMBER
      , consume.CONSUMEDSHOPNAME
      , consume.CONSUMEDSHOPCODE
      , convert(varchar, consume.CONSUMEDDATE ,120) AS CONSUMEDDATE
      , consume.REQUESTJOBNO
      , cancel.CANCELLEDSHOPNAME
      , cancel.CANCELLEDSHOPCODE
      , convert(varchar, cancel.CANCELLEDDATE ,120) AS CANCELLEDDATE
FROM (
            SELECT
                  MERCHANTID
                , VoucherNumber
                , ShopMaster.SHOPNAME AS ConsumedShopName
                , ShopMaster.SHOPCODE AS ConsumedShopCode
                , REQUESTDATE         AS ConsumedDate
                , REQUESTJOBNO
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CONSUME'
     ) AS consume
LEFT JOIN (
            SELECT
                  VoucherNumber
                , ShopMaster.SHOPNAME  AS CancelledShopName
                , ShopMaster.SHOPCODE  AS CancelledShopCode
                , REQUESTDATE          AS CancelledDate
                , row_number() over (partition BY VoucherNumber ORDER BY REQUESTDATE ASC) AS row_sequence
            FROM transtable
            INNER JOIN ShopMaster ON transtable.SHOPID = ShopMaster.SHOPID
            WHERE transtable.REQUESTTYPE = 'CANCEL'
          ) AS cancel ON consume.VoucherNumber = cancel.VoucherNumber
                     AND consume.row_sequence = cancel.row_sequence
ORDER BY
        consume.VoucherNumber
      , CONSUMEDDATE DESC
;

Open in new window

also note you didn't specify the actual table names, and I removed spaces from any column labels in the spreadsheet.

these queries and the assumed shopmaster table may be seen in operation at:
http://sqlfiddle.com/#!3/84505/3
0
 

Author Closing Comment

by:vikalgupta
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now